question

skpani avatar image
skpani asked

User redirection in DB Mirroring

I have configured DB Mirroring with automatic fail over.My users are connected to Server_A .My Partner is Server_B and witness is Server_C.As we know that whenever a fail over occurs Server_B will take the roll from Server_A and it becomes Online.But my point is how the users are redirected to Server_B. Is it the DBA who changes the connection string or the Application Developer or the Database Developer.How ? What happens behind the screen.Please explain this with details..
sql-server-2008-r2database-mirroring
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

raadee avatar image
raadee answered
In case of an error ([Possible Failures During Database Mirroring)][1] on the Principal Server (A) and the witness(C)/Mirror Server(B) registers this, they agree (and form a quorum) to switch the role of principal to Server B. Note that the state of the mirror must also be SYNCHRONIZED for this to be true. Your user application is configured with a connection string where the principal server instance (Server A) is configured as the initial partner and the mirror server (Server B)is configured as the failover partner. Like: Data Source=SQLServerA\\Instance1;Failover Partner=SQLServerB\\Instance1;Initial Catalog=YourDatabase Depending on your API, your connection string may vary. When an error occurs such as described above, application gets disconnected from Server A(Principal/Initial Partner) and will try to connect to the Server B (Failover Partner). **Now there are a couple of caveats to this.** The database on Server B will not come online as the principal until the REDO queue has been processed. And that could take some time depending on the workload and processing power of Server B. Your application must be able to handle the time it takes for the database to failover and come online. **Total time is approximately:** Noticing the error + Redo Phase + Database coming online. Noticing the error could take up to 10 seconds if it is a sever hang (by default), if it is a network error it is instantly noticed. Redo phase could take a long time if your mirror server(B) has less processing power that server A for example. So if your application does not have any retry logic or is able to handle this, the time of the automatic failover may cause your application to time out and go offline. There are a lot of material on database mirroring out there, you should really read up on it, test different scenarios and make notes of how the application reacts to these scenarios. Also do not forget that mirroring is a "database only feature", other changes to the one instance are not synchronized, such as logins. [Connecting Clients to a Database Mirroring Session][2] [transferring-logins-to-a-database-mirror][3] [1]: http://technet.microsoft.com/en-us/library/ms190913(v=sql.105).aspx [2]: http://technet.microsoft.com/en-us/library/ms175484(v=sql.105).aspx [3]: http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you @raadee
0 Likes 0 ·
You are welcome.
0 Likes 0 ·
ramesh 1 avatar image
ramesh 1 answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.