Free SQL Server Performance Dashboard & Screensaver

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:

• Example SQL Server Connection Strings
• Database Mirroring in SQL Server 2005
• What's New in ADO.NET
• Using Database Mirroring
• .NET Framework Class Library - SqlConnection Class
• .NET Framework Class Library - SqlConnection.ConnectionString Property

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.

• Monitoring SQL Server Database Mirroring with Email Alerts
• Implementing Database Mirroring in SQL Server 2005 across domains
• Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness
• Evaluating SQL Server Database Mirroring as a Disaster Recovery Solution
• Adjusting the automatic failover time for SQL Server Database Mirroring
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback