I haven't seen the VS for DB Developers yet, however, I've been using the "Database Projects" of Visual Studio for years to integrate scripts into Source Safe! They've been awesome! I can create any folder structure I want and also it sorts things alphabetically!

Usually I create one solution then have multiple Database Projects (one for each DB). I can then manage the connections right in the Projects so that the scripts can be executed right from VS to multiple servers. Usually I just delete the pre-canned folder structure and make one like so:

The "_Install" folder contains my batch files that can be used for scripting the entire database or just a subset of changes for a particular release. Since I create "generic" scripts which can be used for the creation or alteration of DB objects, I don't really have to worry about different batch files. Usually when different versioned deployments need sent out, I zip up the batch files and only the affected scripts (maintaining their folder structure) into a backup folder (i.e. Scripts\2007-11-01.zip). This way I can always see what was released per deployment.

It's been a great help because I can also have Developers edit their scripts themselves, then review the scripts that have changed before deploying them. This way I don't need developers touching the DB all the time of sifting through different DBs or Servers just to find the latest version of a Proc. With the connections being the in the Project, the Developers have rights to script the objects into the Development servers, but only DBAs have rights to script them to the Staging / Prod Servers. So it's nice having everything in one place!

Does anyone know of a way to have SQL source control at an object level instead of at a project level? What I really want to see is table X and what the history is on that object. What I understand of VSS is that you have to add a script of the table to a project and that will by under source control. However, if someone else makes a change to the exact same table later, there is no connection to the source control that was made prior in your project.Am I misunderstanding this? Is there a better way?Thanks

Just like using source control elsewhere, if you modify the code outside of source control - there's not going to be any tie-in/history in the Source Control system. That's true here too.

In this case - I've found that you can get around the human factor by simply putting up barriers to entry. Meaning- if a developer has access to everything, then it's altogether too easy for them to go in and "just update something" without following protocol; if, on the other hand, the only thing they see is their own local DEV instance, and all other environments are "limited permissions" to them, then the only way things gets moved into UAT, and then into Prod, is through checking in code.

Still - it's ultimately no different from using Source control for .NET code, or any other language. You have to respect the SCC solution's place, or it's worthless.

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

OK, I think I am definitely confused based on your reply of how SQL source control is working. Let me give a scenario and hopefully then I can understand how this really works:

Tom - SQL developerSally - SQL developer

Tom creates a table "CUSTOMER" and adds this script to a project called "CUSTOMERS" and checks this in.Sally updates the table "CUSTOMER" and adds this script to her project that has other scripts on it as well for her specific project.

Looking at Source Safe, first of all there is really no way to tell where table "CUSTOMER" has been modified and secondly which projects have touched "CUSTOMER".

Are you saying that each table, SP, View, etc. has their own project and when someone works on this object that they check out that project? If so, when a developer is working on a development project that touches multiple tables and SPs, he/she would be checking in potentially many projects into VSS?

It's customary everywhere I've worked to maintain a single project per database, and to tag work items to specific SQL objects being updated.

In your case, both developers would share the project, and would therefore be aware of each other's check-outs. If a conflict should appear, it would have to be resolved at check-in time. I don't think VSS was very good at identifying conflicts, so you'd usually want to run a comparison against the checked-in version before checking an object back in (in case someone checked it out and modified it before you get to it), OR implement an exclusive lock scenario.

In this case you might end up with separate projects for your App and your data, but it's the only way I know to do it. Work item tracking with the right SDLC helper app is pretty good at keeping track of related checkins under multiple projects.

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

OK, perfect. That helps tremendously. A few more questions on how to accomplish this, as you can probably guess by now we have NO source control on our SQL Servers at all at this point and something that I deperately want to change:

1. When creating the project for an existing DB that has many tables, views, sps, triggers, etc is there an easy way to create all of these script files other than scripting each one individually?2. Can you give me a sample of what a project would look like? (i.e. Tables CUSTOMER CONTACT etc SPs add_customer add_contact etc)3. Is there a way to label what scripts go together? For example, when working on a development project and I am modifying 2 table scripts and 2 sp scripts is there an easy way to show that these were changed for this one specific development project?

Thanks for all of your input! Your thoughts are extremely helpful to me and I hope will better organize our development efforts moving forward!:D

In addition to Steve's article on how to handle VSS integration - you can script the objects using SQL Server 2005's "Generate Scripts" option. If you look at the options - you can get it to generate separate script files per object, and you can regulate how much detail you want added to each of the objects (extended properties, permissions, etc....)

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

aber (1/2/2009)3. Is there a way to label what scripts go together? For example, when working on a development project and I am modifying 2 table scripts and 2 sp scripts is there an easy way to show that these were changed for this one specific development project?

When you check in the changed files to source control, you might consider checking in all related files together and use the same check-in comment, which provides some sort of common label.

Don't forget that you can periodically use tagging/labelling that exists with all decent source control systems. This is especially useful as you can define a version of your schema objects that you can later retrieve in one go.