November 6, 2008 at 9:11 pm
we are planning to set up database mirroring in sql 2005:
1. If a web application is running against the primary database and if the primary db fails, so it will automatically fail over to the secondary database, in that case do we change the connection string of the web application to pint to secondary db?? or can we add both the servers in the connection string? so that even after failover to the secondary, we do'nt have to change the connection string.
2. While the db mirroring is running from primary to the secondary db, can we use the secondary db(mirror) as a read only db and use that mirror db for creating/running reports? If we can't use the mirror db for reporting purpose, then can we use the db snapshots that we take against the mirror db for writing/running reports against the db snapshots.
Please let me know your ideas, thanks!!
November 6, 2008 at 10:15 pm
1. Yes, you can code the connection string to redirect/connect automatically, when there is a failover, to the mirrored database.
I remember Kimberly Tripp has webcast mentioned how to code the connection string.
Implementing Database Mirroring, series for IT Professionals, presented by Kimberly L. Tripp
2. You can use snapshot of the mirrored database for reporting purpose or R/O.
November 6, 2008 at 11:29 pm
Mh (11/6/2008)
we are planning to set up database mirroring in sql 2005:1. If a web application is running against the primary database and if the primary db fails, so it will automatically fail over to the secondary database, in that case do we change the connection string of the web application to pint to secondary db?? or can we add both the servers in the connection string? so that even after failover to the secondary, we do'nt have to change the connection string.
yes you can. but make sure to change the connectionstring only after the completion of the previoius dB transactions. you can use windows service to track all thes on the UI level.
2. While the db mirroring is running from primary to the secondary db, can we use the secondary db(mirror) as a read only db and use that mirror db for creating/running reports? If we can't use the mirror db for reporting purpose, then can we use the db snapshots that we take against the mirror db for writing/running reports against the db snapshots.
Please let me know your ideas, thanks!!
why to disturb the secondary dB. create views for the reports on the primary dB
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 7, 2008 at 8:57 am
Vivien Xing (11/6/2008)
2. You can use snapshot of the mirrored database for reporting purpose or R/O.
But if we want the secondary(mirrored db) which will be read only to be used for creating/running reports, can we use it? And incase even if it allows us to run reports against the mirrored db, is it a good practice to run reports against the mirrored db?
If it's not a good practice to run reports against the mirrored db, so we can create snapshots either against the primary db or the mirrored db, and then we can use that snapshot for running the reports. Please advice. Thanks.
November 7, 2008 at 10:28 am
Please read about Implementing Application Failover with Database Mirroring at:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx#EMD
Connection String
The SQL Native Client data providers introduced a new connection string keyword to support some of the new features in SQL Server 2005. The failover partner keyword is used to specify the second partner of the database mirroring session in the connection string. SQL Native Client connects to whichever server is the principal at the time the connection is made.
Following is an example connection string:
Data Source=SQLA\INST1;Failover Partner=SQLB\INST1;Initial Catalog=DBMTest;Integrated Security=True
Mirroring database only allows connections from mirroring session and database snapshot. You cannot access it for reporting purpose.
HTH,
MJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply