Connecting to an Instance of SQL Server
The first programming step in a SQL Server Management Objects (SMO) application is to create an instance of the Server object and to establish its connection to an instance of Microsoft SQL Server.
You can create an instance of the Server object and establish a connection to the instance of SQL Server in three ways. The first is using a ServerConnection object variable to provide the connection information. The second is to provide the connection information by explicitly setting the Server object properties. The third is to pass the name of the SQL Server instance in the Server object constructor.
Using a ServerConnection object
The advantage of using the ServerConnection object variable is that the connection information can be reused. Declare a Server object variable. Then, declare a ServerConnection object and set properties with connection information such as the name of the instance of SQL Server, and the authentication mode. Then, pass the ServerConnection object variable as a parameter to the Server object constructor. It is not recommended to share connections between different server objects at the same time. Use the Copy method to get a copy of the existing connection settings.
Setting Server object properties explicitly
Alternatively, you can declare the Server object variable and call the default constructor. As is, the Server object tries to connect to the default instance of SQL Server with all the default connection settings.
Providing the SQL Server instance name in the Server object constructor
Declare the Server object variable and pass the SQL Server instance name as a string parameter in the constructor. The Server object establishes a connection with the instance of SQL Server with the default connection settings.
Connection Pooling
It is typically not required to call the Connect method of the ServerConnection object. SMO will automatically establish a connection when required, and release the connection to the connection pool after it has finished performing operations. When the Connect method is called, the connection is not released to the pool. An explicit call to the Disconnect method is required to release the connection to the pool. Additionally, you can request a non-pooled connection by setting the NonPooledConnection property of the ServerConnection object.
Multithreaded Applications
For multithreaded applications, a separate ServerConnection object should be used in each thread.
Connecting to an Instance of SQL Server for RMO
Replication Management Objects (RMO) uses a slightly different method from SMO to connect to a replication server.
RMO programming objects require that a connection to an instance of SQL Server is made by using the ServerConnection object implemented by the Microsoft.SqlServer.Management.Common namespace. This connection to the server is made independently of an RMO programming object. It is then it is passed to the RMO object either during instance creation or by assignment to the ConnectionContext property of the object. In this manner, an RMO programming object and the connection object instances can be created and managed separately, and a single connection object can be reused with multiple RMO programming objects. The following rules apply for connections to a replication server:
- All properties for the connection are defined for a specified ServerConnection object.
- Each connection to an instance of SQL Server must have its own ServerConnection object.
- All authentication information to make the connection and successfully log on to the server is supplied in the ServerConnection object.
- By default, connections are made by using Microsoft Windows Authentication. To use SQL Server Authentication, LoginSecure must be set to False and Login and Password must be set to a valid SQL Server logon and password. Security credentials must always be stored and handled securely, and supplied at run time whenever possible.
- The Connect method must be called before passing the connection to any RMO programming object.
See Also
Tasks
How to: Connect to the Local Instance of SQL Server by Using Windows Authentication in Visual Basic .NET
How to: Connect to an Instance of SQL Server by Using SQL Server Authentication in Visual Basic .NET
How to: Connect to a Remote Instance of SQL Server by Using Windows Authentication in Visual Basic .NET