Best Practices & Monitoring Points For SQL Database & Server

Best Practices :

1. Application and Database should be on different servers

2. Proper Backup plan should be implemented (Based on Data size and RPO and RTO)

                Plan :    

                i. Full Backup on Sunday

                ii. Differential Backup on Daily

                iii. Log hourly or every fifteen minutes

                iv. System database backup plan should also there

                v. Restore drill

                vi. Backup reports

3. Proper Indexing

                i. Based on Data type, Data size, Fill factor

                ii. Implement Indexs suggested by Sql server engine having high impact and monitor

                iii. Remove indexes not in use

4. Remove all the objects which were never used

5. Update statististics every week or more frequently(depending upon data insertion and updation and deletion)

6. Email notification

                i. Define mailer profile

                ii. Create operators

7. Define Bottlenecks for CPU Utilization (Performance)               

8. Create Logins and give proper roles not all

9. Proper naming convention of sql objects like procedures with prefix usp

10. For heavy data searching use Full text search

11. Proper history clean up

12. Always logoff after completion of task on prod environment

a. SET NOCOUNT ON should be in every proc

b. Proper try and catch should be there
c. Use SARGABLE rules

-> . DR plan should be ready (Disaster Recovery)

->. Apply high availability solution ( Logshiping or replication or clustering) depending upon the use of data

--------------------------------------------------------

Monitoring :

A. Backups, backups should be validated and monitored, Define retention period

B. Monitor job failed and job status

C. Monitor services

D. Monitor CPU Utilization & system performance

E. Monitor database performance

F. Monitor disk spaces

G. Monitor tables (based on iteration prepare plan for partitioning and archival)

H. Monitor security

I. Monitor users and delete orphan users

J. Use ssrs for monitoring create ssrs reports(less costly) or use third party tool (more costly)

K. Restricted Access

L. Long running query and job alert

M. Agent Job history configuration and maintenance

N. Monitor database integrity