April 8, 2015 at 8:26 pm
Comments posted to this topic are about the item State v Migrations
April 9, 2015 at 3:03 am
Hi Steve,
Aren't the release problems you descibe what proper source control systems and processes are there to address?
Source control branching, coupled with a high level database management system, such as Visual Studio Database Project (now part of SSDT), or Redgate's Source Control product.
In my opinion and experience, the main problem is a mind set one, where database code is treated as a second-class citizen to other code projects, such as web or application code.
Application developers are frequently the source control "owners" of the database code as well, and don't know what to do with it, or how to manage it alongside their other code.
Rant over... 😉
April 9, 2015 at 6:26 am
The major problem with scripts is you are violating DRY (don't repeat yourself - aka normalization). The problem with the manual compare option is it is manual, and therefore not scalable if you need to deploy across many environments.
What about the option of using Rake (for Rails) or Hibernate's auto update, or even some other schema export if the code defines the schema? I know this is anathema among the DBAs, but working yourself out of a job by doing the right thing is rarely possible*, and automation just lets you do more with less... and if the deployment of everything else is automated, why shouldn't the database upgrade also be.
* I feel that if you are afraid of working yourself out of a job, you are spending your energy on the wrong thing. I've always found that the reward for a job well done is more work than you can handle.
April 9, 2015 at 6:27 am
We do practice deployments on copies of production.
April 9, 2015 at 6:34 am
Doing a comparing to development is frequently untenable. It is development, it is where the developers go to play, and frequently forget to clean up after themselves. Not that they should have to, that is arguably the point of a dev environment. It is where they can play, test, learn, and experiment, without having to worry about breaking anything.
Here, we deploy the scripts that come from the dev team to the QA environment (a backup of prod). Once the scripts and the code pass QA, we apply them to UAT (another copy of prod). IIF that upgrade is perfectly clean, then those same scripts get applied to prod.
April 9, 2015 at 7:59 am
Xavon (4/9/2015)
Doing a comparing to development is frequently untenable. It is development, it is where the developers go to play, and frequently forget to clean up after themselves...
That's what staging/pre-prod is for 😉
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
April 9, 2015 at 8:00 am
Either way, testing is essential. Automated testing is preferrable.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
April 9, 2015 at 8:52 am
Hi Steve,
We are upgrading all our databases with migration scripts. We implement it in a way that the tool (that is used to run the conversions) knows which conversion scripts has to be executed (in the proper order) and which conversion scripts have already been executed.
Each executed conversion is logged in a table in the database. We also have a file that list each conversion in the proper order for each versions.
Everything run smoothly because we are doing this for years now.
Best regards.
Carl
April 9, 2015 at 9:28 am
The one thing that helps you find problems with your process and code is testing, which is something I'd recommend you implement no matter what method you choose for deploying your changes.
Motherhood, apple pie, and the flag are also recommended. 😛
April 9, 2015 at 10:10 am
Steve, one of your comments was interesting "...it usually requires I've got good control of production to prevent changes from being made there that aren't also made in development. "
Ok, lets hope those who manage production have good control of said.
Still, this is a problem that does not have an clean, easy solution.
The more you are prepared, the less you need it.
April 9, 2015 at 10:45 am
I guess that I've been lucky. We made a decision that all of our clients have the identical schema even if there were tables that they were never likely to use. Going from version X to version Y is the same for everybody. Even so we would grab a copy of production and test the deployment on a dev machine in our office.
I like the idea of a "manifest" to do database upgrades and some stored procedure (or application) to make the current database match the manifest. Such a mechanism would require a lot of testing before trusting it completely.
Like Steve says whatever you use needs testing. "Practice, drill, rehearse."
ATBCharles Kincaid
April 9, 2015 at 11:52 am
We have a hundred customers with various versions - some customers are 2 major releases behind, some are a few point releases.
When we went live with our first customer, I realized we needed the ability to flexibly upgrade customers.
So I structured our database scripts in such a way that we can move any customer from any previous release to a subsequent release.
We have a build tool that we provide the versions, and it uses that to figure out what scripts need to be run. Our scripts are divided into tables, keys, indexes, stored procedures, views, functions, data, conversion, etc, and each script is numbered to make sure it runs in the correct order. Sometimes a release has 200 sql scripts in it.
Every couple of releases, we roll the patch scripts into the main branch to reduce the number of patch folders that have to be run on the trunk of our source control.
It works really well, with continuous integration and nightly smoke-tests for all the code, to make sure there are no serious issues.
April 13, 2015 at 11:52 am
Carl B. (4/9/2015)
Hi Steve,We are upgrading all our databases with migration scripts. We implement it in a way that the tool (that is used to run the conversions) knows which conversion scripts has to be executed (in the proper order) and which conversion scripts have already been executed.
Each executed conversion is logged in a table in the database. We also have a file that list each conversion in the proper order for each versions.
Everything run smoothly because we are doing this for years now.
Best regards.
Carl
Lots of people do this (and I have as well). As long as the process is solid and doesn't allow for ad hoc changes, or your table doesn't get altered somehow, this works great.
April 13, 2015 at 11:54 am
Andrew..Peterson (4/9/2015)
Steve, one of your comments was interesting "...it usually requires I've got good control of production to prevent changes from being made there that aren't also made in development. "Ok, lets hope those who manage production have good control of said.
Still, this is a problem that does not have an clean, easy solution.
Definitely this is an issue. I know plenty of customers/clients that don't have this and production changes are made.
Part of our DLM Dashboard[/url] work is to catch these changes and allow people to feed them back to development environments.
April 13, 2015 at 12:15 pm
This also permit us to include conversion scripts that only modify the data (DML not DDL) which I don't know how this would be done with the other method (State).
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply