Backup Power/Generator
Importance: Important
Difficulty: Hard
Used to:
-
Increase power HA
-
Increase outside forces HA
If the power goes out for a much longer than a few minutes, then you will need to have access to backup power through your own onsite generator. Of course, only the most critical SQL Servers and data centers need, or can afford, this capability.
Tight SQL Server/Database/Object Security
Importance: Critical
Difficulty: Easy
Used to:
-
Increase human error HA
-
Increase outside forces HA
One of the easiest, but often neglected ways of boosting SQL Server’s high availability is to incorporate tight security at the SQL Server, database, and object level. As a rule, prevent all access to everyone for any reason, and then only very selectively give the absolute minimum permissions to users to get their jobs done.
Don’t Use SA for All Your SQL Server Access
Importance: Critical
Difficulty: Easy
Used to:
-
Increase human error HA
As a SQL Server SA, it is very tempting to use the SA account, or an Active Directory account with SA rights, to do all of your work within SQL Server. And in some cases, you have to be an SA to perform some tasks. But in other cases, you don’t need to have SA rights to work with SQL Server. In those cases, it is safer if you use a different account with less than SA rights. This way, if you make a mindless mistake, there is less chance that the mistake will destroy data or bring down your SQL Server.
Periodic Security Audits
Importance: Important
Difficulty: Easy
Used to:
-
Increase human error HA
-
Increase outside force HA
In many cases, the only way to find out if people are accessing data they shouldn’t be is to audit user activity. This includes security at the physical server, SQL Server, database, and object level. You want to ensure that all unused accounts are deleted and that users are not accessing data they shouldn’t.
Maintain Good Documentation
Importance: Critical
Difficulty: Easy
Includes:
-
Setup documentation
-
Disaster recovery plan (test plan regularly)
-
Script library (multiple copies)
-
Run/Procedure book (keep maintained)
As much as everyone hates to write and maintain documentation, it is critical for SQL Server high availability. If case of any problems, documentation will speed up the recovery process and reduce errors. It will also help others to resolve issues if you (the DBA) are not around.
Good Change Management
Importance: Critical
Difficulty: Easy
Used to:
-
Increase human error HA
Includes:
-
Change management software, versioning
-
Well-documented change management process
-
Document all changes
-
Make only one change at a time
-
Test before rolling into production
-
Have a backout plan
Another boring, but critical area is to keep good track of what is happening on your SQL Servers. This helps to provide an audit trail of what has happened to your SQL Servers, often helping you in identifying recently introduced problems. In addition to documenting changes, be sure you test them before you put them into production, and also come up with a backout plan should the change fail.