Security
My web manager maintains security of the EC2 based SQL Server instances with following important best practices.
Port based security
Using VPC and AWS security group concept, we will open only following ports on EC2 instance hosting SQL Server to known IP address sources.
| Service | Port Number | Source |
| Remote Desktop | 3389 | Known external IP addresses |
| SQL Server | 1433 | 1) Web Server Private Addresses 2) Localhost |
This will encapsulate the server from any other unauthorized access.
SA user prohibited
SQL Server provides default “sa” user abbreviated as “system administrator”. This user has unlimited access to all databases. This user will be prohibited to be used by web application as it opens security risk for attacks via web application safety holes.
My web manager will create a separate dedicated user to be used by web application. This user will have limited access to server resources to ensure it does not provide elevated access to DB resources to unknown users.
Complex user passwords
For any server users or database users, we will generate a complex long passwords. This will reduce password guess attacks which are very common for easily guessable passwords.
Backup
My web manager is following below backup strategy to have adequate backup of databases for disaster recovery.
Daily Database Backup
My web manager has a custom developed script to create a daily backup of given databases. This script creates a database backup of given databases at a specified time daily, creates a compressed version of this database and copy this database backup in S3.
Separate S3 backup is created with unique permissions to provide maximum security to database backups.
Database backups into S3 are following below life cycle strategy to keep adequate DB copies for a disaster recovery event.
| Backup Storage | Lifetime | Cost Impact |
| Most Recent | 1 – 30 Days | High storage cost
Low retrieval cost |
| Recent | 31 – 90 Days | Med storage cost
Med retrieval cost |
| Old | 90 – 365 Days | Low storage cost
High retrieval cost |
Weekly Volume Backup
We will create a full volume backup of DB server weekly on sunday early morning. This will create a volumetric backup of the entire DB server so in a disaster event, the entire volume can be re-instated in order to restore the database.
Use case
In a disaster event, we propose following steps to be taken in order to restore database to the most recent backup data.
- Restore most recent weekly volume backup.
- Replace respective database from most recent daily database backup.
Considering this scenario, at any point of time, we can ensure retrieval data from previous day.