Configure Integration Services on a Cluster

Hi

I am having an active/active cluster setup where there are 2 virtual SQL Servers one with default instance and other with named instance.

1) i need to know how to cluster integration service.

2) how are the integration packages are run, are they run by the integration
services service, or by the SQL Instance?

3) When fail over happens from one node to other will my packages continue to run?

i had setup Integration Services on a Cluster per the following article: http://msdn2.microsoft.com/en-us/library/ms345193.aspx.... but

when i do fail over or move the group to another node

... getting an error indicating:
An error occured attempting to read properties for the 'xxx IntegrationServices' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no] ...

I understand that integration service can be clustered only once.....

Should i do the clustering in Integration Services in a different Group
than sql server groups?....but then again i wont be able to acces MSDB...

Please provide advice on the matter,,,,,i am desparately waiting for a solution

Thanks in advance....

I

1 - That BOL topic is a good resource and pretty much covers it I think. Of course it doesn't cover you error which is perhaps more on an issue. Some other notes here as well- Cluster SSIS
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ClusterSSIS.html)

2 - Neither. SSIS (like DTS before it) is client side, it is just an object model really. Your packages are run by the process that calls them. So normally you use something like DTEXEC to call them, or the similar SQL Server Agent SISS job step subsystem. (Use CmdExec and DTEXEC even in jobs!). So if calling DTEXEC yourself it runs under you account on your machine. If using a scheduled job and DTEXEC/SSIS Subsystem it runs under the SQL Server Agent service (or proxy account is specified) so in that case it runs on the SQL Server machine that hosts the SQL Agent service. The SSIS service is a bit dumb and is just used to show what is running. Packages call into the service to say they are running, but the service is not the host.

3 - No. If packages are running during failover, then they will not continue to run, restart or do anything really because the SSIS service is not in control. However stopping the SSIS service will cancel a running package.

The error sounds has there is something duplicated, double check that you have followed the instructions you followed.

Which Group you choose is a matter of preference, as discussed in the documentation. The link above gives another reference you may wish to consider.

For my money, I don't see MSDB access as a big deal. I don't think using it as a package store is worth the effort, files are much easier to manage. Logging would be the only consideration, and that would sway me to keep them together. Coupled with the fact that I generally always use Agent to run/host the packages, it makes sense to keep them together for that as well.

|||

Hi

Thanks for the reply

My configuration has two virtual servers server1 and server2 in active /active mode

Server1 (default instance) is on cluster group1 and Server2 (named instance) is on cluster group2 as given below

Group1

Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)

Group2

Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2) SQL Server Agent (SQL2)
SQL Full Text (SQL2)

Both the servers have packages running.

I need to know how to do the clustering.

I tried clustering as per the Microsoft document : http://msdn2.microsoft.com/en-us/library/ms345193.aspx by Configuring Integration Services in the Same Group as SQL Server

then i had to configure it in the two groups (group 1 and Group 2)but cluster groups won't come online on one nodes when i do fail over... was getting an error An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]....

I need to know whether what i did is the correct method ... bcos the two servers were in differnt groups as above, i configured integration service on the two groups as per the document so i had integration service on group 1 as well as integration service on group 2

If i configure it only on a single group will it say group1 then will it be working fine (will group2 ie server2 have integration service running ? and when failover happens packages in both server continue to run ?)

If i go for the other option as per microsoft document that is in a different group than the SQL Server Database Engine

should i go for a third group say group 3 ?..pls tell me how to configure the group ?..wht all resources to be added ...?

Awaiting your reply

Thanks in advance

|||

So now things make sense!

You can only have 1 instance of the SSIS service running per machine. So by adding it to two virtual server groups, you risk trying to run two instances of SSIS on the same machine at the same time, hence the "object exists" or rather "stop mucking about with trying to run two copies of this service, I will not let you."

If running multi-instance cluster, also (previously) known as active/active, then just step back and think about what clustering SSIS will achieve? Since the SSIS service does not support multiple instances on the same machine, and the service is not a true cluster aware service that is installed within the virtual server container, (much the same thing really), then you should not try and cluster it as you will inevitably have clashes.

What does it give you anyway? Storing packages in MSDB or on a shared disk will mean they stay with that instance of SQL, which I'd say is the most important point. Run an instance of the SSIS service on each machine if you want, but don't bother with trying clustering them.

|||

Thanks a lot ............

one more query...

When i do installation of SQL server 2005 should i change the default location of integration services to the shared drive?

In my current I have my packages in C: drive which will not failover to the next node .

How do i go about to make this change...

awaiting your reply

Thanks in advance

|||

You can change the managed file store path after install, which I think would be the best method, and using a shared drive would make some sense.

http://msdn2.microsoft.com/en-us/library/ms137789.aspx

You can actually use any file path, it does not have to be the managed one. I never use it, and find no real need to enumerate packages in SSMS which is about all it gives you.

sqlsql