Recently I’ve been helping out on an SSIS consolidation project and was asked to help understand how to make the SSISDB highly available.
There were a few challenges on the platform we were working on such as no SAN replication, 2 separate WSFCs. Ideally we would want to put the SSISDB in an availability group, but the decision was made to use Log Shipping as they wanted 2 WSFCs for other reasons. It's also worth noting that mirroring across these 2 WSFC is also possible, but unless you have a requirement for near real-time synchronization between the 2 sites then it's not an ideal solution. Oh and remember mirroring is now depreciated from SQL 2016.
Log shipping itself is pretty simple to set up, but how simple is it to set up on the SSISDB?
The answer is “fairly simple”.
Simply setting up Log Shipping from the “primary” to the “secondary” isn’t quite enough though. There are a few steps that need to be done in order to get the SSISDB showing in the catalog, be able to run SSIS packages and get set up for log shipping.
Here is what this type of architecture might look like:
Here are some of the points to take into consideration:
- When initiating DR you will need to re-encrypt the Database Master Key with the Service Master Key before being able to run packages
- When initiating DR ensure any package configurations are configured to the right environment – for example if a package pumps data into the local instance ensure that the instance/database name is correct on the new primary
- Packages cannot be run on the DR site until the SSISDB has been “recovered” as the database needs to be writable, although changes made on the primary will reflect in the SSIS catalog on the secondary when the logs restore
- CLR needs to be enabled on the instance in order to use Project Deployment as CLR procedures are used to deploy to the instance
- It’s important to ensure you backup your Instance Service Master Key and SSISDB Database Master Key on all instances involved, keep them safe and probably most importantly, practice restoring them!
The process is pretty much what’s stated in the following process documented here with a few additional steps to prep for log shipping: https://technet.microsoft.com/en-us/library/hh213291(v=sql.110).aspx
Note: The following TSQL scripts are not supported and are for illustration only and careful consideration should be made before executing these in any environment. Always read the online documentation before undertaking this type of activity.
Steps to run on the PRIMARY:
-- step 1 backup ssisdb database master key
-- step 2 backup the ssisdb
-- step 3 script out the ##MS_SSISServerCleanupJobLogin## login
-- step 4 script out the dbo.sp_ssis_startup stored procedure which exists under master
-- Step 5 script out the "SSIS Server Maintenance Job" SQL Agent job (not pasting the code here as it’s pretty long)
Steps to run on SECONDARY:
-- step 6 enable clr
-- step 7 create ssis asymmetric key
-- note - the path to the executable file could be different depending on your installation
-- step 8 create login based on asymmetric key
-- step 9 restore SSISDB and ensure the database is recovered
-- step 10 execute the script from step 3 to create the ssisdb maintenance job login (ensure script is correct. below is taken from script in step 3)
-- step 11 execute the script from step 4 to create the ssis startup procedure (ensure script is correct. below is taken from script in step 4)
-- step 12 execute the script from step 5 to create the SSISDB maintenance job (ensure script is correct. below is taken from script in step 5) – (not posting the code here as it’s pretty long)
-- step 13 set the start up option for the sp_ssis_startup stored procedure
-- step 14 map the ssisdb cleanup job user to the cleanup job login
-- step 15 encrypt the SSISDB database master key with the new instance service master key
-- step 16 check that the new ssisdb is compatible with the integration services binaries
-- step 17 check that the Integration Service Catalog is now populated with the SSISDB and that packages run
-- step 18 restore the SSISDB again but with NORECOVERY
-- step 19 set up logshipping
- Backup, Restore, and Move the SSISDB Catalog - https://technet.microsoft.com/en-us/library/hh213291(v=sql.110).aspx
- SQL Server Encryption Hierarchy - https://msdn.microsoft.com/en-us/library/ms189586(v=sql.110).aspx
- Configure SQL Server Log Shipping - https://msdn.microsoft.com/en-us/library/ms190640(v=sql.110).aspx
- SSISDB Catalog SQL Server 2012 - https://msdn.microsoft.com/en-us/library/hh479588(v=sql.110).aspx
- SSISDB Catalog SQL Server 2016 (some great improvements around HA!) - https://msdn.microsoft.com/en-us/library/hh479588(v=sql.130).aspx
- SSIS with AlwaysOn - https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/