MS SQL Server Consolidation


I have a lot of databases running on MS SQL SERVER.    Some of them using different versions such as  : 2000 Enterprise Ed., Standard 2000, 2008 R2 Standard, 2008 R2 Express Ed., 2005 Standard, 64 bit version, 32 bit version, Express edition, and some running on VM machines.

The whole environment is a mess, i  really need to do some sort of consolidation.   A better way to manage all the databases, improve backups  management,  also save some money with licenses, etc.

I can use Virtual Machines and open to any ideas for the implementation.

Anyone have any recomendation or idea that can help ?

joe_echavarriaDatabase AdministratorAsked:
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.

Scott PletcherSenior DBACommented:
I'd move them all to the highest-level instance you have, in this case 2008 R2 Standard.  You'll need to add RAM to that box, and possibly CPUs as well (or just carve out a new, bigger virtual box).  Might as well set up DSN entries to point all the existing instance references to the new instance.

If you have any db name collisions, you'll obviously have to plan for that ahead of time.
joe_echavarriaDatabase AdministratorAuthor Commented:
@scottPletcher  -- I also have 2008 R2 Enterprise Ed, and 2008 R2 RTM Exp. Edition.
Aneesh RetnakaranDatabase AdministratorCommented:
I am currently doing a similar project for my client.  
first you need to check with sql upgrade advisor and check whether these databases will be compatible with higher versions of sql server (if not that will be a challenge,  you can restore with the existing compatibility or ask the vendor / developer to update the code for newer versions )
Decide how many instances / versions you need to keep, depending on that, you need to create some more VMs
on a test environment, move the database and do a thorough test, if everything goes well, move it to production.
Its better to make a list with of databases , corresponding applications, webservers, end users, contacts etc to notify them about the upcoming changes
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

joe_echavarriaDatabase AdministratorAuthor Commented:
@Aneesh --- what will be your recomendation regarding clustering, physical servers, VMs , and MS SQL Server versions ?

And how with any recomendations i will save on licenses  costs ?
Aneesh RetnakaranDatabase AdministratorCommented:
That depends on how much your client can afford. In my case the client just wants a couple of VMs.
Physical servers have their own advantages. Everything depends on how much the business can spend and how critical the data and how soon you need to recover the data  

for how much you can save by using VM check this link
Vitor MontalvãoMSSQL Senior EngineerCommented:
Usually with a SQL Server consolidation project there's also a SQL Server migration and upgrade project so are you considering in having new servers with MSSQL 2012 or 2014?
Also, what do you have in mind for High Availability and Disaster Recovery solutions?

Depending on those answers we can provide others scenarios for you.

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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
it depends on your environment, business needs, sql servers inventory : it is your start point,
 plus clear understanding what is running on each server and what for databases there

For example: .. if you have some old vendor supported application that is running on, as an example, sql server 2000
you may need to check with the vendor about their "supported"  sql server version\edition

After reviewing your Sql servers inventory you may find:
--  all servers can be decommissioned,
-- some databases are no longer active and can be decommed too
-- some sql servers must stay as they are until vendor readiness for upgrade
-- some sql servers have a very unique settings that can not be combined with another servers
- some sql server have a very sensitive data and must not allow to be in a "mixed" db environment
-- some sql servers can not be on VM
-- etc
about upgrade of to higher sql server version (edition)
it depends on your developers\vendors ability to adjust code -certify such upgrade
normally started on non prod servers

there are several elements involved; Upgrade Adviser  (Use Upgrade Advisor to Prepare for Upgrades

and possibility to upgrade
Supported Version and Edition Upgrades
joe_echavarriaDatabase AdministratorAuthor Commented:
Thanks guys for all your tips and recommendations.   For now we are evaluating and will take the decision later on.
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
Microsoft SQL Server

From novice to tech pro — start learning today.