2

I can't seem to find a straightforward treatment of this subject in the SSDT docs. Basically, I have a database that's been managed historically by checking raw sql files into folders in source control, and we're trying to adopt SSDT. We have a production database, a QA database, and a shared development database.

It seemed to us that the first step was to use the "Create new project" workflow, clean up our old/stale objects so that the project would build, and then use schema compare to push the changes through our dev/qa and eventually production DBs.

In reading other documentations about the DAC Framework, however, it seems as if this should be getting done through the "Register Data-tier Application" workflow, but it's unclear if this is correct, and if so how we incorporate that into the process.

This process seems simple enough that tons of people ought to have done it by now. Did I just miss a page in the MSDN docs or something? Any help appreciated.

bwerks
  • 8,651
  • 14
  • 68
  • 100

2 Answers2

1

We generally just publish the database project to the target. You can choose to register as data-tier application, but that leads to issues if someone makes a change along the way that didn't come from the project.

Our process: 1. Create project from existing database (some starting point) 2. Clean up the project 3. Build project 4. Repeat steps 2 & 3 until everything is clean. :) 5. Build "Publish Profiles" for each environment we want to target. 6. Publish the database - either by generating a script or just updating the database.

I have a series of articles on my blog that outline the process we use and could be useful. You can find them here: http://schottsql.blogspot.com/search/label/SSDT

You can definitely use the Schema Compare route, but you'll miss out on the power of pre and post-deploy scripts to handle data changes.

Peter Schott
  • 4,521
  • 21
  • 30
0

Have you progressed at all since you posted the question?

The Dacpac packages that are output when you build your database projects are the same ones that you use in SSMS's Deploy Data-tier Application feature (right click on Databases in the Object Explorer).

Here at Red Gate we're thinking about building native support for Dacpacs into Deployment Manager. I'd encourage you to give this a go (it also has a free edition). If there is anything in the existing SSMS Deploy workflow that you think could be improved, let us know and we'll see if it's something we can help solve!

David Atkinson
  • 5,759
  • 2
  • 28
  • 35