SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Change Management Best Practices : Achieving an Automated App


Database Change Management Best Practices : Achieving an Automated App

Author
Message
Darren Fuller
Darren Fuller
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/DFuller/databasechangemanagementbestpracticesachievinganau.asp
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18505 Visits: 20440
Lots of good points highlighted and looking forward to reading part 2.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
R M Buda
R M Buda
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 300

I am a proponent of the manual scripts method (with some automation ).

Most of the drawbacks listed by the author can be summarised as "its a big hassle for the DBA". This is true in many situations, especially for some table and index changes and I am interested in seeing part 2.

For the bulk of development which is stored procs, views, functions and new tables, I use a hierarchical directory of folders sorted in the order that scripts must be applied and have a script called RunSQL which runs all the scripts.

With reference data, my RunSql script can read text files in comma delimited form and these are kept in VSS.


StefanJ
StefanJ
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 11
I am fairly new to SQL Server and originally struggled with the manual approach but now adopt the following approach which seems to work very well for me.

I have a Business Testing environment where I simply restore the Production environment.

I then document the following so that it can be easily replicated during the Production Close.

I then use Redgate SQL Compare to update this enviroment with the changes in the Development environment. I of course scrutinise the changes carefully.

I use Redgate SQL Data Compare to update reference data tables where fields have been added.

And DTS Backup 2000 to copy all the DTS Packages.

Following Business Testing I adopt the same approach to update Production.

I save the reports and scripts created by Redgate. And back up the original DTS packages before deleting and overwriting them.



mark baekdal-145375
mark baekdal-145375
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 6

therefore you cannot say who updated what code and when with a complete history of all changes...

making the change is not change management.


jwrichy
jwrichy
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 11

Good layout of the common solutions. We currently use the manual approach wioth templates for stored procs and triggers. This was working until we grew to about 12 developers. Now it has become a mess since everyone is at different levels of sql scripting and some better then others. We are currently looking for a new solution and are evaluating DB Ghost as a possible solution. Any one use or heard anything about it?

Can't wait for part 2!

Jim





Dave Goerlich
Dave Goerlich
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 1

This looks like it's going to be a very good series of articles. I'm always looking for ways to improve how we manage database change. We are a small team, with myself (DBA and actually lead developer too), and 2 other developers. We maintain 3 separate environments (Development, Beta and Production).

Currently I am the only person responsible for any database changes - schema, stored procedures and permissions. I've implemented a fairly manual approach, but I use Visual Studio.NET with a database project to manage "groups" of changes. VS.NET also will do some automatic scripting of schema changes, but you are on your own as far as stored procedure change scripts are concerned. The drawback is that all "rollout" status of each DB project, as well as any interdependency between projects (project B requires project A be rolled out first) is all completely manual, and I track it all in an Excel spreadsheet for now.

This system is effective for our needs now, but as the team grows, I'm sure we will need to make some changes.




Dave Goerlich

Ross Environmental Services
Peter Schott
Peter Schott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1481 Visits: 1919
Good teaser article to have us looking forward to the next part. This really just highlights some basic methods that are currently used. Some suggestions on better alternatives will be greatly appreciated when part 2 comes along. We use a separate change-tracking/approval system right now. I look forward to seeing how it can all be integrated a little better.

-Pete Schott
Ken McNeil
Ken McNeil
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: 1
Go to www.innovartis.co.uk and get the whitepaper.
Peter Schott
Peter Schott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1481 Visits: 1919
That's pretty disappointing. While I like looking at new products, it's a shame that this is pretty much leading up to "Use DB Ghost". How about those of us who just don't have a good software budget? It's difficult to justify purchasing another tool (even with the ROI calculator - that is a nice touch) to management. Anyone have some best practices that don't involve purchasing a new tool? :-)

-Pete Schott
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