April 26, 2011 at 11:32 pm
ModestyBlaise84 (4/26/2011)
In my opinion, adding a trigger to the production database; creating a table in production database, dropping a table from production database, creating a job in production database - IS RELEASE.Every change made in the production database should be considered as a release.
For example, adding a trigger could affect performances and should be planned ahead, tested, released, monitored. Same with the other changes which makes these steps actually:
Release I, Release II and Release III
Where does FKs fit in? (Their name has to be unique on the database level)
What happens in case of the replication, if changed table is published?
How this data transfer affects tlog and backup size?
What happens in case there is a log shipping set up on the database? How is space affected? How are performances affected?
Depending on the table structure and the way transfer is done, indexes can end up heavily fragmented if created before table is populated.
Although being useful in some cases, it seems to me that this approach has limited usage and hidden risks which should have been mentioned as well.
I agree with you except your conclusion.
Of course you need to plan and test everything before you apply to a production database. That includes the steps to release itself (even when done in a single step, not doing so places a ridiculous amount of faith in the release-script(s)). That doesn't diminish the usefulness of this approach at all (especially if it can get your user's experience up thousand-fold by minimizing actual downtime).
April 27, 2011 at 7:33 am
Great article Solomon. We used similar techniques in our legacy DB2 MVS/390 database in the early 2000's. Except in those cases we had a log reader tool from BMC called LogMax and instead of using triggers/batch jobs for change data capture between the old and new table we just put the old table into a RO mode at implementation time, ran a LogMax against the old table and applied all the transactions to the new table, and then did the switch. I have heard there are log tools for SQL Server from both Toad and RedGate. Have you considered using a log apply tool to do the data capture? Would be interested in your feedback on such an option.
Thanks,
-Todd
April 27, 2011 at 9:12 am
you mention having to update views, udf and procs after the table rename, why is this required?
andrew
April 27, 2011 at 5:19 pm
andrewkane17 (4/27/2011)
you mention having to update views, udf and procs after the table rename, why is this required?andrew
That is because the new table and its constraints, indexes, etc, although having the same name, are different objects (with different object-ids) as far as the server is concerned. So all dependent objects (with their compiled code still referencing the old object-ids) need a recompile to reference the new table. In many cases SQL-Server is smart enough to realize the dependent objects have disappeared and will try a recompile on first use, but this is not a fail-safe method (and I have seen in the past that objects get recompiled on each use after that, or not using the appropriate stats, degrading performance).
Marco
April 27, 2011 at 7:03 pm
Amol Naik, Jason AKA CirqueDeSQLeil, and belgarion:
Thanks! I'm glad you enjoyed the article. 🙂
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
April 27, 2011 at 7:55 pm
rbarbati (4/26/2011)
Good article, great topic.I agree with the use of change tracking in 2008, and would add that triggers are not always straight forward. You must consider referential integrity, cascading events , and the typically more complex object relationships in an enterprise system.
Hello and thanks for both the compliment and the input. I certainly do agree that options are good to have and do appreciate your technique. In the projects that I have done with 60 - 180 million rows per table and plenty of read/write activity all throughout the day, the Triggers did not pose a problem for performance; the UPDATE and DELETE operations were JOINed on the Clustered Index (PK in these cases) and was rather quick.
Since we are only working on one table at a time here (or at least no related tables), there is no concern regarding referential integrity or object relationships. In the case that the table being restructured is a child table where both it and the original table have cascade delete FKs pointing to the same parent table, if a record is deleted from the parent it will delete in both child tables leaving the trigger with no matching rows for the DELETE operation, but no logical harm. In the case that the table being copied is a parent table and both it and the original have a cascade delete FK to the same child table, if the parent gets a DELETE it will cascade to the child table and the trigger will replicate the DELETE to the copy table which will then try to cascade the delete to the child table but there will be no matching row(s) now, but again no logical harm. If the situation is more complex then the FKs can always be added during the release, just before swapping the tables.
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
April 27, 2011 at 8:19 pm
mar10br0 (4/26/2011)
Great article Solomon!I do however avoid triggers like the plague in production-code as a principle...I much more take rbarbati's approach in an earlier reply-post, for this I prepare ALL my tables to ALWAYS include a timestamp-column, so I can find updated/inserted rows since a certain time.
Hello. Thanks for the compliment and the additional input. As with my reply to rbarbati, in my experience the Triggers have not been a problem, even in a SaaS environment with 24/7 access to the Table. But again, circumstances for someone else might be different and so having another method to handle the data sync is certainly valuable and appreciated. I think you could accomplish the same goal as your method with a regular DATETIME field as opposed to TIMESTAMP / ROWVERSION, which would also be human-readable though not any more functional than the TIMESTAMP (just a thought I had as I was reading your comment).
However, and I forgot to mention this in my reply to rbarbati, one advantage of the Trigger method is that the data is ALWAYS up to date without a secondary process (which takes time to run) to accomplish the sync. Since we have data changing all of the time, if we did not have a Trigger then we could run into a case where we sync the data and start the process of swapping the Tables but then a change occurs and there would have to be an additional step to see if any new rows exist in the DELETEME table and if so, then move them back into the "new" current table.
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
April 27, 2011 at 9:23 pm
ModestyBlaise84 (4/26/2011)
Every change made in the production database should be considered as a release.For example, adding a trigger could affect performances and should be planned ahead, tested, released, monitored.
Where does FKs fit in? (Their name has to be unique on the database level)
What happens in case of the replication, if changed table is published?
How this data transfer affects tlog and backup size?
What happens in case there is a log shipping set up on the database? How is space affected? How are performances affected?
Depending on the table structure and the way transfer is done, indexes can end up heavily fragmented if created before table is populated.
Hello. In a sense, yes, every Production change is a "release" of sorts, but for our purposes here the actual "RELEASE" is the one that includes the application changes that are depending on the new table structure. Also, the purpose of my article was not to teach people proper software development methods. I can only hope/assume that people have development, QA, and Staging environments and do these changes as they would do any updates to Production. If people are not testing and/or are doing development in Production, well, then that requires a vastly different article.
As far as your specific concerns go, I addressed most of those in the article: I gave a naming convention for FKs that maintains uniqueness; I did mention that the transaction log would increase and might need to be pre-grown; I addressed when to create the indexes on the new table.
I am not sure why log shipping would be an issue. And if the table is published then it would obviously need an extra 2 steps to drop the current article and add the new article, but that doesn’t really change or invalidate the overall process that I described.
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
April 27, 2011 at 9:41 pm
Todd.Everett (4/27/2011)
Great article Solomon.Have you considered using a log apply tool to do the data capture? Would be interested in your feedback on such an option.
Hi Todd. Thanks and I'm glad you liked the article.
Regarding a log reader (or even the new Change Data Capture feature), that was not really an option since we don't take down the application during our Releases. So the data can be changing at any moment (though we do try to minimize the areas that will be making changes so that they are considerably fewer than normal) and taking the time to sync data changes leaves us open to additional changes being made while that sync is in process. This would require an additional step of comparing the data after the Table swap and if necessary then moving records from the now "old" / DELETEME table into the "new" current table (I mentioned this in a reply above as well). And now that I think about this question again, I just realized that an additional complication with this situation of data in the DELETEME table is that if there is an IDENTITY field, then there might be a collision if new IDs were created just before the Table swap (but after the data sync) and then new rows were added to the "new" current table before you were able to move these "missed" rows over. By using Triggers it is not really possible to be in this situation. But if you have a controlled downtime where you know for certain that data changes are not coming in, then yes, any of these other methods would be fine (though possibly still more work than just going with Triggers ;-)).
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
April 27, 2011 at 10:13 pm
mar10br0 (4/27/2011)
andrewkane17 (4/27/2011)
you mention having to update views, udf and procs after the table rename, why is this required?andrew
That is because the new table and its constraints, indexes, etc, although having the same name, are different objects (with different object-ids) as far as the server is concerned. So all dependent objects (with their compiled code still referencing the old object-ids) need a recompile to reference the new table. In many cases SQL-Server is smart enough to realize the dependent objects have disappeared and will try a recompile on first use, but this is not a fail-safe method (and I have seen in the past that objects get recompiled on each use after that, or not using the appropriate stats, degrading performance).
Marco
Andrew and Marco: Actually, the reason to update those objects after the Table rename is because the objects themselves have changed to accommodate the "new" Table structure but that is not in place until the swap is made so we need to minimize the amount of time when calls can be made to the Procs, etc. (remember, the application is still running during the release) that would point to the incorrect structure and hence cause errors. This is also why we rename all of the objects that have no programmatic name-dependencies first (indexes, primary keys, foreign keys, constraints, Triggers) and swap the Tables at the very end (with the exception of then adding any Triggers that might exist). By doing the Table rename last in the "swap" script and then immediately updating the Procs, etc. we reduce that gap down to 1 or 2 seconds (and in some cases less than a second).
But to Marco's point about the objects having different IDs but yet the same name (i.e. stale references), this is why I have seen some people issue an "sp_recompile" on the "new" table after the rename in order to mark all dependent objects for recompile. I cannot say that I have ever personally seen an issue of a stale reference but doing the sp_recompile against the table is something that I used to do until I was told that it really didn't provide any benefit and I had no evidence to support the idea of stale references*. However, if stale references are provable then all you would need to do is add the "sp_recompile" to the bottom of the swap script.
Take care,
Solomon...
*Note: One might claim that I haven't seen stale references because I am, in fact, updating the objects after the rename anyway, possibly correcting the issue before it is noticeable. True, but I don't always update all objects. Sometimes Views and/or Procs use a subset of fields that are not changing so those objects don't need to be updated.
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
April 29, 2011 at 10:03 am
Thanks Solomon for a great article and I agree this is a great topic. Here at my company we have very large databases and an application that is required to be up 24/7 so we need to make table modifications happen with minimal downtime. So the article and discussion are very useful as we work on a similar process.
April 29, 2011 at 10:07 am
I purchased this book a couple of months ago. It has several useful techniques that could go along with this topic. Refactoring Databases: Evolutionary Database Design
Luke C
MCSE: Data Platform, MCP, MCTS, MCITP - Database Administrator & Database Developer
April 29, 2011 at 10:15 am
Luke C (4/29/2011)
I purchased this book a couple of months ago. It has several useful techniques that could go along with this topic. Refactoring Databases: Evolutionary Database Design
Thanks for the tip Luke. This book looks useful.
October 5, 2012 at 3:24 am
An excellent article that applies a common-sense approach to doing these kinds of changes. It does rely of course on sufficient resources to allow this approach to take place - disk space springs to mind. Suppose you have a 100 million-row table containing VARCHAR data to restructure? Then some back-of-a-fag-packet calculations show that (and these calculations are REALLY approximate):
Average row size: let's pick 6000 bytes, for some in-row VARCHAR data.
Total row size: (6000 + 90-byte header) * 100m rows = 609GB with no row or page compression.
+ indexes, let's say they occupy 20% of the table space = 609GB * 1.2 = 730.8GB
Because you're rebuilding side-by-side, you potentially will need to allocate up to 730GB of 'elbow-room' for processing in the way you've described.
Side-by-side implementations are great but I feel sometimes there is an argument for doing these changes in-situ, especially when dealing with large data sets. You would also have to consider factors like load on the data, for example if your applications are hitting that table with various locks every, let's say 700ms out of 1000ms, then siphoning off the data in the manner you've described would have to be VERY careful that lock times don't increase beyond the app's (or database's) query timeout thresholds, and that you aren't siphoning data in a manner that encourages deadlocks.
Please don't get me wrong, I'm not slating the approach you've described, it's a common-sense and logical approach to doing large schema or table data changes. I would however be wary of this approach if resource is an issue.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
October 5, 2012 at 5:59 am
derek.colley (10/5/2012)
An excellent article that applies a common-sense approach to doing these kinds of changes. It does rely of course on sufficient resources to allow this approach to take place - disk space springs to mind....
Because you're rebuilding side-by-side, you potentially will need to allocate up to 730GB of 'elbow-room' for processing in the way you've described.
Hi Derek. Thanks for the reply.
You are absolutely correct: you would need sufficient disk space for the operation. The specific calculations don't matter as you can tell by the current size of the table and what you are trying to do with it. So a 300 GB table would need another 300 GB at least if you are keeping most of the columns. The main issue is what do you sacrifice due to requirements. Our requirements are that there is nothing more than a "blip" in the system as most of the time we do not turn the application off. So doing these changes in the current table are (or were) not always possible. Changes in Clustered Index, PK, FKs, adding NOT NULL fields all complicate doing it in place. Yes, you should be able to drop and recreate the Clustered Index using ONLINE = ON (IF you have Enterprise Edition!), and now in SQL Server 2012 you can add a NOT NULL field with a default without locking the table, but I have not tested either of these approaches so know how they work in the real world rather than theory. And again, the extent of the changes might dictate this approach as a requirement and in that case the business needs to accept the sacrifice of having that disk space if their primary concern is no down-time. Or maybe you have an update trigger on this table and/or Change Tracking and/or Change Data Capture that would be adversely affected by updating the current table. However, if you just want to change a column or two then yes, you should first look to doing that in-place by just renaming the column and dropping the old one (or whatever the situation demands).
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
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply