How can we help you today? How can we help you today?

Sync regardless of Errors

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"
Tim_S
0

Comments

1 comment

Sort by
  • 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
    AlexYates
    0

Add comment

Please sign in to leave a comment.