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.

Just a few of the properties available are shown below.

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

SQL Server roles can also be looked at as well.

$sqlServer.Roles | Select Name

Same goes for SQL logins on the server.

$sqlServer.Logins | Select Name

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

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

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

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*"

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)'

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

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 

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()

A fun method to use is EnumProcesses()

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

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

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!