Mini Post: Specifying A Failover Partner In A Connection String

Hey everyone, Geon here again with another SQL Something post!

This time we are going to look really quickly at modifying a connection string to have a failover partner. What this means is if you have a database setup that includes Mirroring (circa SQL Server 2008), then you should make sure your applications take advantage of it. Your applications (when configured correctly) can automatically failover to the secondary DB when the current DB becomes unavailable.

Which is pretty cool by itself, but also very useful as it minimizes downtime and any manual intervention needed to point the apps to the failover DB.

And it is really simple to do.


Suppose you have a connection string to a DB like the below for example:


connectionString="Data Source = DBServerAddress; Initial Catalog = TestDB; User ID = TestUser; Password = TestPassword"


Now suppose that DB was set up for Mirroring meaning that if it ever goes down, its Mirrored counterpart will come online. Now, you'd like you applications to point to the 'new' active DB. Simply add "Failover Partner=MirrorServerName;" to the connection string. See below:


connectionString="Data Source = DBServerAddress; Failover Partner = MirrorServerAddress; Initial Catalog = TestDB; User ID = TestUser; Password = TestPassword"


And that's all there is to it. Your app(s) should point to the secondary mirrored DB if the primary is not available. :-)

(See here for more info)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)