Wednesday, December 16, 2009

Sql Server Integration Service in clustering

In Sql Server 2005, you cannot cluster Sql Server Integration Service (SSIS).
During database cluster install, you can pick to install Integration Service. SSIS will only be installed on the node where installation process starts from. For example, suppose you have Node1 and Node2, and the install is started from Node1, then database service and binaries will be installed on both nodes automatically, but SSIS will only be installed on Node1.

Now, suppose the database cluster name is VirtualDb. When it is running on Node1, to connect to Integration Service in Sql Server Management Studio, you can choose to connect to VirtualDb, because under the cover, you are really connecting to Node1, which does have SSIS installed. However, if VirtualDb happens to run on Node2, then you have a problem, because there is no SSIS on Node2. Instead, you will have the message below:

Connect to SSIS Service on machine “VirtualDb” failed:Class not registered——————————BUTTONS:

So, SSIS wise, you can only connect to Node1 or VirtualDb when it is running on Node1, but we are not done yet. You will get an error when you try to browse Stored Packages -> MSDB in Object Explorer. Below is the error you will get:
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in Server 2005 Books Online.Login timeout expiredAn error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)

The reason is that the meta data of package location is controlled by a file located on Node1, and part of it is incorrect. If you browse through the path of Sql Server, look for
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. Open it up and you will see this piece of code:

Here, you can see that the ServerName is a dot, which means default instance of Sql Server on this local machine. To fix the problem you encountered earlier while browsing packages, you will need to change it to VirtualDb. After that, restart SSIS, and it will work for you in Management Studio. The dot here would have worked, had the database service been a non-clustered service. I do believe this should be treated as a Microsoft bug. Instead of just putting a dot here by the installer, it should put in the virtual database server name, when database service is installed as a cluster.

1 comment:

  1. oh, have you heard about the service of how to repair mdf files not detached from sql server? it is the fastest way to open not accessible files