|
||
One of my consulting clients recently bumped into a problem. An ugly little problem with no seemingly easy solution. This client is using SQL Server Merge replication to successfully keep a fleet of laptop users in synch with sales and other critical business data.A key business need in maintaining this replication topology is the need to audit changes, or keep track of who makes changes and when—especially in terms of deleted data. Sadly, SQL Server doesn’t provide any way to natively audit these changes (it would be great if there were a built-in option that allowed for auditing of which subscribers made which changes). Even worse though is the fact that while SQL Server doesn’t support any type of built in auditing, the very nature of Replication makes auditing a tiny bit tricky. This is especially true if you take advantage of how SQL Server 2005/2008 make it so much easier to track schema changes on your replicated articles or objects. One of the easiest ways to implement auditing of SQL Server data modification is just to use triggers - which allow the easy capture of changes to be dropped into specialized audit tables (roughly matching the same structure as the table being audited) that are decorated with additional attributes such as the time when the change is being made, what kind of change (UPDATE, INSERT, or DELETE) and who or what is making the change. This common practice works well, and makes it possible to easily keep auditing records in place. The problem, however, is that if you use Triggers to implement this auditing functionality in a replication topology, you need to be careful about how those triggers interact with replication agents. First and foremost, when implementing auditing for Merge Replication, you typically only want to audit changes back at the publisher. (You can audit changes on the subscriber - but then you’d have to connect to each subscriber to peruse the audit trail or history.) Consequently, what you really want are just triggers on the publisher that capture changes as they are pushed back in from subscribers - or made by admins or users on the publisher. And that’s where the paradox can begin. If you use default schema-options during the creation of your publications, triggers will be included. But if these triggers are pushed to your subscribers, they’ll need auditing tables to handle the execution of your triggers, or your changes will fail. Of course, if you’ve done anything with Replication, you know of the NOT FOR REPLICATION option that can be defined for many SQL Server objects - including Triggers. If you use that option on your subscribers, then you won’t have to worry about changes made by your replication agents causing problems (i.e. via updates). But any local changes will still fail. More importantly, if you define your audit triggers with NOT FOR REPLICATION, then they won’t fire up at the publisher when subscribers are pushing changes back up from subscribers. In other words: the NOT FOR REPLICATION option isn’t what you’re looking for. The solution? Don’t use default schema options with your publications if you’d like to audit changes. Instead, use a custom bitmask for the @ schema_option parameter in sp_addmergearticle - to exclude triggers. Then you can put triggers in place on the publisher (and make sure that you skip adding the NOT FOR REPLICATION option - because you want these triggers to fire when replication agents are uploading changes), and you’ll then be able to audit as needed. The problem that my client is dealing with is that they just decided to drop a number of triggers as part of a plan to clean-up how they were doing auditing before. Things didn’t quite go as planned, and, consequently, they’ve lost some auditing capabilities. That’s not a huge loss - short term. But where it gets painful is when someone (somewhere) deletes data that other people need. Without auditing, the DBA can’t figure out who deleted the data, nor can they (more importantly) easily restore or recover the data as it was before it was deleted. Even worse, of course, is the fact that sometimes it takes a few days for someone to notice that data that they needed was deleted. This, in turn, is complicated by the fact that while the database being replicated isn’t huge, it is being synchronized by users in the field over a semi-slow VPN. That, in turn means that re-initializing is a painful task - both for IT and for the sales team in the field. The long term plan is to schedule some down time, modify articles to no longer include user defined triggers, and then re-initialize the publication. But given how painful that re-initialization process is, it will be a while before it’s put into place. Given my client’s current predicament, it occurred to me that a great temporary solution would be to use SQL virtual database from Idera. It’s insanely easy to use and lets DBAs ‘simulate’ full-blown databases from backups. Just point SQL virtual database at a backup file (or a backup file and a series of log file backups) and within mere seconds, it will ‘attach’ the data in the backup as a ‘virtual’, database that you can query just as you would a real database. It’s quite slick and really has to be seen to be believed (especially in terms of how painless and versatile it is). Moreover, coupled with a decent archival process (such as keeping a special backup every week on hand for a few months or whatever), SQL virtual database can help account for problems in cases where it takes a few days (or a week or more) to find that data has been removed. In some cases, it would also almost make more sense to use SQL virtual database for auditing purposes (if recovery is all that your after) as it doesn’t require additional storage space needed for storing audit records in the database. Instead, just keeping backups on hand (which you should already be doing as part of your disaster recovery plan) would be enough to let you recover data in ‘push comes to shove’ scenarios where something needs to be recovered periodically. HOW CAN IDERA HELP? Idera’s SQL toolbox includes SQL virtual database that lets you attach SQL Server backup files and query them just like they were real databases! Its revolutionary, patent-pending technology allows DBAs to gain instant-access to critical data in a backup file without wasting the time or storage necessary to restore the database Try SQL virtual database for free! Download a free 14-day trial: http://www.idera.com/Products/SQL-toolbox/SQL-virtual-database/ ABOUT THE AUTHOR Michael K. Campbell (mike@sqlservervideos.com) is a consultant with years of SQL Server DBA and development experience. He spends most of his time engaged in consulting, technical evangelism, and creating free online SQL Server Videos. Watch the free SQL Server videos: http://www.sqlservervideos.com For additional information or to download a 14-day evaluation of any Idera product, please visit:www.idera.com . |
||