SQLServerCentral / Red Gate Software / Third Party Products / Products and Books / Change Managment in database development? / Latest Posts

RE: Change Managment in database development?

Steve Jones - SSC Editor — Fri, 29 Jun 2007 19:58:00 GMT

I've heard great things about both Red Gate and Innovartis (dbGhost) as products from people. Haven't really used either, but people seem to like both.

RE: Change Managment in database development?

John Rowan — Fri, 29 Jun 2007 11:05:00 GMT

We've been using Red Gate for quite some time now (maybe forever now that I think about it) and it is a product that I use almost daily. We are a software vendor and have to create custom upgrades for our customers to upgrade their versions. When I say custom, I mean that each client that needs to upgrade their system must have a script created that is tailored to their database as our customers can be at any version and patch level in our development tree and have to get to the destination version.

We have been using Red Gate to create the synchronization script, but it always (and I mean always) requires manual tweaking for data conversions and the like prior to sending to a customer site. Red Gate works great for as a Diff tool, but we are very excited about what DBGhost can do. I downloaded a trial license many months ago to do some ‘proof of concept’ testing on automating our upgrades and I was extremely pleased with the results. We will be getting a second trial here very soon to document the ‘proof’ for the $$ spenders here to approve the product.

I think it is in everyone’s best interest to at least review the DBGhost web-site and take a look at the capabilities that their product has. I originally found the DBGhost product from an article here at SSC that if I remember correctly was a product trial testimonial for Red Gate or one of the other big dogs. The author briefly mentioned DBGhost and I searched the web and found their site. I plan on using their entire product suite and I think it all will add much value to our operation as well as free up more DBA resources to concentrate on other tasks.

BTW… We are using MKS for our source control.

RE: Change Managment in database development?

Malcolm Leach — Fri, 29 Jun 2007 09:36:00 GMT

Hi Terry,

Don't get me wrong - your process isn't bad (sorry if I came across badly back there) - it's just not as optimal as it could be and, in fact, we aren't very far apart in most of our thinking. Perhaps a little history of where we came from might help...

<shimmering effect> We started out writing DB Ghost because we had virtually the exact same process that you use and, althought we were just five guys sitting in the same cubicle, we still managed to create problems when moving to the QA environment such as re-using primary key values for static data, overwriting each others logic changes and writing new code against columns that had been removed by someone else.

We said "we didn't have these problems with release 1.0" why do we get them now? The answer, of course, is that in R1 we just worked on the create scripts and then threw away & rebuilt the database whenever we did a release. So, we mused, all we needed was a black box that can just look at our create scripts, automatically work out what is different, and then make those changes to the target database without losing any data. We had some full-and-frank discussions over whether it was possible - some toys were thrown, harsh words were spoken and some were even sent to bed early. However we decided to write the "black box" and that's where DB Ghost came from (actually there was nearly a punch up over the name as well so maybe some counselling is in order ) </shimmering effect>

The custom scripts feature (before and after sync) is there so that any data migrations etc. (i.e. things that require semantic knowledge) can be slotted in.

Generally speaking the developers all work in their "wild west" sandbox databases and when they're ready, they check in their changes, backup the sandbox database and then use DB Ghost to update their sandbox database from the latest set of CREATE scripts from source control. Better yet, if the scripts are extracted from a baseline/labelled set then you have a known point to work from in source control to answer questions like "what has been checked in since I last synched my database"? As a purist a would suggest simply rebuilding the sandbox database from the scripts and then re-populating the data to avoid those annoying issues you get with corrupted data giving phantom failures during testing.

Another subtle benefit is that DB Ghost not only adds new objects and updates existing ones but it also removes anything that isn't in the source. Thus it tidies up your local database whereupon you can do a last run through unit testing to absolutely make sure nothing was missed from the check-in...

RE: Change Managment in database development?

sql8081 — Fri, 29 Jun 2007 08:49:00 GMT

Me again...

Just wanted to be clear that I'm not against Red Gate's products. Rather, I like them.

But I've seen Development groups get too excited about buying new, cool-looking software that may not be the right fit for them, or ends up not being utilized to its fullest potential.

It's all about trying it on to see if it fits.

Sometimes, I wish that the money that was spent on certain purchases had gone into my salary instead.

~Marc

RE: Change Managment in database development?

Terry Denham — Fri, 29 Jun 2007 08:36:00 GMT

