MsSQL 2012 On Azure

Question

I am looking to host the MsSQL portion of crowd and jira on an Azure hosted SQL Server. The issue I am running into is:

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

This happens on both jira and crowd when trying to restore from my existing installation. Are the tables that don't have clustered indexes temp tables for the import? I can't think of a legemit reason why any normal table should not have at least some kind of clusted index.

Jean-Bernard LA · Answer

Some months ago, I also tried to host the db part of Crowd on a SQL Azure Database. Like BryanC, I stumbled across this missing clustered index on table hibernate_unique_key

hibernate_unique_key is a one column table. Seems to never have more than one row. (might be the reason why solution designers did not see the point of a clustered index. This must have seemed overkill)

Anyway, to make it work, I had to script both schema and data of local crowd db , insert the clustered  index creation in the generated script, before running the resulting script on azure.

Then I had to fiddle with crowd config files to switch database.

Even though it finally worked, I decided to leave out the azure option, as I considered the db scripting and editing would not be a good idea on the long run.

I guess having this clustered index in the db creation script would allow standard support for sql azure, like any other supported database.

I would be glad to read about that in some next release of Crowd

Bryan Clark · Answer

The clustered index requirement is from Azure and not SQL Server 2012 specific. After doing more research and testing with Crowd. It seems the only place in the database tables for Crowd that were missing the clustered index was the table for hibernate. I was able to get Crowd up and running by using SQLAzureMW to transfer the data as an exact copy. This seemed to be the only way to migrate all of my data and add the required clustered index to the hibernate table.

David Chan · Answer

The latest supported version of SQL server is 2008:

* https://confluence.atlassian.com/display/JIRA/Supported+Platforms

To be honest, I'm not sure if JIRA creates any clustered indices.  Previous versions of SQL server did not enforce this requirement, so I doubt any development has been done towards this.