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
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
