September 2, 2008 at 9:03 am
Hello, hopefully this is the appropriate forum for this question.
We've been debating our approach with regards to handling changes in db object definitions. Our environment is SQL Server 2005, with a number of Coldfusion developers using SVN for source control. I mention this to forestall any talk of VSS and any possible integration with SQL Server; the powers that be here use SQL Server but they try their best to stay away from things Microsoft :hehe: In other words, SVN is going to be the source control.
Short version, main issue: we are wondering what is best to store in source control when it comes to tables, views and procs.
Our past approach to db source control was basically nonexistent. This would end up biting us when something in production broke and it was discovered that a dev proc differed from a production proc, and nobody knew why (you know the drill).
What we've started doing is keeping the *current create statement* for any table, view or proc up to date. Each version of the object will then reflect the reality in the database; a previous version can be pulled up for reference to see what has changed. The alternative to this (for tables) would be storing alter table scripts, which would then be able to be run as part of our deployments (web site fixes get rolled out weekly, unless it is a "hotfix"). While I can see some benefit to this, I also like having the chain of create object scripts, which makes it very easy to compare versions. It is also possible of course to store both the alter scripts and the revised create scripts, but I fear anything that requires developers to do that much work when they change things 😛
In addition to tables, views and procs, I'm not sure what to do with things like changes to indexes and granting permissions to new objects (this is currently done manually to give the web users the correct permissions per object; I'm sure there is room for improvement here). In short, any kind of database change that falls outside an object alter/create script that still could be needed for a deployment.
If you've made it this far in this ramble, I commend you! I welcome any and all thoughts on this. For better or worse, we basically took our coding source control approach and are trying to apply it to databases, so I have no clue if we are doing things remotely like the norm 🙂
Thanks, Rick.
September 2, 2008 at 9:17 am
You're definitely on the right path.
One thing you might want to look in to is DDL triggers. They can track a lot of the change scripts, etc., and you can insert those into a table. Periodically dump the contents of the table into source control, and you have a chain that won't be accidentally missing something.
- 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
September 2, 2008 at 9:30 am
This is an area that MSSQL Server is pretty lacking. It would be nice to have something built in, but even using VSS, differencing anythng is pretty ugly.
I like to keep entire schema versions. I keep the entire database schema on a server where I can access it for a couple of versions back and then keep backups of any older versions. This makes it pretty easy to use a differencing tool (Red-Gate's is good, I use xSQL Object here). The differencing tool generates the ALTER's, CREATE's, and DROP's that I need for any version.
This has been easier for us than trying to track individual changed objects. When everything is working, I just script the differences and we are ready to create a deployment package.
September 2, 2008 at 9:30 am
I would go for breaking down all the objects into the individual create scripts necessary to maintain them.
We use Microsofts Visual Studio Team System Database Edition. It can do full rebuilds from scratch, compare versions from source control and generate incrementals (although parts of the incremental scripts have to be edited). I've also tested DBGhost and Red Gate's SQL Compare. Each of these tools can also work with individual scripts under source control. I'd strongly recommend picking up one of the three and putting it to work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 2, 2008 at 9:38 am
Wow, glad I asked. I didn't really think of looking for tools out there that could do some of the work for us, I'll certainly look at some of those being mentioned. And I'm not familar with DDL triggers, but I'll check into it. I'm a recently-converted developer to DBA-ville, and as a dev tend to code with a simple text editor, pooh-poohing fancy tools--I can see that this mindset is very likely limiting me 😀 This is a very different animal so I need to shift gears a bit.
Thanks for all the replies.
September 2, 2008 at 9:45 am
I should have mentioned, VSDB is EXTREMELY expensive. It's a wonderful solution if you can get it, but most people are going to be happy with Red Gate or DBGhost.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply