THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services. Keep up with Andy, join his mailing list!

SSIS 2016 Administration: Create the SSIS Catalog

Creating an instance of the SSIS 2016 Catalog is identical to creating an SSIS Catalog in SQL Server 2012 or SQL Server 2014. Follow these steps to create an instance of the SSIS Catalog on SQL Server 2016.

Open SQL Server Management Studio (SSMS) and connect to an instance of SQL Server. Once connected, open Object Explorer (if it is not already open) and right-click the Integration Services Catalogs node. Click Create Catalog…:

Cat2016_0_0

When the Create Catalog window displays (for the first time) it will appear as shown here:

Cat2016_0_1

Check the “Enable CLR Integration” checkbox to enable the other controls on the form.

I recommend you also check the “Enable automatic execution of Integration Services stored procedure at SQL Server startup” checkbox. This feature causes a stored procedure to execute whenever SQL Server starts. The stored procedure will identify any SSIS packages in a running (or other “active”) status and mark them as “Ended Unexpectedly.” You want this. Trust me. (As my friend Kevin Boles (LinkedIn | @thesqlguru) says, “Push the trust me button and let’s move on,” (paraphrased).

You cannot alter the name of the SSIS Catalog database. It is SSISDB. And, as in Highlander, there can be only one SSIS Catalog per instance of SQL Server.

You must supply a password for encryption used in the SSISDB database. The SSIS Catalog encrypts SSIS packages using a rough equivalent of what we used to call “Server Storage” package protection.

<throwback> Using Server Storage encryption was perhaps the most (perhaps the only) valid use case for deploying SSIS packages to the MSDB database, back in the day.</throwback>

The SSIS Catalog encrypts passwords and values marked Sensitive (such as SSIS Package and SSIS Project parameters).

SQL Server 2016 requires you supply a strong password for the SSISDB database. This is a default in SQL Server 2016. Even if I knew how to defeat it, I wouldn’t tell you. It’s 2016 people. Use strong passwords or risk being pwn’d by the bad guys!

Once configured, the Create Catalog window will appear as shown below. Click the OK button to create an SSIS Catalog:

Cat2016_0_2

What happens when you press the OK button? The SSISDB database is restored. Since I deployed SQL Server 2016 to the E: drive on my Windows Server 2016 Preview 5 virtual machine, the backup file is located at E:\Program Files\Microsoft SQL Server\130\DTS\Binn\SSISDBBackup.bak for me.

Cat2016_0_3

The SSISDB Catalog node will appear under the Integration Services Catalogs node in Object Explorer.

We’re not there yet, but you can find helpful utility applications for Enterprise Data Integration at DILMSuite.com (Data Integration Lifecycle Management Suite).

You will need to refresh the Databases nod in Object Explorer to see the SSISDB database:

Cat2016_0_4

What’s in the SSISDB database? Well, there are tables…

Cat2016_0_5

… views…

Cat2016_0_6

… and stored procedures…

Cat2016_0_7

… and functions and other database-y stuff. It’s just a database. That’s important to remember. It needs the same care and feeding as the other databases in your enterprise. It’s a neat piece of work, but it’s still just a database. It may not scale well for you in your enterprise. You may need to tune it. You definitely need to back it up. You need to store the encryption keys somewhere, preferably somewhere safe and not on the same physical server. Restoring SSISDB needs to be part of your disaster recovery (DR) planning and DR tests.

You are doing DR tests, aren’t you? Regularly?

I hope this post helps you get an SSIS Catalog up and running in SQL Server 2016. If you have any questions, please leave them in the (moderated) comments below or email me using the link in the upper right section of this page.

Enterprise Data & Analytics can help you put SSIS, data warehousing, business intelligence, and analytics to work for your enterprise. We offer:

  • training;
  • evaluation of your enterprise data integration, architecture, and best practices;
  • and consulting.

We are here to help. Contact us today!

:{>

Learn more:

Related Training:
IESSIS2: Immersion Event on Advanced SQL Server Integration Services

Related Articles:
Converting an SSIS 2014 Solution to SSIS 2016
Installing SQL Server 2016 Developer Edition, One Example
SQL Server 2016 Developer Edition is Free
Deploying a Single Package First in SSIS 2016
Stairway to Integration Services

Related Books
SSIS Design Patterns

Help!
Enterprise Data & Analytics – We are here to help.

Published Wednesday, June 15, 2016 10:48 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement