Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Consolidation (25 servers)


SQL Server Consolidation (25 servers)

Author
Message
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
Our company plans to consolidate SQL Servers.
Currently there are about 25 servers. They try to minimize this number to 5-6.

The heaviest are Blackberry databases (over 100GB).
About five SQL boxes are used for Citrix XenApp, VMWare, Lotus Notes.
The rest are pretty light.
They just process very tiny applications w:-Pith maybe
1-5 GB db size (Fax, Invoice)

I suspect in some cases the servers will not be good candidates for consolidation. But I try to understand the decision making process.

Our Data Arcitect plans to consolidate to two SQL Servers in cluster.

What are the steps of the process?
Is there any order of steps you should follow?
Are there any books, articles on this topic?

Thank you,
Rob
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
There's a fair amount to consider when consolidating database servers. The two most important things are resource availability, and support issues.

On resource availability, it's pretty obvious. If you have 5 servers (for example) all running at 100%, and you try to consolidate onto 1 server that's twice as powerful (CPU, RAM, etc.), then it won't work. Too much workload for the hardware. I'm assuming you've already benchmarked all the servers to see whether this will matter or not.

Support issues mainly matter if you have third-party databases, like Citrix, Blackberry, etc., and they are being supported, even partially, by vendors. If one database, say the Citrix one, needs to have an SQL hotfix applied, but another application, the Blackberry one, won't support that same hotfix, then you can't have them on the same server. The only way to find out if that matters or not is to contact the vendors, and ask them about that kind of detail. Get their answers in writing, formally, so you can have documentation to back up the support contract, if it ever matters. Don't just ask over the phone, in other words.

There are other considerations, but they're covered under "moving a database to another server", and that's usually really easy. Connections will have to be updated in applications and websites, and so on. Make sure all database users have compatible server-level accounts. Make sure access security has the right isolation (don't use one account to access all the databases, if you can avoid that). Make sure collations and server-level settings and objects are set up correctly (linked servers, extended events/traces, automatic jobs and plans, SSIS packages, xp_cmdshell options, CLR options, filestream options, and so on).

There's a lot of work to do, but the first two things are the ones you have to answer first, because that will determine if it's even possible to consolidate, regardless of technical hurdles.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
Thanks GSquared,
Very useful suggestions. Thanks a lot.

A question.
I have not created benchmarks.
How do I create them?
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
Another thing to consider is available downtime maintenance windows for each application. Unless there is a time slot where it is acceptable for all the applications to be down at the same time, consolidation can be a big problem.
lptech
lptech
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 3276
Start with the CPU utilization for the SQL Servers. Go back over a 30 day period, and take note of daily processing cycles. Check month and year-end requirements. My experience has been that there will be a few servers that are maxing out, and a bunch that barely do anything. The latter are good candidates for consolidation.

Group the servers by business owner, and take note of SLA requirements. Try to come up with an SLA matrix. It may be technically feasible to consolidate onto two servers, but not if one or more applications has a maintenance window totally out of sync with the rest.

Get this information to the right person as early in the process as possible. It will be a lot easier to get the funds for an extra server or two that way, if required.

Review your license inventory. Depending on how you are currently licensed, you may (or may not) find it advataadvantageousvor one solution over another.

Here is an article from Microsoft on the subject:

http://msdn.microsoft.com/en-us/library/ee819082.aspx
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 380
A comment to SSCrazy reply.

Why it has to be acceptable for all applications to be down at the same time? What if we move databases slowly, one by one, to a new box?

Let's say today I am moving Citrix database at 7PM.
So I notify users that Citrix will be down for 3 hours, move database to a new SQL box, test Citrix with new connection string, done.
I don't have to restart or interrupt new SQL Server box.
So all other apps will continue to be up and running.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
RVO (10/24/2012)
A comment to SSCrazy reply.

Why it has to be acceptable for all applications to be down at the same time? What if we move databases slowly, one by one, to a new box?

Let's say today I am moving Citrix database at 7PM.
So I notify users that Citrix will be down for 3 hours, move database to a new SQL box, test Citrix with new connection string, done.
I don't have to restart or interrupt new SQL Server box.
So all other apps will continue to be up and running.


If you have to perform maintanance at the server level that requires SQL Server downtime, then all of the applications will be down.

"I don't have to restart or interrupt new SQL Server box."
Good luck on never having to restart.
vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
Another thing to look out for is security. Some databases may require CLR be turned on, some may not. Some servers may have cmdshell turned on, some may not.

I have a "food court" server with 40+ databases, from different vendors. As long as they don't require the above, and it is just a simple database, then it can go there. They usually have a virtual app or web server, and I just give them Management Studio and let their Windows VPN login have extra permissions on their specific database, that way they can go in and look and tweak the data if necessary. I don't give DB_Owner rights, they can back up databases. I've had vendors do unauthorized backups before. Took those rights away.

Also, any specific CLR assemblies to move, special jobs that have to run, linked servers, data loads, Reporting Services requirements, Reports, etc. (and any associated security, usernames, etc.)

These things have to be discovered, mapped, and analyzed to make sure nothing affects the others.

Again, just some things to think about.
Markus
Markus
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1583 Visits: 3678
Another thing to look at the database character set and collation. Make sure they are all the same. There 'can' be problems if you move a database with a different character set/database collation to another one. The reason is if there are a lot of queries hitting that database that does a lot of sorting in TEMP DB and the collation is different from the application db and TEMPDB. It can work but you need to test and verify this before you go live.

Also, if you are consolidating make sure the new server has enough memory too. Example, if you have 5 servers with 4 gig of memory each that is a total of 20 gig of memory. Don't put all of these dbs onto one server with 6 gig of memory and expect the same performance.



GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
RVO (10/24/2012)
Thanks GSquared,
Very useful suggestions. Thanks a lot.

A question.
I have not created benchmarks.
How do I create them?


I've found Confio Ignite (free version here: http://www.ignitefree.com/) very useful for benchmarking database servers. You can monitor type and quantity of wait states, and get a very good picture of what resources are being overutilized, underutilized, or are just fine (all 3 bears) on each server.

If, for example, every server is suffering from a lot of CPU/RAM waits, then consolidating onto common hardware is unlikely to be successful, because they'll all add together and be even worse.

On the other hand, a server with medium-high CPU waits, but with low I/O demands, might consolidate well with a server with medium-high I/O and not a lot of CPU, if the consolidated server has improved CPU + RAM and improved I/O, over each of the servers that are being consolidated. They won't collide with each other, in other words.

Best of all, is if you have a whole lot of servers with a lot of idle time on all of them. Add up the CPU waits and find that it's less than a single server can handle, add up the I/O demands and if a dual-channel fabric to a good SAN could take all of it easily, and you have a great opportunity for consolidation.

Ignite, or RedGate's SQL Monitor, or SQL Foglight/Spotlight by Quest, will help you find the numbers you are running on your current servers. I/O bottlenecks, CPU bottlenecks, network latency issues, and so on, will all be measurable with any of those.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search