Can't deploy Migration Script - SQL Compare & Octopus Deploy

phsilva

Hi there, I'm trying to deploy database changes in my software using SQL Server 2014, Visual Studio 2013, DLM Automation Suite, SQL Source Control 5, SQL Compare 11 and SQL Release. I also use Team City to build the software packages (including DB package) and Octopus Deploy to do automated deployments.
Everything is working fine until the Octopus Deploy part where I can't really deploy the database changes/migration scripts.
I'm following the steps on documentation as highlighted here:
http://documentation.red-gate.com/displ ... +templates

In my Octopus Deploy, the steps all run fine, but my Changes.html has the following script:


And when I run the Octopus Deploy steps, I get the following log for Redgate - Create Database Release step:

Executing script on 'SERVER'
VERBOSE: Loading module from path 'C:Program Files (x86)Red GateSQL 
Automation Pack 1ModulesSQLReleaseSQLRelease.dll'.
New-DlmDatabaseConnection, SQL Release 1.3.20.1618, Copyright © Red Gate Software Ltd 2014-2016
VERBOSE: Machine Information:
OperatingSystem: Microsoft Windows NT 6.3.9600.0
OsBitVersion: x64
MachineName: SERVER
CurrentUser: NT AUTHORITYSYSTEM
TempDirectory: C:WindowsTEMPHostProcessName: powershell
ProcessorCount: 4
TotalPhysicalMemory: 7167 MB
AvailablePhysicalMemory: 4056 MB
Is64BitProcess: True
VERBOSE: Parameters:
ServerInstance: DATABASESERVER
Database: DATABASENAME
Username: USER
Password: ********
VERBOSE: Calling: C:Program Files (x86)Red GateSQL Automation Pack 1SCSQLCompare.exe /? /OutputWidth:1024 /out:"C:WindowsTEMPSQL Releaseq3r4ghou.log"
VERBOSE: C:Program Files (x86)Red GateSQL Automation Pack 1SCSQLCompare.exe ended with exit code 0
VERBOSE: Licensed: serial number XXX-XXX-XXXXXX-XXXX
Test-DlmDatabaseConnection, SQL Release 1.3.20.1618, Copyright © Red Gate Software Ltd 2014-2016
VERBOSE: Parameters:
InputObject: database 'DATABASENAME' on server 'DATABASESERVER'
VERBOSE: Checking connection details for database 'database 'DATABASENAME' on server 'DATABASESERVER''
New-DlmDatabaseRelease, SQL Release 1.3.20.1618, Copyright © Red Gate Software Ltd 2014-2016
VERBOSE: Parameters:
Source: scripts folder 'C:Program FilesCustomerDatabases10.5.0.99dbstate'
Target: database 'DATABASENAME' on server 'DATABASESERVER'
FilterPath: null
IgnoreStaticData: False
SQLCompareOptions: -UseMigrationsV2
TransactionIsolationLevel: Serializable
IncludeIdenticalsInReport: False
TemporaryDatabaseServer: null
VERBOSE: Using filter in scripts folder 'C:Program Filesi-conXCustomerDatabasesiCONX10_5_010.5.0.99dbstate'
Making a scripts folder of database 'DATABASENAME' on server 'DATABASESERVER'.
VERBOSE: Calling: C:Program Files (x86)Red GateSQL Automation Pack 1SCSQLCompare.exe "/makescripts:C:WindowsTEMPSQL Releasext1madnv.pvn" /force /options:DecryptPost2kEncryptedObjects,IncludeDependencies /OutputWidth:1024 /server1:DATABASESERVER/database1:DATABASENAME /username1:USER /password1:******** /out:"C:WindowsTEMPSQL Releasec3g4h3fl.log"
VERBOSE: C:Program Files (x86)Red GateSQL Automation Pack 1SCSQLCompare.exe ended with exit code 0

VERBOSE: SQLCompare.exe completed successfully.

VERBOSE: Calling: C:Program Files (x86)Red GateSQL Automation Pack 1SDCSQLDataCompare.exe /server1:DATABASESERVER /database1:DATABASENAME
/username1:USER /password1:******** "/scripts2:C:WindowsTEMPSQL Releasext1madnv.pvn" /include:table:[dbo].[SystemLabel]|[dbo].[SystemSettingDescription]|[dbo].[SystemSetting] /sync /out:"C:WindowsTEMPSQL Releasehco4agdh.log"

VERBOSE: C:Program Files (x86)Red GateSQL Automation Pack 1SDCSQLDataCompare.exe ended with exit code 0

VERBOSE: SQLDataCompare.exe completed successfully.

[b]Creating the update script.[/b]

VERBOSE: Calling: C:Program Files (x86)Red GateSQL Automation Pack 1SCSQLCompare.exe "/filter:C:WindowsTEMPSQL Releasehyseedi2.scpf" 
/options:ConsiderNextFilegroupInPartitionSchemes,DecryptPost2kEncryptedObjects,DoNotOutputCommentHeader,ForceColumnOrder,IgnoreCertificatesAndCryptoKeys,IgnoreDatabaseAndServerName,IgnoreTSQLT,IgnoreUserProperties,IgnoreUsersPermissionsAndRoleMemberships,IgnoreWhiteSpace,IgnoreWithElementOrder,IncludeDependencies,ThrowOnFileParseFailed,UseCompatibilityLevel /transactionIsolationLevel:SERIALIZABLE /include:staticData 
"/scriptFile:C:WindowsTEMPSQL Releasesor5vbwh.sql" /showWarnings 
/include:Identical "/report:C:WindowsTEMPSQL Releasezihnge4y.xml" 
/reportType:Xml /assertidentical /force /OutputWidth:1024 
"/scripts1:C:WindowsTEMPSQL Releasev5wqopqu.cha" 
"/scripts2:C:WindowsTEMPSQL Releasedpo5jbih.0ps" /out:"C:WindowsTEMPSQL 
Releasel5al13ri.log"

VERBOSE: C:Program Files (x86)Red GateSQL Automation Pack 1SCSQLCompare.exe ended with exit code 79

VERBOSE: SQLCompare.exe completed successfully.

Export-DlmDatabaseRelease, SQL Release 1.3.20.1618, Copyright © Red Gate Software Ltd 2014-2016
VERBOSE: Parameters:
InputObject: Database Release
Path: C:Program Filesi-conXCustomerDatabases10.5.0.99Projects-2410.5.0.99PACKAGENAME
Format: Default
Force: False
Exporting database deployment resources to folder 'C:Program Filesi-conXCustomerDatabases10.5.0.99Projects-2410.5.0.99PACKAGENAME'.

Then I get the following log for the Redgate - Deploy from Database Release step:

Executing script on 'SERVER'
VERBOSE: Loading module from path 'C:Program Files (x86)Red GateSQL Automation Pack 1ModulesSQLReleaseSQLRelease.dll'.
New-DlmDatabaseConnection, SQL Release 1.3.20.1618, Copyright © Red Gate Software Ltd 2014-2016
VERBOSE: Machine Information:
OperatingSystem: Microsoft Windows NT 6.3.9600.0
OsBitVersion: x64
MachineName: SERVER
CurrentUser: NT AUTHORITYSYSTEM
TempDirectory: C:WindowsTEMPHostProcessName: powershell
ProcessorCount: 4
TotalPhysicalMemory: 7167 MB
AvailablePhysicalMemory: 4006 MB
Is64BitProcess: True
VERBOSE: Parameters:
ServerInstance: DATABASESERVER
Database: DATABASENAME
Username: USER
Password: ********
VERBOSE: Calling: C:Program Files (x86)Red GateSQL Automation Pack 1SCSQLCompare.exe /? /OutputWidth:1024 /out:"C:WindowsTEMPSQL Release3jjrnjhe.log"
VERBOSE: C:Program Files (x86)Red GateSQL Automation Pack 1SCSQLCompare.exe ended with exit code 0
VERBOSE: Licensed: serial number XXX-XXX-XXXXXX-XXXX
Test-DlmDatabaseConnection, SQL Release 1.3.20.1618, Copyright © Red Gate Software Ltd 2014-2016
VERBOSE: Parameters:
InputObject: database 'DATABASENAME' on server 'DATABASESERVER'
VERBOSE: Checking connection details for database 'database 'DATABASENAME' on server 'DATABASESERVER''
Import-DlmDatabaseRelease, SQL Release 1.3.20.1618, Copyright © Red Gate Software Ltd 2014-2016
VERBOSE: Parameters:
Path: C:Program Filesi-conXCustomerDatabases10.5.0.99Projects-2410.5.0.99PACKAGENAME
Importing database deployment resources from folder 'C:Program FilesCustomerDatabases10.5.0.99Projects-2410.5.0.99PACKAGENAME'.
VERBOSE: Using filter in scripts folder 'C:Program FilesCustomerDatabases10.5.0.99Projects-2410.5.0.99PACKAGENAME'.
Use-DlmDatabaseRelease, SQL Release 1.3.20.1618, Copyright © Red Gate Software Ltd 2014-2016
VERBOSE: Parameters:
InputObject: Database Release
DeployTo: database 'DATABASENAME' on server 'DATABASESERVER'
SkipPreUpdateSchemaCheck: False
SkipPostUpdateSchemaCheck: False
QueryBatchTimeout: 300

[b][size=150]The update script doesn't contain any SQL statements. Skipping the update.[/size][/b]

The step completes successfully, but nothing happens in the target database. I've tried with different scripts, always scripts that either insert data or change data in the target database.
Nothing works.

Can you see anything obvious that I'm missing?

Any help will be much appreciated.

Regards,
Pedro Silva

davej

I was able to get this to work with the exception of Migration Scripts. My scenario was similar, but using Bamboo instead of Team City.

Basically this:

Bamboo:
- Monitor Git for changes
- Get latest source for branch
- Build database (validate) and create NuGet package
- Push to Octopus
- Create a release in Octopus

Octopus:
- Install package
- Redgate - Create Database Release (step template imported into step template library)
- Redgate - Deploy from Database Release (another step template imported in)
- Notify

Everything works fine from the schema and populate with static data, but the migration script I added doesn't get executed.

It seems there have been some changes in V5 since V4 (below is in the V4 documentation):

The current migrations feature isn't compatible with distributed version control systems like Git. However, SQL Source Control 5, add support for Git, as well as branching and merging.
Download the SQL Source Control 5
Scripts created using a previous beta version of migrations won't be compatible with SQL Source Control 5. To learn more, see Upgrading from old versions of migration scripts.

https://documentation.red-gate.com/disp ... on+scripts

I'm using Git so it sounds like it isn't supported, which is fine so I upgraded to V5. But then I found some information that seems to point it not being supported with the DLM automation tools (although it was in V4 by passing SQL Compare options, UseMigrationsV2):

Deploying migration scripts
SQL Compare 11 or later can generate deployment scripts with migrations using the following source/target types:

We recommend using SQL Compare to deploy changes to production, as you have the opportunity to review the deployment script before it's deployed. For a full walkthrough of the deployment process using SQL Compare, see Migration script examples.

It is possible to use the Get latest function in SQL Source Control to deploy these changes, however we don't recommend linking your production database directly to source control.

So it's saying you need to use "SQL Compare 11 or later" to generate deployment scripts with migrations. That to me implies that it can't be done with the DLM tools that are used by Octopus.

Maybe someone at RedGate could confirm this?

Thanks,

Dave

wyrembelski

I am in the same boat as davej.

I am using TeamCity.

Was using SQL Source Control 4 with passing SQL Compare options, UseMigrationsV2. Which worked with TeamCity.

I have now upgraded to SQL Source Control 5.
Now in TeamCity the schema changes deploy just fine but my migration scripts do not run.

Can any Redgate Representative offer help with this?

Thanks

wyrembelski

I have discovered that redgate has replaced the SQL Automation Pack with DLM Automation, which is available in the SQL Toolbelt.
For more details, see Redgate's DLM Automation page.(http://www.red-gate.com/dlmautomation/).
This is required for migration scripts to work in the latest version of SQL Source Control (5).

I haven't upgraded yet, once we have and I have tested and verified migration scripts are working I will update this page with that info.

Thanks

brucevanhorn2

They changed the way migration scripts worked a while back. I'd make sure the migration script you created (that isn't working) was created in the right version with respect to the release of DLM you're using.

RickD43

Did you ever get this working with migration scripts as I am having a similar issue?

Sergio R

Hi RickD43,

You can use migration scripts with DLM Automation, however bear in mind that DLM Automation 2 will only work with scripts compatible with SQL Source Control 5 and newer. DLM Automation 1 works only with migration scripts compatible with SQL Source Control 4 or older.

Thank you,

RickD43

Sergio, I am using DLM Automation 2 and SQL Source Control 6.0.2.6908 and all migration scripts were created by this or the previous version, which was 5. Still having the same issues.

Sergio R

Hi RickD43,

That should work, if your license is supported, can you please log a support ticket so that we can check exactly what's the problem in your case?

You can either go to Customer portal at the Redgate webpage or email us at support@red-gate.com

Thank you,

RickD43

I do not have a support contract. Starting to give up hope of ever getting to a DevOps position as can't find any decent resources for your software except here and this isn't the most helpful.

I have found that if I create a build database on a server (and not use Local DB), then point DLM to that, it works (even though I still get exit code 79 in SQL Compare). This isn't really the way I want to do builds and when I was doing a POC on my local machine, this problem didn't exist, but if there is no other way, I guess I will have to create a new instance of SQL just for this.