Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Tracking Database Schema Changes with DbPro Expand / Collapse
Author
Message
Posted Tuesday, May 15, 2007 10:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3004.asp

.
Post #366189
Posted Wednesday, July 25, 2007 2:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 1:21 AM
Points: 73, Visits: 69

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
Post #384955
Posted Wednesday, July 25, 2007 5:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #384989
Posted Wednesday, July 25, 2007 5:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523

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

 



.
Post #384999
Posted Wednesday, July 25, 2007 7:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:46 AM
Points: 295, Visits: 280
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
Post #385075
Posted Wednesday, July 25, 2007 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #385086
Posted Wednesday, July 25, 2007 8:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:04 AM
Points: 52, Visits: 156

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




Post #385087
Posted Wednesday, July 25, 2007 4:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:57 PM
Points: 25, Visits: 143
You stand here.

I am one among the poor souls.

Eagerly expecting your next articles.

Regards
Kannan
Post #385254
Posted Thursday, July 26, 2007 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 29, 2008 2:20 PM
Points: 2, 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

 

 

Post #385496
Posted Thursday, July 26, 2007 11:14 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:46 AM
Points: 295, Visits: 280
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
Post #385520
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse