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.