The first and most obvious data integrity strategy is to make and keep a solid set of backups. Make backups at least once a day, and make them before you do any scripted data-changing operations (nightly optimizations, index rebuilds, etc.). Backups can be made to external/attached storage or made locally and then copied off either via a script or by hand. But, keep them somewhere other than on your production machine.
Periodically generate SQL scripts for your database schemas. In the event you need to regenerate the schema separately, it's easier to use a script than to restore a backup. SQL Server Enterprise Manager's script generator isn't the greatest, but it gets the job done. (If you want a script that generates INSERT statements to replicate existing data, try this script by Narayana Vyas Kondreddi.)
Keep SQL Server patched. A third-party tool like Gravity Storm Software LLC's Service Pack Manager can help you keep on top of which system components need updating, including Microsoft Data Access Components (MDAC) and other "under-the-hood" technologies.
Turn on torn page detection by default for each database. The amount of overhead generated by turning on torn page detection shouldn't be an issue on a machine fast enough to run SQL Server in a production environment.
ENSURING DATA INTEGRITY IN SQL SERVER
Step 1: Back up, optimize and enable safety features
Step 2: Segregate data aggressively into files and filegroups
Step 3: Consider using implicit transactions
Step 4: Be careful how you enforce internal referential integrity through triggers
Step 5: Use constraints and relationships to keep out bad data
Step 6: Don't expose interfaces that create dynamic SQL to the end user
Step 7: Use a "check-in/check-out" mechanism for contested data
|ABOUT THE AUTHOR:|
| Serdar Yegulalp
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2005 TechTarget