Wednesday, 20 March 2013

How to setup database mirroring session in MS SQL server.

Following is step by step guide for setting up database mirroring session. There are 2 major steps involved in setting up database mirroring setup.
  1. Prepare mirror database.
  2. Configure database mirroring session.
 
Now let's see both of these steps in detail.
 
Prepare mirror database.
  1. Take full backup of the primary database. You can use existing full backup file if you have available one. Before taking full backup make sure that database recovery model is set to full. To check recovery model Right click on database - Click on property - Select Options Tab.
  2. Restore full back from Step - 1. This restore must be done using WITH NORECOVERY option. And resored database name must be same as primary database.
  3. Restore differential backup (if any) taken after full backup used to restore in step 2. Also restore log backup (if any) taken after full backup or differential backup. All these restore must use WITH NORECOVERY option.
 
Now your secodary database is ready to start. Now lets see how to configure actual mirroring session.
         
 
Configure databae mirroring session.
 
  • Open database mirroring setup wizard in SSMS. Right click on database - Select Tasks - Click on Mirror... OR Right click on databaes - Click on Property - Select Mirroring Tab
  •  
     
  • Open Databaes Mirroring Security Wizard by clicking on Configure Secutity... button.
  •  
  • Click on Next. To make things simple we will skip witness setup option. So select No on Include Witness Server page and click next. Deselect witness server instance and click next.
 
     
  • This will open Primary server Instance wizard. Enter end point name and Listener port (leave default for this example) and click on Next.
 
     
  • Click on connect to Secodary Serever. Provide authentication details and connect to secodary server. Leave other details as default and click on next.
 
  • Now your session is almost ready. Click on finish. Which popup below window. You can start mirroring immidiately or start later form main mirroing tab. See below screen.
 
 
 
You can also generate script before clicking on Ok on main tab. Scripit is use full for documentaion and future reference. So it is always advisable to generate script and save somewhere safe, probably in source control.
 

T-SQL reference for Database Mirroring

  • How to manually failover to partner server?
There are 2 ways to manually failover to partner server in SQL server database mirroring session.
First is from primary server server and second is from secondary server. See below script for partner failover.

--1. Manual Failover from Primary server
ALTER DATABASE <Database_Name> SET PARTNER FAILOVER;

--2. Manual Failover from Secondary server
ALTER DATABASE <Database_Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

Failover from primary server does not cuase any data loss but if you do it from secondary server then you are forcing secondary server to loss data. Data loss will only happend if primary server is not accesible while failover.
  • How to remove database mirroring session?
Use below script to remove database mirroring session. This script can be used on either primary server or on secondary server.

--Remove mirroring for database
ALTER DATABASE <Database_Name> SET PARTNER OFF;

  • How to stop and start databaes mirroring session?
Use below script to pause and resume the database mirroring session. You can SUSPEND mirroring session from either primary server or from secodary server. But It can only be RESUMEd from primary server.
 
--Pause/Suspend database mirroring session
ALTER DATABASE <Database_Name> SET PARTNER SUSPEND;
--Resume database mirroring session
ALTER DATABASE <Database_Name> SET PARTNER RESUME;
  • How to add/remove witness server to/from already running database mirroring session?
Use below script to add or remove witness server to/from already running database mirroring session

--Add witness server to database mirroring session
ALTER DATABASE <Database_Name> SET WITNESS <witness_server>;

--Remove witness server from database mirroring session
ALTER DATABASE <Database_Name> SET WITNESS OFF;

SET WITNESS is only allowed on primary server.
 

Wednesday, 13 March 2013

Convert rows into columns using PIVOT and columns into rows using UNPIVOT

You might have used pivot functionality in Microsoft Excel. Same you can do in MS SQL using  PIVOT and UNPIVOT statement. PIVOT is used to convert values in rows to use as column header and UNPIVOT does the reverse of PIVOT. See below example for better understanding.


--PIVOT examle

IF OBJECT_ID('tempdb..#sales') IS NOT NULL
      DROP TABLE #sales

