Standardizing SQL Server CRUD Templates with Rollback Plans
New stored procedures or types, INSERT, UPDATE and DELETE transactions are being executed against your SQL Server databases by developers or other stakeholders on an ad-hoc basis, changing data without rollback plans, controls or an adequate audit trail. As a database administrator, you are losing track of the releases against the databases and one day someone *will* UPDATE without a WHERE clause - you need to take control, and now. How should you do so? Check out this tip to learn more.
In this tip I would like to discuss the benefits of template standardization for Create, Update and Delete batches that you or your development team issue as part of controlled changes. CRUD changes will typically include CREATE PROCEDURE blocks, INSERT, UPDATE and DELETE statements and will typically contain only DML statements - data updates only, no schema-changing DDL.
To some developers especially, standardizing the approach for applying basic transactions to SQL Server may seem onerous or overly Kafkaesque - however, in this tip I will hopefully demonstrate the benefits and suggest an approach that you (as a database administrator) can apply to keep the overhead of change approvals to a minimum and to improve the quality of the code applied to your databases. Standardization also brings the benefits of a short feedback loop - your developers will eventually learn and appreciate the value of templates for day-to-day changes, even if at first they resist.
So, what are the features of a good template? Let's start at the beginning - the header. This is where the purpose of the code is explained and where accountability for implementation is placed. It acts as a change history and an explanation of the transaction batches for future reference. In these respects, header code (and templates in general) go hand-in-hand with source control facilities, as over time you will create a standardized library of applied changes and provide full accountability for both research and audit purposes.
SQL Server Template Header
Let's look at a typical header section.
/* CHANGE/RELEASE NUMBER: 4325123 DATE CREATED: 22/12/2012 DATE TO BE APPLIED: 24/12/2012 AUTHOR: Santa Claus PURPOSE: This release will update the table 'admin.NaughtyOrNiceList' in database 'CHRISTMAS_2012' to modify the record of a child who has been naughty this year. CHANGE HISTORY: Date Description Author ---- ----------- ------ 21/12/2012 Initial version Santa Claus 22/12/2012 Revised, extra names Head Elf APPROVAL STATUS: Pending */
This header is not set in stone - feel free to amend it to your own purposes. However it's clearly laid out - each section is divided, with labels in capitals and values in small case. Tabs between columns also add to a clean layout.
SQL Server Change and Rollback Logic
Now, depending on how strict your change control is (and your adherence to ITIL), you may wish to include a rollback section. One question which I've had to address before is whether rollback code is included as part of the transaction that executes the change, or whether the rollback script should be kept separate. Remember, according to the ACID principles, each transaction is Independent - that is, the state of one transaction does not affect the state of a second, simultaneous transaction - each transaction should be treated as an independent entity. In which case, isn't specifying the rollback separately from the main transaction dangerous, as changes may occur to the base tables between transactions?
The 'all-in-one' approach:
BEGIN TRAN change_4325123 WITH MARK N'change_4325123' CREATE TABLE [rollback].NaughtyOrNiceList_update_4325123 ( child_no BIGINT, status VARCHAR(7) ) INSERT INTO [rollback].NaughtyOrNiceList_update_4325123 ( child_no, status ) SELECT child_no, status FROM admin.NaughtyOrNiceList WHERE child_no = 324235; IF @@ROWCOUNT <> 1 BEGIN RAISERROR('No child with that number has been found!', 10, 1) ROLLBACK TRAN change_4325123 END ELSE BEGIN UPDATE admin.NaughtyOrNiceList SET status = 'Naughty' WHERE child_no = 324235; COMMIT TRAN change_4325123 END
The 'independent' approach:
BEGIN TRAN rollback_4325123 WITH MARK N'rollback_4325123' --CREATE TABLE [rollback].NaughtyOrNiceList_update_4325123 ( child_no BIGINT, status VARCHAR(7) ) INSERT INTO [rollback].NaughtyOrNiceList_update_4325123 ( child_no, status ) SELECT child_no, [status] FROM admin.NaughtyOrNiceList WHERE child_no = 324235; IF @@ROWCOUNT <> 1 BEGIN RAISERROR('No child with that number has been found!', 10, 1) ROLLBACK TRAN rollback_4325123 END ELSE COMMIT TRAN rollback_4325123; -- mind the gap BEGIN TRAN update_4325123 WITH MARK N'update_4325123' UPDATE admin.NaughtyOrNiceList SET [status] = 'Naughty' WHERE child_no = 324235; DECLARE @child_status VARCHAR(7) SELECT @child_status = ( SELECT TOP 1 [status] FROM admin.NaughtyOrNiceList WHERE child_no = 324235 ) PRINT @child_status IF @child_status <> 'Naughty' OR @child_status IS NULL BEGIN RAISERROR('No rows updated!',10,1) ROLLBACK TRAN update_4325123 END ELSE COMMIT TRAN update_4325123
You will note that in the 'independent' approach, I have replaced @@ROWCOUNT with a distinct sub query from the table - this is because certain actions, such as BEGIN TRANSACTION, reset the @@ROWCOUNT to zero which makes it unreliable for my purposes here ( see BOL -> http://technet.microsoft.com/en-us/library/ms187316.aspx). Choose the method that suits you best, or use a different method (temporary table counts or CTEs, for example) if you prefer.
Instinctively, the 'all-in-one' approach looks better, since if the child is not found then the whole transaction is rolled back and the change is not applied. With the independent approach, another transaction could occur between the two transactions (e.g. a DELETE of child_no 324235) which would cause the second transaction to update 0 rows. However, what would happen if the author made a mistake - perhaps left out the second WHERE clause unintentionally?
UPDATE admin.NaughtyOrNiceList SET status = 'Naughty';
The rollback becomes invalid, since it's 'backed up' only the one row of data that was meant to be changed. By the time the error is noticed, the transaction has been committed. We will need to restore back to the transaction mark - which will also undo the rollback part of the script - not ideal, as the rollback script should be independent from the main change for exactly this reason! Whereas with the independent approach, we can rollback the second transaction using the MARK put on it without affecting the rollback table. We can also ensure that we can restore the rollback data to a *known safe point* i.e. before the second transaction was executed, and indeed before any intermediary transactions were processed.
MARKing the SQL Server Transactions and Ensuring Rollback Viability
Using MARK statements also allows us to view the history of applied MARKed transactions by querying the msdb.dbo.logmarkhistory table. Finally, in the case of transactions known to run a long time (i.e. the update of hundreds of thousands of rows, as indeed Santa might do) the separated approach provides a 'resting point' between transactions where other transactions can acquire a lock and process their load without timing out.
In the example above, you will see I used the 'rollback' schema. This is an invented schema used specifically to hold information about changed rows of data in order to provide an audit trail of changes (to complement source control) and to enable swift and accurate rollback if necessary. You may decide that a separate rollbacks database is appropriate, especially if space is an issue (you can keep a separate database on a separate file system if required). It would be wise to implement a purge strategy, for example a scheduled SQL Server Agent Job that removes all tables that have reached a predefined age. For the example above, and in general, you will also need a second rollback script to apply the rollback of the change you applied by replacing the values from the rollback table you created. For example:
BEGIN TRAN forced_rollback_4325123 WITH MARK N'forced_rollback_4325123' IF OBJECT_ID('rollback.NaughtyOrNiceList_update_4325123') IS NULL BEGIN RAISERROR('Rollback table doesn't' exist!',10,1) ROLLBACK TRAN forced_rollback_4325123; END ELSE BEGIN UPDATE admin.NaughtyOrNiceList SET [status] = ( SELECT TOP 1 [status] FROM [rollback].NaughtyOrNiceList_update_4325123 WHERE child_no = 324235 ) WHERE child_no = 324235 END IF @@ROWCOUNT <> 1 BEGIN RAISERROR ('No rows updated!',10,1) ROLLBACK TRAN forced_rollback_4325123 END ELSE COMMIT TRAN forced_rollback_4325123
Another reminder about @@ROWCOUNT - due to peculiarities in the @@ROW_COUNT variable particularly when using batches, you may wish to substitute this with an explicit call to the source table instead, as stated above - I use it generally, as I've found it reasonably reliable. You may also wish to vary the scripts above - for example, in my version of the rollback script, you'll note the nested SELECT statement could return 0 rows, meaning potentially a NULL update where one is not desired. Eliminating the use of @@ROWCOUNT would solve this, possibly at the expense of performance (as each row will essentially be checked twice, which is an expensive operation).
SQL Server Template Footer
The final piece of the template is the footer - this doesn't have to be extravagant, but could be an area where, for example, deviations from the expected change, code peculiarities, comments and other errata could reside. For example:
-- Note: This must be approved and implemented by Christmas Eve!
Or you could use this area to include a test script - assuming you have a test/dev/pre-prod environment, you could include a commented-out section with the test code and the outcome of the test. In cases where CRUD changes are critical, these can ease the pain of getting changes approved (whether by CAB or by individuals) and provide further audit information.
SQL Server Development and Circulation to the Team
Now you have your template, adapt it and create multiple versions to deal with different scenarios - not necessarily limited to CRUD. What if your business regularly creates new tables with similar formats? What about security changes - adding and deleting users or logins, for example (although arguably, as the DBA, this is *your* job!). Templates will take away much of the headache of the tedious administrative work and allow you to concentrate on the more interesting aspects of your role.
Finally, it's important, once you've built your templates, to circulate them to your developers. Get the team leader(s) involved, explain why you've written the templates and what you want to achieve. Engage the developers directly and encourage them to make use of the templates - using them will save them, and you, time and will help to minimize and mitigate the risks associated with releases. Use a standardized approach to build up your library of change templates and it will pay off in the long-term.
- I hope you've found this short tip informative and useful - if you have any comments (or don't agree with something I've said), please post a comment and I'd be delighted to discuss it with you.
- Check out these related tips:
Last Updated: 2013-01-03
About the author
View all my tips