Are you looking for a way to generate an auto inventory of SQL Server installations in your network? Look no further! In this article, we will discuss a step-by-step process to help you generate an inventory report or compare it with your own Excel tracking to identify any mismatches.
When starting this project, it’s important to explore the available tools and utilities that can assist in generating an auto inventory. Here are some tools that you can consider:
- SQL Ping by Chip Andrews
- Found Stone Sqlscan
- ListSqlServers – a VB Project
- SQLLHF v3.2 – written by MattW
- MSSQLScan v0.8 by patrik@cqure.net
- SQL.vbs by MAK
- OSQL -L (This lists only broadcasted servers, which means servers that are currently running)
- SQLCMD -L (Better than OSQL –L but still some servers are missing)
- SQL Scan exe (Part of SQLCritUpdPkg_ENU.exe from Microsoft)
- ADO.Net code for scanning SQL Servers
For this project, we will be using the following tools:
- BIDS 2005 – The Business Intelligence Development Studio
- SQL 2005 Standard Edition
- SQLPing3cl
Now, let’s dive into the steps to generate the inventory report:
Step 1: Creating the necessary tables
The first table we need to create is called “tblSQLVersion”. This table will hold SQL Server version numbers for reference. Here is the script to create this table:
USE [DBReporting]
GO
/****** Object: Table [dbo].[tblSQLVersion] Script Date: 07/31/2008 05:06:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblSQLVersion](
[Version] [int] NOT NULL,
[Serial] [char](10) NOT NULL,
[Descr] [varchar](25) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
The second table we need to create is called “SQLInv”. This table will hold the CSV data from the SQLPing3cl application. Here is the script to create this table:
USE [DBReporting]
GO
/****** Object: Table [dbo].[SQLInv] Script Date: 07/31/2008 05:03:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SQLInv](
[ServerIP] [char](15) NULL,
[TCPPort] [int] NULL,
[ServerName] [varchar](55) NULL,
[InstanceName] [varchar](55) NULL,
[BaseVersion] [char](20) NULL,
[SSNetlibVersion] [char](20) NULL,
[TrueVersion] [char](20) NULL,
[ServiceAccount] [varchar](55) NULL,
[IsClustered] [varchar](25) NULL,
[Details] [text] NULL,
[DetectionMethod] [varchar](55) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Step 2: Running SQLPing3cl and importing the data
Now that we have the necessary tables created, we can proceed with running SQLPing3cl to scan for SQL Server instances throughout the network. Here is the command to run SQLPing3cl:
SQLPing3cl.exe -scantype range -startIP 100.100.101.0 -EndIP 100.100.101.254 -Output 100.csv
This command will scan the specified IP range and output the discovered SQL Server instances to a CSV file named “100.csv”.
Once the scan is complete, we can import the data from the CSV file into the “SQLInv” table. There are different methods to accomplish this, but one option is to use the Import/Export wizard in SQL Server 2000. Simply follow the wizard’s steps to import the data into the table.
Step 3: Deploying the inventory report
Now that we have the data in the SQLInv table, we can deploy a report to the reporting server. For this, we will be using BIDS (Business Intelligence Development Studio). Here are the steps to deploy the report:
- Open BIDS from the Start Menu.
- Create a new Report Server Project.
- Add the existing report file (RDL) to the project.
- Modify the data source in the report to point to your server.
- Add the modified data source file to the project.
- Set the TargetServerURL in the project properties to the URL of your reporting server.
- Deploy the report.
Once the report is deployed, you can access it through a browser and view the complete list of SQL Servers in your environment. The report provides details such as instance name, version, service account, and TCP/IP port.
Advantages and Conclusion
Generating an auto inventory of SQL Server installations offers several advantages:
- A complete list of SQL Servers, whether they are stopped or running, can be generated. This method pings each IP and reads the instance name from SSNETLIB.DLL, providing a comprehensive list of instances.
- Details such as instance name, version, service account, and TCP/IP port can be included in the report. This makes it a complete inventory report that can be presented or reported to management.
- Reporting Services allows for exporting the report in various formats, such as PDF, making it suitable for presentations.
In conclusion, generating an auto inventory of SQL Server installations can save time and provide valuable information for managing your SQL Server environment. By following the steps outlined in this article, you can easily generate an inventory report or compare it with your own tracking to identify any mismatches. Happy inventorying!