Using PowerShell to Administer Microsoft SQL Server

In one post it is impossible to describe all the features that appeared after the introduction of Window Powershell support in Microsoft SQL Server, but with this post I will try to show at least some of these features. Starting with SQL Server 2008, SQL Server includes two snap-ins:

1. The Provider of SQL Server, which provides a simple navigation mechanism - "inside" SQL Server, you can use the commands dir, ls, cd, Set-Location, Get-Location, etc .;
2. A set of cmdlets for specifying SQL Server actions (for example, executing the sqlcmd script).

Thus, now, without using third-party software, you can get all the necessary information from SQL Server in your PowerShell scripts.

How can this be useful for the administrator?

So, if you have already installed client utilities (SQL Server Management Studio), then your system already has the above-mentioned snap-ins and no additional steps are required. If not, you can download Management Studio for free here .
After installing Management Studio and connecting to the instance of SQL Server you are interested in, you can run the sqlps utility directly from there:

If you type the command `dir` in the sqlps window that appears, you can see a curious picture (pay attention to the objects in ObjectExplorer in SSMS and the result of the` dir` command ):

Instead of using sqlps, you can simply add new snap-ins like this: To make sure that they are installed, see the get-pssnapin –registered cmdlet.
add-pssnapin SqlServerCmdletSnapin100;
add-pssnapin SqlServerProviderSnapin100;

So, after adding these snap-ins, you can see the output of the Get-Psdrive cmdlet: By executing

cd SQLServer: \ SQL \ ServerName \ InstanceName \, we will achieve the same result as starting sqlps from SSMS.
Now a little about the biggest, "goodies." Cmdlets In total, five cmdlets are available to us:

1. Invoke-Sqlcmd is almost the same as the sqlcmd utility. Using this cmdlet, you can execute SQL queries on the necessary SQLServer.
2. Invoke-PolicyEvaluation - Checks whether the target set of SQL Server objects matches the conditions defined in the policy-based management schemes.
3. Encode-Sqlname - SQL Server identifier encoding. In SQL Server, you can assign an identifier in square brackets to almost any value, which can make it difficult to access it using PowerShell. To do this, use the Encode-Sqlname cmdlet, which turns "My: long_and | complexity | identy" into "My% 3Along_and% 7Ccomplexity% 7Cidenty"
4. Decode-Sqlname - Performs the inverse transform
5. Convert-UrnToPath - Converts URN strings used by the SMO object model to “readable” paths.

The most important thing for me is the Invoke-Sqlcmd cmdlet, which allows you to execute arbitrary SQL queries on the server.
Imagine the situation that you need to collect information on used memory on a dozen instances installed on a number of physical servers on your network. To achieve this, you can use third-party utilities that allow you to query a certain number of instances, you can use the same sqlcmd, manually indicating from which servers you want to receive data. With PowerShell, this task can be solved easier and more elegantly. The following script, for example, displays information about setting up 'Max Server Memory (MB)' on all instances registered on your computer:

$ServerList = dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | where {$_.Mode -ne "d"}
foreach ($RegisteredSQLs in $ServerList)
{
$dt = invoke-sqlcmd -ServerInstance $RegisteredSQLs.ServerName -database master -Query "SELECT SERVERPROPERTY('servername'), SERVERPROPERTY ('instancename'), value FROM sys.configurations
WHERE name = 'Max Server Memory (MB)'";
echo $dt >> C:\tmp\memory.txt;
}

Please note that in this case, for the account under which this script is run, login on SQL Server should be created and it should have enough rights to execute the request to sys.configurations. If you use SQL logins, you must specify the username and password in the parameters for the Invoke-Sqlcmd cmdlet.

Now imagine that you need to configure seven servers in exactly the same way, or add the same login to them, or make backups of all the databases on the server ... I hope I managed to show that using PowerShell, together with SQL Server, makes it much easier life of a database administrator.

References:

1. Understanding SQL Server PowerShell, Books Online - msdn.microsoft.com/en-us/library/cc281954.aspx
2. Using the Invoke-Sqlcmd cmdlet, Books Online - msdn.microsoft.com/en-us/library/cc281720.aspx
3. SQL University - sqlchicken.com/sql-university