By: Joe Gavin | Comments (2) | Related: > Azure Backup and Restore
Problem
You need to migrate a database from Azure SQL Database to an on-premises SQL Server. Yes, we are taking a database currently on Azure SQL Database and moving it to an on-premises SQL Server.
Why? I needed to test this scenario because:
- We're the vendor's only client with the database on Azure SQL Database, so of course Azure is blamed as the source of any issue
- There is an upcoming upgrade of the application, so it's been decided we want to move the database on-prem at that time
- Due to the small, < 1 GB size of the database and it not having much activity it will probably go on our SQL Server 2012 'Food Court' (See Learn to Speak DBA Slang for the definition of Food Court and some other entertaining DBA slang definitions. I learned quite a few new ones.) so it was tested on SQL Server 2012
Solution
Going from on-premises SQL Server to Azure SQL Database or Azure SQL Database to on-premises SQL Server is basically the same thing. We know that the traditional way of simply backing up the database from one server and restoring to another is not supported in Azure SQL Database, so we need a way to export the object creation scripts and export / import the data.
The basics steps to achieve the above are:
- Extract the data definitions of each object on source side
- Bulk copy the data out of each table to files
- Recreate the objects on the target side from the extracted definitions
- Bulk copy the data back into the newly created tables
Let's walk through the example.
I presumed it would be necessary to make changes to the source database at some point in this process, so the first step was to get a copy of the production database over to a test server. I created a copy of the production database on prodazuresqldatabaseserver on the test server, testazuresqldatabaseserver using my favorite new Azure SQL Database 'CREATE DATABASE ' option, 'AS COPY OF' to create a transactionally consistent copy of production in test.
CREATE DATABASE mydatabasesource AS COPY OF prodazuresqldatabaseserver.mydatabase
There are a few options to do this, but SQL Server Management Studio, being my familiar friend, seemed to be the best way to facilitate the migration so that's what I chose.
Let's get started.
Right click on source database and select Tasks > Export Data-tier Application...
On the below screen click Next.
Choose a directory, name the file and Save the *.bacpac file locally.
Click Next.
Verify settings and click Finish.
I was hoping to see all green checkmarks and a list of 'Success' in the Results column, but as we can see, I did not.
Clicking on an Error link under any of the results shows this:
The errors tell me there are two stored procedures referencing the database name in the FROM that appears to be a cross database query. This, of course is not supported in Azure SQL Database. I wasn't too concerned as this is a test copy of the production database, so I went in and removed the database name references from both stored procedures. As a side note, I still can't figure out how the stored procedures were created in the first place. I'm just guessing it may have been created on Azure SQL Database V11 at the time and V11 allowed the create to work. This is just a guess and I may never know the answer but it's not worth worrying about.
Now we can go back to the top and start again at Export Data-tier Application. This is more like it. All steps have succeeded.
Press Close, and we're ready to import the *.bacpac file to a database on our target server.
Import Database to On-Premises SQL Server
Right click on Databases and select Import Data-tier Application...
Click Next.
Choose the .bacpac file that was created above and click Next.
Did not want to see this coming, more errors. Now what?
The error tells me it failed trying to create a Databased Scoped Credential, that's used for auditing and is not supported pre-SQL Server 2016.
I wasn't too concerned with stopping auditing briefly on the test server as it is test and that was the only way I could see to drop the Credential. To stop, go to the Security section of the Azure SQL Server panel and select Auditing in the Azure Portal.
Set this to Off and click Save.
Disabling Auditing will drop the Credential (it gets added back when Auditing is enabled again).
Go back to the beginning and follow the steps to create the .bacpac. Now we can try the import again. Mydatabase was created successfully so it must be dropped first. Go back to Import Data-tier Application… and follow the steps again. Here is what we want to see when everything works.
To re-enable Auditing change it back to On and click Save.
And we're done.
Summary of Steps
Now that I know the workarounds, my checklist for the production cutover looks something like this:
- Make a copy of production on testazuresqldatabaseserver.database.windows.net.
CREATE DATABASE mydatabasesource AS COPY OF prodazuresqldatabaseserver.mydatabase
- Remove database name references from Proc1 and Proc2
- Temporarily stop Auditing on testazuresqldatabaseserver.database.windows.net to remove Database Scoped Credential as they are not supported pre Azure SQL Database / SQL Server 2016.
- Export Data-tier Application to .bacpac
- Reenable Auditing on testazuresqldatabaseserver.database.windows.net.
- Import Data-Tier Application to create and import database on an On-Prem SQL Server.
- Create SQL Authentication app login and sync user.
-- create login
USE [master]
GO
CREATE LOGIN [app]
WITH PASSWORD=N'SuperSecret',
DEFAULT_DATABASE=[MyDatabase],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
-- drop user
USE MyDatabase
GO
DROP USER app
GO
-- add user back and add to roles
CREATE USER [app] FOR LOGIN [app]
GO
ALTER USER [app] WITH DEFAULT_SCHEMA[ app]
GO
ALTER ROLE [db_datareader] ADD MEMBER [app]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [app]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [app]
GO
Next Steps
This was just to show you one particular scenario and reiterate why you sometimes need to test, find workarounds, test again, rinse, lather, repeat until you have your process down.
Here are some links to migrating from on-prem to Azure SQL Database where you'll find other ways of migrating between platforms:
- http://sqlworldwide.com/how-to-migrate-a-sql-database-to-microsoft-azure-sql-v12/
- Using the Data Migration Assistant (DMA) tool to migrate from SQL Server to Azure SQL database
- Data Migration Assistant (DMA) to Check for SQL Server Compatibility Issues when Migrating to Azure
- Use Data Migration Assistant (DMA) to migrate on-premises SQL Server database to Azure
- Migrate database to Azure SQL Database using SQL Server 2016 Upgrade Advisor
- SQL Azure Migration Wizard
- Using the Deploy Database to SQL Azure Wizard in SQL Server Management Studio to move to the Cloud
- Move Azure SQL Databases using the Export and Import PowerShell cmdlets
About the author
Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
