none
Upgrading Windows Azure Pack Websites from Update 4 to Update 6 SQL Schema Update Issues

    Question

  • I'm running into this nasty bug with Windows Azure Pack Websites v2 Update 6 when I monitor the Microsoft\Windows\WebSitesUpdate Event Log:

    The upgrade action 'UpgradeDatabase' has failed.   Error:  Microsoft.Web.Hosting.WebHostingException: An error occurred executing script 'C:\Program Files\IIS\Microsoft Web Sites\Scripts\Update\0031.sql'. ---> Microsoft.Web.Hosting.WebHostingException: An error occurred executing script 'C:\Program Files\IIS\Microsoft Web Sites\Scripts\Update\0031.sql'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Online index operations can only be performed in Enterprise edition of SQL Server.
       at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.Web.Hosting.RetryPolicy.ExecuteAction[R](Func`1 action, SqlExceptionMapper mapper)
       --- End of inner exception stack trace ---
       at Microsoft.Web.Hosting.RetryPolicy.ExecuteAction[R](Func`1 action, SqlExceptionMapper mapper)
       at Microsoft.Web.Hosting.SqlHelper.ExecuteScript(Server server, String scriptName, String sqlContent)
       --- End of inner exception stack trace ---
       at Microsoft.Web.Hosting.SqlHelper.ExecuteScript(Server server, String scriptName, String sqlContent)
       at Microsoft.Web.Hosting.Common.DatabaseUpdate.PerformTransactionalAction(Server server)
       at Microsoft.Web.Hosting.Common.DatabaseManager.PerformActions(Server server, IEnumerable`1 actions)
       at Microsoft.Web.Hosting.Common.DatabaseManager.ExecuteUpgradeActions(List`1 updateActions)
       at Microsoft.Web.Hosting.Common.DatabaseManager.UpgradeDatabase(InstallationResourcePaths paths)
       at Microsoft.Web.Hosting.UpgradeManager.UpgradeDatabaseInPlace()
       at Microsoft.Web.Hosting.Utilities.DatabaseUpdater.UpgradeHostingDatabase()
       at Microsoft.Web.Hosting.Utilities.DatabaseUpdater.DoHostingUpgradeWithLease()
       at Microsoft.Web.Hosting.Setup.Tasks.UpdateTasks.UpgradeDatabase(String scriptsLocation)
       at Microsoft.Web.Hosting.UpdateService.UpdateManager.DoWork(Object unused)

    It seems like the SQL script used to upgrade from Update 4 to Update 6 used a SQL Server Enterprise edition only feature and it's causing the update to fail on the controller. Is there an ETA from MSFT to resolve this issue?


    All posted advice may not be all that accurate. As always, test it in a lab before you try my advice! Best of all, the documentation is your friend.

    Thursday, April 30, 2015 4:56 PM

Answers

