Best way to handle starting SCA project from existing dev DB?

PeterDaniels

I've been running into some difficulties when starting a project from a DB that exists on a shared dev server and already has schema objects in it, then deploying via PS scripts to an empty DB (QA, UAT, etc).  I have been choosing the shared dev DB as the "Target DB" when creating the project, and a local empty DB as "Development".  The baseline script is imported, and it's simple enough to deploy the project via VS to the empty local dev DB.  The issues come up when I use the powershell scripts (e.g. New-DatabaseBuildArtifact, New-DatabaseReleaseArtifact) to deploy to a new empty DB (e.g. QA, UAT).  The only way I've been able to get it to work is by:

1) Unchecking "Mark First Folder as Baseline"
and
2) Adding <SkipBaselineCheck>True</SkipBaselineCheck>

to the .sqlproj file.

Is this by design?  I didn't have any problems using VS (and MSBuild?) to deploy to an empty target DB (and leaving "Mark First folder as baseline" checked).  It's only when using the PS scripts.  Seems like we'd want more consistency in our deployment processes.

Anyway, wondering if there is a better/recommended way to start a project where there is already a shared "dev" DB.  Should I connect to it as dev and import the existing objects after I create the project?  And leave the Target DB unconnected when I create the project?  Then switch to using my local dev DB for true dev sandbox and deploy to the "shared dev" (integration) DB?

Kendra_Little

That makes sense, thank you for clarifying, @PeterDaniels !

Coincidentally, I met with members of the SCA team just this morning to chat about clarifying concepts around baselining in our docs. One of my questions was, "are there times when we do NOT want to create a baseline, and what are those exceptions?"

The list of exceptions I have as of this moment is:

1. New project / greenfield development, when there is no production and we're just starting by adding objects as we go. There's really nothing to baseline in this case.
2. When learning the SCA product for the first time, it can make sense for people to work a little with the migrations scripts first and get a feel for things first sometimes before diving into baselining (simply because there is some complexity with establishing an initial-baseline, and it can make sense to start with more daily-task style things).

You have an interesting case, because you're somewhat in the middle of "new" development and "existing database". There is no production yet, but you've already got activity that's been completed in an integration environment so you aren't at the "starting from scratch point" either. (Just reiterating this for readers as the context gets lost sometimes.)

So, which to do?

If you choose to create a baseline from the integration environment (set it as your "target" on the baseline wizard, use an empty db as "development" as you describe in your first post here), you should be able to deploy from that to an empty database.

I just re-ran through that scenario with the PowerShell cmdlets (gist of my simple script here) and it worked -- it will deploy the baseline to the empty database, and then apply incremental migrations on top of that.

• In this case, in TargetedDeploymentScript.sql there will be a line like "----------------- BEGIN INCREMENTAL MIGRATION: "Migrations\1.0.0-Baseline\001_20190204-1330_kendar.sql" ------------------" 
• This will be followed by all the code from the baseline, then other migrations

However, if the database you deploy to isn't totally empty --for example, if you create a user table in it (I created an empty one named "foo") -- that's when I wouldn't expect the deploy to work. SCA detects that the database isn't empty, so it doesn't want to lay down the migration script. Is it possible that this was the case in your initial testing? (The user objects in question could be getting sneakily created by accident by being in model, for example.)

• In this case, in TargetedDeploymentScript.sql, there will be a line like "

  PRINT '----- baselined: Migrations\1.0.0-Baseline\001_20190204-1330_kendar.sql (marked as deployed) -----';"

• The code for the baseline itself won't be in there.

• In my test, this caused the deployment to simply roll back when it hit errors, since I was using the default option to do it in a transaction

If you do already have a lot of objects in the dev/integration environment, it might be useful to track down if this is failing in your case due to deployment target databases not being empty.  In that case, I'd personally like to create a baseline, because it seems like a quite convenient way to encapsulate your starting point. As long as when you deploy to dev/qa/uat they are truly empty databases (or I suppose they could be a restored copy of your integration database, just as long as you are 100% sure the baseline doesn't need to be deployed), that should work, from my testing.

As to what exactly is happening behind the scenes differently in the MSBuild task, I am not sure on that account. I believe the release artifact functionality may be more recent and may not yet be integrated into all plugins.

Kendra_Little

Yay, glad you solved the case!

One experiment I did: I tested putting an object in my model database, and then deployed using SCA to a database name that didn't exist. That worked fine, because in the compare check at the beginning it saw that the database just wasn't there. The object was created based on model and it still deployed the full baseline. Might not be useful if you want to configure the production database specially (files, filegroups, etc) in a way that isn't handled in the pre/post scripts, but just sharing in case it ever comes in handy. 

I'm currently working on some suggestions for how we can improve the docs about baselining overall, basically adding content that we don't have now that is more clear on what baselines are, when they are useful, and making it easier to get to existing content. Thanks for raising this issue, it's been very useful in terms of thinking through our docs and what would be helpful.

Kendra_Little

Hi Peter,

The recommended pattern for starting is to create the baseline from your production database. Have you seen this article yet? Working with an existing database.

Often the shared Dev database is not in sync with prod (either due to ongoing work or other issues), so prod is preferable to create the baseline. 

Just checking on this bit first before handing anything further because I'm not sure if you mean in your case that there is no prod yet / new development and ONLY a shared dev database?

Kendra

PeterDaniels

Thank you, @Kendra_Little (and nice to hear from you!).  I'm familiar with that doc.  This situation is a little different in that we didn't have a prod DB.  Developers had a start on the DB on a shared dev server. It didn't exist anywhere else (no QA, no UAT, no prod). I created the SCA project and set the shared dev DB, which I refer to as "dev integration", as the "target" DB.  I set my local empty DB as "dev" in SCA.

Methinks the right way to do this is to set the shared DB that already has objects in it as "dev" and not to set the "target" when creating the project.  Then refresh the shared dev DB, import the scripts (no "baseline"), then set the connection to my local dev DB and deploy the project.  At lease that technique was successful for me.  The other option, is a bit more of a kludge - uncheck the "Mark first folder as baseline" and add the <SkipBaselineCheck>True</SkipBaselineCheck> element to the unloaded sqlproj file.

PeterDaniels

Thank you, @Kendra_Little.  Much appreciated.  I believe my issues are being caused by the fact that SQL Server 2014 put my domain user account in the model DB (as a DB user) during install. SCA doesn't like that at all. I removed that from model (and therefore my "empty" targets), and things worked OK in my test project.

I verified that this is what caused my "real" project to barf, too. SCA somehow sees that extra user as a delta and New-DataBaseReleaseArtifact chooses to NOT include the baseline script. 

I removed the account from the model DB, recreated the target, and voila.

This almost smells like a bug, but perhaps only for SQL 2014 (and?).

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)   Jun 17 2016 19:14:09   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) 

PeterDaniels

I tried that, too, @Kendra_Little. And, as you said, not an option for our environments where the DB is pre-deployed, sized, file layout set, etc.

The odd part to me is that I don't recall adding my account to model db. I think the SQL 2014 install did that...or perhaps a code I made during install did that.  Would be nice to have some cleaner handling in SCA for these scenarios.

Also, glad to hear the team is looking at cleaning up the docs. Seems like there is a fair amount of content that is no longer accurate or relevant. Of be happy to help in that process.

Thanks for your help, and honored to collaborate with a "celebrity".

Cheers,
-Peter