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.