February 19, 2018 at 8:33 am
Hopefully someone more knowledgeable than me can help with this one.
I am building out a SQL 2017 Always on availability groups 2 node cluster, currently i have a single synchronous AG and the user databases reside in that AG. I can successfully fail over and fail back these databases without issue.
Now my requirement is to add SSISDB to the AG, I know there are some limitations in doing so regarding patching etc. but as its a POC this now that can be put aside.
The issue I have after following the MS blog on what to do is what actions are necessary on the secondary nodes, unless I am missing it I don't see any mention of what is required in the blog:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog#Step1
Having followed all the steps successfully if I try and fail over from the primary to secondary node I get a warning that data loss will occur as the SSISDB does not exist on the secondary node. Now in this case should I be taking a backup and restoring the database onto the secondary node prior to attempting the fail over? Or should I have setup this by creating the database on the secondary node?
to add one more complication into the mix this is being built on the AWS EC2 platform, but that really shouldn't cause any issues.
MCITP SQL 2005, MCSA SQL 2012
February 19, 2018 at 8:53 am
Turns out using the backup and restore method worked adding the database on the secondary using this method, it would be good though if someone could confirm this is the best way to do this though.
https://blogs.msdn.microsoft.com/mattm/2012/03/23/ssis-catalog-backup-and-restore/
MCITP SQL 2005, MCSA SQL 2012
February 19, 2018 at 9:45 pm
RTaylor2208 - Monday, February 19, 2018 8:53 AMTurns out using the backup and restore method worked adding the database on the secondary using this method, it would be good though if someone could confirm this is the best way to do this though.https://blogs.msdn.microsoft.com/mattm/2012/03/23/ssis-catalog-backup-and-restore/
Yep, It was right.
The most awaited / expected feature of SQL Server latest versions was the ability to add SSISDB to AG, before 2016 version the failover of the SSISDB database has to be done manually, on which you end up having more work to do.
In SQL Server 2016 it was introduced with the possibility for automatic failover of SSISDB. ( If you have chosen Always ON for SSISDB as well )
Please refer below, I think you can get a similar idea of what you've done.
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog
Apart from these, I'd like to hear from other experts and their views.
February 20, 2018 at 2:26 am
Thanks for the reply, yes I have been through the pain of doing this manually in prior versions, its still not perfect in 2016 / 2017 but a marked improvement.
MCITP SQL 2005, MCSA SQL 2012
February 28, 2018 at 4:25 am
RTaylor2208 - Monday, February 19, 2018 8:53 AMTurns out using the backup and restore method worked adding the database on the secondary using this method, it would be good though if someone could confirm this is the best way to do this though.https://blogs.msdn.microsoft.com/mattm/2012/03/23/ssis-catalog-backup-and-restore/
you need to create a credential for the ssidb master key so that it may be read and decrypted on fail over
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2018 at 10:22 am
Perry Whittle - Wednesday, February 28, 2018 4:25 AMRTaylor2208 - Monday, February 19, 2018 8:53 AMTurns out using the backup and restore method worked adding the database on the secondary using this method, it would be good though if someone could confirm this is the best way to do this though.https://blogs.msdn.microsoft.com/mattm/2012/03/23/ssis-catalog-backup-and-restore/
you need to create a credential for the ssidb master key so that it may be read and decrypted on fail over
This is somewhat similar to SSRS so that when two nodes share a single database they can still function over a NLB.
My question though: Are there any special considerations with regards to the IS Catalog? The database issue is pretty straightforward but it isn't clear to me whether or not I have to deploy projects and packages to both servers or whether a deployment to a single server will be enough.
I haven't read anything about this yet but this seemed like a good place to bring up a spontaneous question about it.... 🙂
March 6, 2018 at 4:44 am
once the ssisdb is in an AG the deployments are performed once
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 6, 2018 at 7:29 am
Hi Perry, thanks for the answer.
Sounds like an elegant solution......now to test!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply