We’re moving our Octopus server to AWS (we don’t plan on using hosted Octopus) with the database running on AWS RDS. When installing, i’m getting this error:
SQLException has occured in statement #0 of this TSQL script: ‘Octopus.Core.UpgradeScriptsPre.Script0000 - Database options.sql’; Line: 1; Procedure: ; Error Number: 1468; Excerpt: ‘-- This script cannot run inside a transaction https://technet.microsoft.com/en-us/library/ms191544(v=sql.105).aspx’; Message: The operation cannot be performed on database “myDb” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
I’m pretty sure there’s no way on RDS to temporarily break the mirroring for a single db. Is there any way for me to make this work? Can I execute the upgrade scripts manually outside of a transaction, for instance?
For our hosted Octopus offering we are using AWS RDS to host the Octopus database. I will speak the people responsible for configuring the hosting platform and RDS instances and get some more information about how the we are using RDS to host Octopus.
We are currently hosting Cloud Instances on Sql Server Web Edition which doesn’t support mirroring.
The script that is falling over contains the following statements
IF EXISTS (SELECT * FROM sys.databases WHERE name='$databaseName$' AND is_read_committed_snapshot_on = 0)
BEGIN
EXECUTE sp_executesql N'ALTER DATABASE [$databaseName$] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE';
END
ELSE
BEGIN
PRINT 'Read committed snapshot isolation is already on'
END
You can work around the error by executing the ALTER DATABASE statement directly yourself, and then it should get past that step.
okay so this is something that needs to be done prior to each upgrade? Do I need to run some other script to disable snapshot isolation afterwards? A clear step-by-step procedure would be nice (as part of the rest of the upgrade documentation you publish)
In terms of this issue that you are seeing from this specific script, it should not require any further workarounds, as the ALTER DATABASE statement is only executed if is_read_committed_snapshot_on is 0.