April 28, 2009 at 12:28 am
We have around 15 MS SQL servers, 2000 & 2005 mix, each running one copy of SQL and about 4 databases on each. CPU on each machine max's out at around 5%.
I would thus like to move these databases from the 15 machines into one machine. These machines are all Dell 2950s. Is there any way I can use say 6 or 7 of these machines to run one version of SQL Server instead of buying another new server?
I was thinking of putting VMWare 4 on these 6 or 7 hosts- would SQL recognise the 6 or 7 sets of CPUs and treat those as one big machine? Thanks.
April 28, 2009 at 1:27 am
Is there any way I can use say 6 or 7 of these machines to run one version of SQL Server instead of buying another new server?
If I understood your question right , are you planning to run one instance of SQL 2005 in active\passive cluster then yes but you need additional hardware to share drives among the nodes which is most case will require expensive SAN, this needs to be compared to cost of new server.
Also when you migrate from 2000 to 2005 you need to check if the compability mode needs to retained or reatined to use some application features. This is well documented on MSDN site.
I was thinking of putting VMWare 4 on these 6 or 7 hosts- would SQL recognise the 6 or 7 sets of CPUs and treat those as one big machine?
Most installation have seen is large DTC or server with 32 GB RAM,8CPU and SAN\RAID running 4GB VMWARE INSTANCE in which there are one instance of SQL server 2005, but this type of cofiguration will also depend on workload or load test you have benchmarked.
Virtualization links on the same below..
http://support.microsoft.com/kb/897615
http://support.microsoft.com/kb/956893
--:-D
Cheer Satish 🙂
April 28, 2009 at 2:36 am
You maybe don't need virtualization.
You can use instances, for example the 8 old servers can be 8 instances on the new server.
N 56°04'39.16"
E 12°55'05.25"
May 6, 2009 at 9:26 am
I'd be wary of instances, you need to really manage memory between them.
If you go VMs, you then get into resource issues, potentially. You can't necessarily put 4 VMs on one server and have things work well. The CPUs balance OK, but the I/O is often an issue.
If you are consolidating, is there a reason you can't just merge more databases onto one instance?
May 6, 2009 at 9:49 am
Have you tried to do any tuning? How many users do you have on each database server? It sounds like there are a lot of opportunities to reduce load first. Can you please give us some statistics: Avg. queue length, Memory per box, memory for sql server, ## of CPU per box, avg. number of CPU/Duration, reads/writes per 10 most expensive queries.
I have worked in one organization that was max out with servers but after careful tuning load was close to 5% in CPU. After that I added another two instances to the same box and the load was still below 25%.
Alex Prusakov
May 6, 2009 at 6:08 pm
Thanks all. The boxes are all Dell PE2950s with 4 CPUs & 4 GB RAM running Win2K3 & MS SQL2000 & 2005. We've come to the conclusion that we will purchase a new "Super SQL Server" and we should be able to use the existing licenses on the new box as CALs.
May 6, 2009 at 7:05 pm
Seeing as your existing boxes are very lightly used then you should get a big win from consolidating.
One thing you will run into though is memory issues. Even though your current servers are running fine once you start consolidating the server is going to have to do more "switching"; managing more connections and handling lots of different SQL statements running against different databases.
I'd go for a 2 (or even 4) quad-core system with 32GB (or 64GB if your budget can handle it, although that depends a bit on the sum of the database sizes and the total number of connections), running SQL2k8 x64 (SQL2k5 x64 as a fallback position if you don't want to handle too much upgrade in one bite). Hopefully you can consolidate all databases into one instance but a server of this spec gives you options if you have to run multiple instances (eg. one application requires sa rights: a far too common occurrence).
Don't consider 32-bit if at all possible because the server overhead of managing AWE memory and 2GB limit for SQL Server's "working set" of memory, especially if you have to go with multiple instances, are likely to substantially reduce the performance benefit you would expect with x64.
SQL2k8 gives you options for even more control over the disparate workloads. The Resource Governor will help stop rogue queries hogging too much of the server.
Seeing as your current servers are so lightly used you should be able to migrate the databases of one of those to another with very little risk. That will free up a server for you to start testing the upgrades/migrations on, which will be a big plus for management when you're going to be asking for a fair chunk of money for your new production server.
May 7, 2009 at 8:41 am
Be VERY careful assuming that you can consolidate a bunch of servers just because CPU usage on each is very low. What is the IO demand?? That will be the thing that gets you into trouble performance wise here. You may need a BUNCH of RAM and/or a VERY good IO subsystem (good being defined as high IOPS, not large amounts of storage capacity).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 8, 2009 at 5:35 pm
Thanks all. This is fantastic information which will help me immensely. The disparate servers are all running at about 5% CPU usuage and IO is VERY low. Most of the databases have less than 10 users.
I will definately go for the x64 2008 version of SQL!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply