March 4, 2015 at 5:27 pm
Peter Schott (3/4/2015)
Gary Varga (3/4/2015)
These issues are no different from those for non-database code.I think I'd disagree with that to an extent. If I'm modifying table structures or doing refactoring, there are a lot of other things going on behind the scenes. I know I've been hit hard before with what should have been a "rename" operation, but looked like a "drop/add" situation. There's also no data to maintain with most non-database code. You drop a new file, you're good to go. If you change something in the database, your new stored proc may not work or may not work as expected because of data changes.
I'd also say that the order matters a bit more w/ database ops because I might need to refactor in a certain order where each step preceding is needed for the end result to work. That's a bit different from being able to just drop the end files where they should go. I may have done some data manipulation along the way from step 1 to step 10 so can't just drop the changed proc/table in step 10.
The issues may be similar, but I've found that DB changes tend to be a bit different because the state of the database matters a lot more than the version of the executable and isn't quite as easy to change overall.
I get all that and agree with your points about the difficulties in the scripts to deploy but I was referring to deployment of only part of the code set worked on in a sprint. Merging and branching of code is the same, in my opinion, regardless of it being SQL or C# say.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
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 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply