none
Deciding on physical server or cluster instance

    Question

  • Hello,

      I am working on a checklist for customer use which will aid them in whether to request a stand alone SQL server (physical or virtual) or an instance on our SQL cluster.

      So far I am only finding questions and recommendations on hardware to be used for stand alone systems or VM hosts.  Can someone point me to a posting of questions to ask customers so that they can determine the best SQL server/instance solution for them?

    Thanks in advance,

    campcookie

    Monday, August 01, 2011 5:13 PM

Answers

  • As RamJaddu stated earlier you will need to take into account SLA's, RPO's - Recovery Point Objectives, RTO's Recovery Time Objectives, Does your application require High Availability ? Does your Application Require Disaster Recovery ?

    A physical cluster does provide you the ability to give you application High Availability but this comes with added cost due to the cost of having mulitiple physical servers plus the associated bits and pieces.d You can also achieve HA with the technologies also mentioned by RamJaddu. The other side of things to look at from a HA point of view is if you have a virtual environment. Depending on the environment the use of VMotion can give you what you need.

    If you need DR then that adds more cost due to having to have a second site for your hardware to reside  in.

    Unfortunately there is no magic answer as to the requirements of the database. It comes down to vendor requirements (if it is a vendor application), how important is the data, how long can the business accept in loss of data ? can the business continue with a 2 - 24 hour down time in the server ? Depending on the size of your databases as well as the number of databases can also impact on the Edition of SQL Server you would need to have installed to take advantage the functionality. As you start to provide these HA options the bottom line is they all come down to $$$$$. How much will it cost Vs How much can the business afford to lose if there is down time.

    I hope this helps.


    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    • Proposed as answer by Peja Tao Wednesday, August 03, 2011 7:37 AM
    • Marked as answer by Peja Tao Monday, August 08, 2011 9:13 AM
    Monday, August 01, 2011 9:11 PM

All replies

  • You should ask what is Customer SLA requirement.
    SLA = Service Level Agreement. SLAs are agreements between you and your customers. If you're a DBA, then your customer is typically the company for whom you work. Examples of SLAs are:
    In the event of a corruption, or other disaster, the maximum amount of data loss is the last 15 minutes of transactions.
    In the event of a corruption, or other disaster, the maximum amount of downtime the application can tolerate is 20 minutes.
    Usually, it's a combination of SLAs such as those above.
    Here's the catch - an SLA is really more than just an agreement between you and your customers - it's more like a contract that you're obligated to meet. This means that if you're a DBA with zero-downtime and zero-data loss SLAs, you need to make sure that in the event of a corruption you can actually meet those SLAs. The obvious thing is that if the SLAs cannot be met then the business will suffer downtime and data loss. The not so obvious thing is that if you're the one who agreed to the SLAs in the first place, and when the disaster strikes, the capabilities of the system are far below the SLA's requirements, then you could lose your job - resume/CV time - I've heard of it happening...
    here are some high availability options
    Database-mirroring 
    Use a rolling upgrade process to upgrade database instances in a database-mirroring session.
    Take advantage of write-ahead functionality on the incoming log stream on the mirror server.
    Use page read-ahead capability during the undo phase to further improve performance.
    Provide reporting capabilities with a database snapshot as a source for reports on the mirror server.
    Failover clustering
    Enable failover support by sharing access among nodes and restarting SQL Server on a working node.
    Increase scalability with support of up to 16 nodes in a single failover cluster.
    Support a rolling upgrade process for servers participating in a failover-clustering configuration.
    Peer-to-peer replication
    Replicate changes in near real time, while all databases also handle their primary responsibilities.
    Boost scalability, availability, and processing capacity by configuring applications to use peers and to fail over to another peer.
    Protect against accidental conflicts with built-in conflict detection.
    Increase availability by dynamically adding a new node to an existing topology.
    Log shipping
    Provide database redundancy by using standby servers to automatically back up transaction logs.
    Increase availability by providing multiple failover sites.
    Reduce the load on the primary server by using a secondary server for read-only query processing.
    Use a rolling upgrade process to upgrade database instances in a database-mirroring session.
    Take advantage of write-ahead functionality on the incoming log stream on the mirror server.
    Use page read-ahead capability during the undo phase to further improve performance.
    Provide reporting capabilities with a database snapshot as a source for reports on the mirror server.
    Failover clustering
    Enable failover support by sharing access among nodes and restarting SQL Server on a working node.
    Increase scalability with support of up to 16 nodes in a single failover cluster.
    Support a rolling upgrade process for servers participating in a failover-clustering configuration.
    Peer-to-peer replication
    Replicate changes in near real time, while all databases also handle their primary responsibilities.
    Boost scalability, availability, and processing capacity by configuring applications to use peers and to fail over to another peer.
    Protect against accidental conflicts with built-in conflict detection.
    Increase availability by dynamically adding a new node to an existing topology.
    Log shipping
    Provide database redundancy by using standby servers to automatically back up transaction logs.
    Increase availability by providing multiple failover sites.
    Reduce the load on the primary server by using a secondary server for read-only query processing.
    hope this helps you....

    http://uk.linkedin.com/in/ramjaddu
    Monday, August 01, 2011 5:47 PM
  • Thanks for the quick response.

    I have a good grasp on supporting the SLAs associated with providing either solution.  I think my question may have been better stated from the customer's prospective by asking "when should I use an individual physical or virtual SQL server over an instance on an SQL server cluster and vice versa?"

    What I am looking for is the decision points used to determine where to put my database.  Is there a default recommendation to go with an instance on an SQL cluster and then an additional set of questions used to determine if a stand alone system, whether physical or virtual is a better solution?

     

    Monday, August 01, 2011 6:01 PM
  • As RamJaddu stated earlier you will need to take into account SLA's, RPO's - Recovery Point Objectives, RTO's Recovery Time Objectives, Does your application require High Availability ? Does your Application Require Disaster Recovery ?

    A physical cluster does provide you the ability to give you application High Availability but this comes with added cost due to the cost of having mulitiple physical servers plus the associated bits and pieces.d You can also achieve HA with the technologies also mentioned by RamJaddu. The other side of things to look at from a HA point of view is if you have a virtual environment. Depending on the environment the use of VMotion can give you what you need.

    If you need DR then that adds more cost due to having to have a second site for your hardware to reside  in.

    Unfortunately there is no magic answer as to the requirements of the database. It comes down to vendor requirements (if it is a vendor application), how important is the data, how long can the business accept in loss of data ? can the business continue with a 2 - 24 hour down time in the server ? Depending on the size of your databases as well as the number of databases can also impact on the Edition of SQL Server you would need to have installed to take advantage the functionality. As you start to provide these HA options the bottom line is they all come down to $$$$$. How much will it cost Vs How much can the business afford to lose if there is down time.

    I hope this helps.


    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    • Proposed as answer by Peja Tao Wednesday, August 03, 2011 7:37 AM
    • Marked as answer by Peja Tao Monday, August 08, 2011 9:13 AM
    Monday, August 01, 2011 9:11 PM