October 23, 2018 at 9:20 pm
Comments posted to this topic are about the item Rolling Back Migrations
October 23, 2018 at 11:47 pm
And all this stuff really works well if all releases happen in sequential order, and you don't have someone come in halfway through a development phase screaming for a patch that directly contradicts the current state of affairs.
"But no, we don't want a full database upgrade, just the bit that is important, right? I'm sure that it only involves one small change, and that change won't effect anything else. Yeah, give me the complete list of everything that has changed and I'll point out the one small thing to extract and isolate. It's really easy to extract and then merge back database changes."
October 24, 2018 at 1:03 am
I'd agree with Steve. Migrations work in the vast majority of cases. The problem is that non-DBAs have a tendency to think the majority of cases = all cases.
Source control of the DB is an absolute must but so is the training to ensure that everyone is using it correctly. This is particularly true with regard to branching, merging and releasing.
If you have an SMP product like SQL Server then at least developers can have their own local instance in which the DB can be torn-down, rebuilt to the state of the live DB and then their migrations played. But if you have an MPP platform with no single node developer option then this presents a serious challenge.
In the application development world there is trunk driven development and feature switching. In the DB world I am not clear if this concept exists or how it would be executed. I feel that there are a lot of processes and ways of working that are still to be defined when it comes to DB source control and deployments.
October 24, 2018 at 6:40 am
that in a year of development, they never had to roll back any changes.
That's good. A testament to good testing as well as good development. But that doesn't mean you have to stop planning for rollbacks. They happen, hopefully infrequently.
I would never add new columns and drop old ones in the same deployment.
A good tip. I will have to consider that for the future.
October 24, 2018 at 7:43 am
I usually create manual script for roll outs to production. It makes sense for me and works really well. Every time there is an update in test database, I will copy it to the script, so at the end I have the complete test to production script. After through testing it just one simple step of running script on production database. Any minor issues can be corrected in production directly. One important thing is to not overwrite database objects like stored procedures and functions, rather keep the old copies and create the new ones.
------------------------------------------------------------------------------------------------------------------------------------
Buy experiences, not things
October 24, 2018 at 8:01 am
Hello Steve,
In the first paragraph of the op-ed you state,
"It's a part of what SQL Change Automation from Redgate Software does, and it's also what Microsoft and other companies see as the future of database deployments."
This is the first time I've seen anyone state that Microsoft sees migration scripts as the future of database change management. Can you point me to any evidence of this because this takes me by surprise. For the last 8 years or so, Microsoft has been vested in the DSC (Desired State Configuration) technology in many areas of their operations and hence SSDT tooling (DACFx) around the SQL Server publish management.
October 24, 2018 at 8:47 am
Data migrations is something that I've seen for years. It appears in just about every training course I've ever had (in my previous job), blog posts, etc. As a developer I come across data migrations frequently.
And yet, I've never used data migrations. In my previous job, I was just afraid to do it. Perhaps if we'd have the database in source control....
In my current job, they won't allow it. And I don't have the necessary permissions to effect any changes to the database schema. What we typically do is propose some change, wait for weeks or months for approval, write a SQL script to make the change in test, wait longer for (I'm not sure what, but some sort of additional approval), then give the SQL script to the DBA to apply to production.
Kindest Regards, Rod Connect with me on LinkedIn.
October 24, 2018 at 8:59 am
David.Poole - Wednesday, October 24, 2018 1:03 AMI'd agree with Steve. Migrations work in the vast majority of cases. The problem is that non-DBAs have a tendency to think the majority of cases = all cases.
I think they work in all cases, just not always well. If you add a column in dev, change data, then drop that column because it's a bad idea, that works in prod, but not necessarily what we want to do. We might want to alter the script, which could be a problem. Hot fixes, out of order deployment, branching and poor merging, all cause issues.
October 24, 2018 at 9:02 am
qbrt - Wednesday, October 24, 2018 8:01 AMHello Steve,
In the first paragraph of the op-ed you state,"It's a part of what SQL Change Automation from Redgate Software does, and it's also what Microsoft and other companies see as the future of database deployments."
This is the first time I've seen anyone state that Microsoft sees migration scripts as the future of database change management. Can you point me to any evidence of this because this takes me by surprise. For the last 8 years or so, Microsoft has been vested in the DSC (Desired State Configuration) technology in many areas of their operations and hence SSDT tooling (DACFx) around the SQL Server publish management.
It's mentioned in various talks, and it's an area where people have recognized that DacFX/SSDT falls short. DSC is fantastic for stateless things, like server config, but it doesn't work well with stateful items like databases. There are no shortage of cases where SSDT fails to work well and pre/post is used extensively to handle certain problem domains.
I don't have an official statement, mostly because there isn't any official person. Just like SSDT comes and goes in terms of it's development. No statement that "we're not working on this for xx months", but that happens.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply