In many cases, much of companies’ critical and confidential data is held in SQL databases. In this guest post, business tech writer James Harper describes three things database admins must do to keep SQL servers up and running and protect the data they contain.
Perhaps the most important job of a database admin is keeping SQL servers secure and stable. Despite the relative stability and reliability of modern versions of SQL server, databases do become corrupted from time to time — and security is a something that companies of all sizes should be concerned about. What should admins and SQL server support engineers be doing to protect their company’s precious data?
1. Create backup and recovery plans
There are many things that could cause problems with your data. Poorly written code could corrupt it, hardware failure could wipe information, and fires, floods or other unexpected events could be catastrophic. Admins need to do everything they can plan for those eventualities.
2. Define backup requirements
Every company has different requirements for backups, based on how often data must be updated, and how much data they can afford to lose. The amount of data companies are willing to lose is called the Recovery Point Objective (RPO), and the length of time considered acceptable for getting back up and running is the Recovery Time Objective (RTO).
Small companies that don’t use a database much may accept the loss of a few days’ worth of data. But those that carry out thousands of database transactions each day will have requirements that are more stringent.
Database admins should consider the following when defining backup requirements:
- How often is data changed?
- Is it easy to re-enter or re-create data if some is lost?
- How far back in time is it reasonable to expect employees to go when re-creating data?
- How long can the system be offline for recovery without adversely affecting the operation of the business?
- How much data loss would be acceptable?
Once those questions are answered, companies can implement a backup system. Some organizations may need to perform automated backups every day (or even more often), while others may be able to get away with weekly backups.
Some of the automated backups can be stored locally, but at least one copy should be sent to an off-site location. Place local backups on a database server so that there is no network activity required and the backup can be written quickly. Copy the backups off-site during a quiet time of day, when network and internet activity is minimal.
The traditional backup system is to keep three backups of different ages (Grandfather>Father>Son). However, some companies will need to be able to recover data from further back, either because of audit requirements, or because of the possibility of corrupted data going undiscovered for a long time. Data storage is cheap, so it’s best to keep more backups to be on the safe side.
3. Implement security best practices
Database admins must pay careful attention to security — that includes the security of the server itself, the data that is stored in the database and any backups. Things to consider include:
- Change your root/admin password frequently
- Do not grant more privileges on tables than required for each account
- Encrpyt all passwords stored inside a database
- Use TDE to encrypt backups that contain confidential data
- Implement a system to manage the certificates that were used to encrypt those backups (remember that without the certificates the backups will be useless).
About the author: This post was written by James Harper on behalf of dsp, the SQL support specialists. James writes on subjects that help businesses take advantage of the latest technologies.