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