Dilemma with SCA and VS 2019 T-SQL Editor

sbendayan

Greetings,

I am having a dilemma using VS 2019 with SCA add-on.

I am trying to figure out a way to use SCA, along with the VS T-SQL Editor effectively, since SCA gets complicated if you start changing the database without changing the project files first.  I want all my changes to flow in a single direction, from the VS/SCA project to the database, and not the other way around.  (please let me know if I am misguided on this?)

So, I need to figure out a way that the SCA build/deploy/refresh process will work for both databases (the project DB and the Shadow DB), and also have the VS T-SQL Editor work for changing project SQL files and then running them on the database directly through Visual Studio.

So this is where I run into a problem.  The problem started with several objects that were in the VS project not deploying to the Shadow database, though they made it into the project database.  So I decided to put a USE statement with the SQLCMD variable $(DatabaseName) at the top of all my project files.  This fixed the issue, now all the objects go to both DBs.

However, now I can't run the SQL files on the VS T-SQL editor, because this editor cannot see the values of the SQLCMD variables.  Even though I set the connections to open in SQLCMD mode, it can't read the value of the $DatabaseName variable, which is defined at the VS project level.  I thought about using :SETVAR at the top of each file, setting the value to the project DB (I never need to connect to the Shadow DB) but then wouldn't this break the SCA build/deploy process, since it would always deploy only to the project DB (and not the Shadow DB)?

So that's it....what am I missing?

Thanks in advance,

SB

Sergio R

Hi,

It's fine to create the migration scripts in the project and then apply them to the development database, to do this you should right-click on the migrations folder and select Add->New Item
Usually you shouldn't need to prevent migrations from running in the Shadow Database but if you need to, there's an option to exclude objects: https://documentation.red-gate.com/sca/developing-databases/concepts/verifying-projects/excluding-objects-from-script-verification

Kind Regards,

sbendayan

The scripts that were having trouble running on the Shadow DB were not migrations...they were object scripts, such as the schemas.

sbendayan

Also, the problem was that there were scripts that were NOT running on the Shadow database.  So I had to find a way to include the scrips, not exclude them.

Sergio R

Schemas are not Programmable Objects, so they should be scripted on a migration script.
The only object specific script for Schemas they will appear in is in the (Offline) Schema Model, which is not verified in the Shadow Database (and it shouldn't be).
Anyway, it appears that you've worked around your issue. If you haven't we might have to look at your project to understand what's the issue

sbendayan

So, does SCA handle applying ALL objects to both databases (the project DB and the Shadow DB) without me having to do anything to the files?  Does it do this automatically for all programmable objects and migration scripts?  And all other files are excluded?

Sergio R

So, does SCA handle applying ALL objects to both databases (the project DB and the Shadow DB) without me having to do anything to the files?  Does it do this automatically for all programmable objects and migration scripts?

Yes, all migration scripts and Programmable Objects should be deployed automatically to the Shadow Database without the need for manual intervention

And all other files are excluded?

Most files are included, the Offline Schema Model is not because it is not meant to be deployed and represents the state of the objects already being deployed as part of the Migrations or Programmable Objects

sbendayan

So, to recap,

• The SCA project handles propagation to both databases (project DB and Shadow DB) automatically for all migration scripts and programmable objects.  The Offline Schema model is not deployed anywhere.
• Therefore, I should not be using any USE <databasename> statements in the SCA project scripts, as this will break SCA's ability to deploy to both databases.
• All I need to do now then, is to come up with a way for the Visual Studio T-SQL Editor to default to the correct database when connecting, and all is well.

Does all of the above make sense?

Thanks,

SB

sbendayan

Also, are the Pre and Post Deployment scripts excluded from the above rule?  In other words, are they not deployed automatically to both databases?

Sergio R

Pre and Post are also deployed to the Shadow Database by default.
It shouldn't take any special effort to get your scripts to apply in the correct database when deploying, SQL Change Automation Powershell handles that automatically.