If you have multiple SQL Server instances in your environment, it can be difficult to keep the details of each system straight. This is where a list, or inventory, of your SQL Server instances can come in handy. A SQL Server inventory stores information about the SQL Server instances in your environment. For example, you can search your SQL Server inventory to see which SQL Server 2005 instances are currently running SP3.
When it comes to creating a SQL Server inventory, you can either buy a commercial product or build your own solution. There are several benefits to building your own solution. For instance, you can add or remove features as needed. By using SQL Server to build your inventory, you acquire the most compatibility and you don’t incur extra cost, installation, or resource burden. In this article, I’ll introduce a home-grown method to build a SQL Server inventory that can be automatically updated. It requires a SQL Server system that acts as the inventory server, which we run a SQL Server Integration Services (SSIS) package from and publish a SQL Server Reporting Services (SSRS) report to. With SQL Server 2008 R2’s business intelligence (BI) features, we could build the entire solution without much difficulty.
The XML File that Lists SQL Server Instances
We built a SQL Server 2008 R2 CTP instance on a virtualized Windows Server 2003 box with dual-core processors and 4GB of memory for the inventory server. The SQL Server installation also includes SSIS and SSRS. The SQL Server Agent service runs under a domain Windows account that has sufficient privileges on all the target SQL Server instances.
In order to add SQL Server instances to the inventory, we need to know where they are. This solution was originally designed by leveraging SQL Server’s built-in SQLCMD command-line utility with the - L option so that it could list all the SQL Server instances broadcasting on the network without human input. However, it turns out we aren’t necessarily interested in every SQL Server instance found on our network. A majority of these servers are desktop SQL Server database engines and SQL Server Developer Edition instances. Without a good naming convention, they’re very hard to remove from the server list. In addition, it’s very difficult to update the inventory with environment and application information. In the end, we adopted an XML format for listing SQL Server instances that must be updated manually. It’s a one-time effort because in most environments, SQL Server system names don’t change very often. You only need to update the list when a new SQL Server instance is installed or an existing instance is removed or renamed. The SSIS package will scan this XML file every time it starts and run everything else in this package. A sample of the source XML file is in Listing 1, accompanied by an .xsd file in Web Listing 1. You can modify the ServerName, Environment, and AppName attributes to match your environment, and remove or add sections between the row tags.
Creating and Running the SSIS Package
Information is extracted from the target SQL Server systems during the extraction, transformation, and loading (ETL) process. We’ve built an SSIS package using SQL Server 2008 R2 (CTP) Business Intelligence Development Studio (BIDS), as shown in Figure 1. The .dtsx definition of the SSIS package is shown in Web Listing 2. You can import this package into BIDS to review and edit it. You can save it in a file directory and build a scheduled SQL Server job to point at its path. You can also import it into msdb and manage it from there.
Here’s how the package works. The SSIS package imports the XML file into SQL Server. It then maps the list of SQL Server instances to a memory variable and passes it to a Foreach Loop container, inside of which it’s mapped to dynamic SQL Server connections. The loop reaches out to each SQL Server instance to pull version information and stores the information in each SQL Server instance’s tempdb database. The data subsequently flows into the inventory server’s tempdb database and is updated in the final table.
Let’s walk through the package in more detail. First, create a new SSIS project in BIDS. Our naming standard requires prefixing tasks with the initials of the task name. For example, you would use EST for Execute SQL task and DFT for Data Flow task. Once you’ve created the SSIS package, you need to create a few connections: two OLE DB connection managers that point to the master database and tempdb on the inventory server, and the DynamicCONN connection manager, which serves as a placeholder for dynamic connections to target SQL Server instances in the context of the package. You’ll also need to create two package-level variables. The VarDynamicCONN variable is of the string data type and will be assigned dynamic values of target SQL Server connections. The VarSQLServerList variable is an object type that will be used to accept the ADO Recordset object, which contains records of all your SQL Server instances.