SQLServerCentral / Discuss Content Posted by Raj Vasant / Article Discussions / Article Discussions by Author / Source Control in SQL Server / Latest Posts

RE: Source Control in SQL Server

Emito — Mon, 22 Jun 2009 13:30:06 GMT

You can sort files if you install an add-on to the "SQL Server Management Studio" :http://www.sqldbatips.com/showarticle.asp?ID=78I use this tool and works!.Reggards, Emiliano.;-)

RE: Source Control in SQL Server

Jeff Moden — Sat, 03 Jan 2009 21:17:32 GMT

We use a ticketing system to both assign problems and track code. Ticket number in the file name or a folder name works wonders. Each object is stored separately in the form of schema.objectname.objtype.Really helps if you do "change controls"... ;)

RE: Source Control in SQL Server

David Atkinson — Sat, 03 Jan 2009 04:18:37 GMT

[quote][b]aber (1/2/2009)[/b][hr]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?[/quote]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

RE: Source Control in SQL Server

Matt Miller (#4) — Fri, 02 Jan 2009 11:19:05 GMT

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....)

RE: Source Control in SQL Server

Steve Jones - SSC Editor — Fri, 02 Jan 2009 11:10:32 GMT

http://www.sqlservercentral.com/articles/System+Development+Life+Cycle/vcspart1/523/

RE: Source Control in SQL Server

aber — Fri, 02 Jan 2009 11:01:45 GMT

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

RE: Source Control in SQL Server

Matt Miller (#4) — Fri, 02 Jan 2009 10:40:32 GMT

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.

RE: Source Control in SQL Server

aber — Fri, 02 Jan 2009 10:25:46 GMT

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 developerTom 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?

RE: Source Control in SQL Server

Steve Jones - SSC Editor — Fri, 02 Jan 2009 10:24:35 GMT

I agree with Matt. You need rules for Devs and DBAs, backed to some extent by permissions, to ensure that some VCS is used.

RE: Source Control in SQL Server

Matt Miller (#4) — Fri, 02 Jan 2009 10:11:22 GMT

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.

RE: Source Control in SQL Server

aber — Fri, 02 Jan 2009 09:53:52 GMT

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

RE: Source Control in SQL Server

tymberwyld — Fri, 30 Nov 2007 03:36:33 GMT

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:[code]_InstallFunctions CLRProcedures (Categorized folders of Procs for easier maintenance) Products Companies etcTablesViewsQueries[misc files like the database script][/code]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!

RE: Source Control in SQL Server

sqlgreg — Tue, 05 Dec 2006 12:18:00 GMT

Visual Studio Team Edition for Database Professionals includes improved source control functionality. In addition, you can associate assigned work tasks with source changes. I attended a product launch last week during which it was indicated that the Team Edition suite of products is where Microsoft is investing their source control development budget; Visual Source Safe is not being developed anymore.

RE: Source Control in SQL Server

SQLZ — Tue, 05 Dec 2006 07:13:00 GMT

Amongst the other problems that have been mentioned, for me, the biggest problem with VSS integration was something the author alluded to in the conclusion:

"...whenever a script has been updated, the only way to reflect the changes in the source control is to script the file and update the changes".

And vice-versa - when you change your source control file, check it in you then have to remember to apply those changes to the database.

Which is why I developed an integrated source control app that automatically applies the changes to the SQL Server when a script is checked in (link in sig. small plug ). Oh, and the source control database sits on a SQL Server.

But then, as David indicated, some people shy away from the integrated editors - which I can also relate to.

So I then thought I'd throw in intelliense - only for Red-Gate to come and steal my thunder.

RE: Source Control in SQL Server

David Atkinson — Mon, 04 Dec 2006 07:59:00 GMT

Are there any other source control features that would make sense to control from within SSMS?

Noel mentioned labelling. Would this be something that you would want to do from inside SSMS? Or are some features best left to the source control client?

David

RE: Source Control in SQL Server

Rob Sanguin — Mon, 04 Dec 2006 07:22:00 GMT

I agree with Noel wholeheartedly, the lack of alphabetical sorting is extremely annoying and wastes precious time.

However I much prefer to have source control integrated into the 1 piece of s/w where I do all of my DB development (SSMS), so that's a big plus for me.

My other bug-bear at the moment is that when initially adding a file to a project it is created as binary so I can't compare different versions, I have to go into source safe -> right click the file -> properties and change binary to text. This would be a useful feature for SSMS too.

Overall though, it's a good start, but could try harder!

RE: Source Control in SQL Server

David Atkinson — Mon, 04 Dec 2006 05:47:00 GMT

Hi Noel,

For me it's a trade off between a "marginal" improvement and the familiarity of the source control client. If I'm going to have to have my source control client open anyhow (which I do), it's quite a trival operation to alt-tab to it and perform whichever operation I need. As you point out, there are some functions that are only available in the client, so I guess you probably have yours open too. If labelling (and the other functions that you can't do in SSMS) were integrated, would that make your life a lot easier? I definitely agree with the principal that the fewer tools that are required to do a job, the better.

David

RE: Source Control in SQL Server

Noel Kennedy — Mon, 04 Dec 2006 04:37:00 GMT

Hi david,I used to use VSS's client directly as you described. I have found that management studio saves a marginal amount of time as i don't have to go in and out of QA & VSS all the time as was the case with sql 2000. SQL 2005 doesn't replace the VSS client completely as I still have to use this to sort out any messes made by management studio & do things like labelling.

RE: Source Control in SQL Server

Noel Kennedy — Mon, 04 Dec 2006 04:33:00 GMT

pretty bad as well.4. Everytime i open a solution, I get a message about solution vs project binding. While this is less important, it reminds me everytime i use 'source control' that this is a shoddy implementation.

RE: Source Control in SQL Server

David Atkinson — Mon, 04 Dec 2006 04:31:00 GMT

I've always shied away from using any of the source control integration offered by editors/IDEs, instead using the source control client itself for check-outs and check-ins. I can't quite put my finger on why I do it this way, because I definitely like the idea of integration. I think it's because I know that regardless of the application/editor I eventually use to edit my source controlled files, using the same UI for the check-out/in operation is somehow comforting to me - I know that it's something that I've done successfully before, it doesn't involve any more configuring of source control settings, and I have confidence that it will work.

I'd be curious to know if it's just me, or whether most people actually prefer using the built-in source control integration that various IDEs like SSMS and Visual Studio provide.

David Atkinson, Red Gate Software

RE: Source Control in SQL Server

Noel Kennedy — Mon, 04 Dec 2006 04:30:00 GMT

I have found the following significant irritations when using Managmenent studio source control:1. It creates 'connection objects' in the project, these can be very confusing and dangerous as they can easily point to your live environment. This means when you open a script in source control you have to carefully check which environment, management studio has decided to open a connection to.2. It doesn't sort scripts in alphabetical order?? The fact this feature is missing is extremely aggrevating and is surely very easy to develop.3. Getting source safe to create the desired folder structure, is impossible (and if not impossible very difficult). Not sure if this is management studio's fault as VSS is

Source Control in SQL Server

Vasant Raj — Wed, 29 Nov 2006 15:02:00 GMT

Comments posted here are about the content posted at temp