locked
Mirroring with Failover without using SAN or shared storage ? RRS feed

  • Question

  • Hi Everyone,

     

    I have a 2 application servers with Windows 2008 r2 enterprise Servers running on each. I would like to install SQL 2008 r2 standard server on both.( I dont know if to install SQL on VM or not)

    My question is : I want to achieve failover with database mirroring without SAN storage.  So i want my database to be mirrored at all times , and if One Application server crashes my clients can swith to another Windows Application server using Failover Clustering and also my database session is restored on the 2nd Server. (All of this without using external Storage - SAN). Is this scenario possible.

    So what i want is that i want to use the physical HD of each server rather than shared storage for storing DB

    I have read a several forums but I cant understand how we can achieve Database Mirroring with Failover Cluster.

    Would be great if someone could help me with it.

    BR

     

    Wednesday, June 15, 2011 1:31 AM

Answers

  • Database Mirroring and Failover Clustering are two entirely different availability options in SQL Server 2008 R2.  Only database mirroring can do what you describe without 3rd party products.  Have a look at this article for a great overview of Database Mirroring with SQL Server 2008 R2.

    http://msdn.microsoft.com/en-us/library/ms189852.aspx

    If you want/need to cluster SQL without shared storage, you will be implementing a multisite cluster and will need a 3rd party replication solution such as SteelEye DataKeeper CLuster Edition or DoubleTake.

    The biggest difference is that database mirroring is configured per database while failover clustering protects the entire SQL instance.


    David A. Bermingham, MVP Senior Technical Evangelist, SIOS Technology Corp
    Wednesday, June 15, 2011 3:49 AM

All replies

  • Database Mirroring and Failover Clustering are two entirely different availability options in SQL Server 2008 R2.  Only database mirroring can do what you describe without 3rd party products.  Have a look at this article for a great overview of Database Mirroring with SQL Server 2008 R2.

    http://msdn.microsoft.com/en-us/library/ms189852.aspx

    If you want/need to cluster SQL without shared storage, you will be implementing a multisite cluster and will need a 3rd party replication solution such as SteelEye DataKeeper CLuster Edition or DoubleTake.

    The biggest difference is that database mirroring is configured per database while failover clustering protects the entire SQL instance.


    David A. Bermingham, MVP Senior Technical Evangelist, SIOS Technology Corp
    Wednesday, June 15, 2011 3:49 AM
  • Be advised that automatic failover with database mirroring does need a thrid node as the Witness, otherwise it will not work. Also be advised that database mirroring has certain implications that you need to keep an eye on. (e.g. with SQL 2008r2 you can't ensure that two databases fail over together. So if you use cross database queries you could have a problem, just to name one out of quite a few.)

    Lucifer

    Wednesday, June 15, 2011 4:53 AM
  • Hi check the provided link in this post.This will help you Choose your best Disaster Recovery plan
    Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum

    SqlserverBlogForum

    ↑ Blog Animator

    Thursday, June 16, 2011 7:35 AM
  • Clustering

    Failover clustering provides high-availability support for an entire instance of SQL Server. A failover cluster is a combination of one or more nodes, or servers, with two or more shared disks. Applications are each installed into a Microsoft Cluster Service (MSCS) cluster group, known as a resource group. At any time, each resource group is owned by only one node in the cluster. The application service has a virtual name that is independent of the node names, and is referred to as the failover cluster instance name. An application can connect to the failover cluster instance by referencing the failover cluster instance name. The application does not have to know which node hosts the failover cluster instance.

    A SQL Server failover cluster instance appears on the network as a single computer, but has functionality that provides failover from one node to another if the current node becomes unavailable. For example, during a non-disk hardware failure, operating system failure, or planned operating system upgrade, you can configure an instance of SQL Server on one node of a failover cluster to fail over to any other node in the disk group.

    A failover cluster does not protect against disk failure. You can use failover clustering to reduce system downtime and provide higher application availability. Failover clustering is supported in SQL Server Enterprise and SQL Server Developer, and, with some restrictions, in SQL Server Standard. 

    Mirroring

    Database mirroring is primarily a software solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database.

    The mirror database is created by restoring a database backup of the principal database with no recovery. This makes the mirror database is inaccessible to clients. However, you can use it indirectly for reporting by creating a database snapshot on the mirror database. The database snapshot provides clients with read-only access to the data in the database as it existed when the snapshot was created.

    Each database mirroring configuration involves a principal server that contains the principal database, and a mirror server that contains the mirror database. The mirror server continuously brings the mirror database up to date with the principal database.

    Database mirroring runs with either synchronous operation in high-safety mode, or asynchronous operation in high-performance mode. In high-performance mode, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. In high-safety mode, a committed transaction is committed on both partners, but at the risk of increased transaction latency.

    In its simplest configuration, database mirroring involves only the principal and mirror servers. In this configuration, if the principal server is lost, the mirror server can be used as a warm standby server, with possible data loss. High-safety mode supports an alternative configuration, high-safety mode with automatic failover. This configuration involves a third server instance, known as a witness, which enables the mirror server to act as a hot standby server. Failover from the principal database to the mirror database typically takes several seconds.

    Its entirely depend on business SLA choise either one of above.... protest entire instance Cluster would be best choice .... protect single database then mirroring would be best choice

     


    http://uk.linkedin.com/in/ramjaddu
    Thursday, June 16, 2011 10:55 PM