November 17, 2010 at 2:38 am
Hello,
I am running a 2 node Active/Active SQL 2008 Ent R2 cluster, I am looking at putting SSIS on and I am aware that SSIS is not cluster aware, but have read that its possible to put 2 installs (one for each node, either on the nodes or another stand alone machine) and point the configuration files for each install to a different node, so in the event of a failover any SSIS packages would still run.
My question(s) is, how awkard is it to do this? It strikes me as a little impractical and not entirely reliable, I do not understand how either SSIS install would know that the other has not run (due to a failover) and run its packages.
Is there a better way? Could you point the SSIS packages to the cluster_network_name\cluster_instance_name of the cluster and still run successfully after a failover?
Presumably, anyone running SSIS may also run reporting services, I wonder how SSRS would fit into such a situation.
Thanks in advance for reading my post. Anyway advice or opinions are welcome.
Regards,
D.
November 17, 2010 at 7:54 am
Why can you install SSIS on both nodes and store all your SSIS packages on a shared disk ideally a SAN, which both servers can access?
November 17, 2010 at 8:13 am
Hello, Thanks for taking the time to get back.
Thats certainly an idea, its something that has not been mentioned or suggested (or even hinted at)on any of the Microsoft sites. Is this something you have done before? Is this a common implementation that I've just missed the boat on?
Regards,
D
November 17, 2010 at 8:19 am
The solution proposed by aaa-322853 is the one in which I would recommend too. Please remember that anything that is stored or accessed to/from none shared storage will cause a failure. Also remember that when using file shares for any potential deployments, that you remember to create them also as a clustered resource.
November 17, 2010 at 8:30 am
I employ this with a current client and it works with no issues at all.
When the disks are presented to the active server there are no surprises.
As for configurations I opt for a XML file first to provide the connection string to the SQL Server (which is the 2nd config) in order to populate the remaining configs. For me this config file is on the C drive (same copy of the C drive of each server).
The reason for this is that for Dev and UAT servers you would logically assume would always have a C drive and not neccessarily a J drive for example. This makes promoting packages using the config from dev and UAT up to production straightforward.
November 17, 2010 at 8:54 am
What does this do to SQL licensing?
Assuming active/passive, the cluster typically only needs licensing for the active servers, but now you've got 2 possible machines that can actively run SSIS (independantly of each other).
Does this imply that you'd need licensing for the passive node? If so, I'd think (because of the other passive clustered components of SQL) that the passive would need to be the same edition as the active.
If you're running enterprise on the cluster but didn't need the full enterprise features from SSIS, you could install a lower edition of SSIS as a separate instance on the active, and also have SSIS as a separate instance on the passive. This still adds up the licensing costs - the clustered instance, the active SSIS instance, and the passive SSIS instance.
Also, with a cluster there is only one active node, in this setup how does SSIS determine cluster ownership, to keep both nodes from running the same thing at the same time?
November 17, 2010 at 9:32 am
As long as your SSIS instance is for disaster recovery, licensing is not an issue.
November 17, 2010 at 9:38 am
One small thing you need to do when running SSIS on a cluster.
On each node, go to the C:\Program Files\Microsoft SQL Server\100\DTS\Binn directory, and modify the MsDtsSrvr.ini.xml file.
On the line with <ServerName>.</ServerName>, replace the "." with the name of the virtual SQL instance.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 17, 2010 at 9:40 am
With most SQL components the passive node can't even run, thereby enforcing the clustering license model.
As I'm interpreting the installation, there is now a possible 2nd SSIS installation that COULD be running on the passive node, in parallel to to the active node.
So MS will simply trust the license-holder that they don't intend to run on both? I've been through software audits enough to be gunshy of looking like a violation COULD be possible.
I do like the idea of installing on both nodes, my questions are simply if how MS would interpret the configuration.
November 17, 2010 at 11:24 am
SSIS is not licensed separately, and it is in fact installed on all of the cluster nodes during the installation. If MS had a problem doing it this way, it wouldn't be done this way. On clusters, you only need to license the number of active node installations that you have.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 17, 2010 at 3:32 pm
we like to store our ssis packages in msdb and have SQL look after the security. To set this up so everything still works on a failover we edit the MsDtsSrvr.ini.xml file on each physical node and enter the virtual sql names into the file. That way the packages are accessible on either physical node. The process is described in the following doc.
Just another way to skin the cat.
Andrew
November 18, 2010 at 2:50 am
Thanks for the link old hand, good article.
Regards,
D.
January 20, 2018 at 7:22 pm
Hi,
I'm having 2 node Active Active SQL 2012 Cluster, the Nodes are Node1 and Node2 and Instances are SQL1 and SQL2.
I've added Integration Services in Node1 and SQL1 , for this I've done 2 things:
1: changed the registry path from C:\ProgramFiles\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml to my Shareddrive\Packages\Config\MsDtsSrvr.ini.xml
2: Modified MsDtsSrvr.ini.xml file to use Instance for SQL1.
Now I want to add SQL2 on Node2 in cluster but I'm confused how to change the MsDtsSrvr.ini.xml file and registry file now so that I can have both Clustered instances running separate SSIS packages.
Any help would be great..
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply