header image

Connecting via SMO to a named instance

Posted by: | February 28, 2012 | 1 Comment |

A question came up in tonight’s User group session regarding connecting to SQL server instances using SMO

If you have just a default instance – just give the server name

$server = New-Object -TypeName “Microsoft.SqlServer.Management.Smo.Server” -ArgumentList “W08R2SQl12”


If you have a named instance the give the instance name as well

$serverI = New-Object -TypeName “Microsoft.SqlServer.Management.Smo.Server” -ArgumentList “W08R2SQl12\instance_name


These work as long as the SQL Server browser service is running. 

If it isn’t this is what I think you have to do.

This is untested and a best guess. I will try and test.

$cons = “server=W08R2SQL12\instance_name,port_number;Trusted_Connection=true;multipleactiveresultsets=false”

$cn = New-Object -TypeName “System.Data.SqlClient.SqlConnection” -ArgumentList $cons
$serverZ = New-Object -TypeName “Microsoft.SqlServer.Management.Smo.Server” -ArgumentList $cn
If anyone manages to test this please let me know – I’ll test as soon as I can

under: PowerShell and SQL Server

1 Comment

  1. By: Chad Miller on March 1, 2012 at 12:18 pm      

    I tested your code and it works fine. Although generally I’ll omit the instance name if I provide the port number. The instance name isn’t need if the port number is provided, but to be clear it works either way. I also generally use the fully qualified computername (hostname.domain.com) in order to leverage DNS to re-point SQL Servers in a DR scenario. So the connection string will would be
    $cons = “server=W08R2SQL12.Aceme.com,port_number;Trusted_Connection=true;multipleactiveresultsets=false”

    Another way of specifying SQL Server port number is to use the SQLConnection object:

    try {add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91” -EA Stop}
    catch {add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo”}

    try {add-type -AssemblyName “Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91” -EA Stop; $smoVersion = 10}
    catch {add-type -AssemblyName “Microsoft.SqlServer.Smo”; $smoVersion = 9}

    $con = new-object (“Microsoft.SqlServer.Management.Common.ServerConnection”) “Z109943W.aceme.com,1862”
    $server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $con

    Lastly since a connection isn’t made to the SQL Server until properties are retrieved, I prefer to explicitly call $con.Connect() or in your example $cn.Open() before creating my SMO server object.