November 13, 2008 at 8:26 am
Hi, I'm with a client today and he asked me if we could use a virtual machine for the sql server. The intent here is that if case of emergency, he could just remake the machine in the blink of an eye to make sure to not lose anything anywhere.
My questions here are as follow :
1 - Can the VM backups include the live DB files (I'm guessing no but I have no way to test it). This would mean that we could rebuild the entire server without having to run any scripts or restore any databases.
2 - Do you know any pitfalls of using this method?
3 - Is there any performance degradation using this setup?
4 - What is a normal downtime to rebuild a machine manually and restore all the dbs (4 dbs, total size under 1 GB, all zipped backups accessible on a FTP server). The rebuild would include Windows server 2008, sql 2005 standard 64 bit and IIS 7.0. I know it's impossible to give an exact figure here, but would you expect to be able to rebuild in less than 5 hours, 2 days? A ballpark idea here is fine... when the time comes I'll do a "performance" test myself.
November 13, 2008 at 1:49 pm
Ninja's_RGR'us (11/13/2008)
1 - Can the VM backups include the live DB files (I'm guessing no but I have no way to test it). This would mean that we could rebuild the entire server without having to run any scripts or restore any databases.2 - Do you know any pitfalls of using this method?
3 - Is there any performance degradation using this setup?
4 - What is a normal downtime to rebuild a machine manually and restore all the dbs (4 dbs, total size under 1 GB, all zipped backups accessible on a FTP server). The rebuild would include Windows server 2008, sql 2005 standard 64 bit and IIS 7.0. I know it's impossible to give an exact figure here, but would you expect to be able to rebuild in less than 5 hours, 2 days? A ballpark idea here is fine... when the time comes I'll do a "performance" test myself.
I personally don't know any of these answers except for #4 as I have not worked with virtualized SQL Servers.
#4 depends on if you have the hardware available as well as the actual time to setup the machine. I figure at least 2 hours for the OS install and updates, then the SQL install is about an hour with the updates and this depends on the peices you are installing. Then all your restores which really with the small size shouldn't take more than and hour I would guess. So lets say at a minumum you are looking at 4 hours and I'd plan at least 8 for a full restore.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2008 at 3:08 pm
Ninja's_RGR'us (11/13/2008)
Hi, I'm with a client today and he asked me if we could use a virtual machine for the sql server. The intent here is that if case of emergency, he could just remake the machine in the blink of an eye to make sure to not lose anything anywhere.My questions here are as follow :
1 - Can the VM backups include the live DB files (I'm guessing no but I have no way to test it). This would mean that we could rebuild the entire server without having to run any scripts or restore any databases.
2 - Do you know any pitfalls of using this method?
3 - Is there any performance degradation using this setup?
4 - What is a normal downtime to rebuild a machine manually and restore all the dbs (4 dbs, total size under 1 GB, all zipped backups accessible on a FTP server). The rebuild would include Windows server 2008, sql 2005 standard 64 bit and IIS 7.0. I know it's impossible to give an exact figure here, but would you expect to be able to rebuild in less than 5 hours, 2 days? A ballpark idea here is fine... when the time comes I'll do a "performance" test myself.
One of the selling points of VM is the ability to "flip a switch" and run SQL on another VM server if the first one dies. It accomplishes this so quickly because the Virtual instance is really just a file and VM can push that file anywhere it needs to. This does not apply to the databases. Those should be stored on a SAN outside of VM and treated no differently than you do now.
So, #1 is no, VM doesn't include the DB files, but that's ok. You rebuild the server and the DB files are really on the SAN.
#3, there might be a hit, depending on how you have it set up. We're planning to use VM for our SQL servers. For our main production box, there will be one physical server, running just 1 instance of VM dedicated to production, so it should not be a problem. The benefit is in DR since we can use VM to fire up another virtual server on another box if this one blows up.
For #4, I hear it's a matter of minutes provided you have planned for the ability to port the VM environment to another VM server.
Disclaimer: I haven't actually used it, just repeating the sales pitch.
November 13, 2008 at 4:12 pm
There is some performance hit to running on a VM, but it is hard to predict exactly what it will be. You are sharing real hardware with other VMs, so some of it depends on what the other VMs are doing. However, you have the option of moving VMs around from one host system to another and adjusting the CPU and memory allocations, so you should be able to get good performance with some tweaking.
We have at least 20 SQL Servers running as VMs, some with low usage, but some with high transaction workloads. We plan to convert another 20 to 30 soon and are starting to get more aggressive about the workloads we put on VMs as we gain more experience.
November 13, 2008 at 5:28 pm
If there's only 1 VM setup on the server, no competing software whatsoever, is there any noticable performance degradation (anything above let's say 5-10%)?
November 13, 2008 at 5:55 pm
I don't know for sure, but I don't believe so. VM has come a long way from it's inception.
So far as fast recovery of databases go, some SAN types have the ability to "snapshot" or "clone". We used one at my old job and it would "copy" a 1TB server (400+ databases) in under 11 minutes. It takes a while to build the first "snapshot" or "clone", but after that, it's a cake walk.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2008 at 8:41 pm
Ninja's_RGR'us (11/13/2008)
If there's only 1 VM setup on the server, no competing software whatsoever, is there any noticable performance degradation (anything above let's say 5-10%)?
That what we'll be doing for our main DB, just 1 VM setup on the physical box. We'll have multiple virtual servers running SQL for our low impact and dev-test environments. We've been told that with the current versions of VM now, performance should not be an issue. Even if it is 5%, the DR option is a nice feature.
November 14, 2008 at 3:17 am
Hi
the first question i need to ask you is
what platform are you using for virtualisation?
We currently use VMWare VI3 comprising of 10 ESX 3.5 hosts
Dont get ESX server confused with VMWare GSX server or MS Virtual Server 2005, neither of these are suitable to run a SQL VM in anger
Ninja's_RGR'us (11/13/2008)
My questions here are as follow :1 - Can the VM backups include the live DB files (I'm guessing no but I have no way to test it). This would mean that we could rebuild the entire server without having to run any scripts or restore any databases.
depends how you set the VM up and whether the VHD's are local or SAN, NFS based. Supply more info for a fuller answer
Ninja's_RGR'us (11/13/2008)
2 - Do you know any pitfalls of using this method?
our set up has been carefully planned, we have a custom .NET app that installs a SQL server with a chosen number of instances. We can usually bring a dead VM back (without a backup) in about 4 hours. Our SQL VHD's are SAN based and these LUN's are also replicated off site
Ninja's_RGR'us (11/13/2008)
3 - Is there any performance degradation using this setup?
depends on your hypervisor. The way ESX handles is resources is far superior to MS VS2005 and GSX server
Ninja's_RGR'us (11/13/2008)
4 - What is a normal downtime to rebuild a machine manually and restore all the dbs (4 dbs, total size under 1 GB, all zipped backups accessible on a FTP server). The rebuild would include Windows server 2008, sql 2005 standard 64 bit and IIS 7.0. I know it's impossible to give an exact figure here, but would you expect to be able to rebuild in less than 5 hours, 2 days? A ballpark idea here is fine... when the time comes I'll do a "performance" test myself.
are you rolling out from a template? As i said we can usually get around 4 hour turnaround, if we have a VM backup even sooner.
Supply more info about your set up and i'd be glad to talk further
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 14, 2008 at 7:17 am
Wow thanks Perry. This is something we are just exploring at the moment, we have nothing setup.. My client wants to build the best DR plan possible for as cheap as possible (there's a good oxymoron :w00t: ).
Since I have no experience with VM I came to this site (especially for the DB recovery part).
So for Perry and anybody else who care to comment, what would be your recommended setup to make sure everything runs smoothly. How many boxes to buy, what software to use for the VM, the whole 9 yard.
Here's all the info I can supply at this point :
SQL machine :
We are running windows 2008 server 64 bit, sql 2005 standard 64 bit, 250 mb raid HD for the os, program files and all db files (only 1 set of drives).
Web server :
Windows 2008 server 64 bit, IIS 7, MySQL server to host 1-2 old applications, again single set of raid drive for everything.
Backup server is off site where I ftp all the backups. My client can lose 1 day of data without major consequences, but we would like that number as low as possible... and the recovery time of the server room as low as possible as well.
November 15, 2008 at 4:33 am
you need to research and decide on a virtualisation product and storage method first then build from there as the hypervisor and storage mediums form the foundation of the systems
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 15, 2008 at 4:49 am
Any recommended products from your experience?
November 15, 2008 at 7:51 am
Here's a link to Kimberly Tripp's blog where she provides some links to SQL Server Virtualization resources (albiet for 2008, but may apply to 2005 as well):
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Virtualization-with-SQL-Server.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 15, 2008 at 9:10 am
Thanks Jack, while we're on the 2008 subject. I've once heard that there is a "free" upgrade path from 2005 standard to 2008 standard. Is that a myth, or does anyone have a link to a site that tells newbies like me how to do that?
November 15, 2008 at 12:04 pm
Ninja's_RGR'us (11/15/2008)
Any recommended products from your experience?
personally you cant beat VMWare VI3. There are a number of players in this arena now, but remember VMWare have a 10 year + head start over the others. Its a solid platform and configurable\scalable (the whole ESX kernel is under 32MB 😉 in size). Hyper V is running on a windows platform, 'nough said 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 15, 2008 at 12:20 pm
Remi,
There's a huge hidden benefit to all of this if it's running on a SAN. Most SAN companies have software that make "nearly instantaneous" copies of the entire system including SQL Server files. Lookup "SNAPSHOT BACKUPS" in Books Online and then Google "SNAPSHOT BACKUP 3-PAR SQL SERVER". Companies other than 3-PAR also have similar capabilites. We used the technique at my old company to make a full copy of a 1 Tera-Byte production server to a reporting server everynight in less than 11 minutes. Nasty fast.
The method will also provide instant updates to a remote server so that if you get whacked by a fire, hurricane, or earthquake, the proverbial flip of a switch will cause the remote system to take over with virtually no outage.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply