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?
Thanks for getting in touch.
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)
EXECUTE sp_executesql N'ALTER DATABASE [$databaseName$] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE';
PRINT 'Read committed snapshot isolation is already on'
You can work around the error by executing the
ALTER DATABASE statement directly yourself, and then it should get past that step.
Let me know how you go.
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)
I should have mentioned earlier that Sql database mirroring is not a supported configuration.
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
ah, gotcha. We just confirmed that RDS was supported and went to town on it.
We’re not users of Octopus HA, so I would expect any limitations to be called out here https://octopus.com/docs/installation/requirements or here https://octopus.com/docs/installation/sql-server-database - afaik they’re not.
I have updated the two pages you referenced to call out the mirroring and sql replication as being unsupported. Thanks for the feedback.
Apologies for any inconvenience.
nice and clear. Thanks. We’ll continue running as-is and just use the workaround at least for now.