How to Attain SQL Server High Availability at Minimal Cost

Regular Performance Tuning

Importance: Important
Difficulty: Easy

Used to:

  • Increase performance-related HA

Includes:

  • Regular performance monitoring

  • Regular performance tuning

  • Maintaining statistics and indexes

As mentioned earlier in this article, a poorly performing SQL Server can hinder high availability. Because of this, you must regularly monitor your SQL Server’s performance, ensuring that it is performing at its optimum capacity. If you identify hardware bottlenecks due to increasing load, then you need to plan to beef up the hardware as soon as you can.

Smart Job Management

Importance: Critical
Difficulty: Easy

Used to:

  • Increase performance-related HA

  • Increase data HA

Includes:

  • Run DBCC command to check data integrity

  • Rebuild indexes and update statistics regularly

  • Run SQL Server jobs at times that don’t interrupt user’s access to data

  • Check available disk space regularly

  • Etc.

SQL Server jobs are not only necessary for checking the integrity of your data, but they must also be scheduled in such a way as to reduce the impact on users. Not running necessary jobs, or running any job at the wrong time hurts high availability.

Check All Logs Daily

Importance: Critical
Difficulty: Easy

Used to:

  • Increase hardware HA

  • Increase OS HA

  • Increase SQL Server HA

  • Increase human error HA (security-related)

Includes:

  • OS Logs

  • SQL Server Logs

While most logs have a lot of unnecessary information, they often include useful nuggets of information that can help you prevent future problems. Because of this, you need to take a regular look at them. You can do this manually, or you can use a third-party tool to monitor the logs for you, only alerting you to critical problems.

Check Disk Space Daily

Importance: Critical
Difficulty: Easy

Used to:

  • Increase hardware HA

When SQL Server runs out of disk space, it can fail. Because of this, you need to daily check on how much empty space you have. As a rule of thumb, you want to have at least 20% free space. If you don’t, performance will be hurt and you further risk running out of space due to unexpected factors, such as a suddenly growing tempdb or log file. You can check this manually, or use third-party software.

Use Well-Designed Applications

Importance: Important
Difficulty: Varies

If You Write the Apps:

  • Write well-designed, well-tested code. Bad code can crash OS, SQL Server, or even hardware.

  • Applications should not be hard-coded for specific versions of SQL Server, server name, instance names, IP addresses, etc.

  • Put all user objects in user databases

  • Use fully-qualified names for all objects

  • Reuse database connections

If you don’t write the apps, you don’t have much control on how the affect SQL Server high availability. But if your company does write them, write them with high availability in mind.

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 |