Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Web snatching single user mode Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 2:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:36 AM
Points: 42, Visits: 1,054
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.

Ta
L
Post #1547212
Posted Tuesday, March 4, 2014 5:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:26 AM
Points: 42,990, Visits: 36,144
Disable the login or shut down the web app.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1547251
Posted Tuesday, March 4, 2014 9:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:36 AM
Points: 42, Visits: 1,054
Thanks Gail,

I think disabling the login would be quicker and simple, will do... :)
Post #1547408
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse