Azure PaaS Deployment Failure - Invalid column names in "web" database

Question

I am trying to deploy Sitecore 8.2 Update 2 instance to Azure PaaS. I've installed a fresh instance on my local machine, installed content/media/template packages from our previous version, republished the entire site, and gotten it to work fine locally.

Then I followed the instructions here:

http://borisbrodsky.com/sitecore/sitecore-8-2-azure-deployment-with-arm-templates-and-powershell/

I was able to upload a XP0 web deploy package to blob storage and deploy - I receive no errors during the deployment. However, when I then load the site or or the sitecore admin screen - I receive the same message:

Server Error in '/' Application.

Invalid column name 'TemplateID'.
Invalid column name 'ID'.
Invalid column name 'Name'.

[SqlException (0x80131904): Invalid column name 'TemplateID'.
Invalid column name 'ID'.
Invalid column name 'Name'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3277272
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +345
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +5002
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +92
   System.Data.SqlClient.SqlDataReader.get_MetaData() +102
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) +602
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +3152
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +704
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +121
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +307
   Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader() +91

[DataException: Error executing SQL command:  SELECT DISTINCT [ID], [Name] FROM [Items] WHERE [TemplateID]=@templateId]

All the databases appear to be deployed -- but the Web database is missing those columns in the item table. Are they not being deployed because I'm publishing my content before creating the web deploy package? Or is there something else I'm missing?

Thanks!

Answer 1

Using DotPeek, I have spotted the issue in the class Sitecore.Cloud.Packaging.WebDeployPackages.WebDeployPackageBuilder on the Build method, I suspect this is the root cause of the problem.

      {
        "core",
        path1 + "\\Sitecore.Core.dacpac"
      },
      {
        "master",
        path1 + "\\Sitecore.Master.dacpac"
      },
      {
        "web",
        path1 + "\\Sitecore.Reporting.dacpac"
      },
      {
        "reporting",
        path1 + "\\Sitecore.Web.dacpac"
      }

Currently waiting on Sitecore to provide a fix for this.

Answer 2

Once I was able to connect to the database -- I noticed that the Web database and the Analytics database were switched. The Web database contained Facts and the Item table only contained two columns - ItemId and Url. This mirrors the Analytics table on my local.

I double checked my connection strings and verified everything ran locally fine. I do not see anywhere to explicitly set this in the azure packaging. My solution was to just swap the names of my Analytics and Web databases in my local and re-create the web deploy package. Then the deployment seems to work.

Seems like a bug I will try to submit to sitecore support -- unless anyone has any suggestions on what could be wrong in my script.