March 5, 2015 at 1:13 am
Well, Steve's editorial has certainly stirred up a debate, and in our particular ( and peculiar) case I feel not much closer to a solution. Having attended a couple of Red Gate sessions on the source control-based test and deploy process, which seems to me to fit in the state-based model, I wonder what their stance is on all this. I am at SQLBits, so I'm going to go find someone to ask.
March 5, 2015 at 1:12 pm
Bob JH Cullen (3/5/2015)
Well, Steve's editorial has certainly stirred up a debate, and in our particular ( and peculiar) case I feel not much closer to a solution. Having attended a couple of Red Gate sessions on the source control-based test and deploy process, which seems to me to fit in the state-based model, I wonder what their stance is on all this. I am at SQLBits, so I'm going to go find someone to ask.
Pretty sure they're in the "state based" model. SQL Source Control is basically designed to make the database look like what you have in source control. I imagine they have similar ways to track refactoring along the way so it's not just "compare and push" when it comes to renames and things like that. I seem to recall that they have some interesting ways to handle scripts running in the middle of the release as well. (ALTER TABLE, run custom script, resume release sort of thing)
Definitely sit down with them for a demo if you can and ask questions. If it's anything like the PASS conference, they'll have quite a few people on hand to help out and can bring in others if you have questions that go beyond what the first person knows.
In your case, I'd say that blocking out some time to try out different ideas would be really helpful. I know that's easier said than done, but if you don't commit the time to it, it probably won't happen. We had to take time over several sprints with someone maintaining the DB code in the sprints while someone else did a lot of the heavy lifting to prove out concepts.
March 6, 2015 at 3:18 pm
To handle instances of data migration due to column renames, have you considered a mapping feature in SQL Compare? I believe there is something similar in SQL Data Compare where you can say that Column A should be mapped to Column B for purposes of matching records and comparing their data.
For SQL Compare: If a change is detected to a column on a particular table, give the user an option to map the existing column's data to new/altered columns. Move the data to a temporary location during the change and map it back into the altered table once the update is complete. Warnings of changes to data type and options for handling conversion would be a nice feature there.
Migrations to split data from one table into several or to merge data from several tables into one would likely need a more visual interface for users to easily describe how data should flow... that would certainly be an interesting challenge.
May 21, 2019 at 7:47 am
What we do is:
Thus if a customer is on version X and wants to get to version Z they simply upgrade the DB by running the version Y upgrade script followed by version Z upgrade script - simple.
May 21, 2019 at 1:56 pm
"..The problem is that at some point a state based deployment process won't work. It's actually impossible for a state based approach to work in all situations. Simple actions, like renames, can't be handled by examining two states. In order to properly manage changes, and keep your data intact and safe, you need to understand "what" happened during the process, not just the end result.."
The biggest problem with generating deployment scripts from a shared development environment using a schema comparison tool is that you're potentially picking up changes that are not intended for release. The QA / DevOps team and the development / DBA teams need to collaborate on the process of building deployment packages.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 22, 2019 at 6:51 am
"..The problem is that at some point a state based deployment process won't work. It's actually impossible for a state based approach to work in all situations. Simple actions, like renames, can't be handled by examining two states. In order to properly manage changes, and keep your data intact and safe, you need to understand "what" happened during the process, not just the end result.."
The biggest problem with generating deployment scripts from a shared development environment using a schema comparison tool is that you're potentially picking up changes that are not intended for release. The QA / DevOps team and the development / DBA teams need to collaborate on the process of building deployment packages.
When we make a release we create a developer/testing database that can be used in conjunction with the single generated upgrade script to test the new build version. Thus the test team are always testing with what we would deploy to customers but they are using there own specific instances of databases not a shared one, despite the source database backup they restored being shared.
Viewing 6 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply