Learn more about SQL Server tools


Latest from MSSQLTips

ADO.NET Connection Strings with SQL Server Database Mirroring

MSSQLTips author Jay Dave By:   |   Read Comments (1)   |   Related Tips: More > Database Mirroring

In a recent tip (Digging into Database Mirroring in SQL Server 2005) you outlined the architecture for SQL Server 2005's Database Mirroring.  This technology seems like a great advancement over log shipping but how do I incorporate the connectivity into my front end applications?  I assume that ADO.NET has a part to play in ensuring the application will connect to the correct SQL Server instance.  What are some of the items that I should be aware of when using SQL Server 2005 Database Mirroring?  Can you also provide me with a example of the ADO.NET connection string and explain the components?

In SQL Server 2005, if you connect to a database that is being mirrored with ADO.NET or the native SQL Server client, your application can take advantage of the drivers' ability to automatically redirect connections when a database mirroring failover occurs. In order to do this you must specify the initial principal server and database in the connection string and optionally the failover partner server.

There are many ways to write the connection string, but below is one example.  In this example 'ServerA' is the principal, 'ServerB' is the mirror, AdventureWorks is the database when using Windows Authentication to connect to the SQL Servers:

"Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;"

From a database mirroring and ADO.NET perspective, the failover partner in the connection string is used as an alternate SQL Server if the initial connection to the principal SQL Server fails.  If the initial connection to the principal SQL Server succeeds, then the failover SQL Server parameter will not be used.  If not, then the failover partner should accept the connection and your users should be able to use the application as expected.

The great advantage of using the database mirroring support built into ADO.NET and the Native SQL Client driver is that you do not need to recode the application, or place special code in the application, to handle a database mirroring failover.  If you do not use the ADO.NET or Native SQL Client automatic redirection, you can use other techniques that will enable your application to fail over. For example, you could use Network Load Balancing to manually redirect connections from one server to another, while the client just connects to a virtual server name. You might also write your own redirection code and retry logic.

Here is some additional information related to using ADO.NET and SQL Server 2005 Database Mirroring:

Next Steps

  • When evaluating Database Mirroring in SQL Server 2005, be sure to not only look at this technology from a database perspective but also from an application perspective.  Depending on how your applications are written and what front end technology is used, Database Mirroring can offer a great deal of value to ensure the application is available for the user community.
  • As you being to test simple applications relying on SQL Server 2005 Database Mirroring be sure to testing how your applications behave with during a failover to ensure your front end code and SQL Server configurations are what you expect.
  • For a primer on SQL Server 2005 Database Mirroring check out - Digging into Database Mirroring in SQL Server 2005.

Last Update: 7/19/2007

About the author
MSSQLTips author Jay Dave
Jay Dave has over 10 years of experience in the IT industry working as a trainer, software developer, etc.

View all my tips
Related Resources

print tip Print  
Become a paid author

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Friday, March 23, 2012 - 5:12:19 AM - Kuldeep Singh Read The Tip

This is exact article which I'm looking for. It really helped me. Thanks for sharing with us. Check out this helpful blog too it also helped me in completing my task....


Thanks Everyone!!

More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.