Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 Mirroring Testing


SQL Server 2008 Mirroring Testing

Author
Message
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 2601
Comments posted to this topic are about the item SQL Server 2008 Mirroring Testing

The Redneck DBA
ian-596241
ian-596241
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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?
venablc
venablc
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
ian-596241
ian-596241
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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('Smile');
venablc
venablc
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 21
What is time consuming?
Andrew Peterson
Andrew Peterson
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 714
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.
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 2601
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?

The Redneck DBA
venablc
venablc
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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
ian-596241
ian-596241
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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.
TheRedneckDBA
TheRedneckDBA
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 2601
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).

The Redneck DBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search