News Stay informed about the latest enterprise technology news and product updates.

Step 1: Back up, optimize and enable safety features

Step 1 of our data integrity guides explains how to develop your data integrity strategy.

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
 

Dig Deeper on Database management system (DBMS) architecture, design and strategy

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close