Configure SSIS to SQL Server 2012 Cluster

SQL Server Integration services are not cluster aware by default, the following article shows a documented steps by Microsoft on how to configure SSIS to Cluster.

Install SSIS on all nodes of a cluster, If SSIS is installed after the SQL DB Cluster is built you need to select Stand-alone installation route(As shown below) as you cannot add SSIS to an existing SQL cluster during installation.

Open Failover Cluster Manager, connect to the cluster role to which SSIS need to be added as resource(usually SQL Server Role), Right Add Resource->Generic Service

 

Select SQL Server Integration Services and Click Next

Click Next

Click Finish, Now you should see SSIS added to as a resource of the Cluster Role.

Now Right Click on SQL Server Integration Services 11.0 Resource and select properties, In the Properties box go to Dependencies Tab and add SQL Server Resource and a disk resource where SSIS config file will be placed (We will look about config file in the next step)

 

Go to C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ copy MsDtsSrvr.ini.xml to the drive(Drive must be a part of SQL Server cluster role) and folder you have selected, I have chosen T:\SSIS.

 

Open this file in notepad modify <ServerName>.</ServerName> to <ServerName>SQLServer1\TEST2012</ServerName> with the SQL Server instance name in the cluster and <StorePath>..\Packages</StorePath> to some UNC path on the clustered drive if you store packages to file system and save the file. Sample file below

 

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>SQLServer1\TEST2012</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>T:\SSIS\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

 

Go to Registry and Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile to the UNC path and file name of the service configuration file on the select clustered disk. In my case T:\SSIS\MsDtsSrvr.ini.xml

 

 

Once the registry is updated SSIS has been successfully updated to cluster. Try starting SSIS resource in the Cluster and try failing over to other nodes on the cluster.

 

 

PowerShell to Script SQL Database Object and ReApply on a different Server

This week I had a requirement to move SQL 2012 database to a SQL server 2008 R2 server and the two servers reside in a different domain with no trust. Since SQL 2012 backups are not compatible with lower version servers the only way to move the databases was to script database with and apply the script on the destination, doing this manually took a lot of time so I decided to automate this process in PowerShell.

I took MSDN blog http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-database-objects.aspx as reference for scripting a database object using SMO in PowerShell and created my script the database using EnumScript() function instead of Script() function to accomplish my requirement. 

1.       ScriptDB.ps1 will script all the objects with data and will save it in a folder with ServerName->DBname. I have base path to save the script file is U:\, If you want to save It another location just change the following variable in the first line of the script $script:basepath = "U:\" to wherever you like or you can get the value as an argument.  The Script will create a folder in< ServerName>-><Dbname> and sub-folders Tables,Views,SP and Functions in the <dbname> Folder. 

2.       ReApply.ps1 will reapply the scripts saved in step one on the destination server. You have to pass the base folder path as Argument.

Script Database (ScriptDB.Ps1) -

ReApply Database (ReApply.Ps1) -

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Create Availability Group in SQL Server 2012 Step by Step

The following are the steps needs to create an Always on Availability Group.

The SQL Instance used for this exercise are SQL Server1 and SQLServer2.

I going to use 3 user databases (ContainedDB, ReportServer and AGTest) in my SQLServer1 for creating Availability group

1.       Take a Full Backup followed by T-Log backup of all the databases on the primary server (SQLServer1) which needs to be included to availability group.

