Server Consolidation
-
My firm is looking at consolidating MS-SQL 2005 servers. I can see circumstances where it would not be appropriate but a large number where it would. We are a large firm with a variety of business activities. As such we have a large number of applications and we run as a minium two environments for each application, production and test. For many applications we run more: dev, training, etc... This results in a large number of SQL servers, over 200 last count. So a lot of room to consolidate where it make sense but at least management does not just want to do a blind consolidation regardless of if it does not make sense in every case.
Now I've been doing some research and have not found anything that has been a been that great. Many of the discussion papers on the pro/cons have been lacking in rigour. I have not been able to find any good white papers on methods and best practice.
I can see a number of areas I need to consider:
* The circumstances under which to consolidate and when not to.
* One application/environment per instance verse many per instance.
* Environments, what is the best way to manage this? I'm primarily concerned with separation production environments from other environments. It's bad if a playing in a test environment takes down a production one but if training goes down as a result it's not that big a deal.
* Risks, these need to be qualitative. Many of the applications we would consider consolidating are just not business critical. A certain level of increased system instability or downtime can be tolerated.
My question are:
* What other areas or questions should I consider?
* Resources that cover this topic in a detailed, analytical way?
- EBH
If brute force is not working you're not using enough.
-
Alex,
It depends on your business process and your billing / payment practices. For example, if there are 2 departments that pay out of their budgets and you consolidate their databases, who will pay for the server support, who will pay for the SW etc.
It also depends on the security and how applications are implemented. SQL Server has one set of logins for all databases. So if you have HR apps you would not want to consolidate their databases with Sales apps.
I find it working best if you consolidate databases for one department or business application owner. If your application support is different from database support, I may consider to consolidate based on the application support model.
Think of the maintenance / upgrades: it is easier to nofity one department or business owner. Also when doing SQL Server and OS related patches you have to think what happens when one application vendor supports certain version and patch level and another one does not support?
You should consider compliance issues: do the database have to be FDA or SOX compliant? Should C2 audit be implemented? etc. You may want to consolidate databases tha have to be compliant in the same way.
Hope it helps.
Regards,Yelena Varsha
-
Yelena,
I don't quite agree with you.
The main issues which should be taken intro consideration at servers consolidation are:
-- performance
-- overutilization of server resources (including tempdb)
-- concurrency issues
-- availability
-- maintainability
-- network traffic
Security is not an issue in this case, having a server login is not enough to get into HR database - you need to have a child user object for it as well as granted permissions. And the billing questions are out of our, DBAs scope. After all, departments can share server expenses.
Alex,
The primary candidates for consolidation are those databases which have a lot of import, export, bcp, dts, linked servers, TDC transaction, SSIS processes.
-
if you are buying new hardware, then make sure you look at 64 bit. Memory is still expensive, but next year it should fall. you can get an Intel box with up to 32GB of RAM and an AMD box with up to 128GB of RAM.
HP will charge you around $75,000 for an AMD box with 128GB of RAM. $64,000 of that is just for RAM.
Best thing about 64 bit is no more 4GB limit and this will reduce tempdb usage by a lot
-
All of the above thoughts are good ones, in my opinion. And 64-bit should definitely be considered if you can buy new hardware.
The reason you don't see much written on this is that it's very subjective. What can work in one environment, may not work in another. There are so many tradeoffs for what to consider that you could make this a full-time job for many people.
I think the best thing you can do is get a list of all your servers together. Separate them into production and non-production at first. You're right that you don't want runaway queries on a test system to crash production.
I'd look at some metrics for resource usage to start with on the production machines and see which ones might work well together. I've often moved databases (and their apps) together when I see the servers trundling along at 4% CPU for weeks. Of course you need to take the different sizes of CPUs, etc. into account. Resource usage is a big issue, consider RAM usage, disks, etc., making your best guess as to whether systems will play nice together. Of course don't forget that the users' tolerance needs to be considered. The sales guys might not use their server much, but if it goes down because the Training system eats up resources, you might be quickly un-consolidating things. Consider what the impact is in your environment.
Security is an issue when you're using Windows groups, especially because there could be accidental GRANTs that impact things. If there are sensitive data elements, be careful of consolidating. You can do it, but carefully audit things. Especially when you have apps that "require" sa or some high privileged account. I know it's not necessary, but sometimes it gets pushed through.
Last thing, be careful of "periodic usage". I used to have a large cube (on Oracle), that was basically unused for months, but for a week after the close of each quarter it was pounded to get accounting numbers closed. Definitely couldn't consolidate that with anything that couldn't be shut down when the cube was being used.
Hope this helps.
-
To Mark:
I don't agree with you too: if the department boss pays out of his budget he will not allow another deparment apps on it. The secure app and database servers are normally on their own subnets behind their own firewall in the corporate environment and even if the server is not utilized a lot you will not be allowed to put a phone directory app on it. If the app and the database have to be SOX compliant the DBA will not have administrative rights on the server on the regular basis and it means that he will not be able to even use SQL Server Surface Area Configuration as much as he wants. In addition every little change in permisions on the server has to be logged which will not fly for the CRM apps where the new login is issued through the application user interface.
I have many more examples for you
Regards,Yelena Varsha
-
Yelena,
There is a good article about SQL Server consolidation in May 2007 issue of SQL Server Magazine. If you are subscriber to paper edition, you can read it online too:
http://www.sqlmag.com/Articles/ArticleID/95461/pg/1/1.html
Thanks,
Mark
-
I’ve been following this debate closely, many things I had not thought of.
Cost allocation between divisions does not apply to us so much. Our IT infrastructure is centrally managing and cost allocation is not an issue. I can see how that could be a primary concern but our organisation has learned the pitfalls of that kind of accounting.
I’m more interested in the technical aspects (because I don’t have to deal with the financial aspects). How do we manage upgrades? How do we reduce the administrative costs? How do we mange performance seamlessly?
- EBH
If brute force is not working you're not using enough.
-
First and most important thing in this undertaking is careful planning. Prior to making decision you have to collect a lot of information for each server. It may include, but not limited to:
-- Min, max and average memory, IO, cpu consumption for each server. If your daily distribution is uneven, you have to collect this info on hourly or more granular basis using perfmon with saving data to log file, profiler and dynamic management viws such as sys.dm_os_performance_counters. Use dedicated server to collect this type of information - data will be saved in one place and your collection process will not impact performance of target servers.
-- Record the numbers of DTS, SSIS packages, linked servers, inter-server scheduled jobs, procedures that involve DTC transactions, bcp (bulk load) in/out scripts, for each server. Build a matrix in Excel listing all servers and their inter-connections to other servers, one speadsheet for each environment ( I mean production, QA, development environments) and for type of databases (OLTP, Data warehousing). Don't include stand-by, backup, disaster recovery servers in this list.
-- If you have replication, mirroring, log shipping, come up with some metrics to measure their performance and latency for each pair or group of servers.
-- If you have different versions of SQL Server, you have to plan additional path for apgrading them to latest version prior to consolidation. If this is a case, I would recommend to perform uppgrade first on test servers. For development servers you can perform upgrade in-place, while for QA and production severs do side-by-side upgrade. All these should be followed by intensive testings.
After you collected all this information you can do planning phase:
-- Research on hardware you will have to purchase. Of cource I recommend 64-bit.
-- Based on all the metrics you collected above, carefully map couples (or groups) of servers you want to consolidate together.
-- Plan actual migration. Keep in mind that besides databases migration which is easiest part, you also have to move bunch of server objects (logins, maintenance plans, DTS, SSIS packages, Jobs, etc). You will also have to modify jobs, SSIS, scripts to accomodate new server names.
-- Notify application developers that they have to modify connection strings in all their application code for new server name and/or IP address.
-- Check and document all scripts residing on hard-disk. You may need to update them with new server names too.
-- Notify all up-stream and down-stream processes (including you clients and vendors) about server changes.
-- Plan to avoid name clashes.
-- Don't plan to install any other than SQL Srever software (like Office, ect) on production servers. Try to install single instance per server. Don't share production servers with other database platforms, like Oracle.
-- If you plan for clustering - build clusters first, and then perform migration.
-- If you have to merge some databases - what I mean is if you have two Sales databases doing pretty much the same thing and residing on different servers and you want to merge them in one - it will be another big project.
-- Create appropriate windows user groups; it will be also a good idea to create universal login SIDs accross all servers.
-- Do your consolidation one pair (group) at a time. Record actual performance metric and compare them with estimated ones. If nessessary, make corrections to your estimates.
Also work closely with system administrators in order to ensure that the results of consolidation will not affect network performance. This projects will take significant amount of time, maybe even more than year. But remember, the more time and efforts you will spend now, during the planning, the less problems you will have after the actual consolidation. Your management should properly budget this project, you may even hire some consultants in addition to your staff.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply