#1443 - Improve SQL Server
The goal of this project is to increase security, efficiency, and consistency of the A-Team SQL Server instances, with an overarching goal of minimizing risk.
We have completed the following items:
Remaining items include improving on our data protection plan:
We have completed the following items:
- Upgraded all of the relevant instances to the same supported version of SQL Server.
- Implemented performance and activity monitoring tools across all of the servers.
- Enabled and configured failover clustering (AlwaysOn) to offload production reporting to another server and provide high availability in the event of a connection failure.
- Restricted access through the colon prompt to only the A-Team (and a few exceptions) to all of the Colleague SQL Servers.
- Leveraged instant file initialization feature (IFI) to dramatically reduce backup and restore times.
- Created new group across all servers to restrict and organize who has administrative access to the SQL Server instances.
Remaining items include improving on our data protection plan:
- Revamping SQL Server backups to perform more frequent backups, which reduces our Recovery Point Objective (RPO) and smaller backups, which reduces our Recovery Time Objective (RTO).
- Basically RPO (Recovery Point Objective) establishes the amount of data you can reasonably afford to lose and still maintain business continuity. The more frequent the backups, the less data that can be lost since the last backup - in case, in an emergency situation, we ever have to revert to the last backup.
- RTO (Recovery Time Objective) establishes how long an application can be down before there is a measurable business loss. The smaller the backup, the quicker to restore, if we ever have to, in an emergency situation.
- More consistency and integrity checks, which ensures all backups are restorable.