SQL Server 2008 Admin

 Disaster Recovery in SQL Server 2008


To ensure disaster readiness, you should maintain system logs in a secure manner and perform recovery of a database on another SQL server. The full set of recommendations for disaster readiness is as follows:
-Ensure that your personnel are trained in restoring a database or server.
-Have your staff test the backups and recovery sets.
-Ensure that the regular database and transaction log backups are performed frequently.
-Maintain system logs in a secure manner.
-Keep detailed records of what service packs were installed and when.
-Keep records of the SQL server configuration.
-Perform and document the restoration of a server or database to another server.
-Maintain a base-functionality script that may execute insert, update, and delete statements or a batch of stored procedures to determine if everything is working correctly after a restore.

How to make Databases available even when the operating system (OS) disk fails

Configure your SQL Server 2008 implementation in the following manner:

-SQL Server files: RAID 5
-Operating System files: RAID 1
-Transaction files: RAID 1

To meet varying requirements for response time, data reliability, and performance, you typically use different RAID levels depending on how you are designing the SQL Server deployment. These RAID levels are:

-RAID 5: Known as Striping with Parity. The parity information is written across all disks in the array. You need minimum of three disks to form a RAID 5 array. This RAID level offers better read/write performance as long as all disks in the RAID 5 are present. If one disk is missing, the read performance is degraded. RAID 5 stripes data across all disks in the array.

-RAID 0: Known as Disk Striping. This RAID level stripes the data across disks in the array, offering better throughput on the read/ write operations. However, there is no data protection offered in this RAID level. If one disk fails, the data stored on the disk will be lost.

-RAID 1: Known as Disk Mirroring. You need minimum of two disks to form a RAID 1 array. One primary disk is used for read/write operations, and the data is replicated to the second disk. This RAID level offers better read performance and fault tolerance, but slower write performance than RAID 0. The scenario states that the database must be functional even if a drive fails in the system. To meet this goal, you must have the operating system files on RAID 1. Using RAID 1, you will mirror the operating system so that even if a disk fails, the operating system will function. The transaction logs should be placed on a separate RAID 1 array. SQL Server writes data to the transaction logs and maintains serial information of all the modifications that occurred in a SQL database. The transaction log files can be used for rollback and roll forward operations from a SQL Server database. To enhance the performance of SQL databases, you should place the transaction log files on a dedicated RAID 1. Finally, the SQL Server files and filegroups should be placed on a RAID 5 array to achieve the best performance throughput.