SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Always on and SSISDB


Always on and SSISDB

Author
Message
RTaylor2208
RTaylor2208
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11926 Visits: 1562
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
RTaylor2208
RTaylor2208
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11926 Visits: 1562
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
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3664 Visits: 531
RTaylor2208 - Monday, February 19, 2018 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/

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.

RTaylor2208
RTaylor2208
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11926 Visits: 1562
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217036 Visits: 18594
RTaylor2208 - Monday, February 19, 2018 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/


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" ;-)
kevaburg
kevaburg
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16470 Visits: 1284
Perry Whittle - Wednesday, February 28, 2018 4:25 AM
RTaylor2208 - Monday, February 19, 2018 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/


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.... Smile
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217036 Visits: 18594
once the ssisdb is in an AG the deployments are performed once

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
kevaburg
kevaburg
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16470 Visits: 1284
Hi Perry, thanks for the answer.

Sounds like an elegant solution......now to test!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search