Starting from SQL Server 2016, you can
add SSISDB as Availability Database in Always On Availability Group (AG). This enables
high availability and automated failover for SSISDB. This article provides step
by step guide to add SSISDB to Availability Group.
Related articles in Always On Availability Group (AG):
How to configure Read-Only routing on SQL Server 2016 Always On Availability Group (AG)?
Step 1: Create SSIDB on primary replica:
Step 3: Adding SSISDB to Availability Group:
Step 4: Select database and decryption password:
Step 5: Selecting Secondary Replicas:
Step 7: Validation:
Validate and fix the failed requirements and click on Next button.
Step 10: Enabling Always On Support:
On Enable Support For AlwaysOn wizard, click Connect All to connect to all secondary replicas and click OK button to close the wizard.
Step 12: Verifying Always On Support:
Step 13: Monitoring SSISDB Availability Database Health:
Make sure, you have installed SQL
Server Integration Services on both primary and secondary replicas.
Related articles in Always On Availability Group (AG):
How to configure Read-Only routing on SQL Server 2016 Always On Availability Group (AG)?
Step 1: Create SSIDB on primary replica:
Right-click on the Integration Services Catalogs and click
on Create Catalog Wizard option
 |
| Creating Integration Service (SSIS) Catalog |
On Catalog Creation Wizard select the Enable CLR integration and Enable
automatic execution of Integration Services stored procedure at SQL Server
startup. Then enter the password on Password
and Retype Password text boxes and
click OK button.
 | |
|
Step 3: Adding SSISDB to Availability Group:
On primary replica, add SSISDB to an
Availability Group (AG). Expand the Always
On High Availability and then Availability
Groups. Right-click on Availability
Group and click on Add Database
option.
 |
| Adding SSISDB to Availability Group (AG) |
Step 4: Select database and decryption password:
On Add Database to Availability Group wizard, click Next button. All the eligible databases
will be displayed here. Against SSISDB
you will see Password required. Enter
the password on Password column and
click on Refresh button.
 |
| Always On - SSISDB prerequisites |
Now you will see the Meets prerequisites on status column.
Click on the Next button.
 |
| SSISDB Selection for AG and decryption password |
Step 5: Selecting Secondary Replicas:
On Connect to Existing Secondary Replicas page, you can either connect
to selective secondary replicas by clicking on Connect button against each replica name or you can connect to all secondary
replicas by clicking on Connect All
button.
You will be prompted to enter username and password, now connected to secondary replica as mentioned user. Click on the Next button.
 |
| Choosing secondary replicas for SSIS Availability Database |
Step 6: Initial Data Synchronization:
On Select Initial Data Synchronization page, Select your data synchronization preference. Since, my SSISDB size is very small and my
cluster nodes are in same data centre, I am selecting Automatic seeding. You may use Full
database and log backup option or use other options as your requirement and
click Next.
 |
| SSISDB - Always On - Initial Data Synchronization |
Step 7: Validation:
Validate and fix the failed requirements and click on Next button.
 |
| Adding SSISDB to Availability Group (AG) - Validation |
Step 8: Summary:
Verify and confirm the choices made in
the adding SSISDB to Availability Group wizard and click on Finish button to continue.
 |
Adding SSISDB to Availability Group (AG) - Summary
|
Step 9: Results:
The Results page provide the status of
adding SSISDB to Availability Group. Click on Close to exit the wizard.
 | |
|
Step 10: Enabling Always On Support:
Right-click
on the Integration Service Catalogs
and click on Enable Always On Support
option.
 |
| Enabling Always On Support for Integration Service Catalog |
Step 11: Connecting to secondary
replicas:
On Enable Support For AlwaysOn wizard, click Connect All to connect to all secondary replicas and click OK button to close the wizard.
 |
| Enable Support for Always On - Connecting secondary replicas |
Step 12: Verifying Always On Support:
Post completing the previous step, you
can verify the Always On support by checking the SQL Server Agent jobs. The
following 2 jobs are created for Failover Monitor and Server Maintenance on
both primary and secondary replicas.
 |
| Verifying Always On Support for Integration Service Catalog |
Step 13: Monitoring SSISDB Availability Database Health:
Open the Availability Group Dashboard and check the status of the SSISDB and
Availability Group Database Synchronization
status.
 |
