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! 🙂

Getting started

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.

create database in azure portal[3]create molni database in portal

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.

 open firewall in sql azure

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.

connect to local databaseconnect to database

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.

generate scriptsgenerate scripts choose 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.

save scripts as single fileadvanced scripting optionsadvanced scripting options

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.

sql parser failure

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

SQL WITH NOLOCKSQL HINT COMMA

Next all tables need to have clustered indexes. If you run the script below you’ll see which ones doesn’t.tables without clustered index

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:

Fun, eh? 🙂

Now open Import and Export Data (32-bit).

importexport[3]

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.

select database to copy fromdestination db

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.

tables not viewsenable identity insert

Next-next-finish and we’re off! Hopefully you’ll get a result like below. 🙂import export success

daniel
daniel
Developer
Recent Posts
  • This is really cool man! I’m impressed. Thanks for sharing!

    How’s the performance?

  • Thanks Martin! I haven’t done any load testing, but performance should be good. Cloud and all. 🙂

  • Steve Celius

    Wow ^ 2!

  • Tom Stenius

    This is awesome! Keep up the good work. Looking forward to the upcoming blog posts

  • Thanks Steve & Tom!

  • Sweeeet 🙂

Contact Us

We're not around right now. But you can send us an email and we'll get back to you, asap.

Not readable? Change text. captcha txt

Start typing and press Enter to search