Restrict access to azure managed instance db before running migration

bbrown_GGbbrown_GG Posts: 3 New member
edited August 14, 2020 5:52PM in SQL Change Automation
We are using SQL change automation to track changes and generate migration scripts.
Before moving to an Azure Managed Instance db (compatibility set to SQL 2014), we were able to run this command to kick out other users and end their transactions:
 ALTER DATABASE  [$(DatabaseName)] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

But after migrating to an azure managed instance that command results in an error:
"This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again."

How can we end open sessions transactions to allow the migrations to proceed safely?
Tagged:

Answers

  • Kendra_LittleKendra_Little Portland, OR, USA Posts: 68 Gold 1
    Hi there,

    Not sure if this bit is clear or not -- Azure SQL Managed Instances simply don't allow you to put the database into restricted user or single user mode. This is one of the limitations of Managed Instances. We don't have a way to work around that because Microsoft doesn't allow it to happen.

    Generally I would recommend that people work to be able to run deployments online without killing off all other transactions when possible. Is this an option in your case? If not, I would be curious to know what the specific pattern you have which prevents it -- there might be a more graceful way to work around the problem.

    If you do wish to kill off all active transactions, one workaround you may find suggested on the internet is to find another statement which supports ROLLBACK IMMEDIATE (Microsoft refers to this as the termination clause), which is supported on Managed Instances and to use that other commands and to turn that option on and then off.

    A list of commands that support the termination clause is here.

    However, this is definitely a hack and I would be wary of this approach.

    I personally have sometimes experienced a deadlock on some "ALTER DATABASE" commands which weren't pleasant to handle, and you can't put multiple ALTER DATABASE commands inside a transaction. So if you did hit a weird deadlock on the second command, you could end up putting the database into an odd state.

    I know this isn't a complete answer, but hoping to get more info on why you aren't able to do online deployments.

    Kendra
    -------------------------------
    @Kendra_Little
    DevOps Advocate at Redgate
  • bbrown_GGbbrown_GG Posts: 3 New member
    Hello Kendra, thank you for the quick and detailed response.

    I did see that restricted user mode is not an option in Managed Instances.  

    The requirement to kill active transactions is simply a long standing preferred practice on this project, used with maintenance windows at deployment time.  It's been there for years and I'm not personally aware of a the specific pattern requiring it- which doesn't mean there isn't one.  I don't have direct access to the db in question to check its options.  Possibly this is simply a heavy handed way to avoid deployment time problems. 

    I can revisit the 'offline' practice with the stakeholders, but of course they will want to know what the options for achieving a safe deployment are (online vs offline vs ?) and the pros and cons of each option.  Does Redgate have recommendations or best practices for when and how to run the migrations generated by Change Automation? (I've skimmed some of the redgate docs, like Automated Deployments , but not seen this topic covered)

    (FYI: while I know how to query in SQL and look up concepts I would not call myself and dba and we don't have a dba directly on staff)

    After looking at the set options page with termination clauses you linked to I see why you warn that it's a hacky solution- we would be toggling a setting we don't need like 'change_tracking_option' back and forth which would have side effects.

    Thanks,
    -Ben
  • bbrown_GG said:
    We are using SQL change automation to track changes and generate migration scripts.
    Before moving to an Azure Managed Instance db (compatibility set to SQL 2014), we were able to run this command to kick out other users and end their transactions:
     ALTER DATABASE  [$(DatabaseName)] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

    But after migrating to an azure managed instance that command results in an error:
    "This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again."

    How can we end open sessions transactions to allow the migrations to proceed safely?

    When to migrate to the cloud is a crucial business decision. Take advantage of azure migration services to help safeguard your business from ransomware and human errors with Azure backup. It’ll be better if you take the support from an azure migration consultant.


  • bbrown_GGbbrown_GG Posts: 3 New member

    In the end we ended up skipping this end-other-sessions step because, on an azure managed instance, its value was determined to be less than the potential harm.

    Details:
    We considered creating scripts for finding and ending/killing existing sessions for any user other than the one running the upgrade.  But those commands could run into problems(deadlocks) with the managed-instance processes which, well, manage the db. A lock up of that type would result in a locked database that could not be fixed by ourselves, only by Azure support because only they can access the OS running the SQL server (this is the downside of the managed instance concept).  

    The risk in skipping this 'end-other-sessions' step is that a table access deadlock occurs, which would be annoying, but wouldn't take down the whole db, and should be fixable by ourselves without needing OS level access and thus Azure support.
Sign In or Register to comment.