Sitecore
Moving Sitecore databases to SQL Azure
November 14, 2015
0

As part of my series of articles on a lightweight Sitecore infrastructure, I’m going to show how to efficiently move the Sitecore databases to the Microsoft Azure SaaS-based SQL offering, SQL Azure, as a cost-effective, scalable, available and performant alternative to manually creating and managing a SQL Server infrastructure.

Groundwork

Let’s get a little groundwork out of the way around SQL Azure and Sitecore.

SQL Azure
SQL Azure has come a long way since the original offering. The parity between an on-premise SQL database and a SQL Azure database has increased over time and the latest SQL Azure, V12, is now often a viable and attractive alternative to provisioning and maintaining a SQL Server infrastructure with all the maintenance costs involved.

What does this mean for Sitecore?
Sitecore was one of the earliest experience platform vendors to embrace the cloud, with the Sitecore Azure Module enabling deployment to Azure PaaS. In this offering, Sitecore sites are packaged and deployed as Azure Cloud Service (Web Roles) and the databases are converted and deployed to SQL Azure.

Although manual deployment of Sitecore databases to SQL Azure has been possible for a long time, in versions up to Sitecore 8 deployment required manual modifications to the databases. Since Sitecore 8, there are hardly any changes needed; only one change in a database which is not even deployed depending on your setup.

As part of my series of articles on a lightweight Sitecore infrastructure, I’m going to show how we can manually deploy the Sitecore Databases as a cost-effective and performant solution.

A bit about performance, Azure SQL database models, and how this relates to Sitecore
Database Throughput Units, or DTU’s, represent the power of the database engine as a blended measure of CPU, memory, read and write rates, and equates to an ‘average transaction per second’. For more information on DTU’s, see Microsoft’s article on Understanding DTU’s.

Originally there were some tradeoffs as SQL Azure had limited DTU’s for the lower priced database tiers, but recently a very interesting new model has been introduced, called the Elastic model.

The lightest band (100) in the Elastic model allows you to host up to 200 databases in a single pool, with a total limit of 100 GB (for the first tier) and 100 eDTU’s.

The price of the lightest band is very close to a single S3 database in the Single database model but can be used to host multiple databases and the databases share the available DTU’s. In my view, this is a very cost-efficient way to store the different Sitecore databases and still get a relatively high performance, comparable to the S3 band in the Single model.

To compare

  • In the Single model, the cost of 5 databases with 100 DTU’s is $1,0080/hr
  • In the Elastic model, the cost of 5 databases with 100 DTU’s is $0,3000/hr

Of course if all databases have a high utilization or the combined growth is above 100 GB, then you’ll need to look at the next elastic tier but even then, this tier is more cost-effective than 5 separate databases. It’s a saving of at least 30% – 60% depending on your case.

Migrating the Sitecore databases to SQL Azure

With the groundwork out of the way, let’s see how we migrate the Sitecore databases to Azure.

Creating a Azure SQL Server

First we need an Azure SQL Server to host the databases.

  1. Navigate to the new Azure portal, https://portal.azure.com/, and from there choose to create a new SQL Azure database:
  2. Configure a new server in your region.
  3. Choose a temporary name and the cheapest plan as we will delete this database and import the Sitecore databases later:
  4. Wait for the database server to provision and then navigate the SQL Server overview:
  5. Delete the temporary database:

The server is now ready for the deployment of databases.

Migrating the databases to the Azure SQL Server

With the server in place, the next step is to migrate the Sitecore databases to Azure.

  1. Attach the Sitecore databases in a local SQL environment. I created a temporary SQL Server in Azure for this but you could do this on any local SQL Server or SQL Express instance with SQL Server Management Studio:
  2. Important: in order to successfully migrate the Sitecore_sessions database, delete the CreateTables stored procedure using the statement below:
    • DROP PROCEDURE [dbo].[CreateTables]

    See the following link for more information: https://community.sitecore.net/developers/f/5/t/1261

  3. Right-click the Sitecore_analytics database and choose Tasks – Deploy Database to Windows Azure SQL Database:
  4. Enter the details for the remote Azure SQL Server and select a database plan. I chose the basic plan as we will be adding the databases to an Elastic pool later:
  5. Click Next and confirm the creation of the Database.
  6. Validate the results are successful.
  7. Repeat the above steps for the other Sitecore database. Note. The Core database deployment timed out more than once in the last step using this method. Guessing it was performance-related, I configured this one to be created using the Premium tier and this solved the issue.
  8. Navigate the SQL Server overview and choose the option Add pool:
  9. Configure the pool and add the previously migrated databases to the pool:
  10. The result can be seen below, all databases are now part of an Elastic pool:

If your situation is different, you can review the following page for other ways of migrating the SQL databases to SQL Azure:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-cloud-migrate/#use-a-bacpac-to-migrate-a-database-to-azure-sql-database

Adding a Sitecore user to the databases

With the databases created, we want to create a separate account for Sitecore to access the databases. This avoids using the administrative accounts that allow for far more access then needed.

  1. I used SQL Management studio to connect to the SQL Server and then executed this script to create the login:
    CREATE LOGIN Sitecore
    WITH PASSWORD = ‘password’
    GO
  2. And then for each database add the login as db_owner:
    CREATE USER Sitecore FOR LOGIN Sitecore
    GO
    EXEC sp_addrolemember N’db_owner’, N’Sitecore’
    GO

With this in place, you can configure the Sitecore connections strings to point to the new Azure SQL databases.

In conclusion
With the latest additions to the SQL Azure, Microsoft’s SQL PaaS offering has become a viable alternative to hosting and maintaining a custom IaaS SQL platform.

With the Elastic offering, SQL Azure has become even more cost-competitive, providing an alternative to the individual pricing per database where this might be less cost-effective to attain the same performance.

With the SQL Server Management Studio, migrating the Sitecore databases to SQL Azure has become an easy task, especially since Sitecore 8 where the only database that needs modification is the session database, which may not even be in use in your specific case.

Leave a Reply