1

We originally dismissed using database projects in conjunction with TFS as our solution for our deployment and soucecontrol needs. However, in the interest of thoroughness, I'm exploring and prototyping it.

I've set up my database project (with add to source control checked). I've checked in the changes. Now, where do you develop from? I've tried ...

  1. connecting to the remote development server to make changes
  2. syncing schema to (localdb)\Projects and making changes there
  3. directly in the Source Control Explorer

With option 1 and 2 I don't see an automated way to add code to source control. Am I suppose to be working in the Source Control Explorer? (this seems a little silly)... Is there a way to commit the entire solution to source control? My apologies in advance, I'm a database developer and this concept of a "solution" is very foreign to me.

Also there were a lot of chatter about Visual Studios doing a lot of ugly things in the back ground that turned a lot of development shops off of database projects. Can someone share your experiences with me? Some of the pitfalls and gotchas.

And yes, we have looked at Redgate SourceControl (very nice tool).

Improve this question
2
  • Given that you are using the VS 2010 version of database projects (and not the newer stuff), then the Visual Studio Database Guide should be very helpful for you. Commented Dec 13, 2013 at 3:21
  • @HolisticDeveloper another option would be to install SSDT for VS2010 (from msdn.microsoft.com/en-us/data/tools.aspx), which tries to simplify things a bit versus the older database projects. As mentioned in one of the other answers, if you're looking to use TFS you'd generally work with SQL Server database projects in the Solution Explorer just like any other project type. This gives you a number of benefits in addition to TFS integration, and it's easy to deploy up to your test and production databases, or generate deployment scripts if verification is needed. Commented Feb 3, 2014 at 20:00

3 Answers 3

Reset to default
4

Generally people do one of two things:

  1. Develop in Visual Studio, via the Solution Explorer. Just open the project like you would any other project, add tables, indexes, etc. You even get the same GUI for editing DB objects as you get in SSMS. All changes will automatically be added to TFS Pending changes (just like any other code change), and can be checked in when you're ready.

  2. Deploy the latest DB (using Publish in VS) to any SQL Server, make your changes in SSMS, then do a Schema Compare in Visual Studio to bring your changes back into your DB project so they can be checked into TFS.

I've been using DB projects for many years and I LOVE them! Every developer I've introduced them to, refuses to develop without them from that point on.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+1 DB Projects are outstanding. Reference checking and partial projects are amazing features that I am heavily reliant on.
2

I'm going to explain you briefly how we use DB projects with TFS.

We basically have one DB already done and if we require any changes or new tables we create them or alter them directly in SQL Server (each developer has its own dev SQL Server).

Then in VS from the SQL Server Object Explorer we drag the tables we want into the DB project so when we check in the changes, every user in TFS would be able to get them and then publish that project that will generate and execute a script into the DB.

This is the way we use to develop when we need to add specific tables or records to the DB so we don't have to send emails with scripts or have them stored in an specific location (even with source control). This way we can get latest version of the project and publish it to ensure we have the latest DB version although it requires the user (who made the changes) to add them to the DB project.

Other way could be to do all the changes (and can be done without any problem) directly in the DB project and then publish it. That one would be a more right way to do it so you do all the changes directly in a source controlled project, but as you know, is always more comfortable to work directly through the SQLMS.

Hope this helps somehow.

Improve this answer

1 Comment

thanks for the reply. Just wondering if you're just using database project to hold objects... what benefit do you get out of using database projects. I guess if you're using it "publish" to other servers but I do not wish to use this feature due to the sensitive nature of the data. I'm worried this automated feature will mess up the data and create work work than necessary. Have you had any bad experiences with mucked "publish"?
1

We use the SSDT tools and have implemented the SQL Server Database Project Type to develop our databases:

http://www.techrepublic.com/blog/data-center/auto-deploy-and-version-your-sql-server-database-with-ssdt/

The definition of database objects and peripheral SQL Code (e.g. functions, sprocs, triggers etc) sit within the Visual Studio project and all changes are managed through VS. The interface is very similar to SSMS and, at this point doesn't cause any issues.

The benefits of this approach for us are as follows:

  • An existing SQL database can be imported into the SQL Server Project and managed through Visual Studio.

  • SQL object definitions & code can managed through the same version control system as the rest of the application code.

  • SQL Code can be checked for errors within Visual Studio in much the same way as you'd check your C# / VB for compilation / reference errors.

  • You can compare database schema's (within Visual Studio) between environments and easily identify key changes that you need to be aware of.

  • The SQL project can be compiled into a DACPAC file for automating deployment to different servers using a CI / Build Server (using the sqlpackage.exe utility without any custom scripts or code).

In essence developers can have a local version of the database to work on but would manage any changes through VS, then publish the changes to their local database. Once the changes are complete, the changes are committed to your version control system and then built centrally & automatically through a CI / Build server to ensure that all changes integrate and play nicely in much the same way that your other code is.

Hope that helps :)

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.