Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Chapter 15. Building SQL Server Inventory

In a complex enterprise environment, it can be a daunting task to track and manage hundreds or even thousands of SQL Server hosts and instances. Business units inside the company constantly ask for new instances to be built for new projects, or for migration of existing projects. SQL Server instances reside on different hosts with different operating systems and hardware. It is critical to have an inventory that includes every instance in the environment, and use it to keep up with changes on every instance. This inventory can also be used for auditing, capacity planning, and budget planning.

This chapter presents an inventory tracking solution tailored for SQL Server. The topics discussed include the following:

• SQL Server inventory

• Hosts

• Clusters

• ClusterNodes table

• Servers

• Databases

• Supplementary tables

SQL Server Inventory

The inventory is stored in a centralized SQL Server database. The database should be set up preferably on a failover cluster instance with at least two nodes. If the company has servers in different regions—say, North America (NA), Europe (EU), and Asia (AS), then each region should have a replicated copy of the centralized database to reduce network bandwidth for reads across continents, and increase redundancy. Peer-to-peer replication introduced in SQL Server 2005 is very suitable in this scenario.

Because we are just demonstrating how to set up an inventory, in our simplified example the inventory database resides on the default ...