Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Source Control in SQL Server Expand / Collapse
Author
Message
Posted Friday, November 30, 2007 3:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
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!



Post #427990
Posted Friday, January 2, 2009 9:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 3, 2014 6:59 AM
Points: 2,154, Visits: 213
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
Post #628922
Posted Friday, January 2, 2009 10:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:04 PM
Points: 7,105, Visits: 15,454
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?
Post #628938
Posted Friday, January 2, 2009 10:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #628954
Posted Friday, January 2, 2009 10:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 3, 2014 6:59 AM
Points: 2,154, Visits: 213
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?
Post #628955
Posted Friday, January 2, 2009 10:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:04 PM
Points: 7,105, Visits: 15,454
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?
Post #628970
Posted Friday, January 2, 2009 11:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 3, 2014 6:59 AM
Points: 2,154, Visits: 213
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
Post #628988
Posted Friday, January 2, 2009 11:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #628992
Posted Friday, January 2, 2009 11:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:04 PM
Points: 7,105, Visits: 15,454
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?
Post #628995
Posted Saturday, January 3, 2009 4:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:12 AM
Points: 31, Visits: 405
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
Post #629247
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse