Load Balancing and Mirroring Solution With Microsoft Server Products RRS feed

  • Question

  • We are developing big web based application for a company and now I need to offer the server configuration for it. I will simply describe the required system below.

    System 1 (Main System)

    A-There will be multiple Web Servers (with IIS 7) that will handle web requests.
    B-There will be multiple Application Servers that will run web services.
    C-There will be multiple SQL Servers that will handle requests by application servers.

    System 2 (Failover System)

    This system will have the same configuration with Main System. There will be realtime mirroring between two systems. If any error occurs in Main System, we must wake-up Failover System in minutes without any data loss.

    - Systems must be capable to enhance capacity if needed.
    - How about SQL Server load balancing capabilities and storage management? If we use several sql servers, how we will be able to balance requests between them. Where will the database file be stored in? What about possible deadlocks?
    - Which microsoft licences we must buy? Do we need to pay for same licence fees for Failover System ?

    I need detailed information and recommendation about this scenario.

    Thanks in advance...

    Thursday, April 23, 2009 9:07 AM

All replies

  • Database mirroring and failover clustering (as well as log shipping for that matter) are not load balanced solutions. Only one instance is actively processing SQL Server requests at a given time, and in the case of clustering, the entire instance would fail over to another node or with log shipping and database mirroring, a switch to the standby is the only time it would be able to be actively used other than for read-only purposes. SQL Server does not have a feature like Oracle's RAC.

    If you need to do something like load balancing for your main database, you're going to have to build it into your application and do something like partitioning your databases or data dependent routing to make it all work. SQL Server can scale out, but it's something you take into account into the application architecture as well as SQL Server.

    If your "standby" system is being actively used, then it needs a full license. Microsoft is pretty clear about this. So if you're scaling out SQL Server, every server/instance will need a proper SQL Server license. Now, if your standby server is purely just that - for disaster recovery - there are other rules which may or may not apply. The best place to ask is your Microsoft licensing rep for what your architecture will wind up being as they will be the final word.

    You must also define data loss - is it when the end user hits send? If so, your application must account for that. Database mirroring and failover clustering both will have the least, and possibly no, data loss. Failover clustering is transactionally consistent to the point of failure, meaning when the databases go through the recovery process, any incomplete transactions will be rolled back. Database mirroring, depending on if you are mirroring synchronously or asynchrnously, may mean you have literally no data loss, but the reality is that there could be  a chance that, for example, something could have failed during the synchronous process when everything "blew up", so that last transaction did not get applied at the mirror.
    Allan Hirt Blog: http://www.sqlha.com/blog Author: Pro SQL Server 2008 Failover Clustering (Apress - due out June, 2009)
    Thursday, April 23, 2009 4:56 PM
  • As previously mentioned, SQL server does not handle load balancing like Oracle's RAC.  You have a few options if you are tyring to design an application ot distribute the processing loge between multile SQL servvers.  This can be doen via database partitioning.  This link pertains to partitioning of SQL server 2000 but it will give you a better grasp of the concept. http://msdn.microsoft.com/en-us/library/aa216844(SQL.80).aspx

    Failover clustering sounds like it is more the solution that you are looking for.  Failover clustering would put two servers in a Windows failover cluster.  In the event that the SQL service or the server itself fails the cluster will failover to the second server.  Here is more information on SQL Server Failover Clustering.  http://msdn.microsoft.com/en-us/library/ms189134.aspx

    We are currently using a SQL Server 2008 fileover cluster on Windows Server 2008.

    If you have an Enterprise Agreement with Micorosoft you are usually allowed to have a 'cold spare'.  This means you can have a backup of your current system ready to go in the event that the main system goes down.  If you have the server up and running at all times this is a 'hot spare' and you will have to have licensing for that.  Check with your license provider for the particulars of your EA.

    Hope this helped.

    Thursday, April 30, 2009 4:21 PM