December 10, 2008 at 12:14 am
Comments posted to this topic are about the item SQL Server 2008 Mirroring Testing
The Redneck DBA
December 10, 2008 at 2:52 am
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?
December 10, 2008 at 5:23 am
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.
December 10, 2008 at 7:10 am
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(':)');
December 10, 2008 at 7:31 am
What is time consuming?
December 10, 2008 at 9:34 am
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.
December 10, 2008 at 9:44 am
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
December 10, 2008 at 10:08 am
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
December 10, 2008 at 10:13 am
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.
December 10, 2008 at 10:20 am
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
December 11, 2008 at 8:56 am
You correct, i never realised...
Snapshots aren't standalone copies of the database, they appear much more complicated and still require the use of the original MDF file. For this reason they cannot be used on any other physical server than the one they were created on.
As long as physical resources such as processor power are not a problem you can however attach the snashot to a new database on the same server which may yield performance improvements in regards to locking.
I haven't had time to read them properly myself but here are a few links which look like they explain how it works and what they can be used for quite well...
http://msdn.microsoft.com/en-us/library/ms187054(SQL.90).aspx
http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1176142,00.html
January 30, 2009 at 10:12 am
How did you implement 2005(principle) to 2008(Mirror)...
It won't let me configure the security?
I have to manually create the endpoint on 2008? Then use that?
January 30, 2009 at 10:23 am
If by manually you mean by scripting it instead of using the GUI setup in SSMS, then yes, I did it manually.
I haven't ever played with the GUI, so I can't give you much advice there if it's giving you trouble. Other than I would suggest not mirroring from 2005 to 2008 unless you have plans to upgrade your 2005 box in the near future.
The Redneck DBA
January 30, 2009 at 2:49 pm
I haven't experiencince mirroring between 2005 and 2008 (just 2005 to 2005) however i did find the GUI pretty buggy and would recoomend doing it "manually" where possible.
January 30, 2009 at 6:01 pm
it worked fine using 2008. I connected to both and setup the mirror(2005 to 2008).
I am fully patched on 2008 so maybe thats your problem. Worked flawless. Monitor in 2005 obviously doesnt like it though but it see's it.
You have to truly monitor from 2008
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply