none
database offline RRS feed

  • Question

  • Take a .Net Winforms App.. mix in a flakey wireless network connection, stir with a few users who like to simply pull the blue plug out occasionally and for good measure, add a Systems Admin that decides to reboot the SQL server box without warning now and again just to keep everyone on their toes.

    What are the suggestions and strategies for handling this sort of scenario in respect to :

    • Error Handling - for example, do you wrap every call to the server with a Try/Catch or do you rely on some form of Generic Error Handling to manage this? If so what does it look like?

    • Application Management - for example, do you disable the app and not allow users to interact with it until a connection is detected again? What would you do?

    • Moved by Bob Beauchemin Saturday, October 20, 2012 7:48 PM Moving to the forum for SqlClient questions (From:.NET Framework inside SQL Server)
    Saturday, October 20, 2012 3:50 AM

Answers

  • Hi Irelia,

    Answer depends on type of your application. There are applications that can work offline - Microsoft Outlook for example. Such applications doesn't treat connectivity exceptions as critical, they can save your work locally and synchronize it later. Another applications such as online games will treat communication problem as critical exception and will quit if connection gets lost.

    As of error handling, I think that you should control exceptions on all layers rather than relying on some general exception handling piece of code. Your business layer should understand what happened on lower layer (data access layer in our case) and respond correspondingly. Connection lost should not be treated as unexpected exception in my opinion. For good practices of exceptions management I recommend to take a look at Exception Handling Application Block:

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

    Concerning application behavior, you should answer yourself on the following question "Does my application have business value for customer in disconnected state?" In many cases it would be beneficial to end user to be able to continues their work in disconnected state. However such behavior tremendously hard to implement.

    Especially for your scenario Microsoft developed Disconnected Service Agent Application Block:

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

    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Edited by Iric Wen Saturday, October 20, 2012 4:48 PM
    • Marked as answer by Irelia Sunday, October 21, 2012 1:41 AM
    Saturday, October 20, 2012 4:47 PM

All replies

  • Pretty much you have to assume that the computer can go down any time, even in the most reliable installation.

    TRANSACTIONs are the main protection of data integrity in case of computer or server malfunction, hence the name Transact-SQL.

    It makes sense to use TRY-CATCH for catching errors on the server-side without involving the client.

    It also good idea to have logging tables which can be used for automatic / manual recovery of interrupted processes.

    Use SQL Server Agent because in addition to scheduled job execution you get nice history log.

    Programmatic job control: http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

    Use SSIS if you need full error control.

    On the app side, inform the user of interruption in server access, better than a frozen screen. Probably a good idea to batch the workflow and send the batch quickly when the server connection is up.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server

    Saturday, October 20, 2012 8:54 AM
  • Hi Irelia,

    Answer depends on type of your application. There are applications that can work offline - Microsoft Outlook for example. Such applications doesn't treat connectivity exceptions as critical, they can save your work locally and synchronize it later. Another applications such as online games will treat communication problem as critical exception and will quit if connection gets lost.

    As of error handling, I think that you should control exceptions on all layers rather than relying on some general exception handling piece of code. Your business layer should understand what happened on lower layer (data access layer in our case) and respond correspondingly. Connection lost should not be treated as unexpected exception in my opinion. For good practices of exceptions management I recommend to take a look at Exception Handling Application Block:

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

    Concerning application behavior, you should answer yourself on the following question "Does my application have business value for customer in disconnected state?" In many cases it would be beneficial to end user to be able to continues their work in disconnected state. However such behavior tremendously hard to implement.

    Especially for your scenario Microsoft developed Disconnected Service Agent Application Block:

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

    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Edited by Iric Wen Saturday, October 20, 2012 4:48 PM
    • Marked as answer by Irelia Sunday, October 21, 2012 1:41 AM
    Saturday, October 20, 2012 4:47 PM
  • Thanks Iric, that helps!
    • Edited by Irelia Sunday, October 21, 2012 1:41 AM
    Sunday, October 21, 2012 1:41 AM