Pros and Cons
-
I've been doing some research on a subject lately, and I am interested in what the community thinks. And for once, this poll has to do with SQL Server 🙂
One feature that has been touted a bit lately is the ability of SQL Server 2005 to handle server consolidations for your existing SQL Servers. Given the growth in its capabilities as well as the hardware advances it can take advantage of, I'm wondering what the general feeling out there is from people that actually have to support the systems.
Are you comfortable consolidating 2, 3, 4 or more SQL Servers onto one physical Windows server and is it worth it?
This can be consolidation onto a single instance, or the migration of other SQL Servers to one Windows server as separate instances. There are some good pros and cons to both methods as well as the idea in general.
For myself, I would be comfortable consolidating the mailing and web site databases onto one server, single instance with an increase of RAM to handle the additional caching. We have a 2GB and a 1GB server now and I'd probably look just go to 4GB on one server.
However I would be nervous about moving my test databases to the same instance, or perhaps even a separate instance on that machine. I supposed I could always "cripple" the test instance and run it with 512MB of RAM or so, but I wouldn't want to do this unless I could buy a bigger server and run 8GB or more of RAM, preferably an x64 server to avoid the 4GB limits and the need for windowing of memory through AWE.
But the second part of the questions, is it worth it, tends to dissuade me. I do not get any great additional benefits from the consolidation. I could not redeploy either of my other servers since they already do double duty running other software. And I doubt I would see much of a performance improvement as things are tuned about as well as they can without application rewrites.
So for me, it's yes I'm comfortable, but no it isn't worth it.
Steve Jones
-
Interesting. We've been discussing the idea of putting our dev and demo on a single box in two instances.
We haven't examined all the pros and cons yet but a couple of advantages would be saving on licensing costs with Server plus User CALs for a single server versus two and it would save rack space in our server room.
The challenge would be to get a box with enough disk and memory for both instances. Being a government agency, we sometimes have a hard time convincing management to buy hardware.
I think I'd be comfortable with it but it'll take more research to know if it would be worth it.
Greg
Greg
-
I would not be nervous of the physical process but would be very nervous from the perspective of capacity.
I worked on a project where it was calculated that running 2 instances of Sysbase server on one box would cope well with the task it was supposed to do. What we found was that there were times when the busy periods on both servers coincided with huge impacts against both.
I think it requires careful analysis. How busy is each database, how have their demand on for resources expanded over time and how are they predicted to expand? Add 20% to your figures and predict when they will compete for resources.
-
I have no issues with the concept of consolidating multiple production instances on to fewer servers. Likewise multiple test instances could be consolidated to few (non-production!) servers. If you look outside the Windows world it is common for single database servers to handle far higher workloads that has been typical for Windows boxes. On mainframes, SAN storage has been standard for about a decade, and this type of storage capacity is a major building block to make DB server consolidation possible.
Now that SANs are becoming more common in Windows environments, plus Windows at long last has good quality 64-bit exploitative software, we have the I-O bandwidth and processing power to do some serious consolidation.
However, we still have to do due diligence before consolidating DB servers/instances. As Dave Poole pointed out, you need some long term usage statistics to ensure that the processing peaks do not co-incide on a consolidated box. (I have some T-SQL that can gather these stats on SQL7 & SQL2000 - mail me on evassie@cls-services.com if you want a copy). If you can show your new server has the capacity, then go for it. I produced stats that showed we could consolidate 7 separate SQL Server boxes to 1 instance, we did it, and the final workload was about what was predicted.
Do not forget the non-DBA aspects of consolidation. Each physical box has a fixed overhead cost to support it, with a annual cost of at least a few $K. If you can cut your server numbers by as low as 50%, you get cost savings that impress senior managers.
However, I would advise you still kep Dev, Test and Prod on separate physical servers, and avoid virtual Windows servers for running DBMS software. You need to isolate Production in order to have a chance of meeting your SLAs. You need the I-O power of a physical Windows box to drive a DBMS, it will be a few years yet before Microsoft get virtual server performance up to the levels achievable on other platforms.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
We have currently one database server, with multiple instances, so our consolidation is done for us. Server has 6GB, 4 CPUs and runs fine. Our ERP is on it, along with a lot of other stuff. Consolidating is good since you have only one machine to admin.
As for the Test server, we decided to go with a separate box. Replicate of our Production server, same instances, same SP level. We prefer this set-up as it allows us to really test some stuff without even thinking of screwing something!
Note that our environment is not very big. No heavy querying. (besides the ERP and Altiris). I would minimize multiple instances, as it adds to the admin side of it.
-
We have some of our databases running in a test environment on a seperate server but other test databases are run on our production box which houses 20-30 live databases. This has not been an issue for us. But as a DBA I am concerned about all my production databases being located on a single system but we also have a very good backup system which includes using imaging software to take an image of the entire system on a regular schedule. In the event of hardware failure we can restore the entire system including the databases in a reasonable amount of time.
So my conclusion is if you cannot afford multiple systems then have an extremely reliable backup plan. Always test the restore of your system on a regular basis. And if possible keep on "hounding" your supervisor for another system so that some day you could split the load.
-
I've never been a fan of "putting all your eggs in one basket". I purposely NEVER buy a motherboard that has sound, screen card, network etc all onboard. All it takes is one of those things to break. It would cost you much more to replace a motherboard, than to just replace the broken component.
All it takes is for the cleaner to pull the wrong power plug, and you have lost ALL your services.
Same with a Hard Drive dying. Instead of just affecting your mail/web/sql, it affects them all.
Also, someone mentioned only one machine to Admin. Yes, handy, but also only ONE machine to attack (thinking about it from the hackers perspective) Someone hacks into your SQL server, and all of a sudden they have access to your e-mail.
Plus, windows wasn't built with TRUE multitasking in mind.
This might be the one and only case where the 'whole isn't greater than the sum of it's parts....'
-
The biggest problem with consolidating servers is not your in-house application suite, but third-party vendor applications, where the vendor insists that the database has to run on its own server. I've had countless battles with said vendors and set up test lab environments to prove that the database can live on another server. It's not until you get it into production and some of the problems you'd had with the single-server scenario actually go away that they start to "get it".
Of course, there are also the applications which "require" that the application use a SQL Login with the sysadmin privileges, or the application won't run, or the application that requires that SQL Server run ONLY as the default instance, using port 1433. My most recent favorite was the application that required that the sa login had to have no password for the installation to complete successfully! Once the installation was complete I implemented a strong password and we've had no problems with the app, but for the duration of the installation I had a major security hole in my network!
Sorry for the small threadjack, but these issues do directly affect the consolidation effort and you have to make certain you won't have your third-party apps stepping on other third-party apps after you consolidate.
(I keep Production, QA and Development all on separate servers, though, because I monitor production systems far more carefully than QA/Dev and production systems get my immediate attention when there are problems, where the QA/Dev environment is less critical.)
Allen White
-
In the instance with dealing with a law environment, multiple instances on the same server would probably be a bad idea. Using the firm I work for as an example, our SQL server is the backend for our billing program and the database size is 6 gigs, after shrinking. I have a smaller database on this same server, and the performance of that smaller database has not been impacted at all. It runs just as well on there as on my copy of MSDE. We have a separate SQL server running our Blackberry software. In this example having all three of these running as multiple instances on the same machine would probably cause slowdowns between each server, what with how much heavy use the billing database undergoes. I believe the slowdown would be caused by the larger of the three demanding more resources from the OS than the other two.
So really, you should look at your environment, how much your databases will grow, and how much use your SQL server has. If you've got a legal billing program, then you want that on a separate server. If it's a timeclock or something that has sporadic use, then multiple instances would be a good idea.
-
In a data center environment, this would certainly save rack space. We get into the habit of simply purchasing a new database server for every application that is purchased. Many of these are underutilized. Therefore, I see a lot of value in doing this and would be comfortable with it but the vendors and the users would have to sign-off on this. That, of course, would never happen.
-
Thanks for the opinions and they are definitely interesting. It seems that this is something MS will push a little to save companies some money as well as sell some new 2005 upgrades. Also look for hardware vendors to use this to "push" 64bit sales.
I do think that 3rd party apps are in general, poorly written to consolidate. I had a huge argument in 2001 with the Great Plains people who insisted that their app needed it's own server to run. And the CFO needed sysadmin privleges to add users. I explained why that wasn't the case and put this with our CRM app on the same server. I also implemented a manual process where the CFO would "request" an account from us, which the DBAs created. Great Plains admins (not sysadmins) could then add the user to the app and give them rights.
-
We have several third party apps. Often they specify that a specific version of SQL Server be used with specific patch levels. Different vendors assimilate the newer database versions at different times and often require new versions of their software before being able to run at the new level. Of course new software versions can take anywhere from a few days to a few months to test.
For some of the apps, applications that are usually installed on the same server as the database and I have found that we have to reboot that server every couple of weeks to keep things clean. While we could go to the effort of separating the application side from the database server it just complicates the support of the overall ERP application.
-
I'm surprised nobody has mentioned power consumption. Our 2 yr old data center is out of power. Any new servers are on hold till something comes out. Were consolidating everything - where possible. And I get uncomfortable, but there is a definitive power advantage to consolidation and every watt is helpful.
-
I am all for the consolidation of servers and have completed a great portion of this at my current company. We currently run all our DEV environment instances on one server and we are working towards this being the same with our test environments. We have even gone as far as creating a Virtual server (VM Ware) for a test of our DEV environment instances. So far so good - actually really great performance. For production we have 2 instances installed on each physical server for our clustering solution (using Veritas clustering services) with only 1 active at a time unless we need to run both and then we do some reconfiguration prior to swinging the instance.
All told, there is great benefit in managing less physical servers and if the proper hardware is put in place and you configure your instances properly you will have no problems supporting the workload of multiple instances. In reality if we were to monitor our utilization of most hardware we would find out that there is typically plenty of bandwidth to share and for the one or two times you hit a peak in your dev or test environment that causes others to suffer will be minimal and not worthy of consideration.
Thoughts?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Hi,
I tend to recommend App/DB server consolidation, not DB/DB consolidation. I prefer to put an application and its database on the same server as opposed to put several apps on one machine and several databases on another machine.
The reasons are:
- User Base Notifications for Maintenance /Upgrade downtime
- Upgrades/ Patches/Server-Wide settings may vary by project /application
- Upgrade timelines will surely different for different applications, especially with going from SQL Server 2000 to 2005
- Logins !!!! The app with SQL Server authentication will create 600 logins on the database server for example. Third-party database moving scripts would delete all logins before re-creating them for the single app. Another app will require the same login name adminuser or testuser as the third app...Do you want more examples?
Yelena
Regards,Yelena Varsha
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply