Building a SQL Server Inventory Report

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.

The package first runs the EST-Create local master table Execute SQL task. This task creates the SQLServerInventory table in the master database on the inventory server. Listing 2 provides the script that builds the SQLServerInventory table. The second task that the package runs is the DFT-Import XML list to local master table Data Flow task. It imports a list of SQL Server instance names from the XML file, converts data from Unicode to varchar through the DC-Convert Unicode Data Conversion transformation, then loads the data into the master table. This step populates only three columns in the table—ServerName, Environment, and AppName. The rest of the columns are left NULL. Note in the XML Source task, the .xml and .xsd files are stored at the C: root, as shown in Web Figure 1. A Data Conversion component converts between XML Unicode and varchar data types in the destination table.

Next, the package runs the EST-Create local tempdb staging table task that creates the SQLServerInventoryStaging table in the inventory SQL Server system’s tempdb database. The schema script to create this table can be found in Listing 3. Now, the package will run the DFT-List SQL Servers into memory Data Flow task. This task retrieves the SQL Server instance names with a SELECT statement as a Data Flow source, as the following code shows:

SELECT ServerName FROM \[master\].\[dbo\].\[SQLServerInventory\] ORDER BY ServerId ASC 

(Note that some lines of code in this article have been wrapped.) The Data Flow task populates an ADO in-memory recordset (the VarSQLServerList variable), as shown in Figure 2.

The core of this package is the FEL-Access each SQL instance Foreach Loop container. It works by reiterating through a collection (in this case, the ADO object source VarSQLServerList variable) and mapping the enumerator value (a SQL Server system name) in each loop to the user-defined VarDynamicCONN variable, as Web Figure 2 shows.

To make the dynamic SQL Server connection, the updated variable User::VarDynamicCONN is used as a Property Expression for the DynamicCONN connection manager, as shown in Figure 3, thus forcing the ServerName property in a connection manager to use the dynamic value in our target SQL Server instance list.

You can see that inside the loop, the EST-Build remote tempdb table Execute SQL task is able to connect to each target server. (For the EST-Build remote tempdb table Execute SQL task schema script, see Web Listing 3.) It basically builds a staging table in the target SQL Server instance’s tempdb and saves all information in it.

The next task is a Data Flow task that simply maps and transports data columns in target server’s tempdb table to the inventory server’s tempdb table. The following is the T-SQL query for the target SQL Server instance:

SELECT * FROM \[tempdb\].\[dbo\].  \[RemoteSQLServerInventoryStaging\]

The following is the code for the central inventory SQL Server system:

SELECT * FROM \[tempdb\].\[dbo\].  \[SQLServerInventoryStaging\]

While the Foreach Loop is executing, all target SQL Server system information is dumped into the central staging table (SQLServerInventoryStaging). By the time the loop ends, the staging table has a wealth of values for our SQL Server farm. You can modify the collection of values returned using the UPDATE statement in Listing 4. The last EST-Update base table task updates the SQLServerInventory table through an inner join from the staging data.

The SSIS package can be scheduled as a SQL Server job, as shown in Figure 4.

The job definition is in Web Listing 4. Be sure to update the .dtsx file path when you check the job script in your SQL Server instance. The script also assumes that SQL Server Database Mail has been set up properly and an operator called DBA can be used for job notification. In my environment, it runs at 12 AM daily, but the schedule and frequency can be customized.

Building the SSRS Report

You can write up a simple SELECT statement in SQL Server Management Studio (SSMS) to check the results of the SQL Server job in a traditional way. However, to make the SQL Server inventory list more presentable, secure, and easily accessible, I’ve built an SSRS report. An elaboration on how to build an SSRS report is beyond the scope of this article. (For more information about getting started with SSRS, see “SQL Server Reporting Services,” June 2007.) However, I’ll mention a few key points that might help you successfully implement this report. The Report Server is a SQL Server 2008 R2 instance that uses a Windows domain account to start up the SSRS service. When configuring the Web Server URL and the Report Manager URL, I chose TCP port number 8888 because the default port 80 has been used by the default SQL Server 2005 SSRS instance. I also chose to install the two default reporting temporary databases (ReportServer and ReportServerTempDB) on a local instance. To confirm the reporting site is up, you’ll use the Report Manager URL (DBAServer:8888/reports_SQL2008R2) and the Report Web Service URL (DBAServer:8888/reportserver_SQL2008R2).