I hope I'm not hijacking this thread but I feel the discussion is a good one. Malcolm (sorry I misspelled it before) the solution I talked about does use a "schema.sql" file as the master copy of the database schema (the same approach your product seems to take), but if you are not storing the diffs, how do I as a remote dev, get your changes applied to my sandbox database without dropping the whole thing.The approach I talked about uses both, the full schema file to represent the 'official' version and diff files that can be applied by team members to update their database environment.You bring up a good point and one that I didn't consider was to use the SCM to lock the 'schema.sql' since this resource is serving as the official version. If the patch diff generation process uses an exclusive lock of the schema.sql file to ensure that only diff is being generated at a time then this is much better than the 'hey i'm about to generate diff 15' approach that I mentioned in my first response.Now I highly recommend in any sandbox approach that devs don't get married to their copy of the database and that they periodically wipe it and recreate it from the official scripts and that any data they are they are using and any tables that they are working on that are not part of the official schema, they should have their own scripts to create the objects/data until that feature is ready to be merged into the official database schema.I see your product does allow pre/post scripts to run so I see how you could batch this up to use ant/nant/msbuild to use any SCM to lock the schema.sql file even if you are not using VSS.

RE: Change Managment in database development?

sql8081 — Fri, 29 Jun 2007 08:35:00 GMT

Terry,

I think you're doing your company a favor by having a solid process in place. Adding additional software into your process often requires your process to change without significant benefits in control or quality. Keep it simple, keep it flexible.

We also use a similar process to yours, but with some minor differences. The differences are probably based on our different organizational structure and methodology, but fundamentally the same.

Database developer sandbox.Shared Application developer playground.QA/Testing environment.Production/Customer delivery.

