Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Maximizing Uptime with SQL Server 2016 Enterprise Edition Availability Features
SQL Server 2016 Enterprise edition has a host of lesser-known features that work together to increase database availability.
Michael Otey
June 23, 2016
3 Min Read
Most people are aware of the primary SQL Server availability features, but there are some lower-profile features you need to know about to ensure you are doing everything you can to optimize availability.
Some of the primary SQL Server availability features include AlwaysOn Failover Cluster Instances (FCI), AlwaysOn Availability Groups (AG) and Database Mirroring. These built-in technologies increase availability by providing different types of server and database protection. AlwaysOn FCI works at the server level; the clusters are designed to protect against unplanned downtime. If a server fails, then the SQL Server service is restarted on a remaining cluster node. AlwaysOn AG and Database Mirroring provide protection at the database level. If a server or database becomes unavailable, then SQL Server can automatically fail over and a secondary replica or database mirror will become the primary database. Here, the database is the focus of the protection not the entire SQL Server instance.
While these are the most well-known SQL Server availability technologies, SQL Server 2016 Enterprise edition has a host of lesser-known features that work together to increase database availability:
On-line Operations
Some of the most important availability features in the SQL Server 2016 Enterprise edition are online operations. These features enable you to make database and configuration changes without requiring that the server be offline or unavailable.
Hot add memory and CPU – The ability to hot add CPU and memory is particularly useful in virtual environments where the VM can dynamically add memory in response to changing workloads.
Online page and file restore – Online page and file restore enable you to perform file and page restores while the database remains online. For the simple or bulk recovery model, you can restore a file that is offline while the database is online. For the full recovery model, you can also restore pages while the database is online.
Online indexing – Online indexing enables you to create, alter and drop indexes while users have access to the underlying table, clustered index data and any associated nonclustered indexes during these index operations.
Online schema change – Another feature that can increase uptime is the ability to perform online schema changes where you can change a table’s schema and that table can be continuously available.
Data Availability Features
The SQL Server 2016 Enterprise edition also has a number of features that are designed to increase data availability.
Database snapshots – Database snapshots are read-only, static views of a SQL Server database. They are transactionally consistent with the source database as of the time of the snapshot's creation.
Fast recovery – Fast recovery is backup and restore feature that enables a database to come online as soon as the REDO phase (applying committed transactions) of the restore process completes. You do not have to wait for the UNDO (rolling back uncommitted transactions) phase of the restore process to run.
Mirrored backups – Database backups are your last line of defense, and being able to restore your backups in the event of a disaster is critical. However, in some cases the backup may be corrupt or unusable. Mirrored backup reduces this possibility by creating a copy of the backup file.
Maximizing Uptime
All of these features address different areas that can impact your database availability. When they are all combined together you can see how the SQL Server 2016 Enterprise Edition availability features can maximize uptime and availability for your mission critical applications.
HPE and Microsoft are the underwriters of this article.
About the Author
You May Also Like
