Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Consolidation (25 servers) Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 8:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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 with 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
Post #1376504
Posted Wednesday, October 24, 2012 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1376564
Posted Wednesday, October 24, 2012 12:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
Thanks GSquared,
Very useful suggestions. Thanks a lot.

A question.
I have not created benchmarks.
How do I create them?
Post #1376623
Posted Wednesday, October 24, 2012 1:05 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:39 AM
Points: 3,105, Visits: 11,494
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.
Post #1376636
Posted Wednesday, October 24, 2012 1:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:31 PM
Points: 226, Visits: 2,132
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
Post #1376646
Posted Wednesday, October 24, 2012 2:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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.
Post #1376680
Posted Wednesday, October 24, 2012 4:15 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:39 AM
Points: 3,105, Visits: 11,494
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.


Post #1376714
Posted Thursday, October 25, 2012 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:35 PM
Points: 191, Visits: 898
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.
Post #1377087
Posted Friday, October 26, 2012 6:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:34 PM
Points: 1,276, Visits: 2,803
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.



Post #1377530
Posted Monday, October 29, 2012 7:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1378233
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse