Post reply

A bigger SQL Server, or a second SQL Server?

  • July 25, 2012 at 8:23 am

    #278540

    Hi,

    We have SQL Server 2008 R2 Web Edition. Our company is growing, so the capacity of our SQL is reaching its limits. We cannot upgrade the server with memory and CPU's because of the limitations in the license of the Web Edition.

    Two questions:

    * Is it better (performancewise and moneywise) to upgrade to a higher vesrion of SQL, so that we can also upgrade to more processors and memory; or is it better to just have a new server with SQL on it, and divide the customers over the two?

    * Assuming we would opt for having two SQL Servers, are there (online) books around to learn us how to manage them both efficiently, for example in order to keep Stored Procedures synced?

    Thanks,

    Ray

  • July 25, 2012 at 9:51 am

    #1517461

    The answer to this depends on many factors.

    1. What are your bottlenecks now? Memory? CPU? Blocking? Other?

    2. What's your budget?

    3. How much control do you have over the development / deployment process of your SQL Server code / schema? Is your organization willing to accept change in this area to accomidate a more controlled deployment process so you can ensure that your SQL Servers are in-synch.

    4. What other challenges do multiple SQL Server (reporting, replication, etc. etc.) are you ready to take on, do you have enough staff to do this?



    A.J.
    DBA with an attitude

  • July 25, 2012 at 10:07 am

    #1517474

    * Is it better (performancewise and moneywise) to upgrade to a higher vesrion of SQL, so that we can also upgrade to more processors and memory; or is it better to just have a new server with SQL on it, and divide the customers over the two?

    If this was my organisation I'd be more inclined to do a complete migration. That way, you won't have any data duplication issues, you won't need to update your SPs with linked server names, and you won't need to maintain two servers. I would definitely purchase at least standard edition, preferably Enterprise. Be aware if you're going up to Standard that there are still restrictions in place - no partitioning, no compressed backups (just two examples of many).

    * Assuming we would opt for having two SQL Servers, are there (online) books around to learn us how to manage them both efficiently, for example in order to keep Stored Procedures synced?

    As above - having two servers for the same job and splitting the data and SPs makes my skin crawl. In terms of books - there are many good SQL Server offerings around, I find the print editions are best as the information's always at your fingertips (or piled on your desk at least) and easily referenced. SQL Server 2008 Administration by Tom Carpenter (Publisher: Sybex) is pretty good, likewise SQL Server 2008 Internals and Troubleshooting by Brent Ozar et al.

    Note that if you are upgrading you may need to license per core rather than per processor. Make sure you price it out properly to avoid any nasty surprises from your license reseller.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • July 25, 2012 at 10:52 am

    #1517516

    derek.colley (7/25/2012)

    no compressed backups (just two examples of many).

    To be picky 🙂 that is only true for 2008 Standard. 2008 R2 allows backup compression in Standard ed.

    Jared
    CE - Microsoft

  • July 25, 2012 at 11:08 am

    #1517526

    It would probably cost far more for development/maintenance time to change the application to distribute the database across multiple servers than it would to upgrade to a more powerful server with standard or enterprise edition.

    However, I would first spend time tuning the system to make sure that the upgrade was absolutely needed.

  • July 26, 2012 at 12:56 am

    #1517744

    Thanks for all your answers. Here are some answers to raised questions:

    * Currently we have no real bottlenecks. Sometimes we see that the CPU is going up t0 80% and more, then we solve the issue in an appropriate manner. Usually we have CPU at 40% - 60% which I think is a good balance between performance and cost. But from Q3 this year we will start with some new clients that will almost double the users, so I have to act now.

    * Our budget is not that big. However, I made it clear to the financial guys that they can't have a system that runs smoothly as it does now, without investing in it. The deal is to keep it as cheap as possible (yeah, Dutch) but keeping the technical standard. Like not buying a BMW if a Volkswagen does the job too.

    * We are in full control of the SQL server; our hoster controls the hardware and VMWare enviroment, for the rest (including the Windows OS) we're on our own. All users are webusers; so nobody besides us writes stored procedures, runs queries etc.

    * We already have a report server, which is a second SQL Server with less power. Daily backups from the production server are restored here, and all reports are ran on this server (except reports that span a period of a month or less and include "today").

    I understand that having just one SQL Server has many advantages. One advantage of having two (or more) is: when a SQL Server has problems (CPU 100%, blocks, heaven forbid a crash); not all customers are affected.

  • July 26, 2012 at 1:18 am

    #1517749

    Do you run your sql server on a node with other VMs? if so, you should isolate your sql server so you get the most bang for the buck.

    For database servers, cpu is usually not the #1 issue. High cpu is caused by bad i/o 90% of the time in a database environment. What's your hardware setup? specifically your raid setup.

Viewing 7 posts - 1 through 6 (of 6 total)

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