Exploring SQL Server Using SQL Server Management Objects (SMO) and PowerShell

Continuing on from my previous article where I showed you how to make an initial connection to a SQL server using SMO, this article aims to dive deeper into the Microsoft.SqlServer.Management.Smo.Server object to view the configuration and available methods of the server.

First thing is to load the assemblies and make the initial connection to the SQL server.

## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, 
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

## Connect to SQL Server
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('Boe-PC\SHIRE')

Just by itself, the SQL server object has a lot of properties.

image

Just a few of the properties available are shown below.

image

You can even enumerate through all of the databases via the database property. This will display the Microsoft.SqlServer.Management.Smo.Database objects. I won’t dive too deep into the database objects for this article, but will for a future article.

$sqlServer.Databases | Select Name

image

SQL Server roles can also be looked at as well.

$sqlServer.Roles | Select Name

image

Same goes for SQL logins on the server.

$sqlServer.Logins | Select Name

image

All of these will be looked at in greater detail in future articles. The main point of this is to show you what is available at the root of the SQL server object.

Digging deeper into the configuration using the Configuration property, we can really take a look at some useful properties that can even be configured.

$sqlServer.Configuration

image

image

As you can tell, there are quite a few things available here look at and possibly configure. Let’s take a look at the currently configured MaxServerMemory to see what it is set at compared to what we are using and how much physical memory is on this system.

MaxServerMemory

$sqlServer.Configuration.MaxServerMemory

image

The ConfigValue is the actual value of the configuration while the Minimum and Maximum are just that, the limits both upper and lower for this particular setting. On a default installation of SQL, this value is set to the Maximum value (not really the best thing).

Before I get into how I changed this, lets also take a look at the physical memory of the server which can be found in the SQL server object.

PhysicalMemory

$sqlServer.PhysicalMemory

image

This value is in MB, so this is basically 4GB of memory on this system. So this begs the question as to how much memory is being consumed by the SQL server? Well, one way to find this out is by using performance counters. What counter should we use to find the information needed? The quickest way is to use Get-Counter –ListSet with a little filtering to find the correct counter.

(get-counter -ListSet "*SQL*" -ErrorAction Stop).Counter -like "*Memory Manager*Total*"

image

In this case, the counter required is \MSSQL$SHIRE:Memory Manager\Total Server Memory (KB). Note that this returns a value in KB. Running Get-Counter with the counter will show just how much memory is being used.

Get-Counter -Counter '\MSSQL$SHIRE:Memory Manager\Total Server Memory (KB)'

image

Looks like this server is using 92200KB (or 90MB) of memory.

image

Keep in mind that this is the current amount of memory being used and would be subject to change.  But going back to the MaxServerMemory and changing it, lets say to 1GB of memory. Doing so is fairly simple:

#Remember that this is in MB
$sqlServer.Configuration.MaxServerMemory.ConfigValue = 1024
#Apply the change to the server
$sqlServer.Alter()
#Verify
$sqlServer.Configuration.MaxServerMemory 

image

There you have it! The MaxServerMemory is now set to 1024 MB. If I didn’t call the Alter() method after making the change, the memory config would not have been updated.

You can also check for any locks on the server.

$sqlServer.EnumLocks()

image

A fun method to use is EnumProcesses()

$sqlServer.EnumProcesses() | Format-Table -Auto

image

Now you can see the current snapshot of activity that is occurring on the SQL Server; similar to viewing the Processes on Activity Monitor.

You are also able to look at the server permissions.

$sqlServer.EnumServerPermissions() | Format-Table -AutoSize

image

These are just some of the things you can look at and explore in your SQL server. Next time we will look at databases using PowerShell and SMO!

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell, SQL and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s