Prashanth Jayaram

Inventory and document your SQL Server estate using PowerShell

January 14, 2019 by

Data growth has raised the stakes in the market increasing the size of SQL Server estates that DBAs are expected to manage. Because of this, fast and efficient configuration management can be very helpful. To this end, let’s delve into this article to generate a SQL Server inventory using PowerShell.

In the previous article, we discussed how to discover SQL instances automatically. In this article, we’ll build on that to see how to capture the SQL Server setting details of the database environment in CSV file. Once complete, we’ll have a script that will iterate through all of our instances and document their most important settings

Script structure

  • In the first part of our inventory, we’ll list the ServerName in the input CSV file. We’re going to import the server list using the Import-CSV cmdlet.
  • Next, we’re going to take a look and see what SQL Server properties are available. In this case, we’re going to pass in the name of the SQL Server.
  • Next, we’ll list the SQL Server instances from browsing the SQL Server folder using the Get-ChildItem cmdlet. The output has several properties such as ComputerNamePhysicalNetBIOS, Name, DisplayName and Instance and so on. In the properties list, one of those is ComputerNamePhysicalNetBIOS. It is then assigned to a variable named $instance.

Let’s take a look at the following PowerShell code.

The SQL Server folder is traversed for the server HQDBT01 using the ForEach-Object cmdlet.

In the output, you can see that the Server HQDBT01 is configured with two SQL Server instances. The first, default instance named HQDBT01 and the second, named instance, HQDBT01\SQL2017.

Note: The InstanceName column has a value SQL2017. This indicates that the SQL Server named instance is up and running.

In the following example, the SQL instances are discovered using the following PowerShell script. But you notice that the named instance CRM2011 has a NULL value for the instance field. This indicates that the named instance is down.



Note: The InstanceName column has a NULL value. This indicates that the SQL Server named instance is down and it is in not running state.

We all agree that there is chance to have multiple instances of SQL on the single machine. In this section, we’ll take a look at fetching the instance names from the aforementioned snippets. Let’s dissect the PowerShell script to understand better about selecting the SQL instance.

The following piece of code compares the DiplayName property with DEFAULT or MSSQLSERVER values. On satisfying the condition, the Name property value is assigned to a variable named $SQL. If the condition is not met, then the InstanceName property is validated for NOT NULL value and the corresponding value of the named instance is assigned to the $SQL variable. As I already mentioned in the above note, the NULL value in the InstanceName field indicates that the named instance is down. In such a case, we’ll not able to build or pull any SQL Server properties.

Note: The authentication is done at the scope of PowerShell session level with the security level of the currently running user access.

Note: You can also see the commented line of the above code to prepare the named SQLInstance

SMO (SQLServer Management Objects)

In this section, you’ll see the working of the SMO (SQL Server Management Objects) and the SQL Provider together. It is evident that DBAs can perform or find a lot of tasks using either one of them. So far, we see that using SQL provider how we derived the SQL instance details. Now it’s time to take a look at how we can use the SQL Management Object library.

SMO Library is a .NET class library that Microsoft designed for working with SQL Server. PowerShell at the very core, treats everything as an object. In this case, the server object has a corresponding entities associated with it. Each server properties can be accessed using object instantiation. For example, a database object has certain properties and methods that can be described by object invocation process. Through the server object, we can go through the different collections such “server information”, “Security information”, “Configuration”, “Server Settings” etc:-. And as we iterate over that collection we can return individual properties of the each of the collections.

In the following example, an array of server names is fed and the generated list of the SQL Instances is iterated through to pull the details of the various collections’ SQL server settings.

In the following example, we can see how to capture the server settings (Generate SQL Inventory) in a CSV file. For this requirement, PowerShell script requires an input file and directory to save the output file

Pre-requisites

Let us discuss in detail about the required parameters:

  1. User account must have the required permission to access the servers
  2. InputFileName – Text File contains a list of SQL instances. For example, C:\Server.txt. The file has list of servers

    For Example, the file serverlist.txt file should list all of the SQL instances like below:

    asdbsp15
    asdbsp17

  3. DirectoryToSave – this is storehouse path for the generated output file.

Let me dissect the internals of the PowerShell script further for better understanding.
I would like to divide this into five parts.

  • The first, the input and output file
  • The second part, listing the SQL instances by traversing the SQL Server folder using Get-Childitem and foreach-object cmdlets
  • The third, an instantiation of the SMO class library
  • The fourth, transforming and generating SQL Server inventory information
  • and the last, writing the data to CSV file

The following are the output columns that are listed as part of the process

  1. ServerName
  2. InstanceName
  3. Edition
  4. BuildClrVersionString
  5. BuildNumber
  6. ErrorLogPath
  7. HasNullSaPassword
  8. IsPolyBasedInstalled
  9. IsCaseSensitive
  10. Platform
  11. IsFullTextInstalled
  12. Language
  13. MasterDBLogPath
  14. MasterDBPath
  15. NetName
  16. OSVersion
  17. PhysicalMemory
  18. IsSingleUser
  19. Product
  20. VersionString
  21. Collation
  22. IsClustered
  23. ProductLevel
  24. ComputerNamePhysicalNetBIOS
  25. ResourceLastUpdateDateTime
  26. AuditLevel
  27. BackupDirectory
  28. DefaultFile
  29. DefaultLog
  30. LoginMode
  31. ServiceAccount
  32. ServiceStartMode
  33. State
  34. MailProfile
  35. NamedPipesEnabled

Here is an image of the generated CSV file.

Summary

We started first here with enabling and customizing inventory, determining which classes contain information that may be useful for us down the road. We also enabled and customized the inventory details to understand the different characteristics of the SQL instances. I will discuss the process of building a full-fledged SQL Server inventory with data manipulation techniques using PowerShell in my next article. With this script you can fully inventory and document your SQL Server estate on demand

I hope you enjoyed reading this article. Feel free comment below for any questions.

Appendix – Script

Prashanth Jayaram
PowerShell, Server management

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

168 Views