Consolidation of SQl2008R2 servers to SQL2012?

Question

Consolidation of SQl2008R2 servers to SQL2012?

KumarVelayutham

SSCrazy

Points: 2204
More actions
December 17, 2013 at 1:35 am

#301469

Dear Friends,
We have 25 SQL Server 2008 servers in our company and my management asked to give proposal for consolidating all sql2008 servers into SQL server 2012 servers.
Please find the attachment for list of SQL servers and suggest me your inputs..like which is suitable for high availability like Failover cluster,Log shipping or Mirroring,etc.
Also provide me the choice to select consolidation type as VM or Multiple Instances in 1 or 2 machines or Multiple databases in a single machine for this type of environment.
Any link or pointers for proposal will be more helpful for me...
Any help will be much appreciated..
Regards,
Kumar

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

I have to ask, how, possibly, from a listing of servers could I tell you which of the databases on those servers would need mirroring? How could I? Possibly? Further, if I told you which of those servers I think needs to have replication installed, would you really do it? Blindly? Surely I'm misunderstanding the question.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 2

Grant Fritchey (12/17/2013)
I have to ask, how, possibly, from a listing of servers could I tell you which of the databases on those servers would need mirroring?
I'm Collecting those information..
How could I? Possibly? Further, if I told you which of those servers I think needs to have replication installed, would you really do it? Blindly? Surely I'm misunderstanding the question.

Advice me to select consolidation type as VM or Multiple Instances in 1 or 2 machines or Multiple databases in a single machine for this type of environment based on the server list?

Regards,
Kumar

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Honestly, I can't.

How much CPU does each of those servers use? If one is maxed out on memory, we probably don't want to put it into a VM, but from a list of servers.... I can't say anything. Which of the servers is IO bound? I don't know. Are any of them basically only using a very small percentage of resources? Then they can be easily consolidated.

But it goes further. Consolidation isn't about servers. It's about databases on servers. You're trying to eliminate the server from your list by moving all the databases onto another server. So we need to understand the loads and requirements of the databases on those servers. Are there any that legally can't share resources? I can't tell.

There's just no real way to provide what you want from the information you've put forward.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 4

OK, I'll bite.

You are looking for a technical solution when you do not have a proper business requirement, or do not fully understand the business requirement.

You mention consolidation. How do you plan to consolidate?

- more databases per instance

- more instances per server

- more guest VM's per physical host

Each of these could work, but without knowing the type of data, your business vector or the workload of any of these databases or servers then it is impossible for us to answer.

If you have PCI-DSS compliance needs for example then you'll probably need to implement TDE, implementing that will also encrypt TempDB which would slow down access to TempDB for those other databases on that instance.

On the other hand if you pay for third party tools for backups or monitoring the more databases per instance the more value for money you get from those products - assuming you have good security models.

In terms of HA/DR, what is the required uptime of each instance and what is your budget?

I could suggest that you have geo-clustered servers with SAN replication, that would provide you with great uptime and availability. I do not know if that would work for your business though.

Database Mirroring may work, but that is being deprecated moving forward, so Availability Groups would be a better choice. I would ask you to read all the info on Availability groups (I can just imagine Grant's face here) to make sure that this would work with your database's as there are a number of caveats.

Hope that helps,

Rich

Hope this helps,
Rich

[p]
[/p]

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 5

Yes,,Thanks for the help..i will check the loads and requirements of the databases and inventory details on the servers.

Regards,
Kumar

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 6

No problem, in the meantime can I suggest that you patch all of your existing servers?

RTM isn't the way to go 🙂

Hope this helps,
Rich

[p]
[/p]

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 7

Based on my understanding,Microsoft assessment and planning tool will help to get the load and all details for each and every server.Is it right?

Regards,
Kumar

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 8

Not to the level of information you require to ensure that all of your business cycles for all of your databases will be able to meet their SLA's.

Ideally you need to baseline each server individually and look at their resource usage to see which if any could play nicely together.

In your initial post you did not mention if you would be using new or existing hardware. If you are buying new hardware this job becomes easier as you can do things like stress test your IO prior to putting any real data on there. i.e. you won't have lots of users complaining whilst running tests.

You can also replay traces from production (assuming you have no compliance issues with that) from boxes you are thinking of consolidating on your new box and monitor the levels of performance.

I did a talk on baselining at the PASS Rally's in Stockholm and Amsterdam last month. Not sure if it's available online yet, but you can see the slides here http://www.slideshare.net/SQLRich/the-day-after-tomorrow-why-you-need-to-baseline-sql-rally-2013-amsterdam

One thing I will say is that the figures in red for counters are now pretty old and I make a point of saying that Moore's law makes a mockery of them. It does give you a starting point though.

Hope this helps,
Rich

[p]
[/p]

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 9

KumarVelayutham (12/17/2013)
Based on my understanding,Microsoft assessment and planning tool will help to get the load and all details for each and every server.Is it right?

Not at all.

As Richard says, you'll need to establish a baseline. I'd suggest picking up a copy of the book Troubleshooting SQL Server[/url]. You can purchase a paper copy or download an e-book for free. It will give you a lot of information on how to gather metrics on the servers. From there you can start figuring out what kind of consolidations you might be able to do.

I'd strongly suggest that you tell your boss that you're WAY over your head on this one. Get a consultant in to help out. We're talking about a pretty major undertaking to gather all the metrics, evaluate them, make informed decisions based on the business needs and the technology needs and abilities, and then implement all of the above successfully. No offense intended, but you're not even maintaining patches on your servers, so I really don't think you're ready for this.

As a DBA, your primary job is to ensure the protection of the business's data. And sometimes, that means protecting it from yourself.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Just to give you an idea of the work involved in your request...

When I was still consulting I got a request to quote for consolidation of three SQL Servers into one. Three, not the 25 you have. I conducted a full day meeting with the business owner explaining what would go into the consolidation project, the notes on what would be required and the process took about 5 full A4 pages of notes. I quoted a period of 3 weeks to implement benchmarks, analyse results, test and finally implement the consolidation, that's above and beyond the selecting and purchasing of hardware for the final server.

What you're asking is not 10 minutes of work, it's likely to be a 3-4 month project for a top-level database consultant.

Edit: Since you want to upgrade as well, add another couple of months for testing of all of those applications on 2012 and fixing anything that breaks (something always does)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

Other key considerations that factor into consolidation:

Security - do any apps have fixed server roles like sysadmin as requirements, those usually have to be segregated

Tempdb Usage - in a consolidated server instance, you only get one, so if there is a lot of usage, that can equate to a lot of contention and problems if you don't plan it correctly

Cross Database Dependencies - do groups of databases have to be on the same server for an application to function?

SSIS/Reporting Dependencies - are there SSIS packages or Reports that access multiple databases, that would require changing if you consolidate?

For the VM stuff, unless you know your host configurations, other VM configurations, and the subscription ratios it is next to impossible to expect any kind of performance requirement would be met from a VM.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 12

I also agree with the others. It isn't as simple as we have 25 servers and want to consolidate down to one.. now do it..

You have to find out what each of these do, what usage they have, what is their most stressful time of day.... you might not be able to consolidate them all down to one. YOu also need to look and see if there are any linked servers from one to another. There are a TON of things to highly consider.

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 13

Why consolidate when you can virtualize and license SQL at the Host level? Maybe several new Cisco UCS-B200-M3 Blades and a nice SAN back end could be in your future (VMware clustered of course)?

We recently completed a yearlong project to “get off” a now unsupported HP Polyserve environment (using VMware) and have no regrets. Like the experts above recommend you can’t just throw this together and expect it to perform without proper design, analysis and testing; however, it is possible depending upon your business requirements and your current environment. An added benefit would be that hardware upgrades will be a piece of cake after you virtualize and you might be able to save some money in SQL licensing too. When you need more power add another B200 to the VM cluster and don’t scrimp on memory!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

DeWayne_McCallie (12/19/2013)
Why consolidate when you can virtualize and license SQL at the Host level?

That's a form of consolidation, multiple physical machines being consolidated into multiple virtual machines on a smaller number of hosts. You still need to do all the baselining, all the analysis, all the testing.

Part of a consolidation project would be to decide whether the consolidation is into multiple databases on an instance, multiple instances on a server or multiple VMs on a host or some combination thereof.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass