Tuesday, 25 June 2013

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.
 


 

4 comments:

  1. Cheap Moncler jackets , combining elegant style and cutting-edge technology, a variety of styles of Cheap moncler jackets men jackets , the pointer walks between your exclusive taste style.

    ReplyDelete
  2. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

    ReplyDelete