none
SQL Server design to minimize application system downtime during patching

    Question

  • Hi All,

    Can anyone please assist and suggest me in what sort of replication technology to choose and combine in order to minimize the multiple Application Server downtime when the OS for the SQL Server is patched ?

    this is the diagram that I have with the current budget and hardware that has been purchased by the management.

    As per the diagram above, I have been granted 1 Physical server in each site with no storage array replication.

    I haven't decide which replication technology or what version of the SQL Server to use 2008 / 2012, but for now the proposed solution is to use Prod - DR replication.

    The downtime that is acceptable is 15 minutes maximum per day for the failover and patching.


    /* Server Support Specialist */

    Saturday, July 20, 2013 9:18 AM

Answers

  • Seeing your current scenario transaction Log shipping would be good option to go with.For creating DR.

    >>The downtime that is acceptable is 15 minutes maximum per day for the failover and patching.

    Why do you need downtime every day for failover and patching its montly or quaterly activity.And AFAIK patching of OS as well as SQL server 20112 takes more than 15 mins.

    Only SQL server cluster can provide no downtime( u can say few seconds) during patching.

    So if i see ur current scenario it seems impossible for 15 mins downtime.For trn logshipping if you make ur read only DB online and if ur DB is small and less complex its possible to do within 15 mins but not guaranteed

    Asynchronous Mirroring would also be option but but it requires dedicated connection between Prod and DR ,if it fluctuates it will create issue.

    In both Mirroring and Logshpping You have to manually create logins and jobs after failover .


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, July 20, 2013 9:52 AM
  • You can (and should) use an AlwaysOn Availiability Group here. You absolutely do not need more servers for an AG.  Create a 2-Node Windows Failover Cluster with your two servers and add a File Share Witness somewhere in the production datacenter.  Then you can create an AlwaysOn AG for your databases.  Keep the DR replica in Async mode except just before patching, when you switch it to Sync mode and perform a manual failover.

    Log Shipping is a poor solution here as failover is painful and failback is worse.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, July 20, 2013 1:01 PM
  • David is totally right with the Approach of the Availability Groups... Simple, easy to Failover, quick to Failover, easy to fail back after patching, no more Hardware needed. Having a fileshare as a Witness is also something that normally is possible in any organization, but even if not the process is possible. Just reduce this to two nodes, only give the one in the Primary DC a Cluster vote and during patching Switch the voting to only have the vote in the DR site...

    What you have to consider for your logshipping Approach, beside the Problems of failback (which actually can be solved...) is that you end up with considerably more downtime as you do Need a "window of silence" to do the Failover, and you could end up with "split brain" Scenarios if you are not extra careful, as your Primary would stay online after the tail backup...

    Saturday, July 20, 2013 3:03 PM
  • >Can Always On do that for me ?

    Yes.  The only thing is that if your datacenters are too far apart to support an AlwaysOn synchronous replica all the time, you will need to switch the DR replica to synchronous before you start the patching process.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, July 21, 2013 12:20 AM

All replies

  • Seeing your current scenario transaction Log shipping would be good option to go with.For creating DR.

    >>The downtime that is acceptable is 15 minutes maximum per day for the failover and patching.

    Why do you need downtime every day for failover and patching its montly or quaterly activity.And AFAIK patching of OS as well as SQL server 20112 takes more than 15 mins.

    Only SQL server cluster can provide no downtime( u can say few seconds) during patching.

    So if i see ur current scenario it seems impossible for 15 mins downtime.For trn logshipping if you make ur read only DB online and if ur DB is small and less complex its possible to do within 15 mins but not guaranteed

    Asynchronous Mirroring would also be option but but it requires dedicated connection between Prod and DR ,if it fluctuates it will create issue.

    In both Mirroring and Logshpping You have to manually create logins and jobs after failover .


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, July 20, 2013 9:52 AM
  • Thanks for the quick reply Shanky, I was thinking to create a cluster and utilizing the SQL 2012 Always On technology, but in this case I wonder if it is possible with the design above with just one server node per each sites ?

    /* Server Support Specialist */

    Saturday, July 20, 2013 9:58 AM
  • Unfortunately its not possible...thats why i have you option of trn logshipping /mirroring.

    For going ahead with cluster and Always on at primary site you need two extra machines.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, July 20, 2013 10:34 AM
  • Exactly, that is why I'm hard pressed to find any way possible to create cluster server with the current hardware configuration as I'm not sure where to put the file share witness and creating the heart beat network. Adding one more node in each sites is not possible due to the licensing and budget limitation. Initially I proposed to create Always On DAG with SQL Server 2012 so that during the patching the production database in each sites can be handled by the other node. But I guess this is not possible anymore with the current situation above.

    /* Server Support Specialist */

    Saturday, July 20, 2013 10:57 AM
  • You can (and should) use an AlwaysOn Availiability Group here. You absolutely do not need more servers for an AG.  Create a 2-Node Windows Failover Cluster with your two servers and add a File Share Witness somewhere in the production datacenter.  Then you can create an AlwaysOn AG for your databases.  Keep the DR replica in Async mode except just before patching, when you switch it to Sync mode and perform a manual failover.

    Log Shipping is a poor solution here as failover is painful and failback is worse.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, July 20, 2013 1:01 PM
  • You can (and should) use an AlwaysOn Availiability Group here. You absolutely do not need more servers for an AG.  Create a 2-Node Windows Failover Cluster with your two servers and add a File Share Witness somewhere in the production datacenter.  Then you can create an AlwaysOn AG for your databases.  Keep the DR replica in Async mode except just before patching, when you switch it to Sync mode and perform a manual failover.

    Log Shipping is a poor solution here as failover is painful and failback is worse.

    David


    David http://blogs.msdn.com/b/dbrowne/

    David what i want to suggest was two nodes are required for sql cluster at minimum adding file share witness is also not possible as per diagram.Also as told by user he has no other physical machine so he cannot implement cluster,he has just one machine.

    So i suggested Trn logshippinh/mirroring as option in current scenarion where u have Hardware restriction I think trnlogshipping/mirroring is only option unless u increase machine and hardware


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, July 20, 2013 1:28 PM
  • David is totally right with the Approach of the Availability Groups... Simple, easy to Failover, quick to Failover, easy to fail back after patching, no more Hardware needed. Having a fileshare as a Witness is also something that normally is possible in any organization, but even if not the process is possible. Just reduce this to two nodes, only give the one in the Primary DC a Cluster vote and during patching Switch the voting to only have the vote in the DR site...

    What you have to consider for your logshipping Approach, beside the Problems of failback (which actually can be solved...) is that you end up with considerably more downtime as you do Need a "window of silence" to do the Failover, and you could end up with "split brain" Scenarios if you are not extra careful, as your Primary would stay online after the tail backup...

    Saturday, July 20, 2013 3:03 PM
  • Thanks for the advice PrinceLucifer, so you are suggesting that by using Database Availability Group (Always On) is possible in this case in my situation ? My understanding here is that I will need to create te file share witness in the Primary DC for the Active node in Primary site, while the primary DC in the DR site no need to be configured? I'd like to minimize the downtime and also avoiding the SQL server operation involvement if possible during the patching, just like I patch my Exchange Server 2007 Cluster (CCR), I just do the following: Apply the patch in Passive node in DR, then reboot Failover to patched DR node Apply the patch on the Passive node in production, then reboot Failover to the patched Production node. Can Always On do that for me ?
    Saturday, July 20, 2013 3:14 PM
  • >Can Always On do that for me ?

    Yes.  The only thing is that if your datacenters are too far apart to support an AlwaysOn synchronous replica all the time, you will need to switch the DR replica to synchronous before you start the patching process.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, July 21, 2013 12:20 AM