In the database developer sandbox, the developer can go hog-wild here. When they have tested their solution, they push it into the next mode (or zone). For tables and data, the scripts that they write are simply the diff scripts to get a table from one build to the next. We do not modify the Create table script in order to generate a change script. It's not that hard to write a change script to modify one or more tables and manage any data migration that is necessary as the result of the schema change. So I don't see the need for software to produce diff scripts. Also, we have setup a naming convention for SQL files that allow any developer to know if another has also changed the same object among the "myriad" of changes that build up over many months or years. Once a table is modified using a change script, you can easily generate the Create Table script in your sleep -- from SSMS, SMO, or other free utilities. We only use the Create table script for record-keeping purposes. All new database are created from a "clean" database backup, maintained by the DBA and created by the change scripts (after they leave the QA/Test zone). All of this is done using batch files, osql/sqlcmd, and SQL script files -- and all of the bat and sql files are also under the same version control (We use MS VSS, but it doesn't matter which one you use).For textual objects (stored procedures, views, functions) we don't bother with Alter statements. We simply have Drop-n-Create statements in the SQL file that defines the object. The db developer simply modifies the syntax in the file and checks it back into version control, where it will be ready for the next step. The only special-cases for these, are dynamically created objects and functions used in Check or Default constraints.

We automate (batch files) pushing builds into QA/testing zone. The automation labels/marks all DB, application, and unit-testing code in source control. The labeled version is then pushe into QA/Testing. Part of the automation runs scripts to check for conformance to standards and inclusion of certain files or scripts. Because of the automation and version control, we have better consistency and quality. Automation also means we spend less than 2 minutes running and documenting a build! Additionally, our final push into Production/Customer delivery is the equivalent of the last QA-Approved build, so there's almost no additional work involved there.

Good luck, Terry.

~Marc

RE: Change Managment in database development?

Malcolm Leach — Fri, 29 Jun 2007 08:23:00 GMT

Hi Andy - I mentioned cross continent as an example to illustrate my point about a source control system being necessary as it doesn't care about time or distance boundaries.

Terry - I'm not specifically talking about DB Ghost here - I'm making a point about source control and how it should be used.

However, in answer to your question; DB Ghost and it's way of doing thing works with ANY source control system.

This is evidenced by our major customers who are using it with VSS, CVS, Subversion, Vault, PVCS Dimensions, Telelogic Synergy, MKS SourceIntegrity, Perforce, Microsoft TFS and Seapine Surround. All DB Ghost actually requires is a set of CREATE scripts that describe the entire schema on disk somewhere to start the process off so which SCM those scripts have come from is basically irrelevant. As the developers make their changes only to the object creation scripts no diff scripts are necessary and there is, therefore, one single source for the schema. It is also easy to look at the schema history in the SCM as the list will show the names of the actual objects that were modified rather than a sequentially numbered set of diff/change scripts.

RE: Change Managment in database development?

Andy Warren — Fri, 29 Jun 2007 08:17:00 GMT

Was just looking on the dbghost site and it says you can manully check the scripts into other SCM's (which I grant seems like more work) so at least not 100% limited to VSS. And Im not saying he's right, dont think I understand the problem well enough to vote for a solution. Which is why some bite sized articles would be nice for the site.

RE: Change Managment in database development?

Terry Denham — Fri, 29 Jun 2007 08:14:00 GMT

If I could offer one suggestion to any vendor developing an approach for database change management is if you can't support different SCMs, then try to leverage MSSCCI so at least others that are not using VSS have a chance to use/try your product by using the same API that Visual Studio uses to communicate with the SCM.

RE: Change Managment in database development?

Terry Denham — Fri, 29 Jun 2007 08:05:00 GMT

But Malcomn was making an argument that the approach I was using was prone to error and was solved by his product by using the SCM to manage the locking of the resource. If his product doesn't use the SCM that I'm using (Vault, TFS, Subversion) then his resource locking mechanism won't work in my scenario and thus his arguments are invalidated for my environment.

RE: Change Managment in database development?

Andy Warren — Fri, 29 Jun 2007 07:59:00 GMT

Not all of us - or even most of us, work in cross continent scenarios, but Im here to learn. The discussion seems valuable. Malcolm & others, you should approach Steve about writing some articles on this to take us from the chaos where we are to where we would like to end up!

RE: Change Managment in database development?

Terry Denham — Fri, 29 Jun 2007 07:56:00 GMT

But your solution only supports VSS, so that invalidates your arguments about using the SCM to manage the changes.

RE: Change Managment in database development?

Malcolm Leach — Fri, 29 Jun 2007 07:45:00 GMT

The requirement for devs to communicate with each other before checking in diffs scripts is the fundamental problem in the process and what makes it resource intensive and prone to error. It also leads to the exponential problem I referred to in my earlier post - as the number of developers in the team increases the amount of communication required increases expontentially. This means that either more time and resource needs to be allocated to the process or, as is more often the case, time and resources are not made available and shortcuts are taken to hit deadlines.

Consider the scenario where you have a team of 10 developers working remotely or across several time zones and this becomes nearly impossible to do reliably.

The main problem with your approach is that it doesn't use the source control system to control the order of changes and prevent multiple updates to the same database object. As the changes are hidden inside the myriad diff scripts that the developers create every check-in works fine and you don't encounter any problems until (if you're lucky) the scripts are run against the QA environment or (if you're unlucky) during the testing phase itself or (if you're extremely unlucky) in the live system.

Finally, yes, my company sells a product (that can be found by typing "database change management" into any search engine ) but that doesn't invalidate what I am saying. I was hoping that this topic might bring out some processes that were beyond what we vendors are hawking and so I was slightly disappointed to see a description of a process that I have seen many times before and has some fundamental problems in deadline driven projects.

RE: Change Managment in database development?

Terry Denham — Fri, 29 Jun 2007 05:24:00 GMT

Malcolm,

I realize you are trying to sell your product but I've been involved with SQL Server for over 13 years and I have never heard of your company.

How I reconcile against the 'official' database is the full schema file. In my case I run the full schema file in a temporary database, run SQL Compare against the sandbox and the temp offical schema to generate the diff which I save to a file for checkin then generate an updated full schema file.

Now the user checks in his code, the generated diff and the updated full schema.

If you are working in this sandbox type environment since you don't have a shared resource that you can lock, it does require the devs to communicate by saying 'hey, im about to generate patch 15'. So if another dev has changes that they need to commit, they will have to wait until patch 15 is checked in, get the latest full schema and now they can generate their diff 16.

And keep in mind this was before SQL Compare 6 and I haven't looked to see what is available since I wrote it a couple of years ago.

RE: Change Managment in database development?

Malcolm Leach — Fri, 29 Jun 2007 02:02:00 GMT

Terry,

How do you reconcile the full schema "official version" from what is represented by the developer's change scripts? This is a necessary step in any approach that tries to use two versions of the truth as the two will always diverge over time.

What normally happens (in my experience of project that use the techniques you describe) is that the "official version" simply gets out of date and so the developers end up copying each others sandbox databases, because *they work*, and the official version *doesn't* so the official version ends up being shelved or marginalized.

Basically, in any process, you should only ever have one version of the truth - that's what we have for every other type of source code and that is what we should have for SQL code as well.

Any process that relies on multiple diff scripts is doomed to be resource intensive and error prone. Why would anyone risk hand rolling an inefficient process against their company's most important asset (the database) when there are inexpensive tools on the market (such as DB Ghost and now SQL Compare 6) that are proven and make everything easy and rock solid?

RE: Change Managment in database development?

Terry Denham — Thu, 28 Jun 2007 16:56:00 GMT

When I think about versioning a database I've used database in various modes:Goal : Anytime a change is made to a database object, any supporting application code should be committed to the version control system in the same check-in. For example, if I am adding a new column to a database table and have updated a stored procedure and have modified a middle tier object that ends up calling the stored procedure, all 3 of these changes should be committed to the version control software in the same check-in.What complicates this is getting the distinct changes to each object (table/sproc script/source code). The stored procedure, source code are relatively easy to deal with but dealing with the table change is the harder problem. It's relatively easy to generate a new table script, but getting other team members to use this new table will require them to drop/create the new table structure. If they have data in the old table structure they have to deal with loosing this data, or scripting it out to preserve their data. This problem is exactly what breaks the sandbox approach. We wouldn't tolerate this if we had to destroy our source code whenever we got latest when anther developer made changes to files we were working on.I can think of a few scenarios in how we manage databases:1. Sandbox mode : developers have local copies of the database (their sandbox) and are able to make changes to their database at will to support new features that they are working on. To support this in the best manner we would need to have modification scripts that are stored in the version control system and are applied to our database. This is essence the database diff that needs to be applied. Now for new developers coming into the project they need a way to be able to get their environment up and running, but they shouldn't have to run hundreds of patch files to get their database environment setup. There should be a full schema that represents the current 'official' version of the database schema.2. Shared mode : in this mode developers are using the same database running on a server. Any change that one person makes could impact another user. In one environment I worked in, we would make changes to our own objects (user owned) so that we could write/test code in isolation. Once the changes were ready we would make changes to the 'dbo' tables. This might have a cascading effect where other code would need to be changed to deal with the updated table/schema.3. Promotion mode : in this scenario it could be either the shared mode or sandbox mode but what we have to do is to move changes from a development environment into a QA/Test/Production database. The current Red-Gate tools could be used to accomplish this sync but if we already have diff scripts in the version control could we not use those changes to actually perform our prop.Another useful feature of checking in schema/procedures into version control through labeling/tagging, we should be able to get the source code/schema/procedures that all match up. So that one could run the schema/procedure scripts to create the database and the code would work against that version of the schema.What I've done to assist me in some of my projects is to establish some procedures as follows:1. There is a table that contains the version of the database schema. I tend to call this table SchemaVersion and it stores the Major,Minor,Build version of the schema.2. There is a Schema.sql file that contains the 'official' version of the schema. New developers on the project would run this file to get them started.3. For each database change a script is generated that is the 'diff' of the modification to the database schema. This 'diff' change would use the information stored in SchemaVersion table to know whether this modification needed to run in the database.I love RedGate's SQL Compare, but the changes that it generates I needed to wrap in a transaction that checks if the SchemaVersion table had been updated and to insert the schema version record into this table (or update the row if only tracking the current version).I wrote a tool that uses SQL Compare API to generate the differences but I take these differences and wrap them in my own TSQL that manages the application of the script by checking the SchemaVersion table. I made the generated SQL re-runnable so that if someone accidentally executed the script again, it would modify the structure unnecessarily.I've been meaning to blog about the tool that I wrote and share the code but I just haven't gotten around to it. I'd like to share with you the approach I used to see if it might fit into your plans.

RE: Change Managment in database development?

Eric Peterson — Thu, 28 Jun 2007 09:42:00 GMT

I think that these approaches were written with only redgate in mind and not a pure approach.

In a truly secure environment, the only changes to the schema or procedures would only occur in a source control package. Each table, procedure, trigger..etc needs to be in the source control. All changes are tested against a staging environment, before checking back into source. A script will build the changes into the system....IE a release.

Eric

RE: Change Managment in database development?

David Atkinson — Thu, 28 Jun 2007 09:40:00 GMT

Thank you all for your comments. We believe that allowing SQL Compare 6 Pro to support a source-controllable representation of a database schema will satisfy many database developers. We also recognise that a great solution would be to link up SQL Server itself with those source controlled files (as Andy has suggested). We are actively researching the best way to provide this sort of integration.

One of the greatest challenges for us is to author tools that are simple and generic enough to provide as much flexibility to the database developer as possible. Bear in mind that there are a vast number of different database development models used in the field. Although our white paper describes three such models, Red Gate tools are flexible enough to support many variations of these.

We have created a short survey at https://www.surveymk.com/s.aspx?sm=qbb94wjddl19bBnzM580ww_3d_3d

Please help us better understand your database development process by completing the survey. We are giving away five copies of SQL Refactor to randomly selected participants. We have two additional mystery prizes to give away to the best/most comprehensive responses.

Thanks for your assistance,

David Atkinson, Product Mananger, Red Gate Software

RE: Change Managment in database development?

Andy Warren — Thu, 28 Jun 2007 07:27:00 GMT

Interesting. I've mostly used model 1 as referenced, and for small/medium operations it works just fine. I see a lot of value in a diff tool for change detection so that developers aren't asked to keep track of changes manually, and of course to avoid missing any changes, even if inadvertent.

Not sure if I agree that source control/versioning should be integrated with these tools. My dream would be integration at the SSMS/db level so that any change applied - anywhere - would be immediately added to source control, and that I could right click an object and roll back to any previous state. Somewhere in there need the ability to group changes that have to be made/rolled back together. Ultimately doing the detection anywhere except DDL triggers leaves the possibility that someone, however well meaning, will bypass the version control phase.

I haven't tried the latest SQL Compare and Malcolm, I have to admit I haven't tried your product at all, but I'll get around to it, so maybe I'm not seeing the big picture! I do look forward to reading your whitepaper as well.

RE: Change Managment in database development?

Roger L Reid — Thu, 28 Jun 2007 07:25:00 GMT

If all we're talking about is keeping schema in sync, it's simple enough - I run sp__revtable on the tables on my Sybase server databases that have not yet been full ported to SQL Server. If I care only about schema, I can simply drop and remakethe tables and indices with the output.As always "it depends". On an established system being rebuilt elsewhere, it's far more important that the new one be like the real thing, as opposed to what the source code thinks it should be. Of course, the source code should reflect this state, so if it doesn't due to neglect over the years, I can use this same method to generate a source list for tables and SCCS those to keep a history.Far more critical and as yet unaddressed is doing the same for the data. HitSW DBMoto can do it if you don't mind pushing all the data every time - but that stinks for large dbs that usually change less than 1% of their data per day.

RE: Change Managment in database development?

Malcolm Leach — Thu, 28 Jun 2007 05:05:00 GMT

I think that about covers it for processes that are likely to deliver any real benefits.

Of course there are always many other "processes" that people employ such as in one of the other posts whereby the production database is detached, zipped and emailed to the developers for them to apply changes however they want and is then emailed back to the production DBA to re-attach. Laughable, of course but it shows that people can think they have a "process" when, instead they have a disaster waiting to happen.

The first scenario, as you quite rightly point out, suffers from a lack of scalability if the database changes are complex and/or there are more than a few people making changes as there is an exponential increase in effort as changes/people increases linearly. This is the problem that all pure "diff tools" suffer from.

Only the last two scenarios offer a solid approach to change management - one thing I didn't see (maybe it's there and I missed it) was that there should be a automated, continuous database build implemented from the source control system with a direct feedback loop (via email) to the development team. That way, when changes are checked in developers get notified almost immediately that there are problems and so can fix them very quickly instead of issues coming out when the next daily or scheduled build occurs when memories have faded somewhat.

The last two approaches are what DB Ghost (www.dbghost.com) has been doing since 2003 and, in fact, our whitepaper on the challenges of database change management has been out there for about 3 years so I'm glad that Microsoft (with VSTS4DB) and Red Gate have finally validated what we have been stating all this time - that solid change management can only be achieved by using that most valuable piece of software - the source control system.

Welcome to the party guys, I'm just surprised you didn't do it sooner

Change Managment in database development?

Tony Davis — Wed, 27 Jun 2007 09:10:00 GMT

This whitepaper presents what we believe are the three most common ways of applying change management techniques to database development:

· Ad-hoc merging – periodic merges of modified database developer instances

· Object level source control – controlling and tracking changes to your database design using a source control system

· Offline development – unlike the previous two, this method does not use a 'live' database. Schemas are developed by directly editing the SQL in the creation scripts,

It discusses the advantages and disadvantages of each approach and describes how we believe Red Gate's SQL Compare fits in to the model you choose to adopt, and improves it.

Have we got it right? Are there alternative techniques that we've unjustly ignored?

We'd really like to hear any feedback you've got to give!

Cheers,Tony Davis (Simple Talk Editorial Director, Red Gate Software)