Option to NOT drop column in target table?

PeterDanielsCRB

I have the unfortunate case where both the source and target tables may contain new columns.  I want to effectively "merge" the two.  I was hoping to sync one way, then the other.  I am not seeing an option to "don't drop column in target table".  Am I missing something?
TIA,
-Peter 

David Atkinson

Sadly this isn't possible right now. Can I ask how your database source and targets can get into this state? I think your best bet today is to use the tool to generate the script and manually remove the column drops.

AlexYates

How about this?

Master = Production.
Dev work is done on feature branches on development databases. (Ideally separate databases per feature/developer. Have you looked at SQL Provision?)

On demand, plus on a periodic basis (e.g. at the weekend/at end of sprint) the following is automated:

From your post above:
1) restore latest prod on the dev server as <DB>_FROM_PROD_<yyyMMdd_hhmmss>
2) rename the dev <DB> to <DB>_OLD
3) rename the <DB>_FROM_PROD_<yyyMMdd_hhmmss> db as <DB>
My additions:
4) <DB> is compared with master in source control, any updates are automatically committed as "DRIFT CORRECTION". This should get all 3rd party and other updates on prod into master. When this occurs you may want to trigger some sort of notification to your team so they can review the updates.
5) master is automatically merged with all branches. If this merges nicely, wonderful, your development changes do not conflict with production drift. If this causes a merge conflict/build error, your developers need to review the problem before they can push to production.

To release code to production:

1) Automatic check that production and master are in sync. If not, abort with a drift warning. Resolve drift by updating master and try again.
2) Master is merged into feature branch to ensure there are no merge conflicts.
3) Any automated tests are run to verify the merged code. (If you don't have tests, write some.)
4) Feature branch is merged into master.
5) Master is deployed to production.
6) Master is merged into any other feature branches.

PeterDanielsCRB

Thanks for your reply, @David Atkinson.  Yes, it is sad that v14 of the best comparison tool around still can't allow me to simply choose to not drop missing columns in the target table.  To answer your question, we have a 3rd party product, Deltek Vision, that makes schema modifications when end users customize the application.  So, that very quickly creates production "schema drift".

David Atkinson

@PeterDanielsCRB - I can only apologise. Rather surprisingly it doesn't come up as a feature request as often as you might think. It's actually quite unusual for customizable third party apps to make schema changes. Usually most customization is done via data configuration.

PeterDanielsCRB

Agreed.  Usually we see some sort of EAV/metadata solution.  This product actually creates new tables, adds columns to tables, and modifies insanely long triggers to work as a column level audit of changes.  And, we have our devs making custom views, etc, which should follow a more typical SLDC/DLM process.  The mix of these is causing us some challenges for both our devops ideas as well as dev DB refresh processes.

AlexYates

There are a couple of things you could do to get around this issue. Neither are perfect, but they should work.

1. Move to a migrations based approach. If your developers use a SQL Change Automation project, rather than a SQL Source Control project, the deployments will be based on scripts that were curated by the developers so you would avoid unexpected column drops.

2. Move to a master == production branching strategy, and set up a process to detect and automatically add changes made in production directly to master. If this ever fails, you will get a broken build to alert you to an issue. All development changes would need to be done on a branch and merged with master when they are ready to be deployed.

Obviously these are both avoiding the issue rather than fixing it. In an ideal world the 3rd party product updates would be going through source control too.

PeterDanielsCRB

Thanks, master Yates @AlexYates.  Nice to hear you chime in.

Hoping to move this client to a devops process with source control soon. And, I am steering them towards SCA for that.  Meanwhile, I'm being requested to "refresh the dev DBs from prod", and find myself with this junky merge issue.

I've considered the prod = master vector, too.  That kinda steers the devs towards developing in production, which is what I'm trying to move away from. 

I've also considered the 3rd party product changes in src.  I think that would be a mgmt challenge as these business end users are so used to doing what they want in production.  Even getting them to contemplate a "dev environment" might blow a gasket.

For now, I'm moving forward with trying to get some process in place where the app-driven schema changes are done first in dev, then in prod.  It's my understanding that there is meta-data and schema changes that go along with these, and we've decided not to go down the rabbit hole of XE to find out all of the changes that happen.  Along with this, I'm going to:

1) restore latest prod on the dev server as <DB>_FROM_PROD_<yyyMMdd_hhmmss>
2) rename the dev <DB> to <DB>_OLD
3) rename the <DB>_FROM_PROD_<yyyMMdd_hhmmss> db as <DB>

It's a hack, but will get be over the hump for now.

Thanks again,

-Peter

PeterDanielsCRB

That looks like a solid solution, @AlexYates - certainly a direction to strive towards.  I appreciate your thoughtful consideration.  Now to see if I can teach the devs about branching and maybe even get them to consider git.

PeterDanielsCRB

Revisiting this one...

I thought perhaps the SCA cmdlet

Sync-DatabaseSchema

might provide some additional capabilities to avoid dropping additional columns in the target DB, using 

-IgnoreAdditional

The docs for that parm say "When SQL Change Automation performs a sync operation, by default it will drop all additional objects in the target database.

If you specify this parameter, additional objects will be ignored."

However, when I tried it in a test, it still wants to drop any additional columns in target tables (see below).  It was a nice try, though.

WARNING: (High) This deployment drops the column(s) [c2] from table [dbo].[T1]. Data in these column(s), will be lost unless additional steps are taken to preserve it.Sync-DatabaseSchema : There are warnings that have caused the operation to abort:(High) This deployment drops the column(s) [c2] from table [dbo].[T1]. Data in these column(s), will be lost unless additional steps are taken to preserve it.To force the operation to succeed regardless of warnings, set the 'AbortOnWarningLevel' parameter to None.At C:\Users\Peter.Daniels\Documents\WindowsPowerShell\Scripts\POC\SCA\SyncWithoutDroppingNewColumns.ps1:31 char:1+ Sync-DatabaseSchema -Source $srcDB -Target $trgDB -IgnoreAdditional+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    + CategoryInfo          : InvalidData: (database 'trgDB' on server 'localhost':DatabaseConnection) [Sync-DatabaseSchema], TerminatingException    + FullyQualifiedErrorId : AbortOnWarnings,RedGate.Versioning.Automation.PowerShell.Commands.SyncDatabaseSchemaCommand

David Atkinson

The problem here is that columns aren’t regarded as objects. 

PeterDanielsCRB

Yeah - I do understand that, David.  They're "minor objects" and currently outside (or actually inside) the scope/grain of the Additional/Missing/Different objects config.  I'd still like to see that functionality someday.