November Happy Hour will be moved to Thursday December 5th.
AI OnAI Off
Unable to upgrade database schema using epideploy.exe
Hi I noticed this problem as well, havent experienced any problems and think it should be ok.
/D
Aug 07, 2014 10:06
Yes, sql update scripts are not idempotent. I have seen this kind of issues time to time when it fails somewhere in the middle and running script once again reports errors. I would suggest to try out upgrade on production db backup before actual deploy, compare schemas between new updated db and some other db that was been upgraded without issues (if you have one). Looking at scripts they are not pre-filling db with some stuff so I assume that they just create objects and exeuction once again should result just in reporting of existing objects.
Aug 08, 2014 8:23
Saidly these SQL-script is not so failure-secure written, if one statement goes wrong, all is not rolled back. I think that EPiServer needs to look into how for example Redgate create their change-script in SQL Compare and rewrite them to be more fail-secure (with XACT_ABORT and so on).
Aug 08, 2014 8:37
Thank you all for your replies.
At this point I've figured, there will be too little value in upgrading production servers to newer versions because of this. Even doing it manually would require downtime for the site because the schema verison need to match exactly to the version of package used, which is annoying.
I hope EpiServer willl improve their migration scripts in the future so they dont' break so easy. Also it would be great if the existing codebase won't refuse to work if the database schema version is higher than package's target. This way we could deploy the schema changes first and then a codebase for each server in a farm..
Edited,
Aug 08, 2014 18:02
It looks like the issue is fixed now. I tried upgrading to EPiServer.CMS.Core.7.13.0 and then EPiServer.CMS.Core.7.14.0 and updrade process worked just fine. Thanks!
Oct 02, 2014 0:31
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
Hi All,
After I updated EpiServer nuget packages and running Export-EPiUpdates command to generate packages, I'm getting an error running epideploy.exe command:
System.Data.SqlClient.SqlException (0x80131904): The type 'dbo.ContentReferenceT
able' already exists, or you do not have permission to create it.
After some investigation I found that EPiUpdatePackage\EPiServer.CMS.Core.7.11.0\epiupdates\sql has 3 sql scripts:
I've tried running them in order and found that 7.10.0.sql is trying to create types that already exist in my database. Ex.:
Creating [dbo].[ContentReferenceTable]...
Msg 219, Level 16, State 1, Line 1
The type 'dbo.ContentReferenceTable' already exists, or you do not have permission to create it.
Creating [dbo].[IDTable]...
Msg 219, Level 16, State 1, Line 1
The type 'dbo.IDTable' already exists, or you do not have permission to create it.
Creating [dbo].[ProjectItemTable]...
Msg 219, Level 16, State 1, Line 1
The type 'dbo.ProjectItemTable' already exists, or you do not have permission to create it.
....
I've tried to compare few user-defined types that already exists in the database with what script is trying to create and it looks like that they have the same columns and types.
I assumed that maybe I can just ignore those errors and continue with the next script. It ran without errors. I checked the site and it looks like it's working fine.
Finally, my question to you guys:
Would this be an acceptable solution to just ignore those errors for our production database? Do you hava any better solutions/ideas how to upgrade to the latest database schema more safely?
Thanks!