Learn More





DNN Community Blog

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.

DotNetNuke 6.0 welcomes SQL Azure



Starting DotNetNuke 6.0, all the editions of DotNetNuke Platform (Community, Professional, Enterprise) will natively support SQL Azure. In order to help the Module Developers and everyone else in the community to use SQL Azure with DotNetNuke, we’d like to note down the methodology we implemented to complete the integration. Most of the changes were needed either due to change in Syntax or Deprecated Commands.

Connectivity Tools

In order to access SQL Azure, Microsoft SQL Server Management Studio needs to be upgraded to 2008 R2.  Microsoft SQL Server 2008 R2 RTM - Management Studio Express can be downloaded from Microsoft here.


Connecting to SQL Azure using SQL Management Studio is no different than connecting to a regular SQL Server, simply supply the Server’s name, Login and Password.

Database Creation

Once connected, simply right-click Databases and select “New Database”


This action will not open the typical “New Database” from SQL Server, instead it will open the SQL Query Window, where all one needs to do is specify a database name. Modify the template provided and execute the command:





Migration Tool

SQL Server Migration Wizard can be downloaded from codeplex. Below is a quick description of the tool:

SQL Azure Migration Wizard (SQLAzureMW) is designed to help you migrate your SQL Server 2005/2008 databases to SQL Azure. SQLAzureMW will analyze your source database for compatibility issues and allow you to fully or partially migrate your database schema and data to SQL Azure.

Syntax Changes

1) Column Alias - Statement such as Select ‘xx’ = ColA is not allowed, instead specify Select ColA AS ‘xx’. Note ‘=’ has been replaced by ‘AS’ and the position of the column name is swapped.

Deprecated Commands

1) Create Table… [ON Primary]  -  Keyword [ON PRIMARY] is not allowed as there is no concept of file group in SQL Azure, simply remove the entire keyword.

2) Deprecated Keywords - Following Keywords are no longer allowed; we simply removed them from the statements: NOT FOR REPLICATION, PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, NOT FOR REPLICATION, ROWGUIDCOL.

More details on deprecated commands can be obtained from this MSDN article “Deprecated Database Engine Features in SQL Server 2008 R2”.

Mandatory Clustered Index

SQL Azure requires every table to have a Clustered Index. Instead of analyzing all the tables one by one, we simply performed a base install on SQL Server and ran the following query to find out tables without a Clustered Index:

--list tables without clustered indexes 
SELECT * FROM sys.objects 
 SELECT DISTINCT object_id FROM sys.indexes 
  SELECT object_id FROM sys.indexes 
  WHERE type_desc = 'CLUSTERED' 

After getting the list of tables requiring Clustered Indexes, the next task was to decide which column was to be selected for the index. We tried a few tricks:

a) In many cases where table had a Primary Key with a Non Clustered index, we simply modified the Create Table script to add CLUSTERED key word next to PRIMARY KEY, e.g.


b) Few tables had no index created as they contain fewer rows. For such tables we picked a column and used the following SQL to create Clustered Index:

CREATE CLUSTERED INDEX [IX_MyTable] ON {databaseOwner}[{objectQualifier}MyTable] ([MyColumn])

MyTable is the table name and MyColumn is the column name.

ASP.Net Memebership scripts

SQL Azure requires updated ASP.Net Membership scripts. DotNetNuke uses four script files located at  Website\Providers\DataProviders\SqlDataProvider: InstallCommon.sql, InstallMembership.sql, InstallProfile.sql and InstallRoles.sql. Updated version of the scripts have been provided by Microsoft, which can be download from Microsoft here. The updated scripts have already been included in DotNetNuke 6.0, there is nothing more needed to be done.


SQL Azure being on the cloud, the connectivity from DotNetNuke application can be slower, especially they are hosted apart. During our testing we noticed ‘Typical Installation’ failed a few times due to http time out (System.Web.HttpException: Request timed out). This does not happen during ‘Auto Install’, mainly because ‘Auto Install’ bumps up the http timeout.

For ‘Typical Install’, the http timeout can be manually increased by adding executionTimeout setting under httpRuntime.



Key points:

1) Keyword “executionTimeout” is case-sensitive.

2) A value of 900 indicates 15 minutes, which is more than enough for any installation.

3) Add setting to web.config prior to starting installation (for Typical Installation only), and remove the setting after installation is completely done.


Using SQL Server Management studio, one can run the following SQLs to get an idea whether all the objects were successfully created in the SQL Azure database or not. The counts are rough counts for a typical 6.0 Community Edition installation

--list tables (95 records)

--list stored procedures (553 records)
SELECT COUNT(*) FROM sys.objects 
WHERE type = 'P' 

--list indexes (192 records)
SELECT COUNT(*) FROM sys.indexes


Now with Beta2.


Comment Form

Only registered users may post comments.


2sic Daniel Mettler (124)
Aderson Oliveira (15)
Alec Whittington (11)
Alex Shirley (10)
Andrew Nurse (30)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (22)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (203)
Chris Paterra (55)
Clinton Patterson (28)
Cuong Dang (21)
Daniel Bartholomew (2)
Dave Buckner (2)
David Poindexter (3)
David Rodriguez (2)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (74)
Geoff Barlow (6)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (270)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matthias Schlomann (15)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Mike Horton (19)
Mitchel Sellers (28)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Peter Donker (52)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott S (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Timo Breumelhof (24)
Tony Henrich (3)
Torsten Weggen (2)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (37)
Will Strohl (163)
William Severance (5)
Try Evoq
For Free
Start Free Trial
a Demo
See Evoq Live
Need More Information?