We’re still working in BIDS 2008, but this time we’ll create a new Report Server project called DBA Reports. Then, we’ll need to create a shared data source called LocalDataSource, which points to our inventory SQL Server system via Windows authentication (integrated security). It’s mapped to the data source in Report Data. A data set is created afterward that has a dynamic SQL query with @ServerName as its parameter, as the following code shows:

EXEC ('SELECT * FROM \[master\].   \[dbo\].\[SQLServerInventory\] WITH (NOLOCK)   WHERE ServerName LIKE ''%'+ @ServerName + '%''    ORDER BY ServerName ASC')

This parameter lets you filter results based on the LIKE operator. The preview pane shows the report in its expected format and with cosmetic effects.

Deploying the Report

Now you’re ready to deploy the report. In the Solution Explorer in BIDS, make sure the project deployment configurations have the correct values—TargetDataSourceFolder (Data Source by default), TargetReportFolder (DBA Reports), and TargetServerURL (the Report Manager URL mentioned previously). The publishing process pushes the data source .rds file and report .rdl file to the report site. You can deploy the LocalDataSource.rds data source file, shown in Web Listing 5, and the SQL Server Inventory Report (legal paper).rdl report file, shown in Web Listing 6, using the Report Manager’s Upload File button. Alternatively, you can deploy these files from within BIDS. Once the report has been deployed, you should check it in the Report Manager using the Report Manager URL.

To view the inventory report, click DBA Reports and then SQL Server Inventory Report (legal paper). By default, the report retrieves all the records in the SQLServerInventory table. Note that the NULL check box, which is next to the SQL Server Name text box, is selected. If you clear the check box, you must fill in a string in the SQL Server Name text box that forces the report to display results that match the string pattern. For example, if you want to see a list of only the SQL Server instances with the number 7 in their names, simply type 7 in the SQL Server Name text box.

The report shows the SQL Server instance name, the last time the inventory was updated, the user name that runs this report, and the time when the report runs. Almost all the column headers in the report permit sorting. In the menu area, there are several standard buttons that let end users print, zoom in and out, search, browse pages, and save the report in one of the seven available formats (Word, Excel, comma-separated value—CSV, XML, HTML, PDF, TIFF). For printing convenience, the report has been tailored to fit an 8.5 × 14" legal-size paper. In the upper-right corner of the report, you can insert your company’s logo. The report user can also subscribe to the report via email or shared directory. To facilitate easy viewing, the report Env column is color-coded. The Server Name column also turns to violet when the server is clustered.

Now, you’ll want to check the configuration of the SQL Server Inventory Report in the Report Manager. On the Home site, make sure the Security option under Folder Settings includes the Windows groups or user accounts that have access to the site. You can add them to the SSRS Browser role via SSMS. Also, ensure the Security option under Site Settings includes the same accounts in the System User role. In the General Properties of the LocalDataSource data source, update the connection string to Data Source=SQLServerNameInstance; Initial Catalog=master. Make sure to connect to the data source using stored credentials from either a SQL Server standard login account or a Windows account. You don’t want to choose the Windows integrated security radio button unless Kerberos authentication is properly set up in the domain’s Active Directory. Finally, check to see if the rsreportserver.config file (located under C:Program FilesMicrosoft SQL ServerMSRS10_50.SQL2008R2Reporting ServicesReportServer in my installation) contains the correct value to ensure the Kerberos authentication is set up properly using the following code:

Reliable and Consistent Information

The solution described here is mostly dynamic. Important environment changes you can make once the inventory job has run include patch updates, adding RAM and CPUs, or changing the security authentication model on monitored SQL Server instances. In the future, I’d like to further improve this solution by automating the updating of the SQL Server instance XML list. In addition, you could add more information to the SQL Server inventory table, such as the default SQL Server installation path or the SQL Server Cumulative Update number, in the future. Nevertheless, this approach saves you time by providing reliable and consistent information about your SQL Server environment, enabling you to focus on critical database issues.

Listing 1: A Sample of the Source XML File

  DEVSQL001DevBiz    QASQL023QASSRS 2008 Test    PRODSQL101ProdInventory    StageSQL015StagingAccounting    PRODSQL015ProdAccouting    SandSQL416SandboxOrder Entry  

Listing 2: Script that Creates the SQLServerInventory Table

--  EST-Create local master table--  Script to build a raw base SQL inventory table:IF OBJECT_ID('master.dbo.SQLServerInventory', 'U') IS NOT NULL  DROP TABLe \[master\].dbo.SQLServerInventory;CREATE TABLE \[master\].dbo.SQLServerInventory (ServerId INT IDENTITY PRIMARY KEY clustered,ServerName VARCHAR(30) NULL,OSVersion VARCHAR(15) NULL,OSSP VARCHAR(5) NULL,Memory VARCHAR(10) NULL,CPU VARCHAR(50) NULL,NetBIOSName VARCHAR(20) NULL,MachineName VARCHAR(20) NULL,InstanceName VARCHAR(10) NULL,SQLEdition VARCHAR(50) NULL,SQLVersion VARCHAR(70) NULL,SQLSP VARCHAR(15) NULL,ResourceLastUpdateDateTime VARCHAR(30) NULL,BuildClrVersion VARCHAR(20) NULL,Collation VARCHAR(50) NULL,IsClustered bit NULL,IsIntegratedSecurityOnly bit NULL,Environment VARCHAR(10) NULL,AppName VARCHAR(50) NULL,ModifiedDate DATETIME NULL DEFAULT CURRENT_TIMESTAMP);

Listing 3: Script that Creates the SQLServerInventoryStaging Table

--  EST-Create local tempdb staging table--  Create Staging table in tempdbIF OBJECT_ID('tempdb.dbo.SQLServerInventoryStaging', 'U') IS NOT NULL  DROP TABLE tempdb.dbo.SQLServerInventoryStaging;CREATE TABLE tempdb.dbo.SQLServerInventoryStaging (    ServerName VARCHAR(30) NOT NULL PRIMARY KEY CLUSTERED,    OSVersion VARCHAR(15) NULL,    OSSP VARCHAR(5) NULL,    Memory VARCHAR(10) NULL,    CPU VARCHAR(50) NULL,    NetBIOSName VARCHAR(20) NULL,    MachineName VARCHAR(20) NULL,    InstanceName VARCHAR(10) NULL,    SQLEdition VARCHAR(50) NULL,    SQLVersion VARCHAR(70) NULL,    SQLSP VARCHAR(15) NULL,    ResourceLastUpdateDateTime VARCHAR(30) NULL,    BuildClrVersion VARCHAR(20) NULL,    Collation VARCHAR(50) NULL,    IsClustered BIT NULL,    IsIntegratedSecurityOnly BIT NULL    );

Listing 4: Script to Update the SQLServerInventory Table

--  Update master inventory table:UPDATE m   SET m.OSVersion = t.OSVersion,   m.OSSP = t.OSSP,   m.Memory = t.Memory,   m.CPU = t.CPU,   m.NetBIOSName = t.NetBIOSName,   m.MachineName = t.MachineName,   m.InstanceName = t.InstanceName,   m.SQLEdition = t.SQLEdition,   m.SQLVersion = t.SQLVersion,   m.SQLSP = t.SQLSP,   m.ResourceLastUpdateDateTime = t.ResourceLastUpdateDateTime,   m.BuildClrVersion = t.BuildClrVersion,   m.Collation = t.Collation,   m.IsClustered = t.IsClustered,   m.IsIntegratedSecurityOnly = t.IsIntegratedSecurityOnly,   m.ModifiedDate = CURRENT_TIMESTAMP  FROM \[master\].\[dbo\].\[SQLServerInventory\] m WITH (NOLOCK)  JOIN \[tempdb\].\[dbo\].\[SQLServerInventoryStaging\] t WITH (NOLOCK)ON m.ServerName = t.ServerName