June 24, 2014 at 4:58 am
Hi All,
I am aware that Microsoft has said that SSIS isn't a cluster aware service. However, is there a way we can tweak few settings and cluster it ?
Current environment details:
Operating System - Windows Server 2008 R2
SQL Version - SQL Server 2008 R2
I have tried few things which I want to share with you and someone can shed more light on this...
I am trying to add SSIS as a service,
In the Failover Cluster Manager,
1. Right-click on Services and applications and click Configure a Service or Application...
2. Click on Next on the High Availability Wizard window.
3. Select Generic Service from the Select Service or Application list, then click Next
4. Select SQL Server Integration Services 10.0 from the list
*** I can use a name and select an IP, however just wanted to confirm if I am heading in the right direction
Any help is very much appreciated!
June 24, 2014 at 5:30 am
I think I have found something really helpful...
Will give it a try! 🙂
June 24, 2014 at 5:50 am
You can get round this if you use SQLAgent to run your SSIS packages.
I've clustered a normal instance of SQL Server + SQL Agent, placed all dtsx and config files on shared disk also.
Integration Services is installed and running on both nodes. SQL Server , SQL Agent, and Shared storage are clustered resources, so fail over nicely. 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
June 24, 2014 at 6:20 am
Nice 1 sqlmunkee...
I am setting this up for my client but definitely I would keep that in mind 🙂
June 30, 2014 at 3:44 am
If you can be certain that you will only ever have one SQL instance on your Windows cluster then it can be safe to cluster SSIS in the way referred to in the link.
If you have more than one SQL instance on the Windows cluster then you can run into problems if you cluster SSIS. This is because SSIS in the other SQL instances will have to use a file-based package store, and any changes to the package store (new/changed packages, etc) will only get registered on the node where SSIS is currently running. When you do a failover and your clustered SSIS is now running on a different node, the other SQL instances will no longer have a working SSIS.
Many people plan to use SSIS unclustered, and make sure the package store is kept synchronised using manual procedures. The use of manual procedures has a risk, but overall many people reckon this is the lowest risk option.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 30, 2014 at 3:47 am
EdVassie (6/30/2014)
If you can be certain that you will only ever have one SQL instance on your Windows cluster then it can be safe to cluster SSIS in the way referred to in the link.If you have more than one SQL instance on the Windows cluster then you can run into problems if you cluster SSIS. This is because SSIS in the other SQL instances will have to use a file-based package store, and any changes to the package store (new/changed packages, etc) will only get registered on the node where SSIS is currently running. When you do a failover and your clustered SSIS is now running on a different node, the other SQL instances will no longer have a working SSIS.
Many people plan to use SSIS unclustered, and make sure the package store is kept synchronised using manual procedures. The use of manual procedures has a risk, but overall many people reckon this is the lowest risk option.
How about SSIS 2012, where everything in the project deployment model is stored inside the SSISDB database? Can SSIS be clustered in that case?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 30, 2014 at 3:58 am
How about SSIS 2012, where everything in the project deployment model is stored inside the SSISDB database? Can SSIS be clustered in that case?
If you are certain that there will be only one SQL instance within your Windows cluster then clustering SSIS is safe. In all other situations clustering SSIS will cause you problems.
If you plan to run your Windows cluster in an Active/Active configuration, then clustering SSIS is not safe.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 30, 2014 at 4:04 am
EdVassie (6/30/2014)
How about SSIS 2012, where everything in the project deployment model is stored inside the SSISDB database? Can SSIS be clustered in that case?
If you are certain that there will be only one SQL instance within your Windows cluster then clustering SSIS is safe. In all other situations clustering SSIS will cause you problems.
Why? Everything is stored inside the SSISDB database, so I would think it would behave like any other database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 30, 2014 at 4:29 am
Yes, if you store your SSIS packages in SSISDB, they will be clustered if the instance that DB sits on is clustered. 🙂
The original question was about clustering the SSIS *service*. This is do-able, but not always necessary. It's quite easy to thing that SQLServer is a type of server, when really it's just a Windows service (albeit quite a special one).
Thinking in service terms, there's no reason to have the SSIS service on the same server as your package files. It's only a runtime environment, is it not ?
The SSIS service doesn't have the same availabilty criteria as SQL Server. It's not a special case and doesn't have the same dependancies as SQLServer IMHO.
So, are we really talking about SSIS package execution fault-tolerance and recoverability ? That's a whole other subject 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply