Free SQL Help for Devs, DBAs, and the Curious

The integrity and availability of data are of critical importance in the Energy sector. As such, Disaster Recovery (DR) planning should be at the forefront of any robust IT strategy. One of the most reliable ways to ensure data protection and high availability is through Cross-Site SQL Server Failover. This strategy leverages multiple SQL Server installations across geographically dispersed locations, allowing for an automatic failover in the event of a disaster.

SQL Server 2012 and SQL Server 2014 come with AlwaysOn Availability Groups that allow for a failover environment for a set of user databases. These Availability Groups support a replica failover environment and offer both manual and automatic failover based on your configuration. The set-up process includes setting up a Windows Server Failover Clustering (WSFC) node, creating an Availability Group, and adding replicas to the group.

USE MASTER;
CREATE AVAILABILITY GROUP AG WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY);
ALTER AVAILABILITY GROUP AG ADD REPLICA ON SQLServer1 WITH (ENDPOINT_URL = 'TCP://SQLServer1:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

For a MySQL 5.6 environment, you can use replication for a similar effect. The master-slave architecture allows the slave server to take over if the master server fails. This approach, however, is not automatic and requires manual intervention to promote the slave server to master.

CHANGE MASTER TO MASTER_HOST='master_host_name',
MASTER_USER='replication_user_name',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;

In IBM's DB2 10.5, you can use the High Availability Disaster Recovery (HADR) feature for a cross-site failover. Like SQL Server's AlwaysOn Availability Groups, HADR allows for automatic failover. However, it only supports one standby database.

CONNECT TO sample;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
UNQUIESCE DB;
ACTIVATE DB sample;

In the cloud, the Azure SQL Database offers built-in high availability and disaster recovery. The Azure SQL Database automatically handles the replication of your data across multiple data centers. In the event of a data center failure, Azure SQL Database will automatically failover to the secondary data center with no data loss.

Disaster recovery planning is complex and requires careful thought and strategy. However, the use of technologies such as SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL can help to ensure that your data remains available and your operations continue even in the face of a disaster.