Powershell script to create multiple SQL Server Connections

1

February 24, 2016 by Kenneth Fisher

As with most of these types of things, I had a need. I want to show how using sys.dm_exec_[requests/sessions/connections] is better than sp_who. Particularly when you have a large number of connections. Well in order to do that I need a large number of connections right? Now I’m sure someone out there has a script to generate somewhat random connections but writing one myself would be good practice and I’d like to get better at Powershell anyway. In the end I need some help and as aways it was plentiful and easy to find. So thanks to Derik Hammer (b/t), Drew Furgiuele(b/t), and of course it wouldn’t be a PoSH script if I didn’t get help from Mike Fal (b/t). (To be honest Mike actually wrote most of the final script)

#This script creates a number of connections ($MaxConnections) 
# to a SQL Server instance ($Server) that connect to a random database and exist/run for 
# a certain amount of time ($WaitType/$WaitTime)
#Driver variables
$MaxConnections = 2;           #Number of parallel connections
$Server= "(local)\sql2014cs" ; #Server to connect to
$WaitType="DELAY";             #Type of wait.  DELAY or TIME
$WaitLength="00:00:10";        #Length of wait. Format is HH:MM:SS
                               #  If DELAY then wait for the amount of time.  
                               #  If TIME then wait until the time specified.
                               # Note: Connections are only exist until the wait is over. 
                               #       They are active the whole time.

#Set Initial collections and objects    
$SqlInstance = New-Object Microsoft.SqlServer.Management.Smo.Server $Server ;
$DbConnections = @();
$dbs = $SqlInstance.Databases | Where-Object {$_.IsSystemObject -eq 0} ;

#Build DB connection array
for($i=0;$i -le $MaxConnections-1;$i++){
  $randdb = Get-Random -Minimum 1 -Maximum $dbs.Count
  $DbConnections += $dbs[$randdb].Name
}

#Loop through DB Connection array, create script block for establishing SMO connection/query
#Start-Job for each script block
foreach ($DBName in $DbConnections ) {

# All of that extra information after "Smo" tells it to load just v12 (for when you have multiple
#   versions of SQL installed.)  Note: V12 is 2014.
 $cmdstr =@"
`Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=$(12).0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
`[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
`$SqlConn = New-Object Microsoft.SqlServer.Management.Smo.Server ("$Server")
`$SqlConn.Databases['$DBName'].ExecuteNonQuery("WAITFOR $WaitType '$WaitLength'")
"@

#Uncomment the next like to print the command string for debugging
# $cmdstr
#Execute script block in jobs to run the command asyncronously
$cmd = [ScriptBlock]::Create($cmdstr)
Start-Job -ScriptBlock $cmd
}

A few things I learned.

Dealing with arrays

Declaring the array

$DbConnections = @();

Adding a row to the array

$DbConnections += $dbs[$randdb].Name

Looping through the array

foreach ($DBName in $DbConnections ) {

Obviously this is not everything you can do with an array or even every way to work with an array but it’s a start.

A multi line command
There are lots of ways to handle multi-line commands (some if not all detailed in the link) but this is how it was done in this script.

The line starts here
`and continues here.

For Loops
For Counter

for($i=0;$i -le $MaxConnections-1;$i++){
}

Where $i is the counter, -le means continue while $i is less than $MaxCounters-1, and last but not least increment $i.

For Each

foreach ($DBName in $DbConnections ) {
}

Where $DBName is the variable each element will go into and $DbConnections is a collection (array, object collection etc) of elements.

Running code asynchronously

Create a ScriptBlock out of string.

[$cmd = [ScriptBlock]::Create($cmdstr)

Run the script block as a job.

Start-Job -ScriptBlock $cmd

One thought on “Powershell script to create multiple SQL Server Connections

  1. […] Kenneth Fisher shows us how to generate multiple connections using Powershell: […]

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,671 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: