Sunday, November 14, 2010

Data and Log Files configuration

1. There are two types of data files in SQL Server  Primary and Secondary

2. You can have one Data Primary file and 32766 Data Secondary file in SQL 2005
3. Transaction log records database modification you can create multiple log files per database to facilitate faster recovery
4. Filegroup is a logical structure that lets DBAs group data files and mange them as a logical unit
5. Filegroup contains data only, you can not create log file
6. Configuring database files with RAID(Redundant array of inexpensive disks) for better peformance
7. RAID 1 known as disk mirroring, provides a redundant copy of the selected disk, RAID1 improves read performance but can degrade the performance of write operations
8. RAID5 stripes the data across the disk of the RAID but it also adds parity information to provide fault tolerance.
9. RAID10 offer better availability and performance than RAID5
10. RAID10 is recommended RAID system for Transaction log, Data files and Index files
11. Always place Transaction log in RAID10 and Data and Index files on RAID5
12. Do not put Data files on the same drive that contains OS files
13. Put the Transaction log file(LDF) on separate drive from data files
14. Put the Tempdb database on a separate drive possible preferably on RAID 10 or RAID 5

No comments: