Step 1: Back up, optimize and enable safety features

By Serdar Yegulalp, Contributor 13 May 2005 | SearchSQLServer.com

Digg This!     StumbleUpon     Del.icio.us

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

 Home: Introduction
 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

Digg This!     StumbleUpon     Del.icio.us

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary