Best Practices with Database Files

1. Create at least one secondary file group in each user database to separate system and application data. Tag the secondary file group as the default file group so that any object created without file group specification will reside on the secondary file group.

2. Keep data files and transaction log files on separate disks or disk arrays.

3. Store database backups on a disk array other than where data and transaction log files are stored.

4. Create multiple files for each user defined file group.

5. If you have large tables with millions of rows ensure to consider table and index partitioning. This feature can be particularly beneficial for data warehousing applications.

6. Try to keep read-only data in a file group separate from read-write data. If the file-group contains static data be sure to tag file group as READ-ONLY.

7. Place TEXT / IMAGE data on separate file groups.

8. Avoid using automatic growth options unless necessary. If you must use automatic growth options be sure to provide plenty of room for file growth and specify the upper limit.

9. Consider database snapshots for read-only activity. For example, can be used for SSRS reports.

10. Become familiar with partial backup and restore operations and use them to improve database availability.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s