question

Buzz avatar image
Buzz asked

Inventory of all SQL Servers

Hi, I have just started my first job as a SQL DBA, I manage over 30 SQL servers in three different domains. I have setup a SQL server as a sql central management server and have added all the servers into the CMS. Can anyone advise the best method to inventory all my sql boxes via CMS?
sql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
I might run a multiple server query to collect all the server-level properties: SELECT ServerProperty('BuildClrVersion') AS BuildClrVersion ,ServerProperty('Collation') AS Collation ,ServerProperty('CollationID') AS CollationID ,ServerProperty('ComparisonStyle') AS ComparisonStyle ,ServerProperty('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS ,ServerProperty('Edition') AS Edition ,ServerProperty('EditionID') AS EditionID ,ServerProperty('EngineEdition') AS EngineEdition ,ServerProperty('InstanceName') AS InstanceName ,ServerProperty('IsClustered') AS IsClustered ,ServerProperty('IsFullTextInstalled') AS IsFullTextInstalled ,ServerProperty('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly ,ServerProperty('IsSingleUser') AS IsSingleUser ,ServerProperty('LCID') AS LCID ,ServerProperty('LicenseType') AS LicenseType ,ServerProperty('MachineName') AS MachineName ,ServerProperty('NumLicenses') AS NumLicenses ,ServerProperty('ProcessID') AS ProcessID ,ServerProperty('ProductVersion') AS ProductVersion ,ServerProperty('ProductLevel') AS ProductLevel ,ServerProperty('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime ,ServerProperty('ResourceVersion') AS ResourceVersion ,ServerProperty('ServerName') AS ServerName ,ServerProperty('SqlCharSet') AS SqlCharSet ,ServerProperty('SqlCharSetName') AS SqlCharSetName ,ServerProperty('SqlSortOrder') AS SqlSortOrder ,ServerProperty('SqlSortOrderName') AS SqlSortOrderName ,ServerProperty('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel ,ServerProperty('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel ,ServerProperty('FilestreamShareName') AS FilestreamShareName Rather than collecting it interactively through SSMS, you also might build and schedule an SSIS package that loops through the servers that are registered in your CMS (msdb.dbo.sysmanagement_shared_registered_servers_internal according to [Phil Factor][1]) then collects and stores the properties in a table you've put together for the purpose. [1]: https://www.simple-talk.com/sql/sql-tools/registered-servers-and--central-management-server-stores/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Buzz avatar image Buzz commented ·
Do you have a link or know of a how to guide on how to create this ssis package to do this inventory?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Here's something to get you started with an SSIS dynamic database connection within a foreach loop container: http://sql-bi-dev.blogspot.com/2010/07/dynamic-database-connection-using-ssis.html Something to keep in mind when inventorying multiple versions of SQL Server is that system objects and properties aren't always compatible. For example, `sp_blitz` doesn't support SQL Server 2000 and the property list in my suggested query is for SQL Server 2008 R2: you'll have to remove some of the properties for older versions. Check Books Online to see which ones.
0 Likes 0 ·
raadee avatar image
raadee answered
Have a look at MAP from Microsoft. [MAP][1] With it you can scan your network for SQL servers (you will find more than you think you have). Then you can generate awsome reports that contain everything you need regarding an overview for server and database inventory. It looks very professional and info is stored in a sql database. If you want to know whats going on in your instances, you can install sp_blitz from Brent Ozar via your CM solution. Execute the sp against you servers and you will get a LOT of info and help For your instances. Save the report in Excel, create a sortable table and start working. [Blitz][2] [1]: http://www.microsoft.com/sam/en/us/map.aspx [2]: http://www.brentozar.com/blitz/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Buzz avatar image Buzz commented ·
MAP was the first thing I tried, but when I run the report, it only picks up 3 SQL servers out of the 30 I manage. My windows account is in the administrators group as well, so I gave up. I will check out this sp_blitz from Brent Ozar, thanks.
0 Likes 0 ·
raadee avatar image raadee commented ·
MAP - First scan then report. If you pick up the servers with the scan, then I do not understand why the report does not come correct. Blitz - It's a goldmine, create an excel document per server. Name the sheet with the date you ran the proc. Paste the result in the sheet (incl header), create a table. Then just color code each row/section. Like: Red - Need to resolve. Orange - In progress Green - Done/Ok Add an extra column where you write info about what you did or status. Work with the issues sheet for a while, run the report again after a couple of weeks, new sheet and so on. Does not matter if you have't fixed all problems in sheet1, they follow you to sheet2 hehe.
0 Likes 0 ·
Tim avatar image
Tim answered
Sounds like you have all your servers loaded into CMS. Are you asking how best to organize them within CMS such as by domain, version, edition, etc or are you needing to run a query against all the servers to get an inventory list of server name, edition, number of cpu, ram, etc? If so getting that information within CMS will vary depending on the versions of SQL. If you are asking how best to organize the containers within CMS to group your servers then that really depends on your needs. For me I break mine out based on domain, then by version and edition. I have different folders for DEV/QA/PROD and then break them out by version such as 2000/2005/2008/2008R2/2012. I like to distingush between the versions due to code that only works on certain versions.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Buzz avatar image Buzz commented ·
I currently have them sorted out in three different folders: 2000,2005 & 2008. I just need to know a quick and easy method to inventory the servers via CMS.
0 Likes 0 ·
Tim avatar image Tim commented ·
The script from Ken above will pull a lot of information for you. If you could list out the data points of the inventory you would like to build then we can help better.
0 Likes 0 ·
DirkHondong avatar image
DirkHondong answered
If you already have listed all your servers in the CMS, maybe you can use (or adapt) the Repository solution Rodney Landrum describes in his "Tacklebox": [ https://www.simple-talk.com/sql/database-administration/sql-server-tacklebox-free-ebook/][1] Take a look at chapter 2. There's also a link in that chapter where you can download the whole solution. With the help of dynamic connections inside a SSIS package sveral different information about your servers & databases will be collected. [1]: https://www.simple-talk.com/sql/database-administration/sql-server-tacklebox-free-ebook/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

crazydba avatar image
crazydba answered
Hi There, I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports. Thnx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

crazydba avatar image
crazydba answered
Hi There, I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports. Thnx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.