SSIS Package to Centrally Monitor All SQL Servers

Attend these FREE SQL Server 2017 webcasts >> click to register

Problem

We usually have situations where we need to pull information from all or several Production or Development servers and share the data. It may be backup information, database size details, etc. Dealing with such situations and coming up with the easiest and quickest way can be difficult. I used to do it by connecting to Central Management Servers where I registered all of the servers in different folders like DEV, PROD, UAT, etc., but this is sometimes quite challenging and has issues as well.

What if we can have the ability to connect to one server at a time, check the status of the server, pull the required information, store in a centralized table and move to the next server. Sounds interesting, right? Check out this tip to learn more.

Solution

We can achieve this approach of connecting to a server, pulling some data, storing the data and then repeat for multiple servers using SQL Server Integration Services (SSIS) packages.  We can setup a process to read a list of servers and have the tasks repeat in the SSIS package for each server.

Before I explain how we can design the SSIS packages for this, first we need to register the SQL Servers in CMS, so that we can query this to pull the required list of servers from the SSIS package. To do this follow the steps given in this link: Registering Servers in SQL server Central Management servers .  The list of servers could come from any table that you create, but I thought this made more sense since I could also use CMS for other queries that I might want to run.

Register Servers in CMS

From the menu in SSMS, go to View > Register Servers and add the servers under Central Management Servers. Create folders for PROD, DEV, etc. based on your environment needs and register each server in the respective group. This is a time consuming process if you have a large number of servers, but it is a one time process and it's going to save a huge amount of time in the future.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Run a Test CMS Query

Once you are done with the above step, connect to SQL Server you are using for the CMS server and run the below query to pull a server list from the CMS server. This information is stored in the msdb database.

Note: server_group_id and parent_id will be different in your case.  You can query dbo.sysmanagement_shared_server_groups_internal to get a list of servers and values and modify the query as needed.

SELECT name
FROM dbo.sysmanagement_shared_registered_servers_internal
WHERE (server_group_id IN
   (SELECT server_group_id
    FROM dbo.sysmanagement_shared_server_groups_internal
    WHERE (server_group_id IN (9,10,11)) AND (parent_id = 6))) 
    AND (name NOT IN ('SQLDBAEXPERTS\SQL2008')
   )
GO

You will get the list of the servers, which will be used as input for the SSIS package server list.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Create SSIS Package

We are going to create a package that looks like this.  We retrieve the server names that we want to query, then for each server we will connect and pull some data and store the data.  If there is an issue connecting we will write that to a log file.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

To create the SSIS package, open Business Intelligence Development Studio (BIDS) (if you are using Windows 2008 then right click and run as an administrator to gain full access) or use SQL Server Data Tools (SSDT).  My example shows how to do this using BIDS, but SSDT is pretty similar.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Once you are connected to BIDS or SSDT, create a new SSIS project as shown below.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Once the project is created, add a new OLE DB Connection to connect to the CMS server as shown below.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Open the package and right click in the package and select variables. Then create the Variables as listed below.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Now add an Execute SQL Task from the toolbox to your package. You can either drag and drop or simply double click the task. It will be added to your package. Right click the task and click Edit to change the details as shown below.  Copy and paste the SQL code from above in the SQLStatement.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Go to Result Set and map variables as shown below and click OK.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Add a Foreach Loop Container from the toolbox and edit properties of the Foreach Loop Container as shown below and click OK.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Add a Script Task in the Foreach Loop Container. Right click the script task and update the properties of the script task as shown below.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Click on Edit Script... and update the code as shown below and put in the public void Main() section:

public void Main()
{

   SqlConnection conn= new SqlConnection("Data Source="+Dts.Variables["User::Srv_Conn"].Value.
   ToString() +";Initial Catalog=master;Integrated Security=SSPI;");

   //MessageBox.Show(Dts.Variables["User::Srv_Conn"].Value.ToString());

   try
   {
      conn.Open();
      conn.Close(); 

      Dts.Variables["User::CFlag"].Value = true;
   }
   catch (Exception ex)
   {
      Dts.Variables["User::CFlag"].Value = false;
   }
}

Click save and close the script task.

Add a Data Flow Task and an Execute SQL Task to the Foreach Loop Container as shown below. Also, connect the tasks as shown below.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Now click on the Precedence arrow between the Script Task and the Data Flow Task and update as shown below.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Then click on the Precedence arrow between the Script Task and the Execute SQL Task and update the properties as shown below.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Edit the Execute SQL Task and add the below SQL code or something similar to capture server information if we are not able to connect due to network connectivity issues or for whatever reason.  This is just an example, you can add more to this if you want.  You would also need to create a table on one of your servers where you want to log this data.

INSERT INTO ServerErrorlog VALUES (?,'Server not able to connect', getdate())
GO

Capturing Actual Data and Store Results

Now double click the Data Flow Task and add an OLE DB Source and an OLE DB Destination as shown below.  In these tasks you can enter the query that you want to run and the central destination of where to store the results.  In our case, we talked about collecting data from different source servers and storing the data in a centralized location.

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

Now your package is almost ready, now it's up to you to determine how you want use it and what data to collect.

Next Steps

  • In a future tip I will demonstrate how we can use above package for different situations where you can pull information from all your production servers for backup status of your databases.

Last Update:

SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers
SSIS Package to Centrally Monitor All SQL Servers

About the author

Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.
Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.
View all my tips

Related Resources