Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Source Control in SQL Server


Source Control in SQL Server

Author
Message
tymberwyld
tymberwyld
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 274
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:

_Install
Functions
CLR
Procedures
(Categorized folders of Procs for easier maintenance)
Products
Companies
etc
Tables
Views
Queries
[misc files like the database script]


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!



aber
aber
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 239
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7636 Visits: 18043
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?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36008 Visits: 18728
I agree with Matt. You need rules for Devs and DBAs, backed to some extent by permissions, to ensure that some VCS is used.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
aber
aber
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 239
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 developer
Sally - 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?
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7636 Visits: 18043
Oh - I didn't understand your question under that light at all.

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?
aber
aber
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 239
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!BigGrin
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36008 Visits: 18728
http://www.sqlservercentral.com/articles/System+Development+Life+Cycle/vcspart1/523/

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7636 Visits: 18043
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?
David Atkinson
David Atkinson
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 507
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.

David
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search