August 26, 2011 at 12:29 pm
We have about 25 separate SQL Servers in our environment, some virtualized and some not, for vendor applications. For example, we have two (virtualized) SQL Servers for our help-desk software: one for live and one for test. Our intranet application is SQL Server based and likewise we have a live and a test SQL server.
I was thinking that maybe there's a possiblity to combine some of these servers...for example, combining all the test SQL servers so that instead of having 7 test servers, each with one or two databases, we'd have 1 SQL server with all the databases for all these vendor applications on 1 server.
Has anyone done anything like this? In searching, I can't quite tell if this is normal practice or something really unusual.
I realize I'd have to be judicious combining servers - e.g. 2 production servers that have high usage at the same peak times might not make sense to combine. And if a vendor application has more than the database on the database server (e.g. the application is on the database server), then it might not work either.
Let me know what your initial reaction to this post is. I'm thinking it'll either be "Why haven't you done this already - I can't believe you have a single server for every application!" or "Eeek - what a terrible idea to use 1 server for multiple vendors/applications!".
Thanks for your thoughts!
Leonard
Madison, WI
August 26, 2011 at 1:19 pm
I can certainly be something that makes sense to do. We have a cluster with a large number of DBs on them to eliminate the need for a bunch of small servers. However, there are a number of things to keep in mind when doing things like this.
1) One of the problems we run into on our cluster is trying to coordinate downtime since there are so many people involved. It's handy to not need to patch as many servers but now when that means you can't patch because there's no downtime that doesn't work for someone.
2) Make sure you build extra capacity into the machine you're building because you're now going to have multiple DBs growing so extra disk space will be needed sooner and if load is likely to increase in multiple DBs you'll see a faster increase than if they're on different servers.
And I'm sure there are other people that will come up with other things to look out for in this process too.
August 26, 2011 at 4:27 pm
Thanks for the tips - having overlapping maintenance windows is something I hadn't thought of yet, but obviously very important!
I did find some resources on Google in the end (I was searching for "combine" instead of "consolidate"). But I'd welcome more tips or pitfalls from forum goers too!
SQL Server Consolidation Guidance: http://msdn.microsoft.com/en-us/library/ee819082(v=sql.100).aspx
The case for SQL Server consolidation (old): http://media.techtarget.com/digitalguide/images/Misc/sqlsc_ebook.pdf
Consolidating Large Microsoft SQL Server Databases on a specific IBM system: http://www.redbooks.ibm.com/redpapers/pdfs/redp4690.pdf
Leonard
Madison, WI
August 26, 2011 at 4:58 pm
It is good to Consolidate Servers and sometimes to is better to have Databases isolated.
You hinted that it is important to be careful in determining what Databases can cohabitate on the same server.
Some Vendor Applications require a dedicated Server.
If you have the application and the Database on the same server I would try and move the application.
If you have a poorly designed database that is a hog and you consolidate it onto a server with good performing databases then that can make your life more difficult.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 27, 2011 at 11:58 am
When looking to consolidate, you also have to be aware of security requirements. Many vendor applications require sysadmin rights - as well as the vendor support requiring sysadmin rights.
If you have an application that requires sysadmin rights, or your vendor support team requires sysadmin rights - you really don't want to be sharing databases. In those cases, it might make more sense to build multiple instances. However, be aware of the OS rights required by the vendors also - you really wouldn't want the vendor for a help desk application having access to business critical data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 29, 2011 at 2:12 pm
Thanks for all the answers - some really good points, esp. on security and performance.
Thanks again!
Leonard
Madison, WI
August 29, 2011 at 2:36 pm
Having done this wholesale about 2 years ago, some thoughts:
1) Watch all FOUR bottlenecks. Network I/O, Disk I/O, CPU, Memory. One server bit us in the butt when we dropped a reporting item on it. Didn't do much but dump a few gigs of data over the network a second.
2) Over-consolidating QA and Dev is very handy and is excellent for price savings, but beware. Try and keep continuously developed applications on boxes with a LOT of overhead, and over-consolidate on rarely adjusted applications. QA can really chew on a box if you have recursive testing scripts that can end up combined with some lousy code.
3) As mentioned, watch your production level maintenance and security. Vendors in particular are feisty about having rediculous access. You can reduce your price load here by using multiple instances on a single machine, especially for lighter vendor apps, and controlling security at the instance levels. Maintenance windows you'll want to make sure you're not backing up the archive when you've got a five nines system hanging out on the same box.
4) A huge benefit of this, especially if your separate apps are heavily integrated, is reducing network load and traffic, and optimization of code to remove the workarounds for linked servers. Try to get your integrated dbs onto the same instance.
5) Have a heavy duty 'hot spare' handy. You can never account for everything. Have a completely 'dead' server on standby in case there's been a mistake and you need to move a troubled application.
6) This is an excellent time to review security policies and get best practices back into play. You need to be particularly concerned with anything that expects database ownership chaining. You can avoid these issues with proper controls from security, and you may need to. The problem here really comes into play when you have two apps that both expect it, use DBO schema, and thought they were both isolated in their own sandboxes prior to this. If it's an issue, remember you can split the instances on a single box and it's the same cost. You license by CPUs, not RAM. Considering the price of SQL Server... RAM is cheap if you're not near your limits in CPU or Server I/O.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 30, 2011 at 12:15 pm
Watch out for instance ("server") level settings, such as collation, MAXDOP, cost threshold of parallelism, default fillfactor, and so on.
Vendor apps that collate to the server default, expecting to know what it is, could be interesting.
Also note you'll probably never get all your vendors to agree to upgrade SQL versions at the same time; some will think SQL 2005 32-bit is barely "certified" and thus must be bleeding edge, others will require 2008 R2 64-bit features when you upgrade the app to keep their "certified" status.
Generally, it works well, but fighting over security and downtime is a big deal. Watch out for overlapping index maintenance and backups, too.
Also, if you have a server problem on a minor vendor app, it's not a big deal. When you have a server problem on 12 minor and 3 major vendor apps, it's a very big deal.
August 30, 2011 at 12:44 pm
Good points Craig.
With respect to the Standby Server you may want to consider documenting the connections strings that you changed when you consolidated so that if you need to move the database to the standby server you can minimize interruption of service.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 30, 2011 at 1:07 pm
That's a good point Welsh Corgi and some really good ideas. I appreciate the thoughtful responses.
We do have one vendor that up until this year would only support SQL 2000...enough said!
Leonard
Madison, WI
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply