SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

 


Filed under: Azure, Azure SQL DB, Cloud Blog Series Tagged: Azure, Bacpac, Cloud Busting, Migration, SQL server

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...