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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 ArchitectCommented:
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 TomoskySD-WAN SimplifiedCommented:
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 McCauleyEnterprise Analytics ManagerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.