12

I'd like to have my database schema synchronized with my Visual Studio .dbproj database project.

Right now, I'm using SSMS for most of my database development work, and I've to manually use the schema compare tool of visual studio when I need the db schema and the .dbproj to be synchronized. Why would I want this? because :

  • It's the only way to checkin changes
  • It's the only way to apply changes
  • Failure to do so will cause some merge issues when getting files (basically, if a "get latest" contains some db changes on db objects I've been updating whithout checking them out, a merge won't be triggered, and it's easy to keep track of which version is which)
  • It's nice to be able to use the search features of VisualStudio on an up-to-date version of the schema
  • It's nice to be able to modify stored procedures from VS (typically when you have renamed a file) using the search results.

Apart from automating the "schema compare" tool to have it run every 5 minutes (which hardly sounds like a "clean" solution), are there ways to achieve this ?

I'd like to keep using SSMS if possible, but I'm also interested in ways to automatically propagate changes made using the "Server Explorer/Database connections" of visual studio to the .dbproj.

Improve this question
1
  • did you find a way of synchronizing your database project with SSMS?
    – user53026
    Commented Apr 30, 2012 at 15:45

2 Answers 2

Reset to default
4

SQL Server Management Studio is primarily a SQL Server MANAGEMENT tool and, as such, is not necessarily built to support the scenarios you're looking for.

Visual Studio Team System 2008 Database Edition and Visual Studio Team System 2010 Pro/Ultimate include the Database Developer tools (VSDB) to help those with extensive database assets integrate their database schema/view/proc/etc. development with their app development.

VSDB allows you to reverse-engineer existing DB's schemas into Visual Studio projects so that you can source-control per-schema-element script files. You can also modify/create SQL Server schema scripts for the creation of practically all schema elements supported by SQL 2005/2008/2010.

VSDB also supports the ability to deploy full/partial DB schema updates to empty/existing DB's where it does a HUGE amount of work to try to only apply the changes necessary to modify the existing DB (if present) so that it mirrors the structure of the source VSDB database project.

VSDB also includes a number of tools to help you compare schemas between DB's and/or DB projects and also allows you to diff and merge table contents.

However, note that while this toolset is supported today, the team has since been disbanded and the product is in maintenance mode. The SQL Server product group now owns the responsibility for creating tools for developers and are hard at work doing just that :)

Improve this answer
2
  • 1
    I'm already a user of VS 2010 db features. But I've yet to find how to use it to instantly modify both my database project and my database project at the same time. Running a schema compare after every single modification is just too much hassle... :(
    – Brann
    Commented Jul 30, 2011 at 13:51
  • Having started looking at VSDB yesterday, I find your last paragraph rather alarming. Sounds like I shouldn't bother investing much time or energy...?
    – Benjol
    Commented Aug 9, 2012 at 12:11
1

there are two ways that I know of with VS2010 ultimate features to do this.

  1. Use SSMS or other tool to edit DB schema then run schema compare periodically (end of the day) and then import any schema changes to the DB project.
  2. Edit the files in your DB project through VS and then create a deployment package that either runs directly on your DB or generates a script for you to run to update your schema in the DB.
Improve this answer
1
  • The second solution is, in my opinion, the best solution. You can set up a deployment process that only takes one click. Editing your files in Visual Studio will automatically check them out of source control. I've worked with the first method, but it's difficult to always do schema compares. Plus, at least for me, the schema comparison tool crashes Visual Studio a lot.
    – Kris Harper
    Commented Apr 30, 2012 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.