Setting Up SSIS in SQL Server Management Studio

Before you can deploy a SSIS project you need to set up SSIS in SQL Server Management Studio. When installing SQL you will select to install SSIS, however, it does not setup the SSISSDB or configure any settings for SSIS.

Here are the steps for setting up SSIS in SQL Server Management Studio.

Step 1:
Right-click on Integrations Services Catalogs and select Create Catalog.

Step 2:
Check the box next to Enable CLR Integration and set a password for your SSISDB and click Ok.

You will then see you SSISDB listed under Databases and under Integration Services Catalogs.

Now you will be able to deploy your SSIS project to your server where you have set up SSIS.

The next post will be on deploying an SSIS project.

Limit Memory Used By SQL

Ever noticed SQL using most of the Memory (RAM) on the server and all your other applications start slowing down? This could be because SQL is using the maximum amount of memory it can get a hold of.

When SQL is installed the default max amount of memory it is allowed to use is set to 2147483647 MB. You can check this by right-clicking on the server at the top in the Object Explorer, selecting Properties, then selecting memory.
To decide what to set the max memory of SQL to use you can use this simple rule; leave 4GB or 10% of total memory free, whichever is larger on your instance to start with, and adjust this as needed. The free memory will be consumed by the OS and other applications on the server.

To calculate this, remember 1GB is 1024MB. If your server has 32GB of RAM then you would take 28 x 1024 = 28672MB. You would then insert this into the max memory.
NB! This does not require restarting your SQL Server instance, but it will cause execution plans to recompile.

Registered Servers in SQL Server Management Studio

Many people hear about Registered Servers in SSMS and then completely forget about it and how useful it can be if they are in a large environment where they need to connect to many servers. Registered Servers allows you to save the server details in SSMS and give it a friendly name so when you need to connect to the server you can just double click on it.

Benefits of Registered Servers:
• Preserve the connection information.
• Determine if a registered server is running.
• Easily connect Object Explorer and Query Editor to a registered server.
• Edit or delete the registration information for a registered server.
• Create groups of servers.
• Provide user-friendly names for registered servers.
• Provide detailed descriptions
• Export registered server groups.
• Import registered server groups.
• View the SQL Server log files for online or offline instances of SQL Server.

The simplest way to register a server is to right click on the instance of the server you are connected to in your object explorer and select Register…

In the popup it will automatically put in the server that you are connected to with the method of connection, in my case Windows Authentication. At the bottom you can give the server a friendly name and description and click save.

To open up the Registered Servers window, you can select View on the top menu and Registered Servers.

Your Registered Servers window shall appear above your Object Explorer window. You can them simply double click on a server to connect to it in Object Explorer.
Registered Servers