SQL Database Mirroring and your web application

Question

You have two servers when you perform a SQL Server database mirroring

You have 1 primary database and 1 mirror database

Do you need to make any changes to web application to tell it that your using database mirroring? If not how does your web application know which database to use when the primary database fails?

Answer 1

It can be set in the connection string. See the "database mirroring" example here

Answer 2

Do you need to make any changes to web application to tell it that your using database mirroring?

1. Yes, add the Failover Partner parameter to your connection string, using either the IP or instance name. IE: (line returns for readability)

   Data Source = myServerAddress; Failover Partner = myMirrorServerAddress; Initial Catalog = myDataBase; Integrated Security = True;

   The mirror doesn't have to exist to test the failover - you can use a dummy address as the Data Source and watched our application use the valid config in the Failover Partner.

2. But mirroring does not cover logins or jobs - this link provides a script for cloning logins from SQL Server 2005 to other 2005+ instances. You will have to use either ALTER USER or sp_change_users_login to sync the logins if the SIDs are not identical on the principal and mirror.