Upgrade on Azure impossible [CRASH]

I have try this morning to upgrade my instance of ST 11 to the version 12 with the Upgrade.aspx wizard but the SQL Script is not compatible with Azure.

I got an error message :

'Filegroup reference and partitioning scheme' is not supported in this version of SQL Server.

Problem: System.Data.SqlClient.SqlException (0x80131904): 'Filegroup reference and partitioning scheme' is not supported in this version of SQL Server.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SmarterTrack.Data.DataLayerShortcut.ExecuteNonQueryWithWait(String query, SqlParameter[] parameters)
   at SmarterTrack.Data.Database.CreateTableTask.Execute(DBProperties props)
   at SmarterTrack.HelperClasses.Threads.DatabaseUpgradeThread.ThreadFunc()
ClientConnectionId:efe72339-c37e-4fc3-bc79-57d1e07b56fa
Error Number:40514,State:1,Class:16

Based on the research I have done the "ON [PRIMARY]" is not supported on Azure. I have install a local version to compare the DB Schema and upgrade manually on a sandbox server my database but the upgrade script didn't look at the structure it self. Somewhere a lock is present I guess.

It is possible to have a new bundle without this ON [PRIMARY] or is it a trick to fix it ?

I saw the new database schema version number in the parameters tables but as well if I change it, nothing change in my upgrade.aspx process.

Is there any possibility to have the upgrade process Azure compliant ?

I propose for a future release to have the SQL upgrade process outer the DLL so it's simpler to update it.

Artionet.Group - Digital Transformation, eGovernment Solutions

Yannick Guerdat Replied

5/3/2016 at 3:33 AM

Ok I have find a way here is my small tutorial who need to be improved by somebody who has time to :

Backup the database and script
Proceed to the upgrade
Once the upgrade process crash go to your Microsoft SQL Server Management Studio
Connect to your Azure Database
Make a fresh install over a local SQL instance with the last version of SmarterTrack
Then you need to compare what change between the Database schema of both versions. For that I have use the tool of Idera named "SQL Comparison Toolset", you can download it for free (15 days trial) under https://www.idera.com/productssolutions/sqlserver/sqlcomparisontoolset/registrationform
Then compare your Azure instance with your fresh last version of ST locally installed. Idera will analyze the database schema and propose you to upgrade your Azure one. Be really careful with this operation and do it table by table. Be sure to understand what you do because then it's impossible to revert back this operation.
Once the database structure is updated we need to force the database to have the schema number. You find this information in the table "st_GlobalSettings". Upgrade the number based on the schema of your local DB.

That's what you need to do to upgrade the schema. But if you try to access your ST platform you are still redirected to the upgrade script because the schema number is in the cache. So restart your website instance and relaunch the URL. Normally then you have the last version installed over your instance.

I hope this small tutorial help some of you.

I'm still not sure if this work around degrade the entire platform performance because without to decompile the DLL and analyze the code (what I didn't do!!!) it's hard to know if something is done after the upgrade of the DB structure.

For sure some improvements need to be done to have SmarterTrack available in the Azure Cloud.

Artionet.Group - Digital Transformation, eGovernment Solutions