Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2008 Mirroring Testing Expand / Collapse
Author
Message
Posted Wednesday, December 10, 2008 12:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:15 AM
Points: 1,510, Visits: 2,164
Comments posted to this topic are about the item SQL Server 2008 Mirroring Testing

Jason Shadonix
MCTS, SQL 2005
Post #616703
Posted Wednesday, December 10, 2008 2:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 3:45 AM
Points: 3, Visits: 15
Hi, so can Mirroring be used to keep an up-to-date copy of the database to be user for reporting queries by the web application?
Post #616798
Posted Wednesday, December 10, 2008 5:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 15, 2010 9:32 AM
Points: 6, Visits: 21
No it can't.

The mirrored database remains in a state of "restoring" until it is brought into service through either an automatic or a manual failover from the principle database. The database is unusable while it is in a restoring state.

You can take snapshots of a mirror database and then restore these onto a live reporting database (although not if you are using sql2008 as your mirror database according to this article) however this could be processor and hard-drive intensive. It would also mean your reporting would not be live. If this was not a problem then you could automate a snapshot from the mirror database onto your reporting database nightly for example.

We are also looking to seperate our "functional" database activity from our "reporting" database activity. Instead of taking the snapshot approach we are looking to use database replication so we will have a live copy of our database which we can use for reporting.
Post #616872
Posted Wednesday, December 10, 2008 7:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 3:45 AM
Points: 3, Visits: 15
I thought this was the case. I've gone down the replication route too but it is quite time-consuming for a large database. It would be great if this mirroring technology could also leave an up-to-date copy of the live database that could be used for reporting. Oh well, never mind! txtPost_CommentEmoticon(':)');
Post #616982
Posted Wednesday, December 10, 2008 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 15, 2010 9:32 AM
Points: 6, Visits: 21
What is time consuming?
Post #617007
Posted Wednesday, December 10, 2008 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 177, Visits: 344
Jason,
Good stuff, and thanks for taking the time to research and write.
This adds to the collective knowledge!


The more you are prepared, the less you need it.
Post #617162
Posted Wednesday, December 10, 2008 9:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:15 AM
Points: 1,510, Visits: 2,164
venablc (12/10/2008)


You can take snapshots of a mirror database and then restore these onto a live reporting database (although not if you are using sql2008 as your mirror database according to this article) however this could be processor and hard-drive intensive. It would also mean your reporting would not be live. If this was not a problem then you could automate a snapshot from the mirror database onto your reporting database nightly for example.


Once you take a snapshot of a mirrored database on the mirror server, how are you restoring that snapshot onto another server?


Jason Shadonix
MCTS, SQL 2005
Post #617173
Posted Wednesday, December 10, 2008 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 15, 2010 9:32 AM
Points: 6, Visits: 21
I haven't tryed the snaphost/restore method but i would imagin it is just a case of restoring the snapshot as you would a database backup
Post #617202
Posted Wednesday, December 10, 2008 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 3:45 AM
Points: 3, Visits: 15
our database is copied via a snapshot replication then pushed to backup sql server. That's the bit that is time-consuming - it takes several hours to complete unfortunately, but does run without any manual intervention to leave a reporting database at a maximum of 24 hours out of date, which is OK for most reports.
Post #617210
Posted Wednesday, December 10, 2008 10:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:15 AM
Points: 1,510, Visits: 2,164
When mirroring, you can take snapshots of your database, but you can't backup or restore a snapshot, or have the snapshot on a different server than the server you are mirroring to. Unless someone knows a trick I don't know of (which is very possible).


Jason Shadonix
MCTS, SQL 2005
Post #617216
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse