I would like to present this final recipe of the book more like a list of best practices that I have collected and implemented during my DBA working years. I would like to stress that few may look elementary, but they is logical, which are overlooked most of the time. The following is the list of DBA manageability practices that are important to handle day-to-day tasks of the SQL Server 2008 R2 environment.
1. Design process: Determine the Project Scope and which roles will be required.
2. Design the SQL Server Database Engine infrastructure, Integration Services infrastructure, and wherever necessary SQL Server Analysis Services and SQL Server Reporting Services infrastructure.
3. Storage: Always perform a capacity planning exercise by compiling the amount of database storage that is needed for the next one or two years.
Required Information
Description
# Users Per Server
Total number of users hosted by that server.
% Concurrent Users
The percentage of users connected to the server during peak times.
Storage Capacity in Gigabytes
The calculated disk storage capacity needed.
% Buffer Factor (for growth)
The percentage of disk storage growth allowed by the system.
Read % of IOPS
The percentage of IOPS that are Read operations.
Write % of IOPS
The percentage of IOPS that are Write operations.
Disk Size (GB)
The drive size being considered for the storage system.
4. Ensure to choose appropriate RAID levels. For RAID 0, 1, or 1+0, calculate the number of drives necessary and divide the total storage by the actual drive size (round the value to the nearest and multiply by two).
5. Security: Analyze the required permission structure and make the necessary adjustments to ensure people have the rights they need to work, but not additional rights that may cause potential problems.
6. In addition to securing your database servers, make sure the system and user database backups and any external programs are also secure by limiting any physical access to storage media.
7. Ensure to include a backup strategy of transaction log backups every 15 minutes depending upon the SLA, daily differential backups, and weekly full database backup. This is to highlight the essentials of planning a Disaster Recovery (DR) strategy for the data platform.
8. Ensure that you have tested the backup files by restoring them to a standby server to ensure that there is no corruption on the backup media.
9. For highly transactional mission-critical databases, ensure to include a daily update on statistics schedule during the early hours of the working day and an alternative day schedule of index reorganization on volatile tables that will have numerous
INSERT/DELETE/UPDATEoperations.10. Also include a re-index of highly transactional and volatile tables that will have numerous
INSERT/DELETE/UPDATEoperations.11. Be sure to perform database integrity checks for the user databases.
12. Wherever possible, document the information pertaining to database, application usage, Service Level Agreement(SLA), data recovery, and escalation procedures.
13. Perform periodic disaster recovery—failover and failback—tests on the database platform.
14. For High Availability and Disaster Recovery purposes, it is ideal to consider Database Mirroring and Log Shipping on mission-critical databases. In case of high Recovery Point Objective, failover clustering is the best option.
15. Replace any redundant hardware by performing periodic checks on server hardware | application, web, and SQL Server. Be sure to involve the hardware vendor for any latest firmware updates on hardware resources such as CPU, Disk, and Network components.
16. Schedule the Log backup before the full backup. If you schedule the Log backup before the full backup, you will only have to restore the last full backup and one transaction log. Scheduling after the full backup will demand the restoration of the last full backup and two transaction logs backups.
17. Establish an Auditing Policy on the Backup Folder. Enabling file access auditing to the Backup Folder will monitor user access to the backups.
18. After the database is backed up in a file, compress and encrypt the files before moving the contents to tape backups or other forms of long-term storage.
Encrypting the backup files will help you protect the confidentiality of the information if somebody gains physical access to the media.