dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Connect-DbaInstance

This command is robust because it initializes properties that do not cause enumeration by default. It also supports both Windows and SQL Server authentication methods, and detects which to use based upon the provided credentials.

By default, this command also sets the connection’s ApplicationName property to “dbatools PowerShell module – dbatools.io – custom connection”. If you’re doing anything that requires profiling, you can look for this client name.

Alternatively, you can pass in whichever client name you’d like using the -ClientName parameter. There are a ton of other parameters for you to explore as well.

See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
and https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx,
and https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

To execute SQL commands, you can use $server.ConnectionContext.ExecuteReader($sql) or $server.Databases[‘master’].ExecuteNonQuery($sql).

Screenshots

dbatools-Get-DbaInstance

Examples

Creates an SMO Server object that connects using Windows Authentication

Connect-DbaInstance -SqlInstance sql2014

Creates an SMO Server object that connects using alternative Windows credentials.

$wincred = Get-Credential ad\sqladmin
Connect-DbaInstance -SqlInstance sql2014 -Credential $wincred

Login to sql2014 as SQL login sqladmin.

$sqlcred = Get-Credential sqladmin
$server = Connect-DbaInstance -SqlInstance sql2014 -Credential $sqlcred

Creates an SMO Server object that connects using Windows Authentication and uses the client name “my connection”. So when you open up profiler or use extended events, you can search for “my connection”.

$server = Connect-DbaInstance -SqlInstance sql2014 -ClientName “my connection”

Creates an SMO Server object that connects to sql2014 using Windows Authentication, then it sets the packet size (this can also be done via -PacketSize) and other connection attributes.

$server = Connect-DbaInstance -SqlInstance sql2014 -AppendConnectionString “Packet Size=4096;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;”

Creates an SMO Server object that connects using Windows Authentication that uses TCP/IP and has MultiSubnetFailover enabled.

$server = Connect-DbaInstance -SqlInstance sql2014 -NetworkProtocol TcpIp -MultiSubnetFailover

Connects with ReadOnly ApplicationIntent.

$server = Connect-DbaInstance sql2016 -ApplicationIntent ReadOnly

Author

This command was created by Chrissy LeMaire. You can find Chrissy on Twitter.

Get-Help

From PowerShell, execute Get-Help Connect-DbaInstance -Detailed for more information on this function.

Source Code

Want to see the source code? View Connect-DbaInstance.ps1 on GitHub
 

Related commands