Restrict access to azure managed instance db before running migration

bbrown_GG

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?

Kendra_Little

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

bbrown_GG

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

Ben_Walker09

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_GG

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.

austinjoy

Use Azure Private Link to create a private endpoint for the managed instance, which allows you to access the managed instance over a private network connection. This will help ensure that only authorized users can access the database.

Use Azure Virtual Network service endpoints to restrict access to the managed instance to specific subnets within your virtual network. This will help ensure that only resources within those subnets can access the managed instance.

Use Azure Active Directory authentication to control access to the managed instance. You can use Azure AD authentication to require users to authenticate with their Azure AD credentials before they can access the database.

Use Azure Private Endpoint to create a private endpoint for the managed instance in your virtual network. This will help ensure that only resources within the virtual network can access the managed instance.

Use Azure Network Security Groups (NSGs) to restrict access to the managed instance to specific IP addresses or ranges. This will help ensure that only authorized users can access the database.

It's important to carefully consider your security needs and choose the approach that best meets your requirements. You may want to use a combination of these approaches to provide the most secure environment for your managed instance.

If you are considering migrating your on-premises or other cloud-based systems to Azure, you may want to consider working with an Azure migration consultant.