Tracking Database Schema Changes with DbPro
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3004.asp
.
-
That's good overview of the schema compare facilities of DBPro but the example process you have chosen is fundamentally flawed as database changes should NEVER be reviewed at deployment time. The time for deciding whether a change needs to be part of a build and deployment is when the change is checked in to the main codeline in a source control system, nowhere else.
Why?
Because that's the way that we manage all other code assets.
For example, if three C# class files have changed you would review any changes made (if that quality check, normally a peer review, is in your process) by using the source control system's history and diff functionality. You wouldn't compare the current source files with the previous source files and pick and choose which changes are going to be deployed would you? No, you simply build and deploy what is checked in to the main codelines in source control and then test, test, test. If the review picked up any less than desirable changes then they should be rolled back or a new version checked in to the source control system.
This pick-and-choose method of deployment is manual and error prone and means that you do not have a solid audit trail of what was in the release and, crucially, why. The best you can do is look through the delta/upgrade script that was produced and attempt to infer what was, and wasn't, deployed.
I think we are so used to the old "write-an-alter-script-and-get-the-DBA-to-check-it" approach that we still use the same mentality when we start to use tools to help with changes. We need to think differently - use source control for database changes and use the tools properly to manage those changes.
I think the first article in your series should have been about the database import and build functionality of DBPro...
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com -
Nice introduction to DBPro. Well written.
But, I have to agree with Mr. Leach on the basic point that instead of comparing databases as a deployment methodology, we in the database development field need to approach our systems, as much as possible, in the same way that the application developers do. This means building out of source control. That's where tools like DBPro, and for that matter the new version of SQL Compare and Mr. Leach's DBGhost, shine. The largest strength in DBPro wasn't the compare utility (frankly, if you have access to SQL Compare, the one in DBPro stinks) but rather it's ability to build an entire database directly from a set of scripts. Further, that fact that you automate this using MSBuild or TeamBuild allows you to add very smooth database deployments for continuous integration and other types of automated deployments. \
I was hoping that you were going to cover how to do incremental builds from those same scripts because that's something I have yet to master using DBPro. If you, or anyone else, has that aspect of the tool thoroughly quantified, that'd make a very useful article.
Keep 'em coming.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you Mr.Leach and Mr.Fritchey for your comments. What I like best with SQLServerCentral is the large number of DB experts who share their thoughts and experiences in the discussion forum. I agree with the points put forward by both of you.
I have seen many people who work on systems which are partially live and partially under development. They do regular updates on the production database and application, which includes bug fixes and/or new features. Lucky are those guys who work on teams those are very much process oriented. But there are lots of poor souls who are deprived of that luxury. For them, an update on a production application is going to be a nightmare. My intention was to introduce DbPro as a helping tool to those guys who need to identify and review the schema changes before doing a production update.
I was planning to write about features like DB Schema Comparison, Unit Testing and Test Data Generation. I will keep your points in mind when I come up with my next article.
Thanks
Jacob
.
-
That was a very well put-together article Jacob. The way you highlighted your points in the images was very helpful in following your message. I am really looking forward to your article(s) on Unit Testing and Test Data Generation as I am just getting started in those areas.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Let me plug in here another solution when it comes to SQL Server Version control - one that is more 'lightweight', simpler and more flexible than the ones mentioned above:
http://www.nobhillsoft.com/Randolph.aspx
and the nice thing is... if you give us your opinion of it (in few paragraphs...pros and cons... why u think its good, why not) we will give you a free copy!! (offer stands till august 3rd 2007)
thanks
Nob Hill Software Customer Support
-
No doubt that Jacob's article on Unit Testing will be as good as the job he did on this one. You might also want to check out the two I posted on the same topic:
http://www.sqlservercentral.com/columnists/gFritchey/2689.asp
http://www.sqlservercentral.com/columnists/gFritchey/2805.asp
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
You stand here.
I am one among the poor souls.
Eagerly expecting your next articles.
Regards
Kannan
-
Does anyone have a way to use DBPRO to automatically take your schema and add it to SourceSafe?
It would be nice to have this done nightly and notify me if the schema changes from the night before.
I know this can be done manually but looking for a way to do this automatically
-
Since DBPro/DataDude is a Visual Studio development tool it's not going to do anything automatically. If you are looking to monitor schema changes then you probably want something more like Idera SQL Change Manager. I have not used the product myself but I am planning on evaluating it and its competitors later this year for a possible purchase down the road.
Disclosure #1: I do not and have never worked for Idera and am not affiliated with them in any way.
Thanks Grant! I had skimmed those articles when they were first published but had forgotten about them. I will take a more detailed look this time and see if my meager beginnings are in line with your suggestions.
Disclosure #2: I have nothing to declare but my variables (printed next to a customs logo on my favorite TechEd t-shirt courtesy of Red Gate)
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
It sounds like you're almost using the tool backwards. We've been spending all our time working on the schema, in source control, and then we automate getting it out to databases. Once we started using this tool, no development was done on a database that we had to automate getting into source control. It's a paradigm shift, but you have to look at source control as your authoritative source of the database under development, not the database itself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Another tool that could help with your schema changes is SQL Compare Pro 6.0 by Red Gate (disclosure #1: I do work for Red Gate; disclosure #2: I'm the architect of the tool in question). It allows you to read in SQL files and compare them directly with another set of SQL files or with a live database. The nice thing is that it allows you to synchronize the changes in both of the directions, so you can update your live database as well as your script files to reflect the changes. Upgrading the scripts directly clearly has some limitations, I summarized these on Source Control and Databases. On the other hand, with this tool you can support both of the common development styles (and their mixtures), i.e. one where you maintain a set of creation scripts and execute them to get a db, and one in which you change the db directly, but want to update your SQL scripts in your source control.
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
This is a replay to the post above by Mark:
Mark, the tool that i have mentioned from Nob Hill Software (http://www.nobhillsoft.com/Randolph.aspx) does exactly that: automatically monitors your database, finds the changes, can notify you of all that's changed (via reports or even email) and pushes the changes into SourceSafe
-
Mark,
If you're running SQL 2005, have you considered creating DDL triggers along with some form of Audit database? I did that on our Dev servers and it's been very useful to know who has created, altered, or dropped objects in the database. I even have some minor insight into the command(s) run. Now if everyone would stop using "sa" just because it has no password......
-Pete
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply