SSIS Package to Centrally Monitor All SQL Servers


By:   |   Updated: 2017-10-13   |   Comments (8)   |   Related: More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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.

cms registered 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.

query CMS data

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.

sample ssis package

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.

start BIDS

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

create new ssis package

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

setup ssis connections

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

setup ssis variables

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.

create ssis exeucte sql task

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

ssis execute sql task

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

ssis foreach loop
ssis foreach loop

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 script task editor

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 steps

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

ssis precedence editor

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

ssis precedence editor

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 data flow task

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 Updated: 2017-10-13


get scripts

next tip button



About the author
MSSQLTips author Atul Gaikwad 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





Comments For This Article




Thursday, August 22, 2019 - 7:17:00 AM - Ikgopoleng Back To Top (82120)

Hi Atul

On the Execute SQL Task that is within the Foreach Loop Container, what will be the source? I am getting error on that task

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

Friday, May 24, 2019 - 12:39:58 PM - Atul Gaikwad Back To Top (80196)

Anu: Did you try running the same query in SSMS before running uner script task. Also, Please remove GO from the statement and try running.

Let me know if still have issues.


Tuesday, May 21, 2019 - 5:10:23 PM - Anu Back To Top (80146)

  Hi Atul , I am getting this error , I am using ssdt 2015, and script that you gave , i have tweak it and its giving me correct output  , would you please help me out  ....

SELECT name

FROM msdb.dbo.sysmanagement_shared_registered_servers_internal

WHERE (server_group_id IN

   (SELECT server_group_id

    FROM msdb.dbo.sysmanagement_shared_server_groups_internal

    WHERE (server_group_id IN (6)) AND (parent_id = 1))) 

    AND (name NOT IN ('mycmsserver')

   )

GO

Error

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()


Friday, July 20, 2018 - 4:51:10 PM - Mylan Luu Back To Top (76689)

 Hi, 

Use VS 2012 shell, ran into error in script task as seen below. Any hint?

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

 


Thursday, October 26, 2017 - 9:38:50 AM - Jeff Moden Back To Top (68818)

This is pretty cool but... I have 285 servers to monitor and I don't necessarily know when a new one comes on board.  Is there a script to use the Domain Controller to find all the servers and discriminate as to whether or not they have SQL Server installed?


Saturday, October 21, 2017 - 9:29:53 AM - Atul Gaikwad Back To Top (68623)

Rick: I just put the code required to use under main void() function. Rest of codes will be added automatically in the script by default. Thanks for the feedback.

Jayendra: Thanks for the feedback. You can use the same package for any SQL server version which you have registered under CMS server, you can filter them based on your requirement. you need to identify server_group_id and node_id for respective server. Let me know if you need any more detail.


Monday, October 16, 2017 - 2:36:20 PM - Rick Back To Top (68443)

 

FWIW, Your VBScript is missing the "using System.Data.SQLClient" for SQLConnection.

 

 


Sunday, October 15, 2017 - 10:35:38 AM - Jayendra Back To Top (68394)

 

 Hi,

 

That was a great package which can ease to monitor many SQL Servers centrally. If I have to use the same package for other SQL DBA Experts as SQL other than 2008, All I have to modify the filter which was in the SQL statement. Will that help me to do the same for any SQL Server ?

AND (name NOT IN ('SQLDBAEXPERTS\SQL2008')

Thanks

Jayendra

 



download





Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools