Sql-server – Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server

azure-sql-managed-instancerestoresql-server-2017

I have three databases that I am migrating to Azure SQL Managed Instance. One is a Utility database, just a small collection of generic functions and procedures. One is the OLTP database for a web application. The third is a read-only archive of the OLTP database containing historic data for analysis and reporting as well as audit tables. I need to be able to restore these databases to on premise development and test servers. So far, I have been unsuccessful.

My first attempt was to use backup to Azure Blob Storage. But no matter what version of SQL Server I use locally, I always get an error that the Copy Only backup is from a newer version and restore is not supported.

Next, I tried Export Data tier application from SSMS v.18.1. Unfortunately, it never gets past the export. I get dozens of errors that read External references are not supported when creating a package from this platform. One of the greatest strengths of Managed Instance over Azure SQL DB is the ability to do cross-database queries. I have a number of views and procedures that link tables in the two databases. Additionally, some procedures in the OLTP database call the functions in the Utility database. Since MI is not Azure SQL database, you would think this wouldn't be a problem!

Searching Google, I found this question on Stack Overflow. So I tried the Visual Studio approach proposed as an answer. Although it appears to work, meaning it creates a multi-gigabyte file on my local machine, attempting to import that file using SSMS gives me an error that reads No data is present in this file. So then I tried the second possible solution using sqlpackage.exe. But that solution doesn't work for me either as it only references exporting the schema. And if I try to export the whole database with /Action:Export I get the same error message as SSMS gives.

I also examined this Database Administrators question. No help – that is what I originally attempted. If anyone has any other thoughts I would greatly appreciate hearing them!

Best Answer

Bacup-restore from a MI is not supported. The backpac extraction fails when it finds few dependencies or unsupported features.This is because SSMS as it verifies your extraction. Try using SSDT (Visual STudio) and you can skip verify extraction (this is by default unchecked) :

Open SQL Server Object Explorer

Connect to your MI. Right click the database and click : Extract Data tier application.

Select Extract Schema and data.

This will succeed.

Use the dacpac file created above to Deploy the database. Right click databases --> Deploy data tier application.

Data and schema :

Related Solutions

Sql-server – Restore DIfferential backup on azure managed instance

Question: Can I restore a differential backup on azure managed instance?

No. Borrowing from my answer here, Managed Instance only supports the restore of full backups; no log or diff. See this thread:

Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup

And the limited syntax diagram for RESTORE in the documentation for Managed Instance:

RESTORE Statements (Transact-SQL)

The docs imply that RESTORE ... FROM URL will implicitly add WITH RECOVERY and so there will be no opportunity for logs or diffs to be applied after the initial restore. So your downtime will be >= the time it takes to take a COPY_ONLY full backup, put it somewhere MI can access, restore it, and re-point your app(s). You might also want to read through this tutorial, for a different approach.

Sql-server – CLR Stored Procedure on Azure SQL Managed Instance error on execution: “Assembly in host store has a different signature than assembly in GAC”

One of the requirements / nuances of working with SQLCLR is that any assembly loaded into both the GAC and SQL Server must be the exact same version (i.e. down to the patch level, not just Major.Minor.*). So, your local instances might all be using 4.7.2 (or whatever), but if the Managed Instance is using 4.7, 4.7.1, 4.8, or anything else, then you will get that error. You would even get this error if one of your local instances was on a server running a different version of the .NET Framework than the one that you grabbed the System.Net.Http.DLL from. Of course, if all of these different instances you mentioned are running on the same physical server, then there is only one OS involved anyway, so of course they all work correctly ;-).

You will need to find out what specific version is being used on the Managed Instance and use that in your Azure install script. You shouldn't need to use the same version locally since the reference to a similar version that has the same signatures should work.

Now, how to find the exact Framework version on Managed Instance? The following query seems to be the best way to get that info:

SELECT olm.[name], olm.[file_version]
FROM   sys.dm_os_loaded_modules olm
WHERE  olm.[name] LIKE N'%mscoreei.dll%';

O.P. replied back that the query above returned a version of 4.7:2623.0 on the Managed Instance, and a version of 4.7:3190.0 on the local (on prem) system. This is the source of the "Assembly in host store has a different signature than assembly in GAC" error

So, in order for this to work, you need to find that specific version of that DLL and package it up to load into the Managed Instance.

P.S. This issue is one of the downsides to using unsupported .NET Framework libraries. They are going to be in the GAC (usually, right?) and if a Windows update (locally, or whatever they do to the host system of the Managed Instances) updates the version then your SQLCLR project stops working. And, if the new unsupported Framework DLL gets converted to a mixed mode assembly (both managed and unmanaged code), then you won't be able to load the new version into SQL Server and will need to re-code your project to not use that unsupported Framework assembly.

P.P.S. What are you using System.Net.Http for? If it's for web services stuff, then you should just use the System.Net.HttpWebRequest and HttpWebResponse classes. You will have to do a little extra coding to construct and parse the XML of the web request, but those classes exist in a library that's fully supported.

O.P. replied back with: "Correct, web services stuff."

Later, O.P. replied back with: "I've managed to successful deploy and execute a basic SQLCLR procedure on Managed Instance using just HttpWebRequest and HttpWebResponse, I think it'll be best to go down that line until Microsoft extend the list of natively supported libraries.."

P.P.P.S. You might want to take a look at SQL# (a SQLCLR library that I wrote), as there is a stored procedure that handles this, based on HttpWebRequest. It handles most (if not all) internally managed HTTP headers, allows for sending custom headers (including userid/password, if necessary), sending POST / GET data, and even some other stuff that most people usually leave out. Just a thought, though for full disclosure, while there is a Free version, INET_GetWebPages is only in the Full (paid for) version.

Best Answer

Related Solutions

Sql-server – Restore DIfferential backup on azure managed instance

Sql-server – CLR Stored Procedure on Azure SQL Managed Instance error on execution: “Assembly in host store has a different signature than assembly in GAC”

Related Question