November 24, 2010 at 9:09 am
We have a SQL 2008 machine and I am looking into creating a clone of it using a VM incase I need to bring the box back online quickly if our main machine crashes. Currently all of our user db's reside on a SAN so it's easy enough to creating identical mapping on the vm machine and connecting to them. But I am a little confused on what(if anything) to do with the master database. Currently all the system db's reside on the C: drive and I was going to setup a weekly snapshot PtoV and restore them (and all of the C: partition of our live box) onto the virtual machine. So our virtual machine would be an identical copy to our live machine, just off the network. I also have backups of master every 15 minutes and same with msdb because we constantly add db's to our production machine. So far my plan is:
1.Scheduled P2V (Weekly)
2.First Boot of SQLVM (Disconnected from network)
a.Verify OS Boot
b.Install VMWare- Tools
c.Reboot
3.Reconnect System to network in VMWare.
4.Optional Restore of C: from system state backup more current than the date of the P2V
a.Either Server 2008 Image based restore or restore via backup exec.
b.Should only be done if any server maintenance has been completed since the last time the P2V has taken place.
5.Reconnect of Compellent drives to SQLVM.
6.Restore of the SQL System databases (Master, MSDB, Etc.).
A. Restart SQL Server in single user mode
1. Stop SQL Services via SQL Server Configuration Manager
2. Open a command prompt and type:
cd C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLTEST\MSSQL\BINN then hit enter
3. Next type
sqlservr.exe -m
to start SQL server in single user mode.
4. Open a new command prompt window and type
sqlcmd then hit enter
Next type
RESTORE DATABASE master FROM DISK = 'path' WITH REPLACE;
then hit enter
6. Once the restore is complete close both command prompt windows
7. Start SQL Services via SQL Server Configuration Manager
B. Restoring model or msdb is the same as for performing a complete database restore of a user database.
8. Reattached SQL Databases from compellent volumes as required. Databases should rebuild automatically after services restart.
Any glaring issues with my plan? Since this is an identical copy of our live environment my assumption is that master does indeed need to be restored along with msdb. Any areas I could improve/change?
November 24, 2010 at 10:04 am
I have a question; when doing a physical to virtual conversion don't all the drives and what they contain get converted to virtual disks thus negating the need to restore the databases?
November 24, 2010 at 10:07 am
Robert klimes (11/24/2010)
I have a question; when doing a physical to virtual conversion don't all the drives and what they contain get converted to virtual disks thus negating the need to restore the databases?
I *think* so, but we haven't gone through a complete walk through yet. I'm just starting to put together the plan. It's just there to remind me to check.
November 24, 2010 at 10:24 am
I am pretty sure that once you do physical to virtual you will not have to restore the databases but you would need to change name and IP of virtual server or shut down your physical box to connect it to your network.
If this is solely a database box I wouldn't even bother with the P2V. Just set up a virtual server, with a sql instance installed and just copy your database backups to it and restore them when needed or schedule the restore if you wanted a warm standby.
another thing you might want to consider is log shipping your databases to a virtual instance, which will give you a shorter recovery time and potentially much less data loss depending on how often you ship logs.
November 24, 2010 at 10:29 am
Robert klimes (11/24/2010)
I am pretty sure that once you do physical to virtual you will not have to restore the databases but you would need to change name and IP of virtual server or shut down your physical box to connect it to your network.If this is solely a database box I wouldn't even bother with the P2V. Just set up a virtual server, with a sql instance installed and just copy your database backups to it and restore them when needed or schedule the restore if you wanted a warm standby.
another thing you might want to consider is log shipping your databases to a virtual instance, which will give you a shorter recovery time and potentially much less data loss depending on how often you ship logs.
Since it's a physical to virtual the name and IP are already the same. It's just not connected to the network initially. I thought about log shipping but the increase in network traffic in our configuration is not worth the time saved. Log shipping is a bit of a pain to setup for 30+ dbs as well. I thought I read its not a good idea with some many dbs because of the additional overhead needed per db. This box will grow to probably 50-100 db's. Any other input on my plan or does it look ok?
November 24, 2010 at 10:42 am
P2V should work fine, I have done it once with a database server a couple years ago and if I remember correctly, did not have to restore any databases. we changed name and IP of server so all we did was change application config to point to new box.
November 24, 2010 at 10:49 am
Robert klimes (11/24/2010)
P2V should work fine, I have done it once with a database server a couple years ago and if I remember correctly, did not have to restore any databases. we changed name and IP of server so all we did was change application config to point to new box.
What about master though. I'll need to move that(intially via p2v) then restoring the most recent version. That part should work as I have it planned?
November 24, 2010 at 11:15 am
I am a bit confused now. Your first step says:
1. Scheduled P2V (Weekly)
I took that as you would be doing a physical to virtual every week. will you also be doing daily backups of your databases which you would then like to restore on the the virtual instance within that weekly period? If that is the case, then yes, your steps to restore master and the other databases look correct. Although I do think a weekly P2V is not necessary, it should only be done once which it looked like you were doing based on last post
I'll need to move that(intially via p2v)
.
In any case your plan should work. Only way to know for sure is to test it out.
November 24, 2010 at 11:26 am
Robert klimes (11/24/2010)
I am a bit confused now. Your first step says:1. Scheduled P2V (Weekly)
I took that as you would be doing a physical to virtual every week. will you also be doing daily backups of your databases which you would then like to restore on the the virtual instance within that weekly period? If that is the case, then yes, your steps to restore master and the other databases look correct. Although I do think a weekly P2V is not necessary, it should only be done once which it looked like you were doing based on last post
I'll need to move that(intially via p2v)
.
In any case your plan should work. Only way to know for sure is to test it out.
Good point. My reasoning for doing a weekly p2V is because of windows/system updates/general registry changes. Just trying to get the vm box as close to a mirror of the live box as possible. This is solely for disaster recovery (within an alloted time frame). I am pretty sure I wont actually have to restore any dbs. When we bring the virtual machine up since it has an updated copy of master and the same drive letters/pathing it should see the mdf/ldf files and reattach itself essentially. Is my logic wrong on that?
November 24, 2010 at 12:07 pm
When we bring the virtual machine up since it has an updated copy of master and the same drive letters/pathing it should see the mdf/ldf files and reattach itself essentially
This is correct. you should just be able to bring up virtual and all should work the same as restarting a physical server.
Then if you wanted to restore a more recent backup in between the p2v restores(ie p2v backup/restore happens on Sundays, want to restore up to Wednesday) then the steps to restore master look correct.
November 24, 2010 at 12:12 pm
Robert klimes (11/24/2010)
When we bring the virtual machine up since it has an updated copy of master and the same drive letters/pathing it should see the mdf/ldf files and reattach itself essentially
This is correct. you should just be able to bring up virtual and all should work the same as restarting a physical server.
Then if you wanted to restore a more recent backup in between the p2v restores(ie p2v backup/restore happens on Sundays, want to restore up to Wednesday) then the steps to restore master look correct.
Thanks for all the input. In our situation we'll most definitely have to restore a updated copy of master because databases are added all the time (Litigation server). That's why I wanted to be especially clear about that part. The initial copy of master is of course restored with the p2v and ill just overwrite that with an updated copy.
I've been reading alot about having to 'rebuild' master using a command line setup and it just confused me on when that would need to be done. I am assuming if one didn't have any backups of master?
November 24, 2010 at 1:09 pm
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply