May 18, 2023 at 9:23 pm
I just got called in on an SSISDB / AG issue. We had a fail over event and suddenly a job that calls a package in SSISDB is not working because the package isn't decrypting properly. So we start digging into it and I found something interesting.
For kicks and giggles, I opened the AG and pretended I was adding a new DB. I scrolled down to SSISDB and it "Does not meeting the requirements" for being in the AG even though there's a check mark next to it. I look back at the AG, sure enough, SSISDB is in the AG group and green for the sync. I open the error message in the "Add Database" wizard and get the following messages:
The database already belongs to this availability group.
This database is encrypted by database master key, you need to provide valid password when adding it to the availability group.
Ummmm. WHAT?
Is it possible for SSISDB to get added to an AG without the password? I seem to have gotten the impression from someone else that this has happened, but I cannot find any documentation via Google-Fu that indicates that this is remotely possible. To add to the drama, someone tried restoring the master key from the original node 1 to node 2 but for some reason, the ##MS_DatabaseMasterKey## isn't showing up on node 2's sys.symmetric_keys table.
My thoughts are that we have to fail back to node 1, verify the job works and everything is okay, remove the database from the AG, possibly decrypt / re-encrypt it, figure out why the ##MS_DatabaseMasterKey## isn't showing up on node 2, then re-add the database to the AG properly. But I'm not 100% on this given the weirdness we are seeing.
Thoughts? Suggestions? Has this happened to anyone else?
May 19, 2023 at 7:59 am
2017 right?
Did you enable the SSISDB AOAG support after the DB was added?
Did the failover/monitor jobs do anything weird, did they fail etc?
If its 2014/2012 then you need to have a monitoring job which detects failover and sorts out the keys etc. My Google-Fu is failing me here at the moment but will edit when I find it.
May 19, 2023 at 2:30 pm
I'm not sure. I wasn't the person who did the original server move / install. I will go back and check, though. Thank you for this information.
A brief glance at the article indicates setting up catalogs, though, and I know the catalogs are already set up because I browsed through it yesterday and could see the projects within them and the packages underneath each project.
May 19, 2023 at 2:45 pm
Yeah sorry it was the "Enable Always On Support for Integration Service Catalog" part of the document I was referring to, do you see these two jobs on each replica?
SSIS Failover Monitor Job
SSIS Server Maintenance Job
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply