August 7, 2016 at 11:40 pm
Comments posted to this topic are about the item Making Complex Table Changes
August 8, 2016 at 12:28 am
The strategy described in the post by Michael J Swart that you linked to is what I have described in detail in the following SSC article: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY![/url]. I used that method quite a few times in a highly transactional environment with very large tables. The functional testing was the easy part since I would run it in Dev and QA environments as early in the sprint as I could. This way I would be able to find problems with enough time to make corrections and still get it to Production a week prior to the actual release.
However, once the pre-release scripts and data movement job were released to Production, I would have to monitor the efficiency of the data movement. Populating an empty table based on a particular condition found in the current table at the start of the process goes fairly quickly. But performance degrades, usually before getting half-way through (especially when dealing with 100+ million rows), so batch size and/or duration might need to be adjusted. Or, sometimes a "temporary" index (possibly a filtered one) can be created to help find the remaining rows to migrate (if the clustered index is not conducive to finding the next ID based on the max value found in the "new" table).
Sure, it could have worked to use a more generic approach and then not do any adjustments during the pre-release period, but I would have needed more than 1 week in order to allow for the slow-down and still guarantee completion before the release date. It's been several years since I needed to do this, but I don't recall having that much buffer time, so I really needed to make sure that the process moved as much as it could the whole way through.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
August 8, 2016 at 8:15 am
Automation, continuous integration, and process are good concepts, but these things are just tools ... a means to and end. If they don't serve the intended end for a given scenario, then don't use them. When it comes to making mass updates to large tables, it's better to turn off auto-pilot and let the DBA and DevOps land the deployment with their hands firmly on the controls.
- Stage the new table. Basically, create using new DDL structure and then INSERT.. SELECT.. from original table.
- Perform some quick post-production QA on the new table.
- Sync any new data from original table to new table.
- Switch from original table to the new table by renaming objects.
- Leave both tables in place for 24 hours.
- Drop the original table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 8, 2016 at 8:30 am
Solomon Rutzky (8/8/2016)
The strategy described in the post by Michael J Swart that you linked to is what I have described in detail in the following SSC article: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY![/url]. I used that method quite a few times in a highly transactional environment with very large tables. The functional testing was the easy part since I would run it in Dev and QA environments as early in the sprint as I could. This way I would be able to find problems with enough time to make corrections and still get it to Production a week prior to the actual release....
Take care,
Solomon..
Great Article Solomon!
When we started with CI back in 2005, it was great for everything - except database changes. Even with ERwin's database "change" listings, all the database changes had to be prepared manually, reviewed and tested and tested.
So, Steve, anything and everything that you and Redgate can do to improve managing database changes will be game changers.
The more you are prepared, the less you need it.
August 8, 2016 at 9:40 am
Eric M Russell (8/8/2016)
Automation, continuous integration, and process are good concepts, but these things are just tools ... a means to and end. If they don't serve the intended end for a given scenario, then don't use them. When it comes to making mass updates to large tables, it's better to turn off auto-pilot and let the DBA and DevOps land the deployment with their hands firmly on the controls.- Stage the new table. Basically, create using new DDL structure and then INSERT.. SELECT.. from original table.
- Perform some quick post-production QA on the new table.
- Sync any new data from original table to new table.
- Switch from original table to the new table by renaming objects.
- Leave both tables in place for 24 hours.
- Drop the original table.
I'd disagree with that. The process you have setup, and you test, ought to be scripted where possible. Most of these items are scripted, so they end up as separate deployments, maybe even from a branch that has a variety of changes taking place over time. The start of each step (and maybe verify) is done by humans, but you aren't expecting DBAs to be running specific code in SSMS where they can make a mistake or forget things. Instead, I'd have a series of deployments ready to go, each of which might be actually released across days (weeks, months).
August 8, 2016 at 9:50 am
Andrew..Peterson (8/8/2016)
So, Steve, anything and everything that you and Redgate can do to improve managing database changes will be game changers.
What we do with the DLM automation, or the capturing of changes with ReadyRoll, isn't really removing work. Instead, we capture what you do, maybe write some scripting for you, but really capture changes in a group and allow you to reliably replay them against another environment.
August 9, 2016 at 12:44 am
Steve Jones - SSC Editor (8/8/2016)
...What we do with the DLM automation, or the capturing of changes with ReadyRoll, isn't really removing work. Instead, we capture what you do, maybe write some scripting for you, but really capture changes in a group and allow you to reliably replay them against another environment.
For me that's key.
This is a difficult task that needs an expert to work out how to apply an appropriate technique for the specific situation. There cannot be a simple generic way because too many of the factors are non-functional requirements. Automating copies of schema objects, copying of data and and renaming of schema objects is the easier part. Doing so in a way that keeps data available as required is a lot harder.
BTW Great articles both, thanks. (The one in the editorial[/url] and Solomon's[/url]).
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply