We all know that SQL Server Integration services is not cluster aware. This means that we cannot set the SQL Server Integration Services to failover automatically. But still in many situations we are required to have SSIS installed on a cluster and make it work when the database engine failover or failback.
To achieve this, we have to install SSIS on all the nodes of the cluster.
Okay, I have a 2 node cluster and I have installed SSIS on both the nodes.
What next?
When we install the SSIS, the configuration file by default refers to "." which means local default instance. But in the clustered environment, we never install the database engine with the node name and hence when we try to open the Integration services it fails with the below error message.
To fix this error, we have to modify the SSIS configuration file "MsDtsSrvr.ini.xml" to refer to the correct SQL Server Instance Name.
This file will be usually located at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" and for SQL Server 2005 at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"
Before editing the file
After editing the file, restart the SQL Server Integration services to refer to the new SQL Server instance name
To achieve this, we have to install SSIS on all the nodes of the cluster.
Okay, I have a 2 node cluster and I have installed SSIS on both the nodes.
What next?
When we install the SSIS, the configuration file by default refers to "." which means local default instance. But in the clustered environment, we never install the database engine with the node name and hence when we try to open the Integration services it fails with the below error message.
This file will be usually located at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" and for SQL Server 2005 at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"
Before editing the file
After editing the file, restart the SQL Server Integration services to refer to the new SQL Server instance name
6 comments:
Thank you Sandesh! I'll be keeping this in my back pocket for sure.
Thanks!! it worked
Thanks for this. Worked great!
Make sure your project doesn't experience date labeling issues or data loss, worse, by entrusting the verification and optimization to a machine learning specialist. Today, finding and hiring such a specialist is as easy as shelling pears, and this can be done in the outsourcing format, which will significantly reduce the cost and simplify your task. Click on this machine learning software development firm and read more about outsource developers.
Hello to everyone on this blog! Would you like to get software that is designed to help businesses manage their customer interactions and streamline their sales and marketing processes? Then you should read about microsoft dynamics crm solutions. This is a great resource that contains a lot of useful information
Post a Comment