Behind the scenes with Integration Services Catalogs Create Catalog

What happens you create the SSISDB catalog for the first time? I had no idea and this question had me wondering why in the world they were doing a restore. There's only one way to find out.

To profiler, we go! I ran a TSQL Trace against a 2014 instance (that had previously had an SSISDB catalog so this might be missing some conditional checks). Line breaks added for readability

First step, it creates a table variable and populates it with values from the SSISDB catalog, if the catalog exists.

exec sp_executesql N'
        --Preparing to access the Catalog object
        DECLARE @t_catalogs TABLE (
        Name sysname COLLATE SQL_Latin1_General_CP1_CI_AS,
        EncryptionAlgorithm nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        SchemaVersion int,
        SchemaBuild nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS,
        OperationLogRetentionTime int,
        MaxProjectVersions int,
        OperationCleanupEnabled bit,
        VersionCleanupEnabled bit,
        ServerLoggingLevel int,
        OperationLogNumberOfRecords int,
        VersionLogNumberOfRecords int)

        IF DB_ID(''SSISDB'') IS NOT NULL
        BEGIN
        INSERT INTO @t_catalogs VALUES(
        ''SSISDB'',
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''ENCRYPTION_ALGORITHM''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SCHEMA_VERSION''),
        (SELECT [property_value] FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SCHEMA_BUILD''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''RETENTION_WINDOW''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''MAX_PROJECT_VERSIONS''),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''OPERATION_CLEANUP_ENABLED''),
        (SELECT CAST([property_value] AS BIT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''VERSION_CLEANUP_ENABLED''),
        (SELECT CAST([property_value] AS INT) FROM [SSISDB].[catalog].[catalog_properties] WHERE [property_name]  = N''SERVER_LOGGING_LEVEL''),
        (SELECT COUNT(operation_id) FROM [SSISDB].[catalog].[operations]),
        (SELECT COUNT(object_id) FROM [SSISDB].[catalog].[object_versions])
        )
        END



SELECT
''IntegrationServices[@Name=''
+ quotename(CAST(SERVERPROPERTY(N''Servername'') AS sysname),'''''''')
+ '']''
+ ''/Catalog[@Name=''
+ ''''''''
+ REPLACE((SELECT Name from @t_catalogs), '''''''', '''''''''''') + '''''''' + '']'' AS [Urn],
(SELECT Name from @t_catalogs) AS [Name],
(SELECT EncryptionAlgorithm from @t_catalogs) AS [EncryptionAlgorithm],
(SELECT SchemaVersion from @t_catalogs) AS [SchemaVersion],
(SELECT SchemaBuild from @t_catalogs) AS [SchemaBuild],
(SELECT OperationLogRetentionTime from @t_catalogs) AS [OperationLogRetentionTime],
(SELECT MaxProjectVersions from @t_catalogs) AS [MaxProjectVersions],
(SELECT OperationCleanupEnabled from @t_catalogs) AS [OperationCleanupEnabled],
(SELECT VersionCleanupEnabled from @t_catalogs) AS [VersionCleanupEnabled],
(SELECT ServerLoggingLevel from @t_catalogs) AS [ServerLoggingLevel],
(SELECT OperationLogNumberOfRecords from @t_catalogs) AS [OperationLogNumberOfRecords],
(SELECT VersionLogNumberOfRecords from @t_catalogs) AS [VersionLogNumberOfRecords]
WHERE
(CAST(SERVERPROPERTY(N''Servername'') AS sysname)=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'RHUDAUR\DEV2014'

It's an interesting query result

Urn	Name	EncryptionAlgorithm	SchemaVersion	SchemaBuild	OperationLogRetentionTime	MaxProjectVersions	OperationCleanupEnabled	VersionCleanupEnabled	ServerLoggingLevel	OperationLogNumberOfRecords	VersionLogNumberOfRecords
IntegrationServices[@Name='RHUDAUR\DEV2014']/Catalog[@Name='SSISDB']	SSISDB	AES_256	3	12.0.2000.8	365	10	1	1	1	0	0

Next up, a quick check to make sure I'm in the admin role

SELECT ISNULL(IS_SRVROLEMEMBER ('sysadmin'), 0)

Does the database already exist? The parameterization of this plus the table variable used in the first looks like the code is designed for more than one "SSISDB" catalog. Why you'd want such a thing is an entirely seperate question.

exec sp_executesql N'SELECT name FROM msdb.sys.sysdatabases WHERE name = @dbname',N'@dbname nvarchar(6)',@dbname=N'SSISDB'

Random check for our version. I assume whatever is issuing the commands uses this.

select SUBSTRING (CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)),1,2);

Hit the registry to see where we have installed the SSIS (DTS) components.

