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).

export 1

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.

export 2
Save to the local disk and manually move the file to the cloud
export 3
… 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.

export 5

export 5b

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)

export 6

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.