May 22, 2008 at 1:01 am
Hi All, I'm looking for advise and confirmation is what I would like to do is possible. I have read a couple of papers here that say something along the lines I am thinking about is possible, but have not seen exactly what I want to do.
I would like to use mirroring to set up a mirror db on a DR server. Should a db then die on the production server I would like to virtualise another environment on the DR machine (I will ensue that there are enough resources on this machine) and use the snapshot command to create a copy of the production db from the mirrored db, in the virtualised environment then cut the users over to this VMed environemt so they can keep going while someone fixes the production server.
This virtualised environment is temporary and when the prodmachine is back do another snapshot back to that from the virtualised environment and cut everyone back to it.
Couple of questions:
1) can I do this in a virtualised nevironment?
2) after the snapshot do I then apply the latest logs to catch it up so to speak
Any other gotchas? Is this a bad idea? Whay am i doing this - sql server and physical server consolidation.
thanks
May 22, 2008 at 6:36 am
If you mean "Database Snapshot" from your mirrored database, what you are suggesting will not work.
I think you are over-thinking this. Mirroring is made for failover.
Here is an article for best practices:
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
and another one for setup:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html
Stick with the defaults - don't try to re-invent the wheel here.
May 25, 2008 at 1:16 pm
Michael - thanks for your reply. I have read the articles you have suggested. Looks like even mirroring can be complicated.
I was originally looking for a way to preserve the DR installation while trying to give the client some flexiability in terms of bringing up an installation using virtual machines.
OK - back to logshipping!
June 4, 2008 at 9:30 pm
OK a differnt situation but this is along the same lines.
I will be getting log shipped transaction logs from a production server (I have not say in this) into a directory on a DW machine.
I have to setup a situation where the copy of the prod dbs on the DW server are able to make use of the log shipped files and still be able to be accessed by SAS for its DW work.
Does anyone have any suggestions on the simplest way to do this?
thanks
oh by the way the prod databases are SQL Server 2000 and the staging database on the DW server is SQL Server 2005
June 5, 2008 at 5:13 am
You cannot log ship from SQL 2000 to SQL 2005. You could apply the logs in NORECOVERY mode leaving the database unreadable until you apply the final log and make the database read/write, but you cannot put the database in standby mode (making it readable) because the database must be writable to make the necessary changes for the upgrade to 2005.
June 5, 2008 at 2:15 pm
Hi Michael
thanks for your reply - this bears out some testing I did yesterday.
I could get the restore done with norecovery and the status of the db would be 'recovering', but could not read the db like you can in sql 2000. Every other recovery type would want to upgrade the compatibility mode which requires write access.
This means that for every restore and trans log shipped I will need to 'activate' the db for read/write and redo the restore and reapply the translog again all over on the next refresh cycle ready for the DW to extract data from the db.
So really going forward for a BAU situation we are going to have to look at transactional replication to get the production data (still on sql 2k). Either that or stay on sql 2k for the staging area for the DW.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply