In my previous blog Get SQL Server services with PowerShell, I presented you the first step of the SQL Server administration through an automated process.
This blog is a follow-up of the previous one, and it will focus on retrieving information of a SQL Server instance with PowerShell
Disclaimer: I am not a developer but a SQL Server dba. If you find errors or some ways of improvement, I will be glad to read your comments!
List all SQL Server instances
To be able to proceed for all the instances, you can easily get all your instance names with this function:
Retrieve SQL Server instance information
In my example, I execute my script on the machine hosting my SQL Server instance.
I use SMO objects to access to the instance information . But you need the instance full name , as follows:
I only give the instance name as parameter because I execute my script on a local server, otherwise I need to give the server name as parameter.
First I initialize my SMO object of my instance like this:
This SMO object contains SQL Server instance main information. To list all properties and the object methods, proceed as follows:
To list the general information of the instance, you can proceed like this:
To list the directory paths related to the instance, here is an example:
To list important instance configuration, here is an example:
By formating the information you retrieve in the instance SMO object, you can generate reports, audit your environment or whatever!
The following capture is an existing dashboard from our Database Management Kit (DMK).
Next steps
The SMO object for the SQL Server instance has a limit number of properties and methods. Sometimes, you need information which are not present in the object. In this case, you must use the “sqlcmd” command and retrieve your information by using T-SQL.
To retrieve any of SQL Server instance information, the “sqlcmd” command would always work. You can also use it to modify the instance configuration.
I hope this blog will help you in your work. In my next blog, I will show you how to access to your database information with PowerShell.