Source control, release processes and all that jazz

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Source control, release processes and all...

Source control, release processes and all that jazz

Rate Topic

Display Mode

Topic Options

Author

Message

dave.farmer

Posted Monday, May 11, 2009 9:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:57 AM
Points: 276, Visits: 230

I'm no DBA (yet?!), just a humble C#/SQL programmer who finds himself suddenly in charge of all things data, including the stuff mentioned in the title.

We are a new team of generalist programmers recently put together to create a new web application from scratch, and the database part is all down to me. Part of that is to define our working practices and controls. We are using Team Foundation Server and Visual Studio Development and Database editions.

We've had a brainstorm and decided there are two main ways to manage the source control and release process:

1) We make changes to the Dev database and, after suitable code reviewing (the exact nature of which is another item on the agenda!) we use Schema Compare (and perhaps Data Compare too for dimension tables - I've not looked at that one yet) to synchronise the database project in Team Foundation Server with the Development DB changes, and then update production from there at release time - again with Schema Compare. This seems like a very quick and simple process in many ways, but has its drawbacks (for instance, a typical "if exists... drop... create procedure... grant execute..." template doesn't work with schema compare as it doesn't understand anything outside the CREATE...END structure). Of course there would be checking and possibly amending of the update script too, but overall it seems to shortcut a lot of work that would be in the next scenario...

2) Maintain templated scripts for all database objects in TFS. Developers check out/change/check in the script, and we maintain a build script/list of changed objects, and some automated process to build an update script from all the items in the change scripts. This is the approach the DBAs at my last company used, but it seemed to incur a lot of DBA work for each release.

I think the tools in Visual Studio Database Edition should be a great help to the DBA, but I'm not sure how to make the best use of them. Ideally I'd like to come up with some hybrid of the two scenarios that allows us to keep well-formed SQL scripts under source control but avoid the overhead of the manual system by making use of the Visual Studio database tools (especially as there's just the one newbie DBA with no support - i.e. me!).

What do you think people? Have you devised the perfect strategy yet?

Cheers,
Dave.

Post #714236

Gift Peddie

Posted Monday, May 11, 2009 12:10 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:07 PM
Points: 2,574, Visits: 10,670

I think your plan is complicated closer to convoluted, here is what we did after two SQL Server hardware failure in one month. All developers save their compiled stored procedures in VSS/TFS as .sql file and you the team lead saves at least a copy of .bak in TFS every day or week depending on number of backups and changes. This means all your developers will be up and running in less than two hours. I can help with most things not covered by my nondisclosure.

Kind regards,
Gift Peddie

Post #714399

Grant Fritchey

Posted Monday, May 11, 2009 1:28 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:03 PM
Points: 6,773, Visits: 13,205

Do you mean you're using Visual Studio Team System Database Edition, or just the regular Visual Studio with a database project? I'm asking because you talked about Schema Compare & Data Compare and I just want to be sure you're not using Red Gate tools with the standard VS.

If you're using VSTS:DB, I'd say you're not using the tool properly. In general, I would want all changes to originate in the code, in TFS, just like it does in whatever .NET language they're writing in. We treat our database, as much as possible, exactly like .NET code. It gets checked in & checked out of source control (TFS, just like you) and changes are made then it's checked back in. Testing is done locally first, then on a shared development server. Sets of changes are marked via labelling and then released to a QA environment. Possibly they go straight from there to Prod, but usually more changes get aggregated & new labels are applied. We always build QA from a label and then use the same label to build prod.

You're pretty close though. There's some great posts describing how to use the tool over at Barclay Hill's blog. Recommended reading. I have a few posts with my fumblings at my blog too, but they're not as good as the other ones..

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems

Post #714452

dave.farmer

Posted Monday, May 18, 2009 4:15 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:57 AM
Points: 276, Visits: 230

Grant Fritchey (5/11/2009)

Yes, we're using Visual Studio Team System Database Edition.

The schema compare option seems much simpler, which is why it appeals to me over having to devise a structured way to control a release - I'm a developer who just happens to be the only one who knows a bit of SQL and therefore find myself responsible for what would normally be DBA tasks.

In the past I've always worked within established procedures that follow the source-controlled script approach you outlined, but as I mentioned this has always seemed to demand a lot of DBA time to administer. As my primary role is development I want to avoid being tied up in administration tasks for much of the time if at all possible - both for my own job satisfaction and in order to maintain a decent level of development output from me to contribute to the team (as it is already I find myself with more hours of development tasks assigned to me than to any of the other team members as this application is very database-heavy).

Cheers,
Dave.

Post #718912

Grant Fritchey

Posted Monday, May 18, 2009 6:22 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:03 PM
Points: 6,773, Visits: 13,205

I do feel your pain. I work as a development dba, so I straddle both disciplines although the overwhelming majority of my work is straight database work.

It does require a certain level of structure & discipline to get started using VSTS:DB, but once yo get the deployments set up & operating, it should reduce your overall workload. At least, I've seen it that way. My biggest problem is I can't get all of my development teams to work on the procedures out source control in the same fashion. Some do it, and their deployments are easy, and some don't, with the time-consuming deployments you're facing where I have to try to determine what has changed, what hasn't and what should be deployed & what shouldn't.

All I can say is, if you set up compound projects so that you have a base project for the database code and then a project for each environment that combines the appropriate server & app code with security, it really does work very well.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems

Post #718980

itamar-863608

Posted Monday, June 08, 2009 9:32 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, September 29, 2009 8:36 AM
Points: 13, Visits: 84

Hi Dave,
No need to pain.. you can use a tool for SQL Version Control, that will do the job for you.

--------

SQL Data compare , Synchronize data from diffrent surces, and many other SQL tools.

Post #730753

Mauve

Posted Tuesday, June 09, 2009 8:02 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 711, Visits: 1,285

See the following post which briefly describes our process.
http://www.sqlservercentral.com/Forums/FindPost702354.aspx
More "meat" can be found in the linked posting (message 474053).
http://www.sqlservercentral.com/Forums/FindPost474053.aspx

(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.

Post #731457

djones-1062714

Posted Wednesday, June 10, 2009 9:55 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 9:24 AM
Points: 10, Visits: 20

What we do is very simple. Maybe too simple you decide. We don’t use VSTS or VSTSDBE. Just VS 2008. We store all the database objects as scripts inside SCM. When we create a new development branch from the QA branch the objects are included in the new branch. When we need to make a change to a stored procedure, we check the scripted object out of the branch make the change, test it, and check it back in and it gets promoted with the rest of the code. When we compile the script on the database sever we compile them with encryption. This prevents any developer from going directly to the server to make a change.

Simple, but as a small shop it works for us.

David

Post #732422

djones-1062714

Posted Wednesday, June 10, 2009 9:57 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 9:24 AM
Points: 10, Visits: 20

Post #732425

djones-1062714

Posted Wednesday, June 10, 2009 9:59 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 9:24 AM
Points: 10, Visits: 20

Weird, my post added twice, but it wont let me delete one of them.

Sorry.

Post #732429

« Prev Topic | Next Topic »

Permissions