Adding SSISDB to AlwaysOn Availability group is not working

Question

Post reply

Adding SSISDB to AlwaysOn Availability group is not working

doodlingdba

Hall of Fame

Points: 3164
More actions
April 24, 2018 at 9:38 am

#330522

Hi there
I have an AlwaysOn Availability Group and am trying to add the SSISDB to it. I am following the instructions in this Microsoft doc: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017
But when I get to the part which says right-click on Integration Services Catalogs, Enable AlwaysOn Support... when the window comes up Connect to all new added secondary replicas, the window is blank - i cannot see my replica listed there. I have restarted the SQL service and still get the same result. And when I then go and open the AlwaysOn Dashboard I see warnings and SSISDB is not synchronising. The warning says Secondary database is not joined.
Any ideas??
Thanks
Doodles

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

I had similar issues when setting this up on SQL 2017. This may be of some help to you:

https://www.sqlservercentral.com/Forums/1924105/Always-on-and-SSISDB

MCITP SQL 2005, MCSA SQL 2012

doodlingdba Hall of Fame Points: 3164 More actions · Answer 2

Thanks... does that mean you need to go through two processes, first this one:

https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017#Step1

and this then this one:

https://blogs.msdn.microsoft.com/mattm/2012/03/23/ssis-catalog-backup-and-restore/

??

Why does the Microsoft doc not mention that you actually need to back and restore the database on the other side? Odd...

RTaylor2208 SSChampion Points: 13298 More actions · Answer 3

Yeah you do, I'm not sure why it doesn't cover it as it would have made sense to.

MCITP SQL 2005, MCSA SQL 2012

doodlingdba Hall of Fame Points: 3164 More actions · Answer 4

I'm confused. When you follow the first document link - to add the SSISDB to the AlwaysOn availability group, what does that look like? Can you see SSISDB listed in your availability group on the primary replica, but the database does not exist on the secondary replica, correct? And if you view the AlwaysOn dashboard, you should see warnings saying your database is not synching correct?

Now i've just followed the steps in the second document link, to restore SSISDB to the secondary replica manually. All the steps executed successfully, however how do you actually get it to synchronize in the AG? The database still isn't in a synchronizing state on the secondary replica? What am i missing here?? Feels like it shouldn't be so complicated! 🙁

mpsmith 63921 SSC Rookie Points: 47 More actions · Answer 5

doodlingdba - Thursday, April 26, 2018 4:49 AM
Thanks... does that mean you need to go through two processes, first this one:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017#Step1
and this then this one:
https://blogs.msdn.microsoft.com/mattm/2012/03/23/ssis-catalog-backup-and-restore/
??
Why does the Microsoft doc not mention that you actually need to back and restore the database on the other side? Odd...

Hey, how you were able to restore the encryption key to a database in a restoring state. If I add the database into the AG, but do not initialize it, then do my restores (bak & trn), the database is left in a restoring state and I'm unable to restore the encryption key. Is there a process I'm missing here?

TIA.

samcorneliusch SSC Rookie Points: 42 More actions · Answer 6

I think you need to add the encrypted data base without GUI, SSISDB database can be added because it has column level encryption not at the Db level for user created databases encryption is at DB level