In an enterprise with several SQL Server instances it is pivotal that you keep track of each instance and its corresponding properties. Properties such as, name, version, service pack level, is it clustered, etc… I have implemented and read about several approaches that will assist in collecting this information. My first implementation involved creating a DTS package, yes DTS Package, in which I created a connection to every instance and imported the information into a centralized repository. Each time a new server was added I would create new connection and import the data from that server

When SSIS was introduced I converted my DTS package to an SSIS package. The process was exactly the same. The package contained a data flow that connected to every instance and similar to the DTS package the data was imported into a centralized repository. The following image depicts a data flow layout similar to my SSIS package:

As the environment grew, it became very cumbersome to add a new connection every time a new server was added. Therefore, I modified the package to utilize a dynamic connection. This removed the need to add a connection each time a new server was added. I created a table that contained a list of instance names. Using a Foreach Loop container, I iterated over the list and imported the data for each instance. Instead of adding a connection I simply inserted the instance name into the table.

This approach worked well until I decided I wanted to learn how to write a CLR table-valued function. I have to admit, this blog post started out as an article, but after trying to explain the details of the C# code I deferred to writing a short blog post. The function accepts one parameter, which is the SQL Server instance. The instance is used to create a dynamic connection, which can be seen below in the code snippet from the function:

Then, using the above-mentioned connection a query is run that will pull the required information from the server. The query uses the SERVERPROPERTY scalar function to retrieve the information. See the following code snippet:

If you are interested in viewing the code in its entirety, feel free to download load it. You can deploy the function directly from the project. Before you deploy, ensure that you have enabled the CLR and set the database to trustworthy. Use the following script to enable the settings:

Finally, before deploying right-click on the project and select Properties. When the Properties tab is activated click on Database and click the browse button next to the Connection String text box. Either select an existing database or create a connection to the database where you want to deploy the function. Then select External from the drop down list labeled Permission Level. See the following for an example:

Now that everything is configured deploy the function and begin using it. To test the function use the following script:

I created a stored procedure that uses a MERGE statement to ensure that the data remains consistent each time the query is run, which is included in the download file. Lastly, I scheduled a job that runs the stored procedure on a nightly basis to capture the information from each server. One thing to note is that the account running the SQL Server where the function is deployed must have permission on the SQL Servers where it is pulling information.

I am far from a C# coder so after downloading the sample project, if you find ways to improve my code please do so. All I ask is that you send me the updated code. If you have any questions or concerns regarding this document please feel free to email me at pleblanc@pragmaticworks.com.

Download: ServerInventory

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.