Creating the EPiServer CMS database on SQL Azure
This blog post is about creating the EPiServer CMS database on SQL Azure and is a part of the series “Running EPiServer on the Windows Azure Platform”. The post entails modifying the EPiServer database and I cannot stress enough that you should never attempt this with a production environment. This is for demo and experimental purposes only. Enough seriousness – let’s go! 🙂
You’ll need to have set up your SQL Azure account and downloaded SQL Server Management Express 2008. If you haven’t done this please see my previous post Getting started with the Windows Azure Platform. You’ll also need to have created a EPiServer database locally.
Create the SQL Azure database
First off we need to create a database. For this demo I’ll name mine molni. It’s very easy, just click create database, name it and leave it with defaults values set and we’re good to go.
Open the SQL Azure firewall
In order to access SQL Azure from your computer you’ll need to open the SQL Azure firewall. This can be done from the Azure Developer portal. You find the firewall settings under SQL Azure –> Database –> Firewall Settings. Add the IP range for your computer here. If you don’t know your IP then simply google “What is my ip” and add your IP like so: XXX.XXX.XXX.0 – XXX.XXX.XXX.255.
Scripting the EPiServer CMS database
We can’t do the backup-restore routine since we have no administrative rights to the SQL Azure environment. Instead we need to script the EPiServer database and then run this script on the SQL Azure. Open up SQL Server Management Studio and connect to the local SQL Server – in my case this is .SQLEXPRESS. You also need to connect to the SQL Azure database. Just hit File –> Connect Object Explorer and connect to SQL Azure with server name <AzureId>.database.windows.net.
Now we’re ready to script the EPiServer database. In your local instance right click the EPiServer database you want to script (it doesn’t matter which, as long as it’s an EPiServer database) and click Select Tasks -> Generate Scripts. Select Script entire database and all database objects.
On the next page select Save scripts to specific location and Save to file. Click advanced and for Script for the database engine type select SQL Azure Database.
Do the next-next-finish and we’re done! Let’s move on to the freaky part of modifying the EPiServer database.
Modifying the database script
Select the SQL Azure database then go to File –> Open and open the script we just created. There are a few things wrong with the script.
First remove the CREATE USER … at the top (the first three rows). The user already exists. If you validate the script now you’ll get the error below.
This means that any row that has the WITH-keyword and e.g. (ROWLOCK UPDLOCK) must be changed to (ROWLOCK, UPDLOCK). Also any row that does not specify WITH before (NOLOCK) must be changed to WITH (NOLOCK).
E.g. for tblScheduledItemLog creating a clustered index would look like this:
CREATE TABLE [dbo].[tblScheduledItemLog](
[pkID] [int] IDENTITY(1,1) NOT NULL,
[fkScheduledItemId] [uniqueidentifier] NOT NULL,
[Exec] [datetime] NOT NULL,
[Status] [int] NULL,
[Text] [nvarchar](2048) NULL
CREATE CLUSTERED INDEX tblScheduledItemLog_Index ON tblScheduledItemLog (pkID)
Add clustered indexes to the tables missing them and parse your script. Create the database structure and then run the script that checks which tables are missing clustered indexes. The list should now be empty. Run the script and you should have a new shiny EPiServer database structure!
Exporting data from sample public EPiServer project
Just for the heck of it we’re going to export the EPiServer public templates data to our site so that we have some sample data in our database. Now this will involve modifying SSIS configuration files – good fun! This is because SSIS between the SQL Server Native Client 10.0 provider to .NET Framework data provider cannot map from source type varbinarymax to DT_IMAGE. To resolve this open the file SqlClientToSSIS.xml which is located at C:Program Files (x86)Microsoft SQL Server100DTSMappingFiles and add the following mapping:
<em>&lt;!-- varbinary (max) --&gt;
Fun, eh? 🙂
Now open Import and Export Data (32-bit).
Select SQL Server Native Client 10.0 server name should be .SQLEXPRESS and database is your local EPiServer database. On the next page choose destination .NET Framework Data Provider for SqlServer. Set encrypt to true, password to your SQL Azure password, User ID to your SQL Azure username and data source to the server address of SQL Azure. Finally the Initial Catalog to the database name you created on SQL Azure.
Click next, leave it with Copy data from one or more tables or views, click next again. Select all tables but not the views. Click Edit Mappings and select Enable identity insert.