SMO & Connection Pooling (and how to avoid it).

First, I’d like to apologise for not being as active the last week or so. I caught some very nasty food poisining from a local restaraunt chain. I won’t give names but thier initials start with “Chipotles”. ;)

I’d like to discuss connection pooling with SMO…

In my process’ that I’ll be demonstrating with, I am performing a serial operation on a list of servers. So each script usually has two connections, one to the ‘main’ db server that gathers the list of servers to query. And connections to the servers in the list. I’ve found, through rudimentary testing that when doing serial operations like this, it’s more efficient for the computer running the script to not use pooled connections. The connection pooling will create about 25% more network connections and leave them for the pool on the server to manage. I was able to reduce the number of open connections by controlling the connections to the servers manually.

So, let’s demo how to control our connections explicitly using the last script as our example:

[System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.SqlServer.SMO”) | Out-Null
$SrvName = “YourServerName”
$DBName = “master”

$SrvObj = New-Object (”Microsoft.SqlServer.Management.SMO.Server”) $SrvName
$SrvObj.ConnectionContext.NonPooledConnection = “True” # Sulu, you have the Com.
$SrvObj.ConnectionContext.Connect() # Connect to the dbserver.
$DBObj = $SrvObj.Databases[$DBName]

$Query = “SELECT name FROM dbo.sysdatabases”

# Execute $Query and return a dataset
$Results = $DBObj.ExecuteWithResults($Query)


foreach ($Row IN $Results.Tables[0].Rows)
{ Write-Host $Row.Name }

Note: These lines will actually connect to the database, so make sure that you have the ConnectionContext.NonPooledConnection set right after you create your $SrvObj object.

$DBObj = $SrvObj.Databases[$DBName]
$Query = “Select name FROM dbo.sysdatabases”

Next up is a WMI based disk query that records information from the remote servers to a permanant table in the ‘main’ dbserver. I see this asked for in the forums all the time, and it’s a perfect segue into some more complex usage of Powershell & SMO.

Explore posts in the same categories: Powershell, SMO, SQL

Leave a Reply

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

You are commenting using your 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

%d bloggers like this: