In this article by Donabel Santos, the author of PowerShell for SQL Server Essentials, we will look at how to accomplish typical SQL Server administration tasks by using PowerShell. Many of the tasks that we will see can be accomplished by using SQL Server Management Objects (SMO). As we encounter new SMO classes, it is best to verify the properties and methods of that class using Get-Help, or by directly visiting the TechNet or MSDN website.

(For more resources related to this topic, see here.)

Listing databases and tables

Let's start out by listing the current databases. The SMO Server class has access to all the databases in that instance, so a server variable will have to be created first. To create one using Windows Authentication, you can use the following snippet:

Import-Module SQLPS -DisableNameChecking
#current server name
$servername = "ROGUE"
 
#below should be a single line of code
$server = New-Object "Microsoft.SqlServer.Management.  Smo.Server" $servername

If you need to use SQL Server Authentication, you can set the LoginSecure property to false, and prompt the user for the database credentials:

#with SQL authentication, we need
#to supply the SQL Login and password
$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$server.ConnectionContext.set_Login($credential.UserName)
$server.ConnectionContext.set_SecurePassword($credential.Password)

Another way is to create a Microsoft.SqlServer.Management.Common.ServerConnection object and pass the database connection string:

#code below is a single line
$connectionString = "Server=$dataSource;uid=$username;   pwd=$passwordd;Database=$database;Integrated Security=False"
 
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

To find out how many databases are there, you can use the Count property of the Databases property:

$server.databases.Count

In addition to simply displaying the number of databases in an instance, we can also find out additional information such as creation data, recovery model, number of tables, stored procedures, and user-defined functions. The following is a sample script that pulls this information:

#create empty array
$result = @()
$server.Databases |
Where-Object IsSystemObject -eq $false |
ForEach-Object {
    $db = $_
    $object = [PSCustomObject] @{
       Name          = $db.Name
       CreateDate    = $db.CreateDate
       RecoveryModel = $db.RecoveryModel
       NumTables     = $db.Tables.Count
       NumUsers      = $db.Users.Count
       NumSP         = $db.StoredProcedures.Count
       NumUDF        = $db.UserDefinedFunctions.Count
    }
    $result += $object
}
$result |
Format-Table -AutoSize

A sample result looks like the following screenshot:

basic-sql-server-administration-img-0

In this script, we have manipulated the output a little. Since we want information in a format different from the default, we created a custom object using the PSCustomObject class to store all this information. The PSCustomObject class was introduced in PowerShell V3.

You can also use PSCustomObject to draw data points from different objects and pull them together in a single result set. Each line in the sample result shown in the preceding screenshot is a single PSCustomObject. All of these, in turn, are stored in the $result array, which can be piped to the Format-Table cmdlet for a little easier display.

After learning these basics about PSCustomObject, you can adapt this script to increase the list of properties you are querying and change the formatting of the display. You can also export these to a file if you need to.

To find out additional properties, you can pipe $server.Databases to the Get-Member cmdlet:

$server.Databases | 
Get-Member |
Where-Object MemberType –eq "Property"

Once you execute this, your resulting screen should look similar to the following screenshot:

basic-sql-server-administration-img-1

To find out which methods are available for SMO database objects, we can use a very similar snippet, but this time, we will filter based on methods:

$server.Databases | 
Get-Member |
Where-Object MemberType –eq "Method"

Once you execute this, your resulting screen should look similar to the following screenshot:

basic-sql-server-administration-img-2

Listing database files and filegroups

Managing databases also incorporates monitoring and managing of the files and filegroups associated with these databases. Still, using SMO, we can pull this information via PowerShell.

You can start by pulling all non-system databases:

$server.Databases |
Where-Object IsSystemObject -eq $false

The preceding snippet iterates over all the databases in the system. You can use the Foreach-Object cmdlet to do the iteration, and for each iteration, you can get a handle to the current database object. The SMO database object will have access to a Filegroups property, which you can query to find out more about the filegroups associated with each database:

ForEach-Object {
  $db = $_
  $db.FileGroups
}

This FileGroups class, in turn, can access all the files in that specific filegroup.

Here is the complete script that lists all files and filegroups for all databases. Note that we use Foreach-Object several times: once to loop through all databases, then to loop through all filegroups for each database, and again to loop through all files in each filegroup:

Import-Module SQLPS -DisableNameChecking
 
#current server name
$servername = "ROGUE"
 
$server = New-Object "Microsoft.SqlServer.Management.Smo.  Server" $servername
 
$result = @()
 
$server.Databases |
Where-Object IsSystemObject -eq $false |
ForEach-Object {
   $db = $_
   $db.FileGroups | 
   ForEach-Object {
      $fg = $_
      $fg.Files | 
      ForEach-Object {
         $file = $_
 
         $object = [PSCustomObject] @{
                Database = $db.Name
                FileGroup = $fg.Name
                FileName = $file.FileName | Split-Path -Leaf
                "Size(MB)" = "{0:N2}" -f ($file.Size/1024)
                "UsedSpace(MB)" = "{0:N2}" -f ($file.UsedSpace/1MB)
                }
         $result += $object
 
      }
   }
}
$result |
Format-Table -AutoSize

A sample result looks like the following screenshot:

basic-sql-server-administration-img-3

We have adjusted the result to make the display a bit more readable. For the FileName property, we extracted just the actual filename and did not report the path by piping the FileName property to the Split-Path cmdlet. The -Leaf option provides the filename part of the full path:

$file.FileName | Split-Path -Leaf

With Size and UsedSpace, we report the value in megabytes (MB). Since the default sizes are reported in kilobytes (KB), we have to divide the value by 1024. We also display the values with two decimal places:

"Size(MB)" = "{0:N2}" -f ($file.Size/1024)<
"UsedSpace(MB)" = "{0:N2}" -f ($file.UsedSpace/1MB)

If you simply want to get the directory where the primary datafile is stored, you can use the following command:

$db.PrimaryFilePath

If you want to export the results to Excel or CSV, you simply need to take $result and instead of piping it to Format-Table, use one of the Export or Convert cmdlets.

Adding files and filegroups

Filegroups in SQL Server allow for a group of files to be managed together. It is almost akin to having folders on your desktop to allow you to manage, move, and save files together.

To add a filegroup, you have to use the Microsoft.SqlServer.Management.Smo.Filegroup class. Assuming you already have variables that point to your server instance, you can create a variable that references the database you wish to work with, as shown in the following snippet:

$dbname = "Registration"
$db = $server.Databases[$dbname]

Instantiating a Filegroup variable requires the handle to the SMO database object and a filegroup name. We have shown this in the following screenshot:

#code below is a single line
$fg = New-Object "Microsoft.SqlServer.Management.Smo.  Filegroup" $db, "FG1"

When you're ready to create, invoke the Create() method:

$fg.Create()

Adding a datafile uses a similar approach. You need to identify which filegroup this new datafile belongs to. You will also need to identify the logical filename and actual file path of the new file. The following snippet will help you do that:

#code below is a single line
$datafile = New-Object "Microsoft.SqlServer.Management.Smo.DataFile" $fg, "data4"
 
$datafile.FileName = "C:DATAdata4.ndf"
$datafile.Create()

You can verify the changes visually in SQL Server Management Studio when you go to the database's properties. Under Files, you will see that the new secondary file, data4.ndf, has been added.

basic-sql-server-administration-img-4

If, at a later time, you need to increase any of the files' sizes, you can use SMO to create a handle to the file and change the Size property. The Size property is allocated by KB, so you will need to calculate accordingly. After the Size property is changed, invoke the Alter() method to persist the changes. The following is an example snippet to do this:

$db = $server.Databases[$dbname]
$fg = $db.FileGroups["FG1"]
$file = $fg.Files["data4"]
$file.Size = 2 * 1024 #2MB
$file.Alter()

Listing the processes

SQL Server has a number of processes in the background that are needed for a normal operation. The SMO server class can access the list of processes by using the method EnumProcesses(). The following is an example script to pull current non-system processes, the programs that are using them, the databases that are using them, and the account that's configured to use/run them:

Import-Module SQLPS -DisableNameChecking
 
#current server name
$servername = "ROGUE"
 
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $servername
 
$server.EnumProcesses() |
Where-Object IsSystem -eq $false |
Select-Object Spid, Database, 
IsSystem, Login, Status, 
Cpu, MemUsage, Program |
Format-Table -AutoSize

The result that you will get looks like the following screenshot:

basic-sql-server-administration-img-5

You can adjust this script based on your needs. For example, if you only need running queries, you can pipe it to the Where-Object cmdlet and filter by status. You can also sort the result based on the highest CPU or memory usage by piping this to the Sort-Object cmdlet.

Should you need to kill any process, for example when some processes are blocked, you can use the KillProcess() method of the SMO server object. You will need to pass the SQL Server session ID (or SPID) to this method:

$server.KillProcess($blockingSpid)

If you want to kill all processes in a specific database, you can use the KillAllProcesses() method and pass the database name:

$server.KillAllProcesses($dbname)

Be careful though. Killing processes should not be done lightly. Before you kill a process, investigate what the process does, why you need to kill it, and what potential effects killing it will have on your database. Otherwise, killing processes could result in varying levels of system instability.

Checking enabled features

SQL has many features. We can find out if certain features are enabled by using SMO and PowerShell. To determine this, you need to access the object that owns that feature. For example, some features are available to be queried once you create an SMO server object:

Import-Module SQLPS -DisableNameChecking
 
#current server name
$servername = "ROGUE"
 
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $servername
 
$server | 
Select-Object IsClustered, ClusterName, 
FilestreamLevel,
IsFullTextInstalled,
LinkedServers,
IsHadrEnabled,
AvailabilityGroups

In the preceding script, we can easily find out the following parameters:

Is the server clustered (IsClustered)?
Does it support FileStream and to what level (FilestreamLevel)?
Is FullText installed (IsFullTextInstalled)?
Are there any configured linked servers in the system (LinkedServers)?
Is AlwaysOn enabled (IsHadrEnabled) and are any availability groups configured (AvailabilityGroups)?

There are also a number of cmdlets available with the SQLPS module that allow you to manage the AlwaysOn parameter:

basic-sql-server-administration-img-6

Replication can also be managed programmatically using the Replication Management Objects assembly. More information can be found at http://msdn.microsoft.com/en-us/library/ms146869.aspx.