Upgrading a database

Vote:
 

Hello everyone,

Due to the frequent bi-weekly updating of Episerver the database gets updated rather frequent and this gives some challenges in an OTAP environment.

Because there is no clear separation of bug-fix releases or functional releases (to my knowledge), we are very careful with updating all of Episerver on 4 different environments with mirrored servers..

But now if we do an update we get to the very confusing part of updating the database to the proper version.

Is there any clear documentation about which update scripts to use, is there one clear script or do we have to run all the intermediate scripts as well? We already run into errors when we try to update database version 7020 to version 7021 but we get errors. Regardless of this is there a less tricky way of updating the database on the different servers. I know it's easy on development machine by using command line updating.

Thanks in advance,

Ron

#138445
Sep 22, 2015 10:28
Vote:
 
#138449
Sep 22, 2015 10:52
Vote:
 

The following errors occur on most of the tables that the script wants to rename:

Rename [dbo].[tblWindowsUser] to tblSynchedUser

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321

Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

The following operation was generated from a refactoring log file 76782dd7-8d51-4cc1-ac02-5970d9c8d8b3, d96309b3-ce5d-4881-9adb-d090bb164a68

Rename [dbo].[tblWindowsGroup] to tblSynchedUserRole

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 321

Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

#138457
Sep 22, 2015 11:06
Vote:
 

<episerver.framework updateDatabaseSchema="true">

Made several attempts with this. This does not work. 

It surprises me that this functionality is not tackled properly. Constant updates to the code and database have BIG impact on OTAP environments..

#138461
Sep 22, 2015 11:27
Vote:
 

Hi,

that error was news to me, we have multiple integrations tests to make sure automatic database upgrades work as epxected.

Can you open a support case and attach the database so we can find the cause of this error: https://episerver.zendesk.com/hc/en-us/requests/new

#138472
Sep 22, 2015 13:48
Vote:
 

if we just see the upgrade script of the database (8.10.0.sql) in EpiServer Core 8.11.0 directory, I see some dubious SQL. IF the scripts partly succeeds there is no full Rollback of the script. Furthermore there are functions used to rename tables, without checking if the databasetable was already there? Isn't this just a bad way to setup an sql script?

I was also very much surprised that still tables are being renamed in the source DB in intermediate Episerver releases. This makes me really hesitant to do upgrades and even makes me reconsider to implement Episerver as CMS tool in a production environment with OTAP.

#138473
Sep 22, 2015 13:59
Vote:
 

I totally understand that errors lowers the trust in a feature, and we are happy to sort our the problem if we can get hold of the database to repro the issue.

The transactions with full rollback support are managed by either the automatic database upgrades feature or the epideploy.exe tool which is used in the Export-EPiUpdates we recommend if you want to run them manually in production.

If you run the SQL scripts yourself I agree it gets a bit more complex since you have to keep track of versions and add transactions yourself but that is not the recommended approach, let the tools we recommend do the job for you.

All upgrades scripts are incremental from one version to another, so if you are on version X then there should be a table Y unless somone manually deleted the table. We strongly advise against making changes to the database. We use tooling in Visual Studio to generate these scripts from one version to another so we are not managing them manually.

#138475
Sep 22, 2015 14:21
Vote:
 

Thanks Per for your patience. We are struggling a bit on the fast Update schedule Episerver is using for it's CMS. But if you have a good tool for it that might be the better way yes. I wasn't aware of that tool so thanks for the tip.

#138476
Sep 22, 2015 14:46
Vote:
 

Hi any news on this feature?

Our tech dep is experiencing the same in stage, we are doing a lift from 8.10 to 9.3

E:\EPiUpdatePackage>update 

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.8.0.sql

 

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.10.0.sq

l

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.11.0.sq

l

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.12.0.sq

l

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.13.0.sq

l

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.14.0.sq

l

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.16.0.sq

l

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.19.0.sq

l

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\7.19.1.sq

l

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\8.0.0.sql

 

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\8.4.0.sql

 

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\8.6.0.sql

 

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\8.7.0.sql

 

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\8.9.0.sql

 

Processing E:\EPiUpdatePackage\EPiServer.CMS.Core.9.3.0\epiupdates\sql\8.10.0.sq

l

 

EPiDeploy was stopped due to an exception, more details:

 

System.Data.SqlClient.SqlException (0x80131904): Either the parameter @objname i

s ambiguous or the claimed @objtype (OBJECT) is wrong.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea

n breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj

ect 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(TaskCompletionSou

rce`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean

asyncWrite)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at EPiDeploy.Sql.ScriptRunner.<>c__DisplayClass5.<ExecuteScript>b__3()

   at EPiDeploy.Sql.DatabaseHandler.Execute[T](Func`1 f, Boolean commit)

   at EPiDeploy.Sql.ScriptRunner.ExecuteScript(StreamReader stream)

   at EPiDeploy.Sql.ScriptRunner.ExecuteScripts(IEnumerable`1 files, Boolean req

uiresValidation)

   at EPiDeploy.Sql.ScriptRunner.<>c__DisplayClass1.<Execute>b__0()

   at EPiDeploy.Sql.DatabaseHandler.Execute[T](Func`1 f, Boolean commit)

   at EPiDeploy.Common.Executor.Execute(ILocation location)

   at EPiDeploy.Common.Executor.Execute(ILocation location)

   at EPiDeploy.Common.Executor.Execute(ILocation location)

   at EPiDeploy.Common.Executor.Execute(ILocation location)

   at EPiDeploy.Common.Executor.Execute(ILocation location)

   at EPiDeploy.Deploy.Execute(Options options, ILocation startPosition)

   at EPiDeploy.Deploy.Run(Options options)

   at EPiDeploy.Program.Main(String[] args)

ClientConnectionId:d1e1411e-4af9-4800-a604-cd5a41a78200

Error Number:15248,State:1,Class:11

E:\EPiUpdatePackage>

Why does not the updateDatabaseSchema kick in? the errors above is when running a manual update.

We have the userDB separate can that be the cause to this?

#141864
Nov 23, 2015 11:32
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.