Azure Cloud “Fear” Busting #3 – There is no going back?

Once you go cloud, there is no going back.  This is false and from a database perspective you can migrate back to your own set of servers.

ET

The Challenge: I am going to write about a way to move from Azure SQL Database (Platform as a service) back to a local SQL Server. I did encounter errors on the way but more importantly I have written how to avoid/solve them.

Another key point I made sure that there were no connections to the database when doing the below as I didn’t want in-flight data movement whilst doing it. If you can’t do this, then you should create a copy of the database and work from that.

Get a BACPAC

Using SSMS (SQL Server Management Studio) I connect to my SQL database of interest (which is in Azure).

bacpac

Right Click on the database find tasks and hit that “Export Data-tier Application” option.

OPTIONS

You have 2 options here, save the BACPAC in an Azure storage container or to a local disk; I am going for BACPAC to local disk option because it is less hassle.

DATAeX

Do not forget to check the advanced tab – here you confirm what data you need.

BACPACDATA

Click the finish button and let it do its job – this was a slow process for me, it very much depends on the size of the data movement and your connection. What you want to see is the green tick!

OP-Complete

If you need a more detailed guide on BACPAC creation, then Steve Jones has a greate post: https://voiceofthedba.com/2017/03/15/create-a-bacpac-sqlnewblogger/

So now you have your bacpac file, move that file to the local disk on your SQL Server then connect to it via SSMS.

ssms

Right click on your database node and select import Data-tier Application and find your bacpac file.

import

errors-data

For the sharp readers out there I bet you guessed what went wrong a few screen shots ago? It was the wrong version of SQL Server! I got all sorts of messages around SQL database V12 compatibility. So, let’s work with a local SQL Server 2016.

2016ssms

A bad workman always blames his tools

So I went through the same process as shown above where I imported the bacpac file into my 2016 SQL Server, don’t try and use an older version of management studio you will get internal errors when trying to import, such as:

warningSSMS

So I started over with the import using 2016 management tools but then I encountered a different error. Warning SQL72014. Net SqlClient Data Provider message as shown below.

importfailed

The target server must have containment set on because in Azure SQL database I used contained users.

On the target/local 2016 server I had to run the following.

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

When contained database authentication is set to 1 for the instance, contained databases can be created or attached to the database engine.

done

I had my database structure (very simple structure I know), the actual data and even the SQL login(s) all locally.

localsqlserver

This is why I love contained users; the portability concept makes life easier.

contained

Thinking about it at a high-level I literally did the reverse of a local SQL Server to Azure migration.

Hopefully this post will be useful for some of you out there and shows you that you can get your data back to your own servers – if need be.

 

4 thoughts on “Azure Cloud “Fear” Busting #3 – There is no going back?

  1. And what’s the total application downtime on a database of, say, 50GB? (Include the time from when you stop app access to the database to the time when it’s available again.)

    Like

    • Hey there Brent! Ok so for my DB which is 15GB it took about 15 minutes to get a bacpac. On the local server ( instant file initialization on) took another 10-15 minutes to create it. Update connection strings about 5 minutes. Total about 25-30 minutes outage. There probably is a better way of moving off Azure..

      Like

      • Yeah, that’s the tough part. Throw in a serious production database (50-150GB isn’t unusual at all) and you’re looking at a decently sized maintenance window if you try to just use bacpacs. There’s much, much more work required if you want to cut that maintenance window down, like set up a SQL Server in an Azure VM for a first phase of staging, or building code to detect & sync differences between the Azure SQL DB’s contents and your SQL Server database.

        Like

  2. Pingback: Going Back From The Cloud – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s