SSWUG Free Expo Event: Real-World SharePoint Administration – Two More Days
Friday, February 11, 2011, 9 a.m. – 1 p.m. PST
Build your skills for deploying and supporting SharePoint by attending this virtual expo. Experts will teach you techniques for managing access control, implementation strategies and tips you can use immediately. With registration you also receive a free complimentary membership to SSWUG for one month. After the expo, the content will be available for further review and study.
Register today to reserve your place!
DBTechCon – Spring 2011
The SSWUG Spring 2011 virtual conference is quickly coming together. 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)
Troubleshooting SQL Server 2008 Snapshot Replication
In this article, Alexander Chigrik explains some problems that you can have when you work with SQL Server 2008 snapshot replication. He also tells how you can resolve these problems.
Featured White Paper(s)
Why and How You Should Find and Fix Index Fragmentation
In a high-volume database you need frequent critical maintenance. If you skip this important task, index fragmentation may ca… (read more)
Database Controls – History Tables
Production controls can find themselves implemented in any number of ways. Today we have a response from one of our readers regarding an Audit technique proving quite effective.
The neat part about this strategy is that it is easy to implement, and the coverage is quite good.
David Writes:
At my company, we do something (unique?) in our system to cover ourselves: we have a trigger on every table, that writes every change to every record out to a mirror table in a separate database with the date/time of the change and the type of change (insert/update/delete).
The trigger is very simple, and is automatically created on every table by a script that we run every time we do an upgrade to our data structure.
This protects us in a few ways: against data corruption due to coding errors (we can go to the mirror tables and get previous values of the data), against dumb mistakes when tweaking data in query analyzer (we can get previous values), to help answer user questions about "who made this change to this data and when", and also troubleshooting any errors in our code (reviewing exactly which data changes were made when).
We have to go to the history tables to get some old values about once every 4 months. But knowing it’s there lets us sleep better at night, and lets our (small) team work a lot faster than if we had to be really careful. And we’ve never had to restore a previous full database backup to go get some data.
Our production database is about 60GB with 12 years of data…. our history database is about 50GB a year. Every couple of years we archive the history to a cheap disk drive and start over fresh, just to conserve server memory and space on our expensive SAN.
PS: Our system is a web-based app, and we have similarly-intense logging in a SQL table of every click by every user, every form submit with all the form variables, etc. That plus this data table logging lets us completely retrace anything that happens in our system…. great for debugging.
I really like the auditing methodology as a production control method. The only problem I have is that the auditing method allows you to fix problems after the occur. I have worked with some companies where, if this were the only implementation, they would be out of business. Corrupt data for even a few minutes would be catastrophic.
I’m sure others of you have experienced the same kinds of situation. How about sharing from your expertise with our readers. I’d be interested in your processes and controls that protect the integrity of your data from evil doers and well doers alike. Drop me a line at btaylor@sswug.org.
Cheers,
Ben