Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

ADO.NET Connection Strings with SQL Server Database Mirroring

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

Problem
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?

Solution
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





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
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....
http://mindstick.com/Blog/27/Connection%20String

http://www.connectionstrings.com/sql-server-2005

Thanks Everyone!!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.