By: Derek Colley | Comments (3) | Related: > Testing
Problem
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.
Solution
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.
Next Steps
- 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:
About the author
Derek Colley is a UK-based DBA and BI Developer with more than a decade of experience working with SQL Server, Oracle and MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
