DBTechCon – Spring 2011
The SSWUG Spring 2011 virtual conference is quickly coming together. You can go to the registration page and see profiles for presenters, and summaries for the sessions to be presented. Without a doubt, this will be the largest virtual conference in the information technology industry. Access more than 70 sessions from well seasoned professionals with in-depth instruction on technologies such as SQL Server, SharePoint, .Net, Business Intelligence and much more. Register any time from now to April 19th. There are early registration discounts for those who register early. Go to the Registration page for more details and to get signed up right away.
$$SWYNK$$
Featured Article(s)
SQL Server 2008 Policy Based Management (Part 1 of 3)
Administrators need to constantly monitor the security surface area of the entire database server, along with permissions and settings for every database, table, user, role, and schema. Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Policy-Based Management consists of three components: policy management, policy administrators who create policies, and explicit administration. Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly force the targets to comply with a policy. Throughout this session, we’ll see examples of how Policy-Based Management can help administrators effectively establish and monitor policies for their database environment.
Featured White Paper(s)
Essential Performance Tools for SQL Server DBAS
Optimizing SQL Server performance can be a daunting task. Especially so for an increasing number of reluctant DBAs faced with… (read more)
Database Controls are not Only History Tables
I wish to to thank all who have contributed to this topic, and there are lots of great ideas. Moreover, I don’t want to scare anyone off from sharing their opinion because we all need to learn from one another.
I received a number of Emails recently regarding Database Controls that validate the need to not only monitor change that is made, but also prevent modification of data, and have the ability to restore data to an original condition when an event occurs corrupting the data.
Here are some of the comments I received from many of you…
Shawn:
I’m not a big fan of the history table approach listed below for two reasons – first is the overhead of the trigger for every dml query, especially in a ecommerce environment where speed is king. Second, I think it would be cheaper (compared to san space), easier, and more efficient to buy a log reader tool?
Lennart:
What about database managed history tables which means you don’t need to create triggers on tables but just let the database itself create
history records where information is kept on how a specific row looked like at a certain time. And, on top of that; temporal queries so that you can ask the database how data looked like at a given time combining the data in the base tables with the data in history tables?
DB2 on the mainframe has this functionality!
Donald:
The solution is a good HISTORY but not good for data corruption, as you point out…It allows them to not work carefully? Really? Data should ALWAYS be entered carefully and rules should be inplace to try and catch bad data (not totally possible).
Detlef:
Nice hint in your newsletter.
In our databases we also would like to know who changed what and when. Not for debugging or controlling issues – just for the users to have a history available on their items.
It doesn´t go so far, that we would like to store the page values or clicks from the users. The described way sounds nice to have a trigger available that automatically connects to each table in the database.
Dave:
I’m currently looking into using Change Data Capture to house changes to data, but don’t like the way it’s implemented and how to get to the data.
I’d like to do triggers on the tables since it’s much easier to read the history, etc. and I like that you’ve created a script to add the trigger to all of the tables. Would you be willing to share this script?
I wondering if some of you have some procedural controls you’d be willing to share. I’m not talking about code; I’m talking about processes or practices you use to reduce the risk of data corruption, and have the ability to back out corrupted data when it exists. Please send your insights to btaylor@sswug.org.
Well, it turns out that I will be teaching a session in the upcoming SSQUG Spring Tech Conference on Database Testing. I will be demonstrating how to test triggers, functions and stored procedures in one session. I will be demonstrating the Audit Trigger and how to test it in this session. The course contents will be posted on SSWUG. You could easily take that example and create a trigger generator using the INFORMATION_SCHEMA.COLUMNS table.
Cheers,
Ben