Balancing scalability with availability RRS feed

  • Question

  • We are beginning to design a new application with SQL Server 2005.  Our current production environment is slated to be two SQL Server 2005 machines with the databases residing on an EMC SAN.  We have requirements to both have automatic failover between servers for availability and also be able to balance the load over two hot servers for scalability.

    Can anyone point me in the right direction for things I need to consider in order to be able to implement both of these requirements?  Can I implement database mirroring (for failover) and transactional replication (for balancing) given the hardware configuration I'v mentioned?  Is more information needed?  Where should I turn next?

    I am coming off a mainly Oracle background for the last ten or so years with a smattering of SQL Server mixed in.   I've tried to hit the ground running on this project, but sometimes find myself hitting the wall running instead.



    Friday, June 16, 2006 8:17 PM


  • That is not correct.

    The instance hosting the mirror database is NOT offline.  It is online and running otherwise transactions would not be able to reach it.  The mirror database is inaccessible, because it is in a recovering state.

    As far as clustering goes, the secondary node is also not offline.  It is online and functioning, otherwise, there would be no way to failover to the node.  There is only one copy of the database within a cluster, so you can't use the secondary node to execute anything.

    The combination that is being asked for - automatic failover along with scalability is not possible.  Failover clustering will automatically failover, but is not a scalability solution.  Database Mirroring running in high availability operating mode will automatically failover, but is not a scalability solution.  However, if your applications are not coded to intercept the disconnect and then reconnect to the cluster, it doesn't matter if the cluster automatically fails over, because only those applications forming new connections will find it.  If your applications are not coded to utilize the new connection library in ADO.NET that shipped with VS 2005, then your applications are not going to automatically failover to the mirror when it gets promoted.

    Replication is your closest bet to getting both fault tolerance as well as scalability.  If your applications are built with a connection library that can detect that the server they are connected to is offline and then reform a connection to a secondary server and continue issuing transactions, then you can accomplish the basic goals.  Your application then handles the automatic failover while the replication engine keeps everything synchronized to allow scalability.  The basic options here are transactional with queued updating subscribers and merge.  I would NOT configure a bi-directional or peer-to-peer architecture unless your are going to have full control over your applications and can guarantee that you can never have a conflict generated.

    Sunday, June 18, 2006 4:29 AM