Hosting Sitecore Databases on SQL Azure
Before beginning this process it is worth ensuring Microsoft SQL Server Management Studio (SSMS) 2008 R2 SP1 is installed on your machine as this release has brought a lot of enhancements which allows you to work with SQL Azure in a similar way to when you connect to a SQL server. The first advantage of this version of SSMS is that you can connect to your SQL Azure subscription within the object explorer. Although the functionality available is a subset of what you are used to, there are quite a few useful features:
- Provides access to the “Databases” and “Security” areas of the tree
- Drill down into the database schema
- Create/Delete tables
- Run SQL queries
- Create new user logins (via script)
Creating Databases with Sitecore Schema and Data
There are a number of ways available for migrating databases to SQL Azure as described on MSDN (http://msdn.microsoft.com/en-us/library/windowsazure/ee730904.aspx). I found using a combination of these methods worked best:
- Creating the database schema using SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/)
- Exporting/Importing the data using SQL Scripts created by SQL Server Management Studio
Creating Database Schema
As mentioned above, the SQL Azure Migration Wizard was very handy at doing this, it provides an easy to use wizard to generate a SQL Azure compatible script of the database and then connect to the SQL Azure subscription and run the script it has created.
Before starting, you will need to edit a stored procedure within the core database on your local SQL server due to SQL Azure not supporting all of the functionality that SQL Server does. If you run the wizard, will see the following error:
StoredProcedure [dbo].[aspnet_Membership_GetNumberOfUsersOnline] — Deprecated feature ‘Table hint without WITH’ is not supported in this version of SQL Azure.
This is simply fixed by adding the “WITH” keyword within the SELECT statement so that it reads the following:
SELECT @NumOnline = COUNT(*)
FROM dbo.aspnet_Users u WITH (NOLOCK),
dbo.aspnet_Applications a WITH (NOLOCK),
dbo.aspnet_Membership m WITH (NOLOCK)
This is the only incompatible aspect of the databases which I came across so once this is updated, you can follow the wizard through creating your database schema scripts:
- Select the radio button “SQL Database” within the “Analyze/Migrate” section of the window
- Enter the server details of the SQL Server which your databases are located
- Select the Core/Master/Web databases to be migrated
- When presented with selecting the database objects to script, click advanced and under the “Script Table / Data” option, select “Table Schema Only”.
At this point, the wizard allows you to include scripting the data as well as the database schema. If you select this option, the data is copied into a dat file for each table and then the bcp utility (http://msdn.microsoft.com/en-us/library/ms162802%28v=sql.105%29.aspx) uses these files to bulk copy the data to the SQL Azure subscription. However, I found that the command the migration tool generated did not successfully carry out the bulk copy which is why I used SSMS to generate scripts of the data (below).
- Once the script has been created successfully, the wizard then provides the functionality to connect to your SQL Azure instance. You will just need the server name (SERVER.database.windows.net) and username/password to connect. After connecting to the subscription, the wizard provides an option for you to select an existing database or create a new one.
- After selecting (or creating) the database to run the script against, the script is executed and you are left with the schema of the database on your SQL Azure database
As mentioned previously, a combination of the migration wizard and the bulk copy utility can be used to import the data into the SQL Azure database. I found the wizard couldn’t successfully carry this out but if you edit the commands to work, it would be a lot quicker to import large amounts of data rather than the scripting approach. To do this, you would need to use the wizard to generate you the dat files and then in a command prompt run the following query:
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe “TABLENAME” in “FILE LOCATION OF DAT FILE.dat” -E -n -b 10000 -a 16384 -q -S “SQLSERVERDNS” -U “USERNAME@SQLSERVER” -P “PASSWORD” -d “DATABASENAME”
However, due to the manual process of setting up these commands, I instead used SSMS to generate scripts to copy the data following the instructions at http://msdn.microsoft.com/en-us/library/windowsazure/ee621790.aspx:
- On your SQL Server database, right click and select all of the objects
- Select the wizard option to script to a file and then click advanced:
- Change the value of “Script for the database engine type” to “SQL Azure Database”
- Change the value of “Convert UDDTs to Base Types” to “True”
- Change the value of “Types of data to script” to “Data only”
- Open the file generated in SSMS and change the connection to your SQL Azure subscription, select the database to import into and run the script
Sitecore doesn’t support running the CMS site in Windows Azure but now that our data has been created in the cloud, it’s functioning without any issues. It feels like the initial migration to SQL Azure could be more friendly but the additional SQL Azure features brought to SQL 2008 R2 SP1 management studio certainly helps with running queries and visualising the database schema without having to do this through the browser interface.