Problems using Octopus deploy with AWS RDS


(trond) #1

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?


(Ben Pearce) #3

Hi,

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.

Regards
Ben


(Ben Pearce) #4

Hi,

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.

Let me know how you go.

Regards
Ben


(trond) #5

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)


(Ben Pearce) #6

Hi,

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 is_read_committed_snapshot_on is 0.

Regards
Ben


(trond) #7

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.