We help IT Professionals succeed at work.

consolidating sql servers

I have 15 sql servers... its a mix of 2008 and 2012 sql servers.

I think we are a good candidate for consolidating the instances.  I would like to get down to 5 sql servers.   there is a management application where I only have 5 keys.  Can someone point me to a good paper or book that explains how you should tackle this.

ie.... what databases should be on what server..  limitiations and other things?
Watch Question

Distinguished Expert 2019
First thing before thinking about consolidation, you should if you are not already knowledgeable about the applications that use the databases and what depends on them.

Limitations are:
System Resources/HDs/Processors/Memory/Network
Database sizes, how active they are and available storage for both Data and transaction log flies.
Applications that use each database and supported SQL version by the vendor. Some proprietery application's current version might only support sql 2008.  If you have a test environment where you can install the application and a copy of database in sql 2008 mode on sql 2012 server to make sure they work, you can transition it to the sql 2012.

Do your existing servers have/use any monitoring to which you can reference as to the load on the server and sql related data? (perfmon)

When you say 15 sql server are you mixing windows servers with SQL instance or windows server with sql instances or a combination of the two, i.e. 3 windows servers with 1 having 3 sql server instances, 1 having 5 sql server instances and 1 having 7 sql server instances.

Are these a mix include sql express, ##SSMEE (WSUS instance)
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

I would add the importance of having the databases grouped by the application working hours so you can maintain a SQL Server instance in a period of time that won't affect the users.
For example, an instance for databases 24x7, other for 24x5, another one for 8x5 and so on.
Marten RuneSQL Expert/Infrastructure Architect
From the top of my head, these criterias will need their own instances.
(Requirements that demand an instance):
Server Collation (only one per instance)
Applications that requires SA privilidges (cannot secure other DB on that instance from this application)
Applications that require special named instance name (yes seen those too)
Applications that require that their DLL is implemented as extended stored procs
Applications that require that they are installed on the same server as the SQL Server
Applications that require their database to be THRUSTWORTHY  (cannot secure other DB on that instance from this application)
Applications that is so important they demand their own HW to minimize downtime
Applications that require TDE encryption (dont want other app to be cryppled by the encrypted tempDB)

There might be other considerations, i e to get the support from the vendor, they require the application to have it's own instance.

And this goes without saying (still writhing it though), all applications that are consolidated support the same SQL Version.

Regards Marten
Aaron TomoskyDirector, SD-WAN Solutions

additionally if some of your "sql servers" currently are also running websites or something else that could be moved off, that will leave more resources for sql when you consolidate.

My usual answer to grouping is to follow security levels. Databases accessed by the same group of people/applications usually belong on the same server, considering load.
Ryan McCauleySenior Data Architect
I know this isn't directly on topic, but I want to make sure you're consolidating for the correct reasons - honestly, if you're doing it so that you don't have to buy any more monitoring licenses, it may be much more trouble than it's worth. If you're consolidating for other reasons and five is your target because of your license count, just be careful that everything is going to fit on those servers and that it's the appropriate target number. While you might save some money on licensing, consolidating the instances will take some serious effort, could run the risk of outages as part of the move, and will involve a lot of expense in terms of your time. Maybe your budget just doesn't permit the purchase, but I'd make a serious argument to augment the licensing before moving forward with an instance consolidation just to save a few dollars.

I've never found it problematic to manage extra instances (especially in newer versions of MSSQL where you can do policy-based management). It is some additional management, but you can push out consistent configuration to each instance, do centralized log collection, manage the SQL Agent jobs and backups from your workstation, and you could write some health check scripts if that's important and not covered by your monitoring tool.

Not trying to derail the effort, but just wanting to make sure you're planning appropriately.