declare @key_value nvarchar(1024);
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
,'SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS\Setup\DTSPath'
, N''
, @key_value output;
select @key_value;

Check to verify the .bak file exists. I presume this is built off the preceding query. Returns a 1 if it was found.

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

Now that we know where a backup is and that we're going to create a database called SSISDB, the code verifies the database doesn't already exist. If it does, it terminates the operation with an error The database, 'SSISDB', already exists. Rename or remove the existing database, and then run SQL Server Setup again.

IF DB_ID('SSISDB') IS NOT NULL
    RAISERROR(27135, 16, 1, 'SSISDB') 

Now we recheck the version except this time we force the failure of the script. I like seeing a reference to "Denali" in the error message.

IF CAST( SUBSTRING (CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)),1,2) AS INT ) < 11
    RAISERROR (27193,16,1, 'Denali or later') WITH NOWAIT

As part of the installation of the SSISDB, we must have CLR enabled. I already had mine enabled so I expect there's a step after this that I didn't capture.

SELECT [value_in_use] FROM sys.configurations WHERE [name] = 'clr enabled'

This step builds out the path where the data files should be located

DECLARE @path nvarchar(1024) = Convert(nvarchar(1024),ServerProperty('MasterFile'));
SELECT @path = SUBSTRING(@path, 1, CHARINDEX(N'master.mdf', LOWER(@path)) - 1);
SELECT @path;

We check to see if the SSISDB .mdf file exists

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.mdf'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

And we check to see if the SSISDB .ldf file exists

DECLARE @CatalogFileExists bit
BEGIN
    DECLARE @CatalogFile nvarchar(1024)
    SELECT @CatalogFile = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.ldf'
    CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)
    INSERT #t EXEC xp_fileexist @CatalogFile
    SELECT TOP 1 @CatalogFileExists=file_exists from #t
    DROP TABLE #t
END
SELECT @CatalogFileExists

This step generates the files contained within our bak. I assume this is used to generate the next command.

exec sp_executesql N'RESTORE FILELISTONLY FROM DISK = @backupfile'
,N'@backupfile nvarchar(67)'
,@backupfile=N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'

Now we're cooking with gas. Here we actually perform the restore of the SSISDB backup.

exec sp_executesql N'RESTORE DATABASE @databaseName
FROM DISK = @backupFile  WITH REPLACE
,MOVE @dataName TO @dataFilePath
,MOVE @logName TO @logFilePath'
,N'@databaseName nvarchar(6),@dataName nvarchar(4),@dataFilePath nvarchar(75),@logName nvarchar(3),@logFilePath nvarchar(75),@backupFile nvarchar(67)'
,@databaseName=N'SSISDB'
,@dataName=N'data'
,@dataFilePath=N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.mdf'
,@logName=N'log'
,@logFilePath=N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV2014\MSSQL\DATA\SSISDB.ldf'
,@backupFile=N'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak'

If for some reason the restore left the SSISDB in read-only mode, force it into read-write.

USE master;
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name]='SSISDB' AND [is_read_only] = 1)
ALTER DATABASE [SSISDB]
SET READ_WRITE WITH
ROLLBACK IMMEDIATE

At this point, we have an SSISDB but it's not secure. We have the ability to store sensitive data in there so we need to protect our jewels.

USE [SSISDB];

IF EXISTS (SELECT [name] FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##')
    DROP MASTER KEY

Secure our database with a master key

exec sp_executesql N'USE [SSISDB];
DECLARE @pwd nvarchar(4000) = REPLACE(@password, N'''''''', N'''''''''''');
EXEC(''CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''''' + @pwd + '''''''');'
,N'@password nvarchar(20)',@password=N'pass@word1'

Create an asymmetric key from our assembly

IF NOT EXISTS(SELECT * FROM sys.asymmetric_keys WHERE name = 'MS_SQLEnableSystemAssemblyLoadingKey')
CREATE ASYMMETRIC KEY
    MS_SQLEnableSystemAssemblyLoadingKey 
FROM
    EXECUTABLE FILE = 'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll' 

I have no idea what this virtual account is for and why we'll drop it if it exists but so be it. I assume we're doing this to ensure it has the correct permissions in the next step.

IF EXISTS(SELECT [name] FROM sys.server_principals where name = '##MS_SQLEnableSystemAssemblyLoadingUser##')
DROP LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser##

Create a login that can create unsafe assemblies. An unsafe assembly? Cats and dogs are signing leases at this very moment.

CREATE LOGIN ##MS_SQLEnableSystemAssemblyLoadingUser## FROM ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
GRANT UNSAFE ASSEMBLY TO ##MS_SQLEnableSystemAssemblyLoadingUser##

I allowed the SSIS CLR to run on startup so the installing is obliging me. Here it drops it in case it existed.

IF EXISTS(SELECT name FROM sys.procedures WHERE name=N'sp_ssis_startup')
BEGIN
   EXEC sp_procoption N'sp_ssis_startup','startup','off'
   DROP PROCEDURE [sp_ssis_startup]
END

Creation of the stored procedure dbo.sp_ssis_startup in master.

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]

I have no idea why they have IF here but they do.

IF (1=1)
BEGIN
 /* Run sp_ssis_startup when Sql Server restarts */
 EXEC sp_procoption N'sp_ssis_startup','startup','on'
END

At this point, we are going to get busy with setting up maintenance for the SSISDB. Drop any jobs named "SSIS Server Maintenance Job" and hope that you didn't have anything vitally important with the same name.

IF EXISTS (SELECT name FROM sysjobs WHERE name = N'SSIS Server Maintenance Job')
EXEC sp_delete_job
    @job_name = N'SSIS Server Maintenance Job' ;

Drop an existing virtual login that will be associated to our job.

IF EXISTS(SELECT * FROM sys.server_principals where name = '##MS_SSISServerCleanupJobLogin##')
    DROP LOGIN ##MS_SSISServerCleanupJobLogin##

Create our login

DECLARE @loginPassword nvarchar(256)
SELECT @loginPassword = REPLACE (CONVERT( nvarchar(256), CRYPT_GEN_RANDOM( 64 )), N'''', N'''''')
EXEC ('CREATE LOGIN ##MS_SSISServerCleanupJobLogin## WITH PASSWORD =''' +@loginPassword + ''', CHECK_POLICY = OFF')

Disable the login we just created...

ALTER LOGIN ##MS_SSISServerCleanupJobLogin## DISABLE

Create our job, owned by the disabled login above

EXEC dbo.sp_add_job
    @job_name = N'SSIS Server Maintenance Job',
    @enabled = 1,
    @owner_login_name = '##MS_SSISServerCleanupJobLogin##',
    @description = N'Runs every day. The job removes operation records from the database that are outside the retention window and maintains a maximum number of versions per project.'

Cleanup, aisle 1. The job step runs a stored procedure to do the cascading deletes

DECLARE @IS_server_name NVARCHAR(30)
SELECT @IS_server_name = CONVERT(NVARCHAR, SERVERPROPERTY('ServerName'))

EXEC sp_add_jobserver  @job_name = N'SSIS Server Maintenance Job',
                       @server_name = @IS_server_name

EXEC sp_add_jobstep
    @job_name = N'SSIS Server Maintenance Job',
    @step_name = N'SSIS Server Operation Records Maintenance',
    @subsystem = N'TSQL',
    @command = N'EXEC [internal].[cleanup_server_retention_window]',
    @database_name = N'SSISDB',
    @on_success_action = 3,
    @retry_attempts = 3,
    @retry_interval = 3;

Clean up the old versions of the .ispac files

EXEC sp_add_jobstep
    @job_name = N'SSIS Server Maintenance Job',
    @step_name = N'SSIS Server Max Version Per Project Maintenance',
    @subsystem = N'TSQL',
    @command = N'EXEC [internal].[cleanup_server_project_version]',
    @database_name = N'SSISDB',
    @retry_attempts = 3,
    @retry_interval = 3;

Create a schedule to run daily at midnight. Again, this might not be optimal for your ETL processing window (see link above).

EXEC sp_add_jobschedule
    @job_name = N'SSIS Server Maintenance Job',
    @name = 'SSISDB Scheduler',
    @enabled = 1,
    @freq_type = 4, /*daily*/
    @freq_interval = 1,/*every day*/
    @freq_subday_type = 0x1,
    @active_start_date = 20001231,
    @active_end_date = 99991231,
    @active_start_time = 0,
    @active_end_time = 120000

Finally, we need to ensure our user can run the two stored procedure in the job (makes sense). Drop that user, like it's hot...

USE SSISDB
IF EXISTS (SELECT name FROM sys.database_principals WHERE name = '##MS_SSISServerCleanupJobUser##')
    DROP USER ##MS_SSISServerCleanupJobUser##

Add the user back in, based on our disabled login.

CREATE USER ##MS_SSISServerCleanupJobUser## FOR LOGIN ##MS_SSISServerCleanupJobLogin##

Give them rights to run internal.cleanup_server_retention_window

GRANT EXECUTE ON [internal].[cleanup_server_retention_window] TO ##MS_SSISServerCleanupJobUser##

Give them rights to run internal.cleanup_server_project_version

GRANT EXECUTE ON [internal].[cleanup_server_project_version] TO ##MS_SSISServerCleanupJobUser##

References

• SSIS Catalog - Backup and Restore
• SSIS Catalog: Part 2 - How to Create Catalog?
• SQL Server 2012 Integration Service Catalogs Installation and Deployment