locked
Consolidation strategy: general purpose vs departmental servers RRS feed

  • Question

  • I'm fairly new to my current work environment.  They're standardizing on general purpose SQL servers.  New application databases are deployed on the least utilized general purpose server, as are existing databases that need to be migrated off legacy servers.  This seems as good an approach as any...  there are always going to be one-off databases that are used organization-wide, or that don't need a dedicated server.  I'm curious if anybody has a different approach such as using departmental servers.  So for example all accounting databases go on one or more servers.  It seems like that would make integration and troubleshooting easier.  If we had to take a server down for maintenance, or if a server crashed, then it would be easier to notify a limited group of users, i.e., all accounting users.  Or for example, in the case of needing linked servers, the accounting servers would only have to link within their own "farm".  What is your approach to consolidating servers, databases, instances, etc?

    Monday, April 30, 2012 10:12 PM

Answers

  • What a can of worms those questions open!

    The trendiest idea these days is "the cloud", just ask for what you want, and let the magic infrastructure genie make it happen.

    But some companies want to do this by virtualizing everything, which does not always make SQL Server happy, especially as performance demands increase and scale gets to a certain point.  Microsoft is fooling around with Azure to try to provide this cloud functionality, and you can look up how that's working for people - so-so, I think pretty well summarizes it.

    But here's the thing, a database professional *should* be considering more than just letting the infrastructure genie set up yet another server, a professional (harumph!) should be worrying about the semantics and data model and functionality of the systems involved.  For that you get into this whole story of transactional versus data warehouse versus data mart versus operational data store versus conformed dimensions versus centralized source versus mirrored/replicated systems for reporting and DR and HA and moby-scale "NoSQL" solutions that still have SQL front ends and OMG!

    Josh


    • Edited by JRStern Monday, April 30, 2012 10:33 PM
    • Marked as answer by Stephanie Lv Monday, May 14, 2012 8:18 AM
    Monday, April 30, 2012 10:33 PM
  • There are a lot of factors to consider in addition to "basic" workload/hardware questions. To name just a few:

    • Version of SQL Server. Different DB/Apps could require different versions (2005, 2008, 2012) of SQL backend. Ideally you would like not only to consolidate it based on the versions but also by upgrade schedule. What if you have DB/APP which is not supported anymore and would require your server to stuck on specific version of SQL Server?
    • Edition of SQL Server - does DB/App require Enterprise edition features?
    • Maintenance window - do you mix 24x7 with 8x5/M-F workload?
    • SLA - does it make sense to put all business critical dbs to the same server/cluster? On one hand it could help with designing HA/DR strategy. On the other - if server(s) is down, all business critical apps are down. And of course, again here is the question of maintenance.
    • Security - does DB/App require elevated permissions on the server? Does Vendor require to access your server for support? Do you need to give developers  access to the server? etc - the list here is huge..
    • Compliance and auditing - those would introduce overhead on the server as well as additional security requirements.

    The list is not full by any means. Consolidation in general is the question from "it depends" category and requires a lot of planning and thinking. Not even mention that a lot of things depend on the budget.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by Stephanie Lv Monday, May 14, 2012 8:18 AM
    Tuesday, May 1, 2012 12:53 AM

All replies

  • What a can of worms those questions open!

    The trendiest idea these days is "the cloud", just ask for what you want, and let the magic infrastructure genie make it happen.

    But some companies want to do this by virtualizing everything, which does not always make SQL Server happy, especially as performance demands increase and scale gets to a certain point.  Microsoft is fooling around with Azure to try to provide this cloud functionality, and you can look up how that's working for people - so-so, I think pretty well summarizes it.

    But here's the thing, a database professional *should* be considering more than just letting the infrastructure genie set up yet another server, a professional (harumph!) should be worrying about the semantics and data model and functionality of the systems involved.  For that you get into this whole story of transactional versus data warehouse versus data mart versus operational data store versus conformed dimensions versus centralized source versus mirrored/replicated systems for reporting and DR and HA and moby-scale "NoSQL" solutions that still have SQL front ends and OMG!

    Josh


    • Edited by JRStern Monday, April 30, 2012 10:33 PM
    • Marked as answer by Stephanie Lv Monday, May 14, 2012 8:18 AM
    Monday, April 30, 2012 10:33 PM
  • There are a lot of factors to consider in addition to "basic" workload/hardware questions. To name just a few:

    • Version of SQL Server. Different DB/Apps could require different versions (2005, 2008, 2012) of SQL backend. Ideally you would like not only to consolidate it based on the versions but also by upgrade schedule. What if you have DB/APP which is not supported anymore and would require your server to stuck on specific version of SQL Server?
    • Edition of SQL Server - does DB/App require Enterprise edition features?
    • Maintenance window - do you mix 24x7 with 8x5/M-F workload?
    • SLA - does it make sense to put all business critical dbs to the same server/cluster? On one hand it could help with designing HA/DR strategy. On the other - if server(s) is down, all business critical apps are down. And of course, again here is the question of maintenance.
    • Security - does DB/App require elevated permissions on the server? Does Vendor require to access your server for support? Do you need to give developers  access to the server? etc - the list here is huge..
    • Compliance and auditing - those would introduce overhead on the server as well as additional security requirements.

    The list is not full by any means. Consolidation in general is the question from "it depends" category and requires a lot of planning and thinking. Not even mention that a lot of things depend on the budget.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by Stephanie Lv Monday, May 14, 2012 8:18 AM
    Tuesday, May 1, 2012 12:53 AM
  • When I am approached with this subject I ask the questions that center around data classification.  What is the nature of the data and what is the SLA for that data look like.  I use this information to then develop a consolidation plan.  This includes things like versions, features, performance, etc.  Hope this helps. 

    Jonathan Gardner PMP Website: http://jonathanagardner.com Twitter: jgardner04

    Tuesday, May 1, 2012 2:24 AM