Administration

This category page is part of a series of Professional PowerShell SMO Recipes. You can find an index of all recipes on the main SMO Recipes page, and if you want to learn more about each recipe and see some sample screenshots, click on its category page.

These scripts were created and tested on Windows 8.1 with PowerShell v4 and SQL Server 2014, though most recipes should work with earlier versions of SQL Server. PowerShell v3 and above is required for many of the recipes. If you have a recipe request, leave a comment and I'll see what I can do. This cookbook will be continuously built, as I work more with SMO.

Recipe Categories

Administration

Programmatically change SQL Server startup parameters

Usually when we deal with SMO, we’re using the Microsoft.SqlServer.SMO. This snippet, however, uses Microsoft.SqlServer.SqlWmiManagement. This is what controls everything you usually see in SQL Server Configuration Manager.

sql-config-mgr

It’s important to note that you must use the server’s network name when working with SqlWmiManagement. This script automatically determines your SQL instance’s network name by using $server.ComputerNamePhysicalNetBIOS.

$sqlserver = "sqlservername\instance"

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][Reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver

$computer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $server.ComputerNamePhysicalNetBIOS
$sqlservice = ($computer.Services | Where-Object { $_.Type -eq "SqlServer" })
 
$originalstartparams = $sqlservice.StartupParameters
$newstartparams = "-dM:\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf;-eM:\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lM:\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf"
 
$sqlservice.StartupParameters = $newstartparams
$sqlservice.alter(); $sqlservice.stop(); $sqlservice.start()

# You can also change it back once you're done
$sqlservice.StartupParameters = $originalstartparams
$sqlservice.alter(); $sqlservice.stop(); $sqlservice.start()

Get SQL Server Default Paths

Knowing the SQL instance’s default paths is often useful when automating database migrations. SMO has a built-in properties, but they can be unreliable. According to Allen White:

When the default location for the user database files is the same as the system database files, the DefaultFile and DefaultLog properties are never initialized. If you change them (using Management Studio or via the registry values) then the properties will be populated, but if you don’t the values will be empty.

To get around this, you can supplement missing information with MasterDBPath and MasterLogPath.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
$defaultdata = $server.DefaultFile
$defaultlog = $server.DefaultLog
if ($defaultdata.Length -eq 0) {
	$defaultdata = $server.Information.MasterDBPath
}
if ($defaultlog.Length -eq 0) {
	$defaultlog = $server.Information.MasterDBLogPath
}

Alternatively, you can use a SERVERPROPERTY T-SQL query.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
$sql = "select SERVERPROPERTY('InstanceDefaultDataPath') as data, SERVERPROPERTY('InstanceDefaultLogPath') as log"
$dataset = $server.databases['master'].ExecuteWithResults($sql)
$defaultdata = $dataset.Tables[0].data
$defaultlog = $dataset.Tables[0].log

Get disk free for all drives, including mounted drives and clustered drives

This snippet uses Get-WmiObject Win32_Volume to get disk info for all drives, including mounted and clustered drives. Thanks goes out to Boe Prox for the straightforward snippet that this recipe was primarily based on.

$sqlservers = "sqlserver\instance","sqlcluster"
$diskCollection = @()
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

foreach ($sqlserver in $sqlservers) {
	$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver
	$disks = Get-WmiObject Win32_Volume -Filter "DriveType='3' and Label!='System Reserved'" -ComputerName $server.ComputerNamePhysicalNetBIOS

	foreach ($disk in $disks) { 
		$name = $disk.name
		$label = $disk.label
		$free = ([Math]::Round($disk.FreeSpace /1GB,2))
		$total = ([Math]::Round($disk.Capacity /1GB,2))
		$percentfree =  [Math]::Round(($free/$total)*100)
		
		# Add it to an object collection, if you want.
		$object = New-Object PSObject -Property @{
			Server = $server.name
			Name = $name
			Label = $label
			Free = $free
			Total = $total
			PercentFree = $percentfree
		}
				
		$diskCollection += $object
	}
}
$diskCollection

Limit RAM usage if max is not set

Failing to set Maximum (and some argue Minimum) RAM will likely negatively impact the performance of your SQL Servers. This script uses the suggestions of Jonathan Kehayias to automatically adjust your SQL Server’s settings. In his book Troubleshooting SQL Server: A Guide for the Accidental DBA Jonathan says the following

As a general base configuration, for a dedicated SQL Server machine, reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. This means that, for a server with 64 GB RAM, the starting point for max server memory should be in the 54 GB range, and then tuned higher based on monitoring the Memory\Available Mbytes performance counter.

Note that the code below will only change your configuration if MaxServerMemory is not set.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
$maxmem = $server.Configuration.MaxServerMemory

if ($maxmem.Maximum -eq $maxmem.ConfigValue) {
	$reserve = 1
	$totalMemory = $server.PhysicalMemory

	if ($totalMemory -ge 4096) {
		$currentCount = $totalMemory
		while ($currentCount/4096 -gt 0) {
			if ($currentCount -gt 16384) {
				$reserve += 1
				$currentCount += -8192
			} else {
				$reserve += 1
				$currentCount += -4096
			}
		}
	}

	$sqlmax = [int]($totalMemory-($reserve*1024))
	$maxmem.ConfigValue = $sqlmax
	$server.Configuration.Alter()
}

Or you can just set it to pre-determined amount for your environment. Say, 80%.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"

$maxmem = $server.Configuration.MaxServerMemory
if ($maxmem.Maximum -eq $maxmem.ConfigValue) {
	$newmax = [int]($server.PhysicalMemory * .8)
	$maxmem.ConfigValue = $newmax
	$server.Configuration.Alter()
}

Find Blocking processes and associated SQL statements

I generally try to stay away from embedded SQL calls, but I was unable to figure out how to get the offending SQL statements without performing a quick query to dm_exec_sql_text. Nevertheless, this is still a high-performance call.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlserver"
 
$blockingCollection = @()
$threshhold = $server.Configuration.BlockedProcessThreshold.ConfigValue
if ($threshhold -eq 0) { $threshhold = 60 }
 
$blocks = $server.EnumProcesses() | Where-Object { $_.blockingspid -ne 0 }
	
foreach ($block in $blocks) {
	$blockedby = $server.EnumProcesses() | Where-Object { $_.spid -eq $block.blockingspid }
	$db = $server.Databases[$blockedby.database]
	$blockingTransaction =  $db.EnumTransactions() | Where-Object { $_.spid -eq $blockedby.spid }
	$currentime = Get-Date
	$transactionBegin = $blockingTransaction.BeginTime
	$blockingSeconds = ($currentime - $transactionBegin).TotalSeconds
	if ($blockingSeconds -gt $threshhold) {
		# Could not find pure SMO for dm_exec_sql_text :|
		$sql = "SELECT top 1 text as statement FROM sys.dm_exec_requests
				CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE session_id = $($blockedby.spid)"
		$dataset = $db.ExecuteWithResults($sql)
		$blockingstatement = $dataset.Tables[0].Rows[0].Item(0)
		
		$object = New-Object PSObject -Property @{
			User = $blockedby.login
			Host = $blockedby.host
			Program = $blockedby.program
			Command = $blockedby.command
			Statement = $blockingstatement
			BlockingSince = $transactionBegin
			BlockingSeconds = [Math]::Round($blockingSeconds,1)
		}
		$blockingCollection += $object
	}
}
$blockingCollection

This took an awesome 30 milliseconds in my lab :D
blocking

Is executing user a sysadmin on SQL Server?

These recipes determine if the account executing the PowerShell statements/making the SMO connection has admin rights, and return $true or $false. Most of the scripts I use require sysadmin access, so this snippet is used extensively. Unfortunately, I could not find the DMO issysadmin equivalent for SMO, so I had to get creative.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
$server.ConnectionContext.FixedServerRoles -match "SysAdmin"
# $result returns $true or $false
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
$sql = "select IS_SRVROLEMEMBER ('sysadmin') as issysadmin"
($server.databases['master'].ExecuteWithResults($sql)).Tables.issysadmin

Get NetBIOS name

Figuring out the SQL Server’s network name can be important when you’re determining disk space, using Microsoft.SqlServer.SqlWmiManagement, or moving files using administrative shares. Here are two ways to find it using SMO.

# Gets the NetBIOS name of the local computer on which the instance of SQL Server is running.
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
$server.ComputerNamePhysicalNetBIOS
# Or, alternatively...
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
$server.Information.NetName

Stop SQL Server and SQL Agent on a remote computer

This script looks a little complex, but I’ve found that .Stop() and .Start() both return success too quickly so I wrote a function to stop the service. Clustered services and stand-alone services act differently, so they’re handled differently. If you attempt to use Get-Service to stop a Clustered SQL instance, it will just cause a failover, which likely isn’t what you’re looking to accomplish.

$sqlinstance = "sqlcluster"

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
[void][Reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlinstance
$netbiosname = $server.ComputerNamePhysicalNetBIOS
$sqlserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $netbiosname

$sqlinstance = $server.name
$instance = ($sqlinstance.split("\"))[1]
if ($instance -eq $null) { $instance = "MSSQLSERVER" }
	
if ($server.isclustered) {
	# ONLY stop the SQL Server and SQL Agent, not the whole role. To stop entire SQL Service role, use
	# $clusteredservices = Get-ClusterResource -Cluster $sqlinstance | Where-Object { $_.OwnerGroup -eq "SQL Server ($instance)" }
	try {
		$remotesqlservice = Get-Service -ComputerName $netbiosname | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" }
		$clusteredservices = Get-ClusterResource -Cluster $sqlinstance | Where-Object { $_.Name.StartsWith("SQL Server") -and $_.OwnerGroup -eq "SQL Server ($instance)" }
		$clusteredservices | Stop-ClusterResource
	} catch { 
		throw "SQL Service is clustered and Service Control Manager could not be contacted. `n
		You may also need to install the Clustering Module. Use: `n 
		Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools"
	}
}
else {
	# It's a stand-alone install of SQL Server
	$sqlservices = ($sqlserver.Services | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" -or  $_.DisplayName -eq "SQL Server Agent ($instance)" })
	$timeout = new-timespan -Minutes 2
	$sw = [diagnostics.stopwatch]::StartNew()
		
	foreach ($service in $sqlservices) {
		do {
			$service.Refresh()
			try { 
				$service.Stop() 
				Write-Warning "Stopping SQL.."
			} catch { Write-Warning "Having trouble stopping the SQL service. Let's try again." }
			
			Start-Sleep 2
		} 
		until ($service.ServiceState -eq "Stopped" -or $sw.elapsed -gt $timeout) 
	}
}

For clustered instance, this script only stops SQL Server and SQL Agent, not the entire role.

offline-clustered-service

As the above code states in the comments, if you’d like to take all resources within the role offline, change $clusteredservices to:

$clusteredservices = Get-ClusterResource -Cluster $sqlinstance | Where-Object { $_.OwnerGroup -eq "SQL Server ($instance)" }

Start SQL Server and SQL Agent on a remote computer

Along the same line as stopping…

$sqlinstance = "sqlcluster"

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
[void][Reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlinstance
$netbiosname = $server.ComputerNamePhysicalNetBIOS
$sqlserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $netbiosname

$sqlinstance = $server.name
$instance = ($sqlinstance.split("\"))[1]
if ($instance -eq $null) { $instance = "MSSQLSERVER" }
	
if ($server.isclustered) {
	try {
		$remotesqlservice = Get-Service -ComputerName $netbiosname | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" }
		$clusteredservices = Get-ClusterResource -Cluster $sqlinstance | Where-Object { $_.OwnerGroup -eq "SQL Server ($instance)" }
		$clusteredservices | Start-ClusterResource
	} catch { 
		throw "SQL Service is clustered and Service Control Manager could not be contacted. `n
		You may also need to install the Clustering Module. Use: `n 
		Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools"
	}
}
else {
	# It's a stand-alone install of SQL Server
	$sqlservices = ($sqlserver.Services | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" -or  $_.DisplayName -eq "SQL Server Agent ($instance)" })
	$timeout = new-timespan -Minutes 2
	$sw = [diagnostics.stopwatch]::StartNew()
		
	foreach ($service in $sqlservices) {
		do {
			$service.Refresh()
			try { 
				$service.Start() 
				Write-Warning "Starting SQL.."
			} catch { Write-Warning "Having trouble starting the SQL service. Let's try again." }
			
			Start-Sleep 2
		} 
		until ($service.ServiceState -eq "Running" -or $sw.elapsed -gt $timeout) 
	}
}

Is SQL Server FILESTREAM enabled?

This is particularly useful when automating restores.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
if ($server.FilestreamLevel -gt 0)  { $result = $true } else  { $result = $false }
# SMO's filestreamlevel is sometimes null, use SQL instead.
# $result returns $true or $false
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
$sql = "select coalesce(SERVERPROPERTY('FilestreamConfiguredLevel'),0) as fs"
$filestream = $server.databases['master'].ExecuteWithResults($sql)
if ($filestream.tables.fs -gt 0 )  { $result = $true } else  { $result = $false }

Force Drop Database

While you can also use $db.drop(), using $server.KillDatabase($dbname) is more effective because it drops all connections to the database, then drops it.

$servername = "sqlservername\instance"
$dbname = "mydb"

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
$server.KillDatabase($dbname)
$server.refresh()

Set Database to single user and back

$servername = "sqlserver"
$dbname = "mydb"
 
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
$db = $server.Databases[$dbname]
# Set to single user
$db.DatabaseOptions.UserAccess = "Single"
$db.Alter()
# And back to multiple
$db.DatabaseOptions.UserAccess = "Multiple"
$db.Alter()

Get info similar to SQL Server Management Studio Activity Monitor Processes tab

I used this technique to collect information for my DB/Login Inventory App, Watch-DBLogins.ps1.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlservername\instance"
try { $server.ConnectionContext.Connect() } catch { throw "Can't connect to $servername." }

$sql = "select IS_SRVROLEMEMBER ('sysadmin') as issysadmin"
$issysadmin = ($server.databases['master'].ExecuteWithResults($sql)).Tables.issysadmin
if (!($issysadmin)) { Write-Warning "Not a sysadmin on $servername, resultset will probably be underwhelming." }

$allprocs = $server.EnumProcesses() | Where-Object { $_.Host -ne $sourceserver.ComputerNamePhysicalNetBIOS -and ![string]::IsNullOrEmpty($_.Host) }
$systemdbs = "master","msdb","model","tempdb"
$excludedPrograms = "Microsoft SQL Server Management Studio - Query","SQL Management"
$allprocs | Where-Object {$systemdbs -notcontains $_.Database -and $excludedPrograms -notcontains $_.Program }| Select Login, Host, Database, Program

In my script, I added the results to a SQL table and collected the information over time.
Watch-DBLogins

Get Server Configuration (sp_cofigure)

$server.Configuration is useful for not only viewing, but modifying server configurations as well. Don’t forget the .Alter() if you make any changes.

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlserver"
$server.Configuration.ShowAdvancedOptions.ConfigValue = $true
$server.Configuration.Alter()
foreach ($sourceprop in $server.Configuration.Properties) {
	$displayname = $sourceprop.DisplayName
	$configvalue = $sourceprop.ConfigValue
	Write-Host "$displayname = $configvalue"
}
$server.Configuration.ShowAdvancedOptions.ConfigValue = $false
$server.Configuration.Alter()

Iterate server list on a SQL Server Central Management Server

Instead of using text files, keep your SQL Server servername inventory in a SQL Server Central Management Server. This script allows you to query all groups or just specific ones.

Want to populate a dynamic parameter with these servers? Check out the code within Watch-DBLogins.ps1.

$cmserver = "sqlserver"
#$cmgroups = "Group1", "Group2"

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers")

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $cmserver
$sqlconnection = $server.ConnectionContext.SqlConnectionObject

try { $cmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($sqlconnection)}
catch { throw "Cannot access Central Management Server" }
 
$serverCollection = @()
        
if ($cmgroups -ne $null) {
    foreach ($groupname in $cmgroups) {
        $cmgroup = $cmstore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$groupname]
        $serverCollection += New-Object PSObject -Property @{
            ServerName = ($cmgroup.GetDescendantRegisteredServers()).servername
            GroupName = $groupname
        }
    }
} else {
    $cmgroup = $cmstore.ServerGroups["DatabaseEngineServerGroup"]
    $servers = $cmgroup.GetDescendantRegisteredServers()
    foreach ($server in $servers) {
	$groupname =  $server.parent.name
	if ($groupname -eq "DatabaseEngineServerGroup") {  $groupname = "Default" }
			$serverCollection += New-Object PSObject -Property @{
				ServerName = $server.servername
				GroupName = $groupname
		}
    }
}
 
# Add the Central Management Server itself.
if ($servers -notcontains $CMServer) { $serverCollection +=  New-Object PSObject -Property @{ ServerName = $CMServer} }
$serverCollection
Want to see more? You can find an index of all recipes on SMO Recipes Index Page page or click on any specific category at the top of this page.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

One comment on “Administration
  1. Edward J Pochinski says:

    Awesome coding recipes !! Enjoyed reading the link very informative and detailed.

Leave a Reply