Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tracking Database Schema Changes with DbPro


Tracking Database Schema Changes with DbPro

Author
Message
jacob sebastian
jacob sebastian
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 2523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3004.asp

.
Malcolm Leach
Malcolm Leach
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 71

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
Grant Fritchey
Grant Fritchey
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29975 Visits: 32502

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 Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
jacob sebastian
jacob sebastian
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 2523

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



.
Tatsu
Tatsu
Mr or Mrs. 500
Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)

Group: General Forum Members
Points: 510 Visits: 307
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.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Grant Fritchey
Grant Fritchey
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29975 Visits: 32502

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 Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
yonision
yonision
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 164

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





kannan ramasamy
kannan ramasamy
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 146
You stand here.

I am one among the poor souls.

Eagerly expecting your next articles.

Regards
Kannan
Mark inmon-448965
Mark inmon-448965
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 55

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


Tatsu
Tatsu
Mr or Mrs. 500
Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)

Group: General Forum Members
Points: 510 Visits: 307
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)

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search