Web snatching single user mode

  • lazy writer

    Ten Centuries

    Points: 1126

    Hi chaps,

    I've deployed a process which will replace copies staging database from staging server to live server. Due to limitations, I couldnt use replication or Logshipping to copy the data to live from staging. I have been using copy database task in SSIS and moving it to the live as a xyz_test and then I am rename it as xyz_live database which will reduces live database disruption towards web service less than one or two seconds.

    while swapping the dB names on live server xyz_test with xyz_live process will take xyz_live to single user mode but very rarely web service login is snatching the session of single user mode and then process failing by leaving the database in single user mode and disaster starts from there as no one can access via web app since the database in single user mode.

    I am thinking of disabling the web app login before taking the database into single user mode and bring it back enabled as soon as database names are swapped.

    I dont have UAT/ Test environment to perform any test - my only guess is SQL Srever will prevent me from disabling web app login as it will have live connections every time.

    As you know that we cannot use ALTER DB statements in exclusive transactions.

    do you think is there any viable solution ??

    any help would be appreciated.



  • Gail Shaw

    SSC Guru

    Points: 1004484

    Disable the login or shut down the web app.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • lazy writer

    Ten Centuries

    Points: 1126

    Thanks Gail,

    I think disabling the login would be quicker and simple, will do... 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply