Sync regardless of Errors

Tim_S

We have inherited multiple databases of legacy code. We are attempting to sync objects to a dev instance. However some of the source stored procs are not valid on the target system (referencing not existing databases, changed table structures, etc.). We'd like to sync everything that is valid. It seems any failure causes large batches of stored procedures to not be synced. We have tried both the -Force option and -AbortOnWarningLevel None. Are we missing something, or is this not possible with the toolset?

Sync-DlmDatabaseSchema -Source $sourcedb -Target $targetdb -AbortOnWarningLevel None -FilterPath "$filterPath\FunctionStoredProc.scpf"

Sync-DlmDatabaseSchema -Source $sourcedb -Target $targetdb -Force -FilterPath "$filterPath\FunctionStoredProc.scpf"

AlexYates

Sync-DlmDatabaseSchema uses SQL Compare under the hood. SQL Compare wraps the entire update into a single transaction. If there are errors at run time the transaction is rolled back. That is why your changes are not being deployed.

This is by design - it is to protect the user from ending up in a position where only half the changes have been deployed and they need to unpick the mess.

In order to solve your problem I propose two solutions. The first is quicker to implement, the second is more effort but safer:

Option 1: Ignore transactions
Of course, you now lose the benefit of transaction, but this should be a quick and easy way to force the behaviour you want.

SQL Compare has various options that you can set, one of which is NoTransations (or nt). To run Sync-DlmDatabaseConnection with this option you could write a script that looks something like this:

$options = "NoTransactions"
Sync-DlmDatabaseSchema -Source $someScriptsFolder -Target $someDlmDatabaseConnection -SQLCompareOptions $options

Option 2: Filter files
If you would like to only deploy specific objects but you would like to keep the security of transactions you should use filters to either filter the objects out of source control our your deployment. You can filter objects out of source control using the SQL Source Control GUI. To filter the objects out at deployment time you can use -FilterPath to reference a .scpf file. The command is documented here:
https://documentation.red-gate.com/display/DLMA2/Sync-DlmDatabaseSchema

Creating a .scpf file is documented here:
https://documentation.red-gate.com/display/SC12/Using+filters