CREATE TABLE #sales (ProductName VARCHAR(200), SaleDate DATE, SalesQty INT)
INSERT INTO #sales(ProductName, SaleDate, SalesQty)
      VALUES('Bike', '2013-01-04', 56)
      ,('Bike', '2013-02-25', 45)
      ,('Bike', '2013-01-26', 87)
      ,('Bike', '2013-03-01', 12)
      ,('Bike', '2013-05-04', 62)
      ,('Bike', '2013-05-04', 73)
      ,('Bike', '2013-04-27', 34)
      ,('Scooter', '2013-03-12', 13)
      ,('Scooter', '2013-04-05', 75)
      ,('Scooter', '2013-01-09', 24)
      ,('Scooter', '2013-01-16', 76)
      ,('Scooter', '2013-01-16', 23)
      ,('Scooter', '2013-05-14', 36)
      ,('Scooter', '2013-01-16', 68)
      ,('Scooter', '2013-02-16', 88)

SELECT ProductName, Jan, Feb, Mar, Apr, May
FROM
      (SELECT ProductName, LEFT(DATENAME (month, SaleDate), 3) [monthName], ISNULL(SalesQty, 0) AS SalesQty  FROM #sales) AS A
PIVOT
      (
      SUM(SalesQty) --Aggregate function
      FOR [monthName] IN (Jan, Feb, Mar, Apr, May) --Column list
      ) AS B

IF OBJECT_ID('tempdb..#sales') IS NOT NULL
      DROP TABLE #sales

 PIVOT has two important section first is aggregate function (see comment in above query) and second is FOR section which contains list of column names and original column which contains the column names.

See below for UNPIVOT example. I have used ouput of PIVOT query as input of UNPIVOT query.


--UNPIVOT example
IF OBJECT_ID('tempdb..#sales') IS NOT NULL
      DROP TABLE #sales 

IF OBJECT_ID('tempdb..#sales_PIVOT') IS NOT NULL
      DROP TABLE #sales_PIVOT           

CREATE TABLE #sales (ProductName VARCHAR(200), SaleDate DATE, SalesQty INT)

INSERT INTO #sales(ProductName, SaleDate, SalesQty)
      VALUES('Bike', '2013-01-04', 56)
      ,('Bike', '2013-02-25', 45)
      ,('Bike', '2013-01-26', 87)
      ,('Bike', '2013-03-01', 12)
      ,('Bike', '2013-05-04', 62)
      ,('Bike', '2013-05-04', 73)
      ,('Bike', '2013-04-27', 34)
      ,('Scooter', '2013-03-12', 13)
      ,('Scooter', '2013-04-05', 75)
      ,('Scooter', '2013-01-09', 24)
      ,('Scooter', '2013-01-16', 76)
      ,('Scooter', '2013-01-16', 23)
      ,('Scooter', '2013-05-14', 36)
      ,('Scooter', '2013-01-16', 68)
      ,('Scooter', '2013-02-16', 88)

SELECT ProductName, Jan, Feb, Mar, Apr, May
      INTO #sales_PIVOT
FROM
      (SELECT ProductName, LEFT(DATENAME (month, SaleDate), 3) [monthName], ISNULL(SalesQty, 0) AS SalesQty  FROM #sales) AS A
PIVOT
      (
      SUM(SalesQty)
      FOR [monthName] IN (Jan, Feb, Mar, Apr, May)
      ) AS B

SELECT ProductName, [MonthName], SalesQty
FROM
      (SELECT ProductName, Jan, Feb, Mar, Apr, May FROM #sales_PIVOT) AS A
UNPIVOT
      (
      SalesQty FOR [monthName] IN (Jan, Feb, Mar, Apr, May)
      ) AS B
 
IF OBJECT_ID('tempdb..#sales') IS NOT NULL
      DROP TABLE #sales

IF OBJECT_ID('tempdb..#sales_PIVOT') IS NOT NULL
      DROP TABLE #sales_PIVOT

One important point to remember here is UNPIVOT is reverse process of PIVOT, but only limited for conversion of columns to rows. It does not regenerate the original result. This is due to aggregate function used by PIVOT. In our example we have used SUM function and there is no reverse function available for SUM to generate original value. There are some senarios where we can generate the original output using UNPIVOT. Please post if you have such example.    

Friday, 8 March 2013

Script to get the dababase backup history.

Often I have to get the backup history for reporting purpose. So I have writtent small script which give all details of database back history which is required for my report. I have used backupset and backupmediafamily tables to get the database backup history. Both of these tables resides in msdb database. So for some reason if you have restored msdb database. Then it will show history till the restored database time. Please see below script to get the database backup history.
 


SELECT
      A.database_name
      ,CASE A.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS backup_type
      ,B.physical_device_name
      ,CAST(CAST(A.backup_size / (1024*1024) AS INT) AS VARCHAR) + ' ' + 'MB' AS size
      ,CAST(DATEDIFF(S, A.backup_start_date, A.backup_finish_date) AS VARCHAR) + ' ' + 'Seconds' time_taken
      ,A.backup_start_date
FROM msdb.dbo.backupset A
      INNER JOIN msdb.dbo.backupmediafamily B ON A.media_set_id = B.media_set_id
ORDER BY A.database_name
      , backup_start_date DESC
 
This script gives history of all database, if you need details for only one database then restict result by using WHERE caluse on database_name field. 
 
For more information on backupset and backupmediafamily tables, see below links.
 

How to load data from PDF file in SSIS package using script component


I have come across the case where I have to read PDF document, parse it and write content to database using SSIS. PDF document is not usual source for any data so there is no component available to read PDF file from SSIS.  I have used iTextSharp library in Script Component to read the PDF file from SSIS package.  I also found very good link where I found good example on how to use iTextsharp library.
 
Using iTextsharp library it is very easy to process PDF document. In my case it was simple case of reading PDF document page by page and parses the content using custom logic. You can do much more than just reading PDF document using this library.

To read data from PDF file and write to database, I have used Script Component as data source, which reads the PDF document and return result and output record of Script Component. The simply used OLE DB destination to write it to database. You might be aware that any third party dlls can be used in SSIS Script component by referencing it. But to add it as reference and use it in script component you have to register dll in GAC. It can be referenced without adding it but dll might not be available on the production environment at the same location. So better add them to GAC. In my case I was not allowed to add dll to production server directly, so I used .Net reflection to load the dll dynamically and added dll location as configuration in SSIS package.

Below is my code


//Below two variable are used to pass ITextSharp dll location and PDF file path.
//Which ultimately supplied from dts config file.
String configITextPath = Variables.itextdllfilepath;
String configSourceDataFile = Variables.filepath;

//Load ITextSharp library           
Assembly assembly = Assembly.LoadFrom(configITextPath);

//Get PdfReader object and create its instance
Type readerType = assembly.GetType("iTextSharp.text.pdf.PdfReader");
object reader = Activator.CreateInstance(readerType, new object[] { configSourceDataFile });

Type extractorType = assembly.GetType("iTextSharp.text.pdf.parser.PdfTextExtractor");

Type extractionStrategyType = assembly.GetType("iTextSharp.text.pdf.parser.LocationTextExtractionStrategy");

object extractionStrategy = Activator.CreateInstance(extractionStrategyType);

//Get number of pages in PDF document
Int32 pages = (Int32)reader.GetType().GetProperty("NumberOfPages").GetValue(reader, null);

//Read PDF document page by page and process it and generate Output rows for script component.
String pageText;
List<PepRecord> pepRecords = new List<PepRecord>();
for (int page = configStartPageNumber; page <= pages; page++)
{
    reader = Activator.CreateInstance(readerType, new object[] { configSourceDataFile });
    extractionStrategy = Activator.CreateInstance(extractionStrategyType);
    pageText = (String)extractorType.GetMethods()[0].Invoke(reader, new Object[] { reader, page, extractionStrategy });

   //code to process PDF file content and create Output rows for script component   
}
 
I know better way to do this is to add dll in GAC and refer from there. But in my case I have to quickly found the solution to process few PDF document suing SSIS package, so I have used reflection to load it.

Thursday, 7 March 2013

How to use a Script Component as a Data Source in SSIS

Script component in SSIS can be used as Data Source, Data Transformation and Data Destination. Here is small tutorial on how to use Script Component as data source in SSIS package. See below step by step guide to add script component as data source in SSIS data flow task.

1.    Add Data flow task to Control Flow of your package.

2.    Right Click on Data Flow Task and click on Edit.

3.    Add Script Component to your Data Flow. It will popup window to ask to set the role of script task in data flow (see below image).


4.    Select Source click Ok.

5.    Now right click on Script Component and click on Edit.

6.    Click on Inputs and Oputputs. Add few columns to default output (Output 0). You can change name of the output but I kept it as default for this example. If you want script task to return multiple data tables, then you can add more outputs and their columns.

7.    Now Add few columns to your Output. Here I have added 3 columns to Output 0 named Column, Column 1 and Column 2. You can change name and data type of the column from right side property window. I have kept the default name and default type (Integer) for this example.


8.    Click on Script tab and click on Edit Script… button to add the rows to your output.


9.  Edit Script will open new visual studio project and opens main.cs file (for C#, it will be main.cv for VB). This file contails ScriptMain class, which has 3 predefined methods as shown below.

public override void PreExecute()
This method is used to perform any prexeuction task like opening connection, initialization valiables etc.

public override void PostExecute()
This method is used to perform any posexecution task like closing connection, dispising objects etc. This method also be used to set ReadWrite variable defined in your script.
     
public override void CreateNewOutputRows()
      This method is used to actually returns the rows to your ourput. See below example where I have added 10 rows to our Output 0


      public override void PreExecute()
      {
            base.PreExecute();
            /*
            Add your code here for preprocessing or remove if not needed
            */
      }

      public override void PostExecute()
      {
            base.PostExecute();
            /*
             Add your code here for postprocessing or remove if not needed
            You can set read/write variables here, for example:
            Variables.MyIntVar = 100
            */
      }

      public override void CreateNewOutputRows()
      {
            /*
            Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
            For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
            */

            for (int i = 0; i < 10; i++)
            {
                  Output0Buffer.AddRow();
                  Output0Buffer.Column = (i + 1);
                  Output0Buffer.Column1 = (i + 1) * 10;
                  Output0Buffer.Column2 = (i + 1) * 100;
            }
      }

10.Add sample code as shown above and close visual studio project for script component. Click on Script Component Edit dialog window. Now your script task is ready to use as Data Flow Source. Start using it as normal source component.

How to do case sensitive comparision in SQL server

Case sensitive string comparison can be done using collation in WHERE statement or by specifying collation for column.

See below example which uses COLLATE keyword while comparing two string values in WHERE clause. This example uses SQL_Latin1_General_CP1_CS_AS. If your data is not in English, then choose appropriate collation.

CREATE TABLE testUserTable (Username VARCHAR(30), pwd VARCHAR(30))

INSERT INTO testUserTable(Username, pwd)
      VALUES('User 1', 'PassWord1'), ('User 2', 'PassWord2')

DECLARE @username VARCHAR(30)= 'User 1'
      ,@pwd VARCHAR(30)= 'password1'

--Query 1: This query will return result
SELECT *
FROM testUserTable
WHERE Username = @username
      AND pwd = @pwd

-- Query 2: This query will not return result
SELECT *
FROM testUserTable
WHERE Username = @username
      AND pwd = @pwd COLLATE SQL_Latin1_General_CP1_CS_AS

DROP TABLE testUserTable

First SELECT query will return result even if password is supplied in lower case. Second query will not return any record, which is actually what we wanted in this particular case.

Here in our example we have specified _CS_ (case sensitive) in our collation selection. This behaviour can be reverted to previous by setting it to _CI_ (case insensitive). This will give use the same result as we get in Query 1 because default collation setting in _CI_.

See below CREATE statement which uses collation while creating table.

CREATE TABLE testUserTable (Username VARCHAR(30), pwd VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS)

In this case we don’t have to specify collation in where clause. Both SELECT queries will give the same result.