BACKUP DATABASE [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_full.bak' WITH INIT,STATS = 1

GO
BACKUP DATABASE [ReportServer] TO DISK = 'T:\Backup\ReportServer_full.bak' WITH INIT,STATS = 1
GO
BACKUP DATABASE [AGTest] TO DISK = 'T:\Backup\AGTest_full.bak' WITH INIT,STATS = 1
GO

BACKUP LOG [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_trn.bak' WITH INIT,STATS = 1

GO
BACKUP LOG [ReportServer] TO DISK = 'T:\Backup\ReportServer_trn.bak' WITH INIT,STATS = 1
GO
BACKUP LOG [AGTest] TO DISK = 'T:\EREISQLUS01_TEST2012\Backup\AGTest_trn.bak' WITH INIT,STATS = 1
GO
 
2.       Restore the Full backup and Log backup taken in step1 in NORECOVERY mode on the secondary server(SQLServer2), You can ignore Step 1 and 2 and choose Availability group wizard to take the backup and restore but I find this way quicker and efficient.

RESTORE DATABASE [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_full.bak'

WITH MOVE 'ContainedDB' TO 'G:\Data\ContainedDB.mdf',
MOVE 'ContainedDB_log' TO 'P:\TLog\ContainedDB_log.ldf',NORECOVERY,STATS = 1
GO
RESTORE LOG [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_trn.bak' WITH NORECOVERY,STATS = 1
GO

RESTORE DATABASE [AGTest] FROM DISK = 'H:\Backup\AGTest_full.bak'

WITH MOVE 'AGTest' TO 'G:\Data\AGTest.mdf',
MOVE 'AGTest_log' TO 'P:\TLog\AGTest_log.ldf',NORECOVERY,STATS = 1
GO

RESTORE LOG [AGTest] FROM DISK = 'H:\Backup\AGTest_trn.bak' WITH NORECOVERY,STATS = 1

GO

RESTORE DATABASE [ReportServer] FROM DISK = 'H:\Backup\ReportServer_full.bak'

WITH MOVE 'ReportServer' TO 'G:\Data\ReportServer.mdf',
MOVE 'ReportServer_log' TO 'P:\TLog\ReportServer_log.ldf',NORECOVERY,STATS = 1
GO

RESTORE LOG [ReportServer] FROM DISK = 'H:\Backup\ReportServer_trn.bak' WITH NORECOVERY,STATS = 1

 GO

3.   Connect  to SSMS of SQLServer1 expand AlwaysOn High Availability-> Right Click on the folder ->select New Availability Group Wizard

4.  Give a name to the Availability Group you want to create and Click Next, 

5.  Select the databases which need to be a part of Availability group and click next

6. Click Add Replica and connect to the secondary server, repeat the step if you want to add more than one replica.

7. I always keep the secondary databases read-only, select appropriately in Readable secondary column.

8. Select the Backup preferences menu on the page and select the appropriate backup preference, I leave it as default(Prefer Secondary)

9. Listeners can be created after the AG is set up.

10. Since we have restored the databases in secondary earlier I select Join option.

11. Click Next

12. Click Next

Availability group has been created now; let’s go back to the server to check it,
 

As shown in the picture above Availability group AGTest can be seen in both Primary and secondary server with corresponding Replica Details.
 

Now let’s see how to create a listener for the Availability group, Go to AlwaysOn High Availability -> AGtest->Availability Group Listeners; Right Click on the folder and select Add Listener.

 

Now fill the port number and Listener group Name(Note:- This Listener Group name and Port number are the details which will be used to connect to SQL Server by the users)

 

I usually chose Static IP Network mode to avoid any firewall and network issues, you can use DHCP if you like.

 

Click OK

Now that the Listener AGTestInstance has been created let’s see how users need to connect to AGTest.

 

In SSMS connect to the <ListenerName,Port Number> as the server name and connect to the AG.

 

PowerShell Script to Assign Static TCP Port to SQL Server Instance

I am designing a new SQL server Farm with more than 100 Instances for one of my clients and it would have been huge effort if I had to assign static TCP to each and every instance manually, but Thanks to Allen Whites Blog on PowerShell Managed Computer Objects in SMO http://sqlblog.com/blogs/allen_white/default.aspx ,This namespace pretty much gives us ability to do all the tasks which are done through Configuration Manager using PowerShell. I have written a wrapper over Allen Whites core script to assign Static port to SQL instances.

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

TSQL to Attach a database with Rebuild New T-Log option

DBA’s often need to attach a database from SQL data file most of the cases provided by Vendor or during DR where T-Log does not have any significance, So we may well create a new log file instead of copying the log file over the network.

The TSQL used to attach a database with rebuild log option is given below,

CREATE DATABASE <dbname> ON (FILENAME = ‘<FilePath>) FOR ATTACH_REBUILD_LOG

Eg:-

CREATE DATABASE Testdb ON (Filename = ‘D:\Data\Testdb_data.mdf) FOR ATTACH_REBUILD_LOG