Partager via

Log Ship SSISDB in SQL Server 2012

  • Article

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:

Basic Architecture

 

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

 USE [SSISDB]
GO

BACKUP MASTER KEY TO FILE = 'c:\temp\ssisdb_dmk_20160524'
ENCRYPTION BY PASSWORD = 'sup3rstr0ngpa55word';
GO

 

-- step 2 backup the ssisdb

 USE [master]
GO

BACKUP DATABASE SSISDB
TO DISK = 'c:\temp\ssisdb_20160524.bak'
WITH COMPRESSION, STATS=10;
GO

 

-- step 3 script out the ##MS_SSISServerCleanupJobLogin## login

 USE [master]
GO

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'BqrNVuywI220GQwJ+CvHHfNgXJOk9JzeKuNLnY6YDPk=',
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

ALTER LOGIN [##MS_SSISServerCleanupJobLogin##] DISABLE;
GO

 

-- step 4 script out the dbo.sp_ssis_startup stored procedure which exists under master

 USE [master]
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[sp_ssis_startup]
AS
SET NOCOUNT ON
/* Currently, the IS Store name is ‘SSISDB’ */
     IF DB_ID('SSISDB') IS NULL
     RETURN

     IF NOT EXISTS(SELECT name FROM [SSISDB].sys.procedures WHERE name=N'startup'
     RETURN

/*Invoke the procedure in SSISDB */
     EXEC [SSISDB].[catalog].[startup]
GO
EXEC sp_procoption N'[dbo].[sp_ssis_startup]', 'startup', '1';
GO

 

-- 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

 USE [master]
GO
SP_CONFIGURE 'clr enabled', 1;
GO
RECONFIGURE;
GO

 

-- step 7 create ssis asymmetric key
-- note - the path to the executable file could be different depending on your installation

 IF NOT EXISTS (SELECT 1 FROM sys.asymmetric_keys WHERE name = 'MS_SQLEnableSystemAssemblyLoadingKey')
BEGIN
     CREATE ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
     FROM EXECUTABLE FILE = c:\program files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll';
END
GO

 

-- step 8 create login based on asymmetric key

 IF EXISTS (SELECT name FROM sys.syslogins WHERE name = '##MS_SQLEnableSystemAssemblyLoadingUser##')
BEGIN
     DROP LOGIN [##MS_SQLEnableSystemAssemblyLoadingUser##];

 CREATE LOGIN [##MS_SQLEnableSystemAssemblyLoadingUser##]
     FROM ASYMMETRIC KEY [MS_SQLEnableSystemAssemblyLoadingKey];

     GRANT UNSAFE ASSEMBLY TO [##MS_SQLEnableSystemAssemblyLoadingUser##];
END
ELSE
BEGIN
     CREATE LOGIN [##MS_SQLEnableSystemAssemblyLoadingUser##]
     FROM ASYMMETRIC KEY [MS_SQLEnableSystemAssemblyLoadingKey];

     GRANT UNSAFE ASSEMBLY TO [##MS_SQLEnableSystemAssemblyLoadingUser##];
END
GO

 

-- step 9 restore SSISDB and ensure the database is recovered

 USE [master]
RESTORE DATABASE [SSISDB]
FROM DISK = N'C:\temp\ssisdb.bak'
WITH FILE = 1
,MOVE N'data' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012_2\MSSQL\DATA\SSISDB.mdf'
,MOVE N'log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012_2\MSSQL\DATA\SSISDB.ldf'
,STATS = 10;
GO

 

-- 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)

 USE [master]
GO
CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'BqrNVuywI220GQwJ+CvHHfNgXJOk9JzeKuNLnY6YDPk=',
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

ALTER LOGIN [##MS_SSISServerCleanupJobLogin##] DISABLE;
GO

 

-- 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)

 USE [master]
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[sp_ssis_startup]
AS
SET NOCOUNT ON

     /* Currently, the IS Store name is ‘SSISDB’ */
     IF DB_ID('SSISDB') IS NULL
     RETURN

IF NOT EXISTS(SELECT name FROM [SSISDB].sys.procedures WHERE name=N'startup')
     RETURN

     /*Invoke the procedure in SSISDB */
     EXEC [SSISDB].[catalog].[startup]
GO
EXEC sp_procoption N'[dbo].[sp_ssis_startup]', 'startup', '1';
GO

 

-- 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

 USE [master]
GO
EXEC sp_procoption N'sp_ssis_startup','startup','on';

 

-- step 14 map the ssisdb cleanup job user to the cleanup job login

 USE [SSISDB]
GO
CREATE USER [##MS_SSISServerCleanupJobLogin##]
FOR LOGIN [##MS_SSISServerCleanupJobLogin##];
GO

 

-- step 15 encrypt the SSISDB database master key with the new instance service master key

 USE [SSISDB]
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'yoursuperstrongpassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

 

-- step 16 check that the new ssisdb is compatible with the integration services binaries

 exec catalog.check_schema_version @use32bitruntime = 0;
exec catalog.check_schema_version @use32bitruntime = 1;

 

-- 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

 USE [master]
RESTORE DATABASE [SSISDB]
FROM DISK = N'C:\temp\ssisdb.bak'
WITH FILE = 1
,MOVE N'data' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012_2\MSSQL\DATA\SSISDB.mdf'
,MOVE N'log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012_2\MSSQL\DATA\SSISDB.ldf'
,NORECOVERY
,REPLACE
,STATS = 10;
GO

 

-- step 19 set up logshipping

 

Learn More:

Comments

  • Anonymous
    May 10, 2017
    Great article... :)
  • Anonymous
    June 06, 2017
    Awesome article!