SQL Server 2008 Mirroring Testing

  • TheRedneckDBA

    SSChampion

    Points: 14001

    Comments posted to this topic are about the item SQL Server 2008 Mirroring Testing

    The Redneck DBA

  • ian-596241

    Valued Member

    Points: 53

    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

    SSC Enthusiast

    Points: 110

    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

    Valued Member

    Points: 53

    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(':)');

  • venablc

    SSC Enthusiast

    Points: 110

    What is time consuming?

  • Andrew..Peterson

    SSCertifiable

    Points: 6719

    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

    SSChampion

    Points: 14001

    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

    SSC Enthusiast

    Points: 110

    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

    Valued Member

    Points: 53

    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

    SSChampion

    Points: 14001

    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

  • venablc

    SSC Enthusiast

    Points: 110

    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

  • mikes067

    Valued Member

    Points: 63

    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?

  • TheRedneckDBA

    SSChampion

    Points: 14001

    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

  • venablc

    SSC Enthusiast

    Points: 110

    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.

  • mikes067

    Valued Member

    Points: 63

    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 20 total)

You must be logged in to reply to this topic. Login to reply