SQL Server 2000 on VMware - Advantages/Disadvantages
-
What are the advantages for the DBA to combine 7 to 9 separate SQL 2000 database servers into one VMWare server? Currently each database server is a separate server with one SQL instance per server. The proposal is to have two Virtual servers that combine about 7 database servers on each VM server.
What are the disadvantages?
Has anyone loaded multiple purchased SQL databases/software on Live VMware servers?
We are currently quering the vendors to see if they support their database software products on VMware.
I need a technical reason why a DBA would want to combine multiple SQL servers and a technical reason why we shouldn't.
Is SQL Server 2000 supported on VMware?
Conceptually it sounds great, combine all these servers into one. But in my life experience if it sounds too good to be true, it usually isn't!
-
I'm not sure why you need VMWare. SQL allows multiple instances on one machine, something that most software does not allow. It also is worth mentioning that not every application needs a separate SQL Server. Many databases can exist on one SQL Server, which often is used in consolidation situations.
The biggest issue with combining databases onto one server (as multiple databases, instances, or with VMWare) is resource contention. SQL Server likes memory, so if you combine the servers, you want to be sure that you have enough memory to meet your needs.
Having everything in one instance reduces overhead, but you have less control over the resources for each application.
-
I personally don't see a need for it either. However, the windows OS server team is pushing for it. I guess it can make their life easier. I just want to make sure it makes my life easier.
Are there advantages on the VMware side for recovery? The presenter from EMC said we could have databases recovered in 5 min. I didn't believe that for a minute.
I think it is going to come down to me comming up with reasons why we as a company should not use the VMware. We have 20 separate database servers most running one database application. Others have multiple databases. We have only purchased applications from vendors that run on SQL2000. As you can imagine each vendor only supports certain SQL versions and service packs. If we combine the database applications it would present a new maintenance nightmare to install a patch for one vendor.
Does anyone have multiple application databases running in a LIVE environment?
TIA
-
I thought Microsofts position on VMWARE is that SQL Server is not supported. Maybe I am wrong but years ago they did not support it.
-
1) Require them to do a real world demo using databases with sizes and transaction levels equal to yours.
2) VM ware has nothing to do with database recover, strictly OS recovery, not guarantee on tha.
3) Virtual Server I think is available free now from MS to use on Windows 2k3 so look before you consider any leap.
4) SQL 2000 and possible the OS will still have to be lincensed for each vitrual OS instance so don't get yourself in trouble.
5) How will it make their lives easier than having a single instance SQL server? I would ask them to give you a list of reasons and weight them against reality.
6) Does you company have a vendor policy against gifts and such, if not I would check to see if EMC is trying to spike the deal and be carefull whomevers deal this is, is not going to land you in hot water if you are a traded company.
7) Who will implement, will it be inhouse or will they provide a contractor the first time? Make sure you have a backout and recooperation clause in any contract if it goes forward if issues arise and/or requirements (which you need to be specific about, especially the guarantee of a 5 minute full recovery time) are not met as expected.
8) Hardware is the biggest issue you will always run into. People think "ooh I can run several servers on one peice of hardware" and they assume that the best box they have can meet the goal. They alway seem to forget that in each virtual space there is a copy of the OS and software and their sizing overlooks this, in addition they don't set the distribution of the hardware resources correctly, and depending on the number of CPUS (and number of cores in each) there will still be time devision for threads to wake and sleep. Many times people just overload the system to it's breakin point which affects everyone.
-
Great list from Antares above and while I agree life is easier for the Windows team, it still needs to work for you and your clients. The hardware resource issues are a concern. And if you are not running Enterprise, then if you drop 4 VMware SQL Servers on a 4 way box, you need 4 separate SQL Server licenses.
-
Why VMware is very tempting from a DR standpoint, the service you provide will not only include recoverability but a host of other items such as suppportablity, performance, and #1 absolute reliability.
Test 1) The first being raw throughput. On your existing hardware, fire up performance monitor. Then create a database of 100 GB (this assumes SQL Server 2000). What the PHYSICAL DISK\disk write bytes per second. Test 2) Create a table with 10 columns. BCP in 30,000,000 rows. What the PHYSICAL DISK\disk write bytes per second. Test 3) Download SQLIO http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en
have it stress the io subsystem.
Test 4) Backup the now full database to the NULL device.
select getdate() 'Start time'
backup database TESTME_100GB to disk='NUL:' with init
select getdate() 'end time'
What the PHYSICAL DISK\disk read bytes per second on the drive holding the TESTME_100GB database and how long did it take to complete datediff(mi,endtime, starttime)?
Test5) Do something dumb like create a datbase of 5 MB and turn autogrow on in 1 MB increments. BCP in 300,000,000 really big rows (200 columns) and time how long it takes. What happens when the OS team put the DB file on a VMDISK of 1 GB?
Now that you have your numbers on your hardware alone, repeat the above using VMWARE.
If the numbers are identical, performance is not an issue. If they are different, ask your users what is important to them - how fast you can restore 1 time per year or how long they wait every single day?
The big kicker is support - What happens when you get 17883 errors or some weird i/o error? Call MS and they may just say "While we we can try to help, we really don't support you doing that as we have never tested that" . I for one do not want to go into the president's office and have to explain, your database is corrupted because the OS group wanted to save 5-6 hours during the yearly restore. I'm not knocking Virtualization - I really like it. Test the dickens out of it. Ask for references from others who are currently doing this and interview their DBA what issues they have had. What I am saying is to go in with eyes wide open.
-
My company is looking into VMWare also. However we have decided not to use it on our critical applications and only on dev, test environments. We have not implemented it though so I don't know about performance issues. Also, most of our application vendors don't support it either.
-
I don't understand the facination with VM Ware or Virtual Server for Production SQL. Any machine with enough power to host mulitiple virtual machines is certainly has enough power to host multiple SQL instances without the overhead associated with virtualization.
Generally the virtualization processing itself costs a fair percentage of the resource. I can see that cost being justified for Front End app servers, or even business layer servers. In these cases the number of servers does seem to expand with out bound and virtualization can help control rack usage and optimize resource allocation. Also, for these machines, DR usually only involves restoring the OS, App code, and configurtion.
I veiw SQL Server as a whole other animal. The product includes mulitple levels of compartmentalization that support both security and resource allocation. Schemas within a database, databases within an instance and instances with in a server surely provide as much flexibility as most environments require.
As for DR, if the failure is a disaster then it probably means the facility is unavailable. I would prefer to have an off site recovery option rather than a local virtual solution.
My money goes to a local/remote solution with the biggest server (processor(s), memory, storage) possible with a combination of separate instances and separate dbs as required.
-
In my previous company we consolidated all development/testing SQL servers (mostly 2000 and few 7.0) onto one VMware ESX server with SAN storage attached.
First, it allowed us to get rid of many old servers and workstations used as servers and free up a lot of space in the server room and in racks. Some of the old server were overused and underpowered, some underused and overpowered. By combining them into one physical box we were able to share hardware resources (CPU, memory, etc)
Second, it simplified the recovery method as we didn't need to backup multiple databases and servers. Instead VMware admins set up nightly snapshot backups of all virtual servers. And yes, the restore is quick, maybe not 5 minutes, but pretty close!!! Once restored you have fully functioning server, including O/S, patchs, SQL, databases, etc.
Though it's great for dev/test environments, I would not recommend virtualizing production SQL servers. As one mentioned before, if you need to troubleshoot a problem and would involve Microsoft support, you'd better not tell them that you're running your SQL on VMware.
-
For Dev/test, it's fantastic. We used to create images of a setup, then shut down VMWare and back up the file. Start it and run testing. Need to restart? Shut down, copy back the image and you're back at ground zero.
In areas where you need multiple machines, it's invaluable. At JD Edwards, the One World server product required 4 machines to setup and test. With VMWare, each developer could run all 4 machines on one. Performance was slower, but for 1 person it was great.
As a matter of fact, the SSC setup here was 4 machines. When we migrated to new hardware, we tested the migration on one machine initially, using 4 Virtual Server sessions.
I'm wary for production, though for things tightly tied to hardware, it definitely helps to abstract. Having to restore a 4 year old server when your old hardware dies is no fun.
-
In my previous company I merged a few dev sql 2000 DB on MS Virtual Server. In the present company I was forced to install SQL 2005 on VMWare von BizTalk. It may be good for test server, but for production server I have some doubts.
For small or dev/test server is a good solution, if you make sure the HW can do it. About the EMC statement: For EMC SAN it exists a tool for snapshots. You can schedule jobs to snapshots the drives. It is good if you have large DB and you want to make once a day a snapshot.
On our VMWare we installed Veritas to backup the server like every other machine. For DB's I have a classic backup plan with SQL Full and Log Backup.
So I would recommend to let the production DB on dedicated server (you may want to start a consolidation with on multiple instances). For dev/test Server is VMWare a good solution.
-
"I personally don't see a need for it either. However, the windows OS server team is pushing for it. I guess it can make their life easier. I just want to make sure it makes my life easier."
Ask the Windows Server team to migrate their applications to VMWare before any business application are migrated. They will suffer the effects when any of Active Directory, WINS or DNS try to run on Virtual machines.
That usually puts a stop to this nonsense.
SQL = Scarcely Qualifies as a Language
-
Thanks for the replys. It appears that no one is actually running their LIVE mission critical databases on VMWare. Their needs to be a list of technical issues other than MS doesn't support it. That in itself should put a stop to the VMWare project in regards to MS-SQL Server.
In the proposal we received from the Vendor hired by the Windows OS group only business servers with SQL databases have been marked for consolidation. I wonder why the other servers such as AD, WINS, DNS, FTP, PRINT servers or other anciliary servers were not included?
I need hard core reasons for us not to go towards Virtualization as proposed. I am not sure with the responses above in this thread I can counter the "promises" of all the $$$$ the company is going to save and the "server recovery reduced to 5 min" by doing this. Some people only see $$$$ aspect.
-
Sorry, please ignore the quote above.
" I wonder why the other servers such as AD, WINS, DNS, FTP, PRINT servers or other anciliary servers were not included? "
Strange, I would put AD, WINS, FTP on VM. We did it in the previous company. For FTP or WEB Server you reach a good load balancing. For WINS ist not a problem. We put DC's on decentrilized server on VM. Funny I recall that the hosting server were File and SQL. In other words we decided to put the application on the HW and the DC on the VM.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply