SQL Server Consolidation (25 servers)

  • 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

  • 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

  • Thanks GSquared,

    Very useful suggestions. Thanks a lot.

    A question.

    I have not created benchmarks.

    How do I create them?

  • 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.

  • 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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

  • Thanks for your message SGquared !

    I tried Ignite software but it can not connect to my SQL Server 2012 Express instance.

    Actually configuration instructions on their support website are not so straight forward. There are some TCP/IP setting s changes involved and the insctructions arenot very clear.

    But I would really like to try this tool...

  • SGquared,

    I was able to install IGNITE.

    Looks interesting.

    I just requested Trial Key to be able to view reports

    and drill down to a server.

    Thanks a lot !!

    Rob

  • Cool biz. That's a good first step towards determining if consolidation makes sense.

    - 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

  • Guys!

    I am trying to come up with a good testing strategy to test Applications connected to a consolidated SQL Server.

    Do we need to have a DEV Application server, install all applications there and start testing?

    Sounds like a time consuming process.

    For example, we have TRACKIT application from Numara Software. It tracks all HelpDesk tickets.

    By default all emails to HelpDesk@company.ca come to TRACKIT application. Do we need to create a HelpDesk1@company.ca and configure TRACKIT on DEV box to process all emails?

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

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