Showing posts with label installation. Show all posts
Showing posts with label installation. Show all posts

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

Sql Installation

Code to check Sql Server Installation Date & Time :
WHERE SID = 0X010100000000000512000000