How to Attain SQL Server High Availability at Minimal Cost

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.

Continues…



Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |