2

With the right version of Visual Studio 2012 we can have database compared and synced

I'm looking for way to integrate this synchronization process into a TFS build as in Continuous Integration for code; now I need that for database schema and data.

Please share if you know how to do it.

Dylan Smith
  • 22,069
  • 2
  • 47
  • 62
Nam G VU
  • 33,193
  • 69
  • 233
  • 372

1 Answers1

3

Things changed a bit between VS 2010 and 2012. Assuming you're using VS 2012 (the link you posted was to 2010 tech), that means you're using SSDT (SQL Server Data Tools).

So long as your SSDT project is part of the solution you've selected to build in TFS Build, it will build and output a .dacpac file. Then you can automate the deployment by calling the sqlpackage.exe here (either directly from the TFS Build, or like I do by having the TFS Build call a powershell script that does the heavy-lifting). sqlpackage.exe takes input as a .dacpac file and a publish profile with the environment-specific config values necessary for deployment.

Update

To drop the not in source object(s) on target database, e.g. the removed tables and columns in .dacpac, use /p:DropObjectsNotInSource=true

Nam G VU
  • 33,193
  • 69
  • 233
  • 372
Dylan Smith
  • 22,069
  • 2
  • 47
  • 62
  • Using `sqlpackage.exe` for this has one issue. `sqlpackage /a:extract` the `database project` to get `.dacpac file` and then, `sqlpackage /a:publish` it to a target database will NOT remove the tables on target database – Nam G VU Dec 19 '13 at 04:08
  • 1
    There is an option you can specify (that lives in the publish profile) that tells it to delete any tables that exist in the target but not in the dacpac. By default it is off. The option is called "DROP objects in target but not in project". – Dylan Smith Dec 19 '13 at 06:19
  • Though turning on the `/p:DropObjectsNotInSource=true` parameter will also drop the user I create on `target database`. I want to keep that so I try to add the parameter `/p:DropPermissionsNotInSource=false` and `/p:DropRoleMembersNotInSource=false` but that is not helping me to keep my users from being dropped. Please support! – Nam G VU Dec 23 '13 at 08:09
  • Turning on `/p:DropObjectsNotInSource=true` will also make my parameter `/p:BlockOnPossibleDataLoss=true` disabled i.e. data sync will get `data loss` – Nam G VU Dec 27 '13 at 07:52
  • May i know how you solved the issue to avoid dropping users from being dropped.. when /p:DropObjectsNotInSource=true – Mohammed Jawed Aug 10 '14 at 09:55
  • @MohammedJawed I know it's too late, but what you need is to pass 'ExcludeObjectTypes'. Ex: /p:DropObjectsNotInSource=true /p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials – Ε Г И І И О Aug 01 '21 at 19:07