By: Atul Gaikwad | Updated: 2017-10-13 | Comments (8) | Related: > Monitoring
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.
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.
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.
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.
Once you are connected to BIDS or SSDT, create a new SSIS project as shown below.
Once the project is created, add a new OLE DB Connection to connect to the CMS server as shown below.
Open the package and right click in the package and select variables. Then create the Variables as listed below.
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.
Go to Result Set and map variables as shown below and click OK.
Add a Foreach Loop Container from the toolbox and edit properties of the Foreach Loop Container as shown below and click OK.
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.
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.
Now click on the Precedence arrow between the Script Task and the Data Flow Task and update as shown below.
Then click on the Precedence arrow between the Script Task and the Execute SQL Task and update the properties as shown below.
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.
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.
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-10-13