All replies

  • Hi,

    This is indeed an issue with the current bits for Windows Azure Pack WebSites Update 6. Thanks for reporting it, and apologies for the inconvenience.

    Can you please send email to the address kranthim at <our-company-name>.com? We will help you fix your deployment, while we fix the issue and update the public bits.

    Thanks,

    Kranthi.

    Thursday, April 30, 2015 6:03 PM
  • There is indeed an issue with upgrade when the SQL Server used with WAP Websites was Standard edition.  There are no issues with Express and Enterprise editions, just Standard.

    To alleviate such situations we fixed the upgrade scripts and published them here: https://github.com/WAPWebsites/v2u6patch

    The archive should be copied to your controller, unzipped and then run the powershell script.  This will apply the patch and resume the upgrade. 

    Sorry for the bother.  This problem will not happen again.

    Chris


    Wednesday, May 06, 2015 9:57 PM
  • Unfortunately, when I ran the patch against the installation, the database schema updates successfully, but I'm running into this error:

    The upgrade action 'UpgradeDatabase' has failed.   Error:  Microsoft.Web.Hosting.WebHostingException: An error occurred executing script 'C:\Program Files\IIS\Microsoft Web Sites\Scripts\Users.sql'. ---> Microsoft.Web.Hosting.WebHostingException: An error occurred executing script 'C:\Program Files\IIS\Microsoft Web Sites\Scripts\Users.sql'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Cannot find the user 'WebWorkerManagerRole', because it does not exist or you do not have permission.
    Cannot find the user 'WebWorkerManagerRole', because it does not exist or you do not have permission.
    Cannot find the user 'WebWorkerManagerRole', because it does not exist or you do not have permission.
    Cannot find the user 'WebWorkerManagerRole', because it does not exist or you do not have permission.
       at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.Web.Hosting.RetryPolicy.ExecuteAction[R](Func`1 action, SqlExceptionMapper mapper)
       --- End of inner exception stack trace ---
       at Microsoft.Web.Hosting.RetryPolicy.ExecuteAction[R](Func`1 action, SqlExceptionMapper mapper)
       at Microsoft.Web.Hosting.SqlHelper.ExecuteScript(Server server, String scriptName, String sqlContent)
       --- End of inner exception stack trace ---
       at Microsoft.Web.Hosting.SqlHelper.ExecuteScript(Server server, String scriptName, String sqlContent)
       at Microsoft.Web.Hosting.Common.DatabaseUpdate.PerformTransactionalAction(Server server)
       at Microsoft.Web.Hosting.Common.DatabaseManager.PerformActions(Server server, IEnumerable`1 actions)
       at Microsoft.Web.Hosting.Common.DatabaseManager.ExecuteUpgradeActions(List`1 updateActions)
       at Microsoft.Web.Hosting.Common.DatabaseManager.UpgradeDatabase(InstallationResourcePaths paths)
       at Microsoft.Web.Hosting.UpgradeManager.UpgradeDatabaseInPlace()
       at Microsoft.Web.Hosting.Utilities.DatabaseUpdater.UpgradeHostingDatabase()
       at Microsoft.Web.Hosting.Utilities.DatabaseUpdater.DoHostingUpgradeWithLease()
       at Microsoft.Web.Hosting.Setup.Tasks.UpdateTasks.UpgradeDatabase(String scriptsLocation)
       at Microsoft.Web.Hosting.UpdateService.UpdateManager.DoWork(Object unused)
    EDIT: Went in the Users.sql file, removed those relevant lines on granting some kind of permission to 'WebWorkerManagerRole'. I'll see how that goes and report back to see if anything's changed.


    All posted advice may not be all that accurate. As always, test it in a lab before you try my advice! Best of all, the documentation is your friend.


    Sunday, May 10, 2015 6:37 AM
  • As updating the Web Farm from UR4 to UR6 progresses, I also ran into this issue where Enterprise edition specific commands were being used again.

    The upgrade action 'PostBinaryUpdate' has failed.   Error:  Microsoft.Web.Hosting.WebHostingException: An error occurred executing script 'C:\Program Files\IIS\Microsoft Web Sites\Scripts\Admin\PostBinaryUpdate.sql'. ---> Microsoft.Web.Hosting.WebHostingException: An error occurred executing script 'C:\Program Files\IIS\Microsoft Web Sites\Scripts\Admin\PostBinaryUpdate.sql'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Online index operations can only be performed in Enterprise edition of SQL Server.
       at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.Web.Hosting.RetryPolicy.ExecuteAction[R](Func`1 action, SqlExceptionMapper mapper)
       --- End of inner exception stack trace ---
       at Microsoft.Web.Hosting.RetryPolicy.ExecuteAction[R](Func`1 action, SqlExceptionMapper mapper)
       at Microsoft.Web.Hosting.SqlHelper.ExecuteScript(Server server, String scriptName, String sqlContent)
       --- End of inner exception stack trace ---
       at Microsoft.Web.Hosting.SqlHelper.ExecuteScript(Server server, String scriptName, String sqlContent)
       at Microsoft.Web.Hosting.Common.DatabaseUpdate.PerformTransactionalAction(Server server)
       at Microsoft.Web.Hosting.Common.DatabaseManager.PerformActions(Server server, IEnumerable`1 actions)
       at Microsoft.Web.Hosting.Common.DatabaseManager.ExecuteUpgradeActions(List`1 updateActions)
       at Microsoft.Web.Hosting.Utilities.DatabaseUpdater.DoPostBinaryUpdate()
       at Microsoft.Web.Hosting.UpdateService.UpdateManager.DoWork(Object unused)

    Patching out that specific SQL script does seem to fix this issue, though.


    All posted advice may not be all that accurate. As always, test it in a lab before you try my advice! Best of all, the documentation is your friend.

    Sunday, May 10, 2015 7:16 AM
  • To resolve this issue, goto the database,

    delete Security/Users/WebWorkerManager in the hosting database if it exists

    delete hosting_WebWorkerManager (there might be a prefix to the name that matches the one of the hosting database) from Security/Logins

    When the update service runs again, it will recreate those identities.

    Friday, May 15, 2015 12:38 AM
  • The patch has been updated to address this situation. Please download it again and rerun.

    https://github.com/WAPWebsites/v2u6patch

    Friday, May 15, 2015 12:40 AM
  • That final patch now works fine.

    Will there be an updated patch for new Websites installations?

    All posted advice may not be all that accurate. As always, test it in a lab before you try my advice! Best of all, the documentation is your friend.

    Friday, May 15, 2015 5:53 AM
  • There is no need to modify the existing PatchWebsitesNewInstall.zip. It should work.
    Friday, May 15, 2015 7:44 AM
  • Here's some update to this issue:

    I've noticed that there's a revised update for UR6 and it's available here:
    http://www.microsoft.com/en-us/download/details.aspx?id=46896


    All posted advice may not be all that accurate. As always, test it in a lab before you try my advice! Best of all, the documentation is your friend.

    Tuesday, May 26, 2015 7:49 PM