User profile for Ivo_Miller

Hi, In terms of how to handle versions in your development/trunk branch, we might recommend organising migrations into directories e.g. Migrations 7.0-Baseline 001-BaselineMigration.sql 7.1 001-SomeMigration.sql 002-SomeOtherMigration.sql 7.5 001-NewMigration.sql In any case, there is no need to create a new baseline, as migrations scripts will not be rerun against a database where they have already been run (there is a __MigrationLog table which tracks this). It is possible to "rebaseline" if desired, incorporating the previous version into the baseline, though currently it isn't a very simple process unless you are using SQLClone. In terms of maintaining multiple branches (it sounds like you have some form of development/trunk branch alongside release branches), it would be recommended to have a separate development database per branch (though you can always spin up a development database for a release branch using the tool or SQLClone as needed so no need to do this up front necessarily). See https://documentation.red-gate.com/sca/developing-databases/concepts/version-control/switching-branches for more information. / comments

Hi,In terms of how to handle versions in your development/trunk branch, we might recommend organising migrations into directories e.g.Migrations 7.0-Baseline 001-BaselineMigration.sql ...

0 votes

Hi, It is worth double checking that you are using equivalent versions of SQL Change Automation for the PowerShell cmdlets and Visual Studio. Until version 4.3, programmable objects were enabled by setting the DeployChangesForProgrammableObjects property to true. New projects created using version 4.3 will instead use the new ProgrammableObjectHandling option (see here for details). Newer versions of the product will continue to support the old property, but if you create a new project using version 4.3 in Visual Studio, you will need to update the version of SQL Change Automation PowerShell to 4.3, or manually add <DeployChangesForProgrammableObjects>True</DeployChangesForProgrammableObjects> to your project file. All the best, Ivo Miller / comments

Hi,It is worth double checking that you are using equivalent versions of SQL Change Automation for the PowerShell cmdlets and Visual Studio.Until version 4.3, programmable objects were enabled by s...

0 votes

Hi, For deployment, if you are using the PowerShell cmdlets directly, see example 6 here: https://documentation.red-gate.com/sca/reference/powershell-cmdlets/new-databasereleaseartifact If you are using the SQL Change Automation release plugin for Azure DevOps or the SQL Change Automation step templates for Octopus deploy, any environment variables you define will automatically be passed in as SQLCmd variables. All the best, Ivo Miller / comments

Hi,For deployment, if you are using the PowerShell cmdlets directly, see example 6 here: https://documentation.red-gate.com/sca/reference/powershell-cmdlets/new-databasereleaseartifactIf you are us...

0 votes

Hi, SQL Chnage Automation always needs a shadow database. By default SQL Change Automation will create the shadow database in the same location as the development database. You can instead set it to a different location, such as on localdb, by adding a ShadowConnectionString entry in the user settings file. Setting the shadow to be local is indeed recommended if it is possible, as it is likely to perform a bit better. See https://documentation.red-gate.com/sca/developing-databases/concepts/shadow-database and https://documentation.red-gate.com/sca/reference/sql-change-automation-project-user-settings for more information. Kind Regards, Ivo Miller / comments

Hi,SQL Chnage Automation always needs a shadow database. By default SQL Change Automation will create the shadow database in the same location as the development database.You can instead set it to ...

0 votes

Hi, The SSMS plugin should at no point perform worse than the Visual Studio plugin. If you do have a reproducible scenario of this we would be happy to look into it. We are aware that we are lacking good progress indications in SSMS, and that may make it appear that the application is hanging even if it isn't. In general, we don't currently know of any scenario where the application actually hangs. It generally completes operations successfully, though some do occasionally take some time. Adding progress information is high on our priority list to improve this experience. In terms of certain operations occasionally taking a lot longer to run. This generally happens when we need to rebuild the shadow and redeploy the project from scratch. I have mentioned in the other post you raised the conditions around the shadow being rebuilt (https://forum.red-gate.com/discussion/87078/ssms-plugin-verify-success-invoke-databasebuild-error-fail). It is possible to avoid shadow database rebuilds with certain patterns of development. In addition, if running the shadow database on Azure the logic for dropping and recreating the shadow can be time-consuming. If your project has a complex baseline script, performance of the shadow rebuild may be improved by using SQLClone instead of a baseline, currently in preview mode. To summarise, we are conscious of the issues you have raised, and there is active development going on to address them. / comments

Hi,The SSMS plugin should at no point perform worse than the Visual Studio plugin. If you do have a reproducible scenario of this we would be happy to look into it.We are aware that we are lacking ...

0 votes

Hi. Thank you very much for your feedback, and sorry to hear you have again had difficulties in this area. We will take the feedback on board. I can confirm that adding the "rebuild" functionality in SSMS is on our backlog, though I cannot currently offer any form of timescale for when we will add it. All the best, Ivo Miller / comments

Hi.Thank you very much for your feedback, and sorry to hear you have again had difficulties in this area.We will take the feedback on board.I can confirm that adding the "rebuild" functionality in ...

0 votes

Verify is doing exactly the same checks as Invoke-DatabaseBuild. It is parsing the syntax of all the migrations and programmable objects and then running a deploy against the shadow database. The only difference is that as a performance optimisation we don't drop the shadow database and perform the deployment from scratch when running Verify. We will drop the shadow database and run the full deployment if the existing state of the shadow database is considered to no longer match the project. This is typically if a migration is manually edited or deleted or a programmable object is manually deleted (i.e. from the Manage Migrations screen, rather than the Generate Migrations screen). This logic works in the vast majority of cases and should always work if you only import objects to the project via the "Generate Migrations" screen. There are unfortunately a few scenarios where the shadow database invalidation logic doesn't work and we fail to delete the shadow database when we ought to. This is solved by dropping the shadow database before verify (equivalent of "rebuild" in Visual Studio). We might make this option more obvious in the SSMS UI in the future. For reference, these are the known scenarios which might cause spurious behaviour as a result of the shadow not being rebuilt: Changing the value of a SQLCMD variable does not currently result in the shadow being rebuilt Changing the values of SQLCMD variables does not cause programmable object scripts that consume those variables to redeploy Adding/removing/changing Pre & Post-Deployment scripts does not result in the shadow being rebuilt Adding a migration, or editing an existing deploy-on-change script, does does not result in the shadow being rebuilt <-- this is an intentional optimisation, but it can have some side effects. For example, 1. add a proc as a PO to a project, 2. apply it, 3. add a migration that calls the proc, 4. apply & verify (succeeds), 5. clean solution to drop shadow db, 6. verify (fails to call the proc in the migration) / comments

Verify is doing exactly the same checks as Invoke-DatabaseBuild. It is parsing the syntax of all the migrations and programmable objects and then running a deploy against the shadow database.The on...

0 votes

This issue should be fixed as of version 4.2.20161, released yesterday. / comments

This issue should be fixed as of version 4.2.20161, released yesterday.

0 votes

There shouldn't be any problems using the two in parallel. The SSMS plugin has been written with Visual Studio support in mind. There are some slight differences in capabilities between the two, but projects should be fully compatible with both. / comments

There shouldn't be any problems using the two in parallel. The SSMS plugin has been written with Visual Studio support in mind.There are some slight differences in capabilities between the two, but...

0 votes

Hi, I believe the setting you want is SyncOptionIgnoreDataCompression (True by default), documented here: https://documentation.red-gate.com/sca/developing-databases/concepts/advanced-concepts/comparison-and-script-generation-options You can set this before baselining in Visual Studio (by opening up the settings file on disk before completing the setup wizard), but not yet in SSMS. You can set it after baselining in both. The ability to set this before baselining in SSMS via the UI will likely be released this week. / comments

Hi,I believe the setting you want is SyncOptionIgnoreDataCompression (True by default), documented here:https://documentation.red-gate.com/sca/developing-databases/concepts/advanced-concepts/compar...

0 votes

How can we help you today?

Ivo_Miller

Comments