Visual Studio Database Project and SQL Server Management

Question

I enjoy using SQL Server Management Studio for change and updating my database. Its easier, faster, and safer than writing changes myself.

I was looking into using some sort of version control for databases and read about using a SQL Server Database Project in Visual Studio 2010.

I scripted out an existing database and imported it into a new SQL Server Database Project. Now, from what I can tell, there is no GUI to edit the database; I can't add columns, change datatypes, or edit existing data without scripting it myself. For instance, in SQL Server Management Studio I can right click on a table-name and select "Design" and then add/edit columns, change datatypes, etc from there.

While Visual Studio's Database Projects has some features SQL Management Studio does not have I don't think I can live without a "table designer".

Is there a table designer built into VS's Database Project I'm just not seeing?

Answer 1

No, there's no table designer.

If you're starting to think about version controlling your database, you ought also to be thinking about writing actual SQL to implement your database objects. That's the route that the Database Projects force you down. If you can't write the SQL for your database changes, how are you going to be able to review and appreciate a diff between how a table was 6 months ago, and how it currently is in your project?

Answer 2

I've been using VS2008 Database Projects for about 10 months now for our version control. Every now and then I do still use the table designer, it is a quick and easy tool. I believe the majority of your question centers around workflow as this is what I found to be the most challenging part about development in a version controlled environment. I would recommend continuing to design your objects in Management Studio or however you're comfortable and then do a create script and import that script into your Database project. There are some quirks when doing this, you'll need to always script the create statement even if you're performing an alter in your environment. As well you'll need to remove any USE statements for your database as the context in which you're importing your scripts will always be in the project you're importing to.

We have found that a successful workflow for us to facilitate code deployment is to have a production branch which is branched to a Main (development branch) and then to test. All new development is done in Main and merged by changeset to each other environment as required.

You can import your scripts from your development environment by right clicking in the solution and clicking import scripts. I recommend that you check all the options to overwrite objects that exist, import extended properties and import permissions.

Answer 3

After changing your DB schema using SSMS's GUI tool, you can use Database project's Schema Compare tool to update your project files (set the source to be your database and target to be your project). This way you can keep using GUI tool to manage the schema and the database project will manage the versioning.

Answer 4

There is no visual table designer in Visual Studio 2010 Database Project. But, concerning version control for databases, there is a workaround - you can use SQL Server Management Studio together with Red Gate's SQL Source Control.
It costs some money but definitely is worth it.