Export Sitecore SQL Server DB to Azure DB … illustrated

So you wanna export your Sitecore databases (7.5 – 8.x) from SQL to Azure …

Sitecore introduced Microsoft Azure support in version 6.3 which meant flexible and unlimited scalability, content delivery across the world, real-time support features like failover and backup among other pluses.

In my last post, I illustrated how to bring a data-tier application .BACPAC file into SQL Server.  While working on the same project, I also needed to restore (well, import) the newly-upgraded Sitecore 8.1 databases in to the Azure cloud.

Below I have documented my steps and cleared the obstacle that you may run into.

Export

Instead of creating a back-up of the SQL database, you export each database as a data-tier application.  (See my last post for the definition).

In the wizard, you are given the choice to export it to a local file on your drive or save it directly in Microsoft Azure. (It uses a temporary file stored locally to accomplish this).

I am saving to the local disk, FTP’ing the files, and then importing the data-tier applications into Azure.

Save to the local disk and manually move the file to the cloud

… or … connect to Microsoft Azure directly.

All was going smoothly … exporting master, web, core, analytics … until, the sessions database. The Sessions database was introduced as part of Sitecore’s database architecture in version 7.5.  As the name implies, it provides storage for the application’s session state.

When exporting the Sessions database, the operation failed.  Diving in to the details of the error revealed that it was failing within a Sessions database stored procedure, CreateTables, that made use of the common pattern, tempdb creation.

The Azure virtual machine I was working with was not set up with a tempdb as yet.

After researching what could be done, I found out that this stored procedure is not needed and the common approach, recommended by Oleg Burov of Sitecore, is to drop it.  Run this simple script before exporting:

USE [Sitecore_session]
GO
/****** Object: StoredProcedure [dbo].[CreateTables] ******/
IF OBJECT_ID(‘CreateTables’) IS NOT NULL
DROP PROCEDURE [dbo].[CreateTables]
GO

(Reference: http://bit.ly/1RAcvwu)

The export for the Sessions database was smooth sailing after dropping that stored proc.

If you missed my post about importing from Azure to SQL, please take a look.

Happy Sitecore-ing!

To learn more about implementing Sitecore Azure, please see the Sitecore community support page.

Import Sitecore Azure DB to SQL Server DB … illustrated

So you wanna import the Sitecore Azure DBs into SQL Server …

Sitecore introduced Microsoft Azure support in version 6.3 which meant flexible and unlimited scalability, content delivery across the world, real-time support of features like failover and backup among other pluses.

Recently, I had the pleasure of working with such a system for a Sitecore upgrade and have documented my questions/hurdles that I overcame when recreating the Sitecore web application on my local machine.

Below is the journey that I took to restoring the Sitecore production databases down to my local database server.  Although there are Azure database interfaces available, I choose to use good-ole SQL Server Management Studio to back up.  Then, to import the db into SQL Server, I attempted to use the studio for SQL Server 2012, version 11.0.3156.0.  HINT:  If you don’t want to repeat my mistake, read this entire post before following along.  🙂

1. Back up

The first step is to take a back-up of the Azure database.  There’s nothing too special about how to perform this step in SQL Server Management Studio.  Run the back-up task for each database.  However, the output file has a different format;   instead of a .bak file, a .BACPAC file is created.  This is a logical backup Azure file of the schema definition and the database’s table data.

2. Time to restore (well, import)

On the Databases node of your server, choose Import Data-tier Application … and follow the wizard (seems simple enough).

What is a data-tier application or DAC?
It’s “.. an entity that contains all of the database and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects instead of having to manage them separately. A DAC allows tighter integration of data-tier development with the development of the associated application code. It also gives administrators an application level view of resource usage in their systems.”- https://technet.microsoft.com/en-us/library/ee240739(v=sql.105).aspx

This is me breezing thru the steps …

I was so confident when going through the wizard, only to have it bite me at the end.  It would have been nice to be warned on that Intro wizard screen, eh? Something like, “Note: The Database Schema Provider only supports file versions up to x.x.” Yeah, you’re right; I wouldn’t have necessarily looked up what schema file version of Azure I was importing but nonetheless …

Let’s add an additional step and re-do the last step, shall we?

2. Install a new version of SQL Server Management Studio.

I chose SQL Server Management Studio December 2015 preview as it was the latest version available when I was working on this:

https://msdn.microsoft.com/en-us/library/mt238290.aspx

You’ll need a later version of SQL Server Management Studio

3. Time to import (again)

This time… it worked!  Rinse and repeat for each database.

Success

Here’s hoping that this makes your import much smoother than mine.  In my next post, I’ll be putting the upgraded, SQL Server databases out into Azure (yep, another hurdle).  Stay tuned!

Reference:  http://stackoverflow.com/questions/28566610/unable-to-import-sql-azure-v12-bacpac-type-microsoft-data-tools-schema-sql-sql