How to Replicate your Data to an Azure VM with SQL Standard

How to Replicate your Data to an Azure VM with SQL Standard

SQL Server Standard is undoubtedly one of the best selling editions due to its cost effectiveness and feature sets. Its bigger sibling, the SQL Server Enterprise Edition is more than 3 times the cost. In most cases, only large companies can justify its cost benefits. Since a lot of existing and potential customers look for SQL Standard functionalities, in this post I am going to talk about how to replicate your data to an azure vm with SQL Standard.

There are four main options for you to replicate your data to an Azure VM (Virtual Machine)

1. Always On Availability Groups (Enterprise Only) or Basic Availability Groups in SQL Server 2016
2. SQL Server Database Mirroring (Standard and Enterprise)
3. Log-Shipping (Standard & Enterprise)
4. Transaction Replication Publisher (Standard & Enterprise)

SQL Server Always On Availability Groups – Enterprise Features

• Support for multiple secondary Database Replica’s of up to 4. In SQL Server 2012 EE, can have 2 Synchronous and 2 Asynchronous replica’s.
• User can create multiple availability groups and multiple databases can be a part of a availability group. These availability groups can failover across the secondary replicas either locally or in a remote site.
• You can configure your backups to be run on any secondary replica’s of a database. Default backups on primary replica will still work.
• The T-Log backups on all replicas will form a single consistent log chain
• Supporting applications can do read intent queries on the secondary replicas.
• Can be implemented with no application changes required.
• Does not require a “shared storage“. This enables you to implement Availability Groups without purchasing expensive a SAN.
• 100% availability during all online operations.

IMPROVEMENTS ON HA & DR in SQL SERVER 2014 Enterprise

• Introduced additional secondary replicas of up to 8 + 1 (2 Synchronous, 6 Asynchronous and 1 Azure Replica)
• Multiple-DB failover either manual or automatic
• New and improved deployment & management wizards in Management Studio
• Availability Groups monitoring dashboard within management studio, new extended events & system center integration.
• Addition of Azure Replica Wizard for storing replicas on an Azure VM.

Introducing SQL Server 2016 Always On Basic Availability Groups – Standard Edition Features

SQL Server introduces the basic tier of availability groups with some restrictions. However, there are no restrictions to replicate your data into an Azure VM. This expands HA and DR planning for SQL Server Standard Editions. Here are the highlights:

• Only one Database per availability group
• You can have many availability groups still resource restrictions apply.
• You can only have one secondary replica (Locally, Remote Sites or on Azure)
• No backups or read queries are supported.
• Software Assurance is required for server mobility rights.

Standard Edition Specific Database Mirroring Features

• Provides a fault-tolerant database and hot Standby options to protect against database or server failures.
• Only synchronous full safety mode is supported. Asynchronous mode is not supported, thereby limited to local sites with low latency.
• Additional witness server is required for automatic failover.
• One of the best alternative for a real time replication solution. It is cost effective and has no requirements for specialized hardware.
• Completely Wizard based with straightforward setup and administration.
• Automatic or manual failover. (Some options are only restricted to Enterprise Edition of SQL)
• SQL Server 2008 does not require a database restart after manual failover
• Automatic, transparent client redirect support with .NET 2.0 and above.
• There are no shared components; two separate copies of data
• SQL Server 2008 onwards compresses the log stream from principal to mirror.

Considerations before Implementing:

• Fail-over occurs at the Database level and not at the Instance or Availability Group level.
• For high number of databases, Instance level failover is recommended and one needs to go for SQL Server Clustering. (Available in both Standard and Enterprise Editions)
• Standard Edition is limited to 2-Node Clustering only.
• If you want Databases to failover in specified groups, consider implementing Availability groups.
• In order for “Automatic Failover” and “Transparent Client Re-direct” to work, you need to have at-least .NET 2.0 application and above. The Application connection string needs to be modified to mention a “Failover Partner” which will point to the secondary server.
• The secondary server, where the mirrored database is available, cannot be accessed by applications or direct queries. Only Database snapshots are allowed.
• You can query the mirrored database by taking a “Database Snapshot” and query the snapshot with is a “Point in Time” data. It is an Enterprise Only feature.
• If you want additional “Read Only” secondary’s, consider using Log-shipping along with mirroring in a hybrid approach. This is also supported in Standard Edition of SQL.

When Implementing Database Mirroring for Azure

You have 2 different options when you are doing On-Premises to Azure with Database Mirroring

• Mirroring with Server Certificates (Partners do not need to be in the same Active Directory domain, and no VPN connection is required.)
• Secondary Database on an Azure VM and the Primary Database running on-premises in the same Active Directory domain for a cross-site disaster recovery solution (A VPN Connection is required). You need to have an additional domain controller on an Azure VM.

Log shipping Options

Unlike Database Mirroring, You can maintain multiple secondary servers and enable Read-only operations on them. Optionally, you can add a Monitor server to monitor the entire deployment setup and check for errors. It also records history and status of backup/restore jobs. You can set up  the monitoring server to raise alerts when jobs fail.

Considerations before Implementing Log-shipping

• Log-shipping is not a Real-Time solution. The secondary servers will have a lag of several minutes or more between backup-copy-restore jobs.
• There will be additional licensing cost if you want to use any of the Secondary servers for Read-Only or reporting queries.
• You can use Log-shipping along with Database Mirroring to achieve more than one secondary copy and read capabilities.
• For implementing Log-Shipping (& Mirroring), a “Full Recovery Model” is required, which can result in significant increase in Log File sizes. To counter this, backup maintenance jobs needs to be implemented with a proper “retention policy” for log files.
• There will be additional overhead for running the “Backup” “Copy” & “Restore” jobs, compared to Database mirroring which uses a lightweight endpoint log compression algorithm for its synchronization method.

When Log-shipping is Used with Azure:

One server running in an Azure VM and the other running on-premises for cross-site disaster recovery. Log shipping depends on Windows file sharing, so a VPN connection between the Azure virtual network and the on premise network.

Note: For successful disaster recovery of your databases, you should also install a replica domain controller at the disaster recovery site(Azure).

Transactional Replication

You have the options to either have an Azure SQL Database (PAAS) or a SQL Server on Azure VM.

Note: You can only use Azure SQL DB as a Subscriber to the Primary On-Premises Publisher Server.

The following versions & updates of SQL Server are required:

• SQL Server 2016 RTOM or CTP 3.0 and above.
• SQL Server 2014 with Service Pack 1 Cumulative Update CU3
• SQL Server 2014 RTM Cumulative Update CU10
• SQL Server 2012 Service Pack 2 Cumulative Update CU8

Additional Resources:

• Setting up Transactional Replication
• Azure DB Transactional Replication described on an episode of Data Exposed
• Migrating a SQL Server database to Azure SQL Database