Sql Server: Everything you always wanted to know about SMO Connections
There are several ways of controlling connections in SMO -- you can control pretty much any aspect of a SMO connection.
Pooled connections
Let's take a look at a typical way how you would obtain a connection using a simple C# sample:
Server svr = new Server();
Console.WriteLine(svr.Information.Product + " " + svr.Information.VersionString + " " + svr.Information.ProductLevel); |
When you run this sample it will print the version, and edition, but you will also notice that the Connect() call is missing. Is there a need to call Connect()? The answer is both "yes", and "no". When you do not call Connect(), SMO will automatically obtain a connection from the connection pool when it needs one (in this case when accessing the svr.Information.VersionString/Edition properties), and after the operation has finished it will return that connection to the pool. This is a nice model as it stops you from needing to worry about when to call Disconnect(). SMO will do that bookkeeping for you and if your application has to exit because of a failure you do not need to release the connection -- SMO already has done that for you.
Explicitly connecting
You can override that behavior by explicitly connecting:
Server svr = new Server();
svr.ConnectionContext.Connect();
Console.WriteLine(svr.Information.Product + " " + svr.Information.VersionString + " " + svr.Information.ProductLevel);
svr.ConnectionContext.Disconnect(); |
What happens here is that SMO will obtain a connection from the connection pool on your request, and will not automatically release it to the pool, until you call Disconnect().
This is very much the same pattern that you would use in SQL-DMO. Also, if you are not able to connect to the server, you will get an exception during the Connect() call. However, there is one slight difference. The connection that is obtained comes from a connection pool. If you call Disconnect() and Connect() in succession then you will likely get the connection from the pool, so this allows you to efficiently manage your connections. Connection pooling means the connection is not immediately closed. It’s up to the connection pool to close it when the lifetime expires.
Non pooled connections
In some cases you want to not draw connections from a pool, but make sure that you get a non-pooled connection that you can hold on as long as your application is running. This can be helpful when running in single-user mode, or when you are paranoid about SMO to do the right thing with your connections and want to have complete control over the connections that are managed. This can be simply accomplished by setting the NonPooledConnection property to true.
Server svr = new Server();
svr.ConnectionContext.NonPooledConnection = true;
Console.WriteLine(svr.Information.Product + " " + svr.Information.VersionString + " " + svr.Information.ProductLevel); |
The connection is then a dedicated connection that will not be returned to the pool. SMO will again, like with pooled connections, connect on demand, but in this case it will hold on to the connection until you specifically call Disconnect(). Connecting is in this case much more expensive, as there will not be a connection in the pool. This type of connection however guarantees that you have that connection for the lifetime of your application. For example, SQL Server Management Studio uses such a connection.
Sharing connections between processes
You can share connections between SMO Server instances, and pass and Create a new ServerConnection object based on an existing object, which is helpful if you need multiple connections with the same setting:
Server svr = new Server();
svr.ConnectionContext.NonPooledConnection = true; // comment this out to see what happens to the SPID's
Console.WriteLine(svr.ConnectionContext.ProcessID);
// do work...
// Pass the connection on to new Server instance -- the connection is reused
Server svr2 = new Server(svr.ConnectionContext);
Console.WriteLine(svr2.ConnectionContext.ProcessID);
// do work...
// The Copy() method will create a new object, and will use a new connection to the server
Server svr3 = new Server(svr2.ConnectionContext.Copy());
Console.WriteLine(svr3.ConnectionContext.ProcessID);
// do work... |
If you run this example, you will see that the ProcessID (SPID) only differs for svr3, as it will not use the existing connection.
Comment out the indicated line, and see if you understand what's happening! (please post a response, don't be shy! ;-).
Other settings
There are various other settings of the ServerConnection object that can be manipulated to finetune SMO's behavior. Examples of this are MaxPoolSize, PooledConnectionLifeTime. Take a look at the object and you will find that all possible connection settings are available.
Some interesting facts
- The default instance is assumed when you do not specify a server name
- SMO uses Integrated security by default (the most secure option)
- Once you have connected, you cannot change the properties of the ServerConnection object. You need to get a new ServerConnection object or use Copy() if you want a new connection with the same properties.
Important note
SMO allows you to pass on ServerConnections to different Server instances, but be very careful when doing so. SMO is making assumptions about various settings, so if you change settings, for example you grab the ServerConnection for other purposes than using it for SMO, and issue a SET NOEXEC ON, then SMO will fail to execute the statements. This will cause SMO to respond unpredictable, with application exceptions likely to occur. It is strongly discouraged to reuse connections for other uses than in SMO. Use pooling and obtain a dedicated connection for purposes other than using it with SMO.