| Monitoring SSIDB Availability Database Health |
Great! You
have successfully added SSISDB as Availability Database on an Availability
Group (AG).
Note: When you are upgrading / patching, remove the SSISDB from Availability Group, apply the patches and add it back to Availability Group as mentioned here. SSISDB in Always On Availability Group rule checks the SSISDB upgradation or patching status. I hope this article helps you. Please share your comments below and if you have difficulty in following any of the above steps, write in comment section, I will get back to you as soon as possible.
Very detailed and well written article!
ReplyDeleteThank you, Deshpande
DeleteBy today, thousands of people from all over the world spend hours per day playing online games. Internet gaming once were very simple, consisting mostly of classic games like gambling internet and the many popular casino games men and women would normally play at a true casino. best ark survival server hosting
ReplyDeleteThis is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information... bestarkserverhosting.com
ReplyDeleteSuch a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. https://serverbrowse.com/
ReplyDeleteJust pure classic stuff from you here. I have never seen such a brilliantly written article in a long time. I am thankful to you that you produced this! https://serverbrowse.com/
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThanks for the nice article. Is this tested?
ReplyDeleteI have couple of questions or concerns...
Most of the packages are scheduled by jobs.
The jobs will be run on Primary. But if failover happened then how this mechanism control the schedule of jobs. Also if the failover happened in the middle of job execution, how the setup will fix that scenario. Awaiting your answers.
Hi Krishna,
DeleteGenerally, in AG we sync all agent jobs and schedules, however, at the time of execution checking, whether the server is in primary role or not, if primary it will go to next step and run further.
Check dbatools ag utilities for syncing objects on AG.
When fail-over happens, the server will wait for either transaction to complete or terminate. In case of terminating, the client has to restart the transaction, most of the times default driver settings will take care of this, you can explicitly handle this server unavailability.
I hope it helps, thank you.
First of all THANK you very much for your prompt response Rathish ji...
ReplyDelete"we sync all agent jobs and schedules" what does it mean... can you kindly shred some light? You mean Always On do that automatically or do we do it manually? If we need to do it manually then can you share your excellent knowledge and experience how to do that?
Can you please take a few minutes for this because our company want to implement this in HA for SSIS in a big project. really it helps your guidance for us sir.
Krishna,
ReplyDeleteAG does not sync automatically, we have to sync manually or schedule it in regular intervals or deploy changes in all the replica instances.
I regularly use dbatools availability group utilities to perform this task:
https://docs.dbatools.io/#Sync-DbaAvailabilityGroup
Now, jobs will be available in all the instances, to avoid multiple execution of same job, you have to verify whether is it try to execute on primary instance, if not stop execution, refer the below article to implement this checking:
https://stuart-moore.com/making-sql-agent-jobs-availability-group-aware-with-dbatools/
If you have further queries, please connect me through LinkedIn.
They are scalable, accommodating, understanding of ultimate goals, ux studio design team requires minimal oversight. It excels both technically and creatively, and is highly proactive, competent, and responsive to feedback.
ReplyDeleteThere are not a ton of databases that can contrast with the security that MySQL offers.Change Site URL Using MySQL
ReplyDeleteWhat a post and it is absolutely much helpful for everyone. This post is really neat and clean. I love your explanation way. I will often visit your blog for knowledge. Keep it up.
ReplyDeleteI really appreciate this wonderful post that you have provided for us. I assure this would be beneficial for most of the people. שרת וירטואלי
ReplyDeleteVery interesting blog. Alot of blogs I see these days don't really provide anything that I'm interested in, but I'm most definately interested in this one. Just thought that I would post and let you know. raklapos szállítmányozás Europa-Road Kft
ReplyDeleteHello,
ReplyDeleteEnable Always On Support for SSIS Not Working in SSMS. No server found to add on "Connecting to secondary replicas". Could you help me?
This comment has been removed by the author.
ReplyDeleteThat appears to be excellent however i am still not too sure that I like it. At any rate will look far more into it and decide personally! theplaynews
ReplyDeleteOmg I Finally Got Helped !! I'm so excited right now, I just have to share my testimony on this Forum.. The feeling of being loved takes away so much burden from our shoulders. I had all this but I made a big mistake when I cheated on my wife with another woman and my wife left me for over 4 months after she found out.. I was lonely, sad and devastated. Luckily I was directed to a very powerful spell caster Dr Emu who helped me cast a spell of reconciliation on our Relationship and he brought back my wife and now she loves me far more than ever.. I'm so happy with life now. Thank you so much Dr Emu, kindly Contact Dr Emu Today and get any kind of help you want.. Via Email emutemple@gmail.com or Call/WhatsApp +2347012841542
ReplyDeleteSome truly interesting info , well written and broadly user friendly.
ReplyDeletechocolate day messages
valentines day wishes
A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. dr-wall
ReplyDeleteHi, Firstly thanks for this article but i have one question. Can we use secondary role for ETL solution? I mean Can we use only secondary roles for SSIS because ı don't want to use primary role. Thanks.
ReplyDeleteThank you for excellent article.You made an article that is interesting.
ReplyDeletedata science training in noida
Aivivu chuyên vé máy bay, tham khảo
ReplyDeletemở lại đường bay việt nam - hàn quốc
vé máy bay đi sài gòn
vé máy bay từ tphcm ra hà nội
đặt vé hà nội nha trang
săn vé máy bay giá rẻ đi Mỹ