August 1, 2012 at 7:16 am
Hi all,
I'm fairly new to SQL development-- I've been at it for about a year and a half now. One thing that I'm still having trouble with is remembering which objects I've changed in the process of developing a specific bug fix. Most times a fix can involve creating or updating multiple objects (e.g. - adding a table or column, updating a proc, creating a new view or index, etc.) as well as maintaining 1 or many data script(s).
Consider all of the sql objects that can be affected, and that I will be working on multiple tickets/fixes at the same time before the solutions are deployed to a production environment on our normal release cycle several weeks later. Couple that with the fact that multiple developers work in the same Dev environment as me.
How do you track all of the objects that you're working on? We use redgate sql source control and maintain a code repository, but when my work is lumped in with the work of several other developers like that, I have no idea what's missing or what I've forgotten to check in. Usually then, the errors will present themselves when we test the deployment to a QA server. I'm glad that at least I can catch the errors there, but I'm sure it looks quite careless to the cadre. Really, my issue is just that I have a terrible memory :pinch:
Any suggestions for how I could personally improve the way I keep track of what I'm developing or how you keep track of all of the changes you make as you go along would be helpful and appreciated!
Thanks!
August 1, 2012 at 7:39 am
I use a combination of things to track changes.
First, I have a folder for each project I'm working on, and store all DDL and DML scripts for the project in there. Ideally, you want to keep that in source control (VSS, TFS, or whatever else you're comfortable with), so you have version-tracking and all that. If you don't have source control for that kind of thing, then a simple Windows folder with the project name or identifier will do, but it's an inferior solution to source control.
Second, I have a DDL trigger on every database on Dev and QA that tracks ALL modifications to any object in any database. I log that in a DBA database on each server. So, I can pull up all changes to anything in Database-X, in time-sequence, with a record of who made what changes when. I can, as need be, replay those changes in order, to get a database to any state (DDL-wise) it's been in at any time. If I drop a database on the dev server, I can re-run all scripts from the original create database, on up to any point in its history, using this.
Third, I use RedGate's SQL Compare to compare the QA version to the production version, or the dev version to the QA version, and get all object-level differences. I can use SQL Data Compare to get data differences, if those will matter. I can use this to create scripts that will bring all objects into sync, or can pick a subset.
I use all three things. Each has its own pros and cons. Sometimes, I use them conjunction with each other. For example, if a dev hasn't kept track of what objects he changed, but needs those changes rolled forward to QA, I can use RedGate to compare the objects in the databases and generate a one-step script that will sync them, and use the DBA database DDL log to pick out what objects in those were modified by that particular dev, and leave other dev's work alone.
Does that help?
SQL Compare does have a cost associated with it. The rest can be done for free.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 1, 2012 at 7:55 am
It definitely does help! The second option really piques my interest. Tracking that in a db would be really helpful. Do you consider it a definitive list, or do you run into holes with what it tracks?
August 1, 2012 at 7:58 am
Two words: Source control.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2012 at 8:29 am
Two words: got it.
😉
August 1, 2012 at 8:34 am
🙂
Can't you assign code to bugs, make bug notes in the checkin comments, etc?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2012 at 8:56 am
We use TFS rather than Redgate's SQL SourceControl, but I'd imagine there is some way to check where you still have things that are checked out.
From your description it sounds like there's a flaw in your process. Do you:
1. go directly to the SQL server, update the objects there and THEN check out and check the updated code back in, OR -
2. go into your source control repository, CHECK OUT the stuff you need to change, make your changes, and then check the code back in and apply it to the server.
#2 is the recommended way to source control (since you put your locks on the file early and others are aware that you're changing those items), but it almost sounds like you're not following that process.
----------------------------------------------------------------------------------
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?
August 1, 2012 at 11:17 am
eg61088 (8/1/2012)
Two words: got it.😉
Fantastic! You might be surprised how many database development shops do not 'have it' and do not 'get it' as to why it is critical. If you're deploying things by hand read this article. It's the best attempt I have seen at crystalizing how to manually manage change for a database.
If you have the opportunity to move away from manually managing change and you haven't heard of or tried SQL Server Data Tools (SSDT) I would highly recommend you look into it. It was released around the time SQL 2012 was released and it attempts to add intelligent deployment techniques to the database world, something the app devs have been taking for granted since the dawn of time since they don't have to deal with existing system state, i.e. the data. While SSDT was released with SQL 2012 it allows us to target any database platform from 2005 to 2012, and it is free! The Database Projects within it are an extension of the Database Projects only available in the Visual Studio for Database Developer editions of VS2008 and VS2010 that cost thousand$ per seat. SSDT requires Vista or newer to install which hopefully you're on.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 1, 2012 at 12:15 pm
Wow it's great to see all of these responses! Thanks to all of you! A few notes though:
@Gila, we can note in the checkin comments which ticket # it is for. The issue is that a check in only occurs when we are fairly certain that the code will work correctly. So if a ticket takes a week to develop in dev, I often don't remember all of the things that I've altered for a solution by the time I'm ready to check them in. Using red gate source control, it would be easy to know which uncommitted changes must be added. Except there are dozens of objects being changed in the same database by different people so it's easy for changes to get lost in the shuffle.
@matt-2, there's no check-out process. That sounds like a pretty good way to track objects that you've worked on!
@OPC, thanks! We use an SSIS package developed in BIDS to do our deployments, but that process is only as good as the developers it supports... It is dependent on everyone having checked in the most up-to-date version of any objects or data scripts needed for a release. PS- Thanks for the great article you've referenced. there are a lot of good points to take home there!
August 1, 2012 at 1:04 pm
@Matt, there's no check-out process. That sounds like a pretty good way to track objects that you've worked on!
It is a critical point, and is the crux of what is discussed in the article I linked to. It amounts to shifting the development paradigm from change the sql server first then sort out what needs to go to the next environment from all the other people working in the server to open a code artifact from source control (existing or an empty file just added) then change the server after changing that code artifact.
There are tons of scripts out there that can help you reverse-engineer an existing database into a set of script files to build an initial check-in into source control. That's your line-in-the-sand, and from that point forward nothing should happen unless it starts with a code artifact stored in source control.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply