Application downtime is a contentious topic especially when it affects business critical applications and where organisations operate 24/7. Few organisations can afford downtime as it often translates into lost revenue that is difficult to recuperate. For many enterprises, service level agreements (SLA’s) are introduced to address these concerns and depending on the services at hand, guaranteeing uptime values of typically between 99.9% and 99.999%. Interpreted, within a given year services cannot be offline for periods greater than 8.7 hours or 8 minutes respectively, potentially resulting in the enforcement of penalty clauses if not the case.
For database servers, this is where the use of high availability implementations have their rightful place. Applications harnessing Microsoft SQL Server can take advantage of the various features on offer that facilitate high availability and disaster recovery configurations. Each have a set purpose and are with restrictions.
Based on past experience, many IT professionals are typically aware of just a handful of these and most often attempt to harness an inappropriate feature for their purpose. In order to comprehend what SQL Server can offer in terms of database redundancy, below is a brief overview of the options available as well as how they should be used:
- Log Shipping
Log Shipping has been available since SQL Server 2005 and is a basic level disaster recovery technology. It comprises of an automated backup/restore process allowing you to create another copy of your database for failover purposes.
The process involves copying of a database backup and subsequent transaction log backups from a primary (source) server and restoring the database along with the transaction log backups to one or more secondary (standby) servers. The target database remains in a standby (no recovery) mode, allowing subsequent transaction logs to be backed up on the primary, copied to the secondary servers and then re-applied accordingly. Any failover to the secondary database is a manual process requiring user intervention.
- Database Mirroring
Database mirroring is both a disaster recovery and high availability technique that’s been available since SQL Server 2005 SP1, encompassing two SQL Server instances either on the same or different server.
One SQL Server instance acts as a primary instance (principal), whilst the secondary is a mirrored instance (mirror). Transaction log records transfer data directly from one server to another, with failover to the standby server being fast. An optional 3rd SQL Server instance can be configured to act as a witness and facilitate automatic failover. With database mirroring, the mirrored database remains inaccessible until such time of failover.
Applications need to be coded to automatically redirect to the standby server and database in the event of a failover. With the introduction of AlwaysOn Availability Groups in SQL Server 2012, database mirroring has been deprecated, and is strongly recommended not to be used for any new applications/implementations.
- Replication
Database replication is an automatic means of copying data from a source database (publisher), via a centralised database (distributor) to one or more target databases (subscribers), ensuring these remain synchronised. Distributors can reside on the same or separate SQL server instance as that of the publisher.
Replication is typically used in distributed systems such as replicating data from server to server or between server and client, thereby supporting scalability as well as availability. Three types of replication are supported, namely:
- Snapshot – data within one database is copied to another, either on the same or different server as once-off replicas.
- Transactional – Users receive full initial copies of the source database and periodic updates as the data changes.
- Merge – data from two or more databases are combined into a single database.
All replication types rely on a number of standalone programs, called agents, that ensure data modifications, additions, and deletions performed at any given location are automatically reflected in the data stored at all the other locations, thereby ensuring data consistency.
- Clustering
Clustering is a high-availability feature designed to increase the uptime of SQL Server instances, with a cluster comprising of two or more servers (nodes), each ideally identical and setup to operate in either an active/passive or active/active mode.
For an active/passive cluster, one server is identified as the active node on which the SQL server instance runs, servicing workload requests, whilst the other is a passive node on which SQL Server is installed but not operational. Should a failure occur on the active node, the passive node switches over to becoming the active node and begins servicing workload requests, be it with some but minimal downtime.
Shared storage is a prerequisite and must be accessible via each node in the cluster, with SAN’s typically being used here. The downside is that SAN’s can be quite costly to commission, adding to the overall cost of your SQL cluster.
- AlwaysOn Availability Groups
Introduced within SQL Server 2012, AlwaysOn Availability Groups is a high-availability and disaster-recovery solution, designed to maximise the uptime of a set of user databases. This configuration requires a set of clustered SQL servers but without the need for shared storage such as a SAN.
One or more availability groups can be created, the latter specifying a set of user databases to be configured; the SQL server instances (availability replicas) on which the database copies will reside, and lastly the address of the availability group on which will workload requests will be serviced (known as the availability listener).
Each availability group supports a set of read-write primary databases, with up to four or eight corresponding secondary databases, depending on the version of SQL Server being used. Optionally, secondary databases can be made available for read-only access and/or some backup operations, thereby alleviating contention on the primary database. Should a failover occur, the switchover is automatic, with little if not no data loss.
As discussed, SQL Server offers various features that can be harnessed today for your business critical applications. It is all about using the feature that is best suited to your environment and requirements. The question you need to ask yourself is simply “Do I have sufficient measures in place to mitigate the risks associated with downtime for that of my business critical applications?
