Categories

Connecting via SMO to a named instance

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

One Response to Connecting via SMO to a named instance

  • Chad Miller says:

    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=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″ -EA Stop}
    catch {add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo”}

    try {add-type -AssemblyName “Microsoft.SqlServer.Smo, Version=10.0.0.0, 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.

Leave a Reply