0

I have tools for propagating database definition changes, but any data changes I make I write scripts for and run them in each environment by hand.

There are some data changes/additions that have to be made routinely and I would like to create a GUI for handling this, but if I did that then I no longer have the sql scripts to run in other environments (unless I generate them from the GUI tool, but then I'm still left having to run those changes by hand in other environments).

One idea I'm mulling over is to write the GUI tool in such a way that it generates the xml files for objects I've added or changed and include these xml files in the publication of the web project. Then the application can look at these xml files and update the database accordingly.

Are there any standard practices or tools for pushing data changes automatically along with your db definition and/or code changes?

EDIT:

Based off the first answer I received it appears like I might have made it sound as though I want a tool to compare dev databases to production databases and propagate all changes. This is DEFINITELY NOT what I was trying to communicate.

One example of what I'm dealing with is language texts that have to be added (no, I can't use resource files for this because end users can modify the texts through the application at runtime). Instead of having to hand write scripts every time I need to add texts for a new item, it would be great if I could just create a GUI tool to simplify the process.

I think it's pretty obvious why I would prefer to use a GUI over hand writing SQL statements, and so the next question is why would I want to propagate this the same way we propagate code changes. Well, this isn't like other test data. This is data that will ALWAYS be propagated to each environment. The same sort of concerns that lead us to use tools to simplify the process of pushing code or database changes to each environment apply to the kind of data I'm talking about here.

1

Why would you want to propagate changes to the data along with the promotion of definition or code changes from Dev to QA to Prod?

I wouldn't think there is something like that out there because if anything, it is better to be able to refresh Dev and QA with data from Prod...not the other way around. In a situation where you need to create new data in Dev or QA because you are testing a new function that deals with something existing data doesn't contain, the thing is it is just that...TEST data. QA best practices dictate the data going in Prod must be clean.

If you must, best practices in my experience are what you have already - use scripts (there are some data migration tools out there that are meant for such a thing), or put them in manually.

| improve this answer | |
  • Thanks, see my edit though as it looks like I didn't communicate clearly about the nature of the data I'm concerned with here. – BVernon Nov 17 '14 at 18:18
0

If you need a way to create reproducible changes to a database, GUI tools are often not the best possible approach. When you change something using a GUI tool, the GUI sometimes can only guess how the change was meant "in general". In SQL however, you can express the exact rules of change you have in mind - and it gives you the ability to push the changes into version control, test the changes and debug them when the test shows up an issue. And, as you noted by yourself, they provide a reproducible solution for your QA and Production environment.

So the first thing you should ask yourself is: do you really need a GUI tool.

However, when you think you have too much tedious work to do with every change, you may buy yourself a schema comparison tool (see here for some suggestions, there are different tools for different DBMS). Those tools can probably help you to compare different versions of your dev database schemas and may safe you some effort in creating the SQLs for your QA and prod environment.

| improve this answer | |
  • Think of it this way: You add images to your projects at the development level, and they get propagated with your code changes as you publish to each environment. But what if the images were stored in sql instead of in files so you can't just drop them into the project? What I'm wanting doesn't seem to exist right now though, so I guess at this point I'm just trying to brainstorm how you could make this kind of thing just as easy. – BVernon Nov 17 '14 at 22:15
  • And to be clear, a data comparison tool isn't what I want either. To continue the image metaphor, you don't have to compare your directory structures to get your image into the next environment. But you do have the option whether you want to copy the output of the picture when you compile so I suppose you'd need a similar mechanism to control data changes as well. – BVernon Nov 17 '14 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.