SQL High Availability Solutions
SQL high availability solutions are designed to offer maximum uptime for windows server environments, which is great for any business that does not want to experience disruption and downtime. There are four SQL Server High Availability options: Log Shipping; Mirroring; Clustering and AlwaysOn Availability Group. Each offers substantial benefits and helps ensure high availability.
Log Shipping offers a low cost opportunity for database high availability.
The process involves recorded changes to a database (logs) being queued on a primary server. These logs are then transmitted at specified intervals and applied to the secondary server. This ensures that a 'warm' database is maintained and can be made available quickly should the primary database suddenly become unavailable.
With Log Shipping the failover is manual making it an ideal low cost option for situations where 100% uptime is not absolutely critical or where connection speeds between the primary and secondary servers are not sufficient for other options.
The key limitation however with Log Shipping is that due to logs being queued for backup there is a window where data loss is possible. Where this is not acceptable we would advise you consider the other High Availability options.
Protect your business from a hardware failure with SQL clustering. Removing any single point of failure, SQL clustering offers high availability with almost instant recovery times. We offer both SQL Server 2008 and Server 2012. Discuss the differences with one of our solution architects to determine what would be right for your business.
Benefits of Clustering
- Almost instant recovery in the event of a hardware failure
- Maintenance can be performed on the servers with minimal downtime
- Data is written in one location, therefore integrity is guaranteed
- Only need SQL licenses on the active node
- Server state is constantly monitored to ensure automated failover
- Same instance name, so there are no code changes
- Easy to migrate to clustered environment
SQL clustering works by grouping two or more servers configured in an active or passive mode utilising SAN storage. The active server performs all of the services and functions, whilst the passive server is ready to take over services if needed.
In the event of the active server going offline, the passive server will take over from the active server. The failed server will then be restored and reconfigured and resume service when back in the cluster.
Mirroring is a feature of SQL Server 2008 and SQL Server 2012 that increases the availability of a SQL Server database. It ensures continuous data availability and helps avoid data corruption or loss following any period of downtime. Mirroring works by ensuring that at least one copy of a database is accessible during system upgrades or restores.
Two copies of a single database are kept on different computers (servers). The principal server provides the database to the end users and the mirror server acts as a standby server that takes over in the event of a failure.
Benefits of SQL Mirroring
Increased data protection
By creating a mirror copy of your data, mirroring delivers complete or almost complete redundancy of data.
Increased database availability
In the event of an issue, inbuilt failover quickly brings the standby copy of your database online without data loss.
Improved availability of production database
To minimise downtime you can sequentially upgrade the instances of SQL Server that are participating in your mirroring session. This reduces interruption to service during upgrades.
SQL mirroring requires multiple servers to run in the correct mirror configuration. You will run a primary server, which will take care of processes and a secondary server acting as a backup server.
The details of the primary server are copied every few minutes and sent to the backup server, essentially mirroring data between the two, hence the name SQL mirroring. In the event of the primary server failing, the secondary (backup) server takes over as the primary with the up-to-date copy of the data.
There are two different methods of SQL mirroring that are used to achieve high availability:
- High-safety mode; known as synchronous mirroring
- High-performance mode; known as asynchronous mirroring
If 100% accuracy is required, the mirror server instance must always stay current; in other words, the system must immediately copy every change in the principal's content to the mirror and vice-versa. In this mode, known as synchronous operation, the mirror is called a hot standby.
Database mirroring can also work when the content is not fully synchronized but some data loss may occur if one of the server instances fails or becomes inaccessible. In this mode, called asynchronous operation, the mirror is called a warm standby.
In asynchronous mode, the principal server sends the transaction to the mirrored server and does not wait for the transaction on the mirrored server to commit. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
Our team of hosting specialists are here to advise you on the SQL mirroring methods and will build a highly resilient solution to suit your specific requirements.
AlwaysOn Availability Group
SQL Server 2012 has delivered significant advances in mirroring, mainly through AlwaysOn Availability Group. This is a concept borrowed from Exchange Server 2010 and is a significant advance in high availability technology. AlwaysOn Availability Group delivers all the functionality that Database Mirroring does on top of a rich set of options that improve database availability and enable better resource utilisation.
AlwaysOn delivers enhanced capabilities that provide availability for specific application database(s) or a group of databases called Database Availability Group. This provides the flexibility to support various high availability configurations.
Key New Technical Capabilities
AlwaysOn Availability Group: a new capability that helps protect application databases from both planned and unplanned downtime. AlwaysOn Failover Cluster Instance: provides protection for the entire instance and is an enhancement to the existing SQL Server Failover Cluster Instance.
NB: Both these capabilities rely on Windows Server Failover Clustering infrastructure which provides a robust and reliable high availability platform. Our team of hosting specialists are here to advise you on SQL mirroring methods and the added functionality now available in SQL 2012.
Your solution will be backed by a team of UK-based technical engineers available 24/7/365, plus a dedicated account manager.
UK Data Centres
We've invested £22m in our UK-based data centres to ensure the highest levels of service, security and performance.