While working with SQL Server databases, you often encounter scenarios where you need a read-only, standby copy of the database, you must move or migrate your database from one server to another with reduced downtime, or you need to configure a disaster recovery solution. Microsoft SQL Server provides you with features for these requirements, such as SQL Server AlwaysOn availability groups, database mirroring, backup and restore, and log shipping.
In this blog, we discuss one of the simplest features, log shipping, which you can use not only for a disaster recovery solution for a SQL Server database but also to migrate your SQL Server databases with reduced downtime. In our previous blogs, we discussed deploying Microsoft SQL Server on Oracle Cloud Infrastructure (OCI) and configuring SQL Server Always On availability groups on Oracle Cloud Infrastructure. Continuing this effort, we walk you through configuring log shipping between two SQL servers in two different OCI tenancies.
What is log shipping?
Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. A backup of this transaction log is known as transaction log backup. Log shipping uses a process of regular, automatic backups of the transaction log on the primary database server and restoration onto one or more secondary database servers using SQL Server Agent jobs.
According to Microsoft, log shipping gives you the following benefits:
-
Provides a disaster recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
-
Supports limited, read-only access to secondary databases during the interval between restore jobs.
-
Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
Architecture
Before going further, let’s explain some terminology.
A tenancy is a secure and isolated partition of OCI to create, organize, and administer your cloud resources. These resources include Compute instances, networks, storage, databases, identity, analytics and more. A tenancy can also be called an account.
A virtual cloud network (VCN) is a virtual, private network that you set up in an OCI region. It closely resembles a traditional network with firewall rules and communication gateways. A VCN covers one or more CIDR blocks (IPv4 and IPv6, if enabled). You can compare it with an Azure Virtual Network (VNet) or Amazon Web Service (AWS) Virtual Private Cloud (VPC).
A dynamic routing gateway (DRG) is an optional virtual router that you can add to your VCN. It provides a path for private network traffic between your VCN and an on-premises network. You can use it with other networking components and a router in your on-premises network to establish a connection by way of site-to-site VPN or OCI FastConnect. It can also provide a path for private network traffic between your VCN and another VCN in a different region.
A remote peering connection (RPC) is a component that you can add to a DRG. It lets you peer one VCN with another VCN in a different region.
In this scenario, we’re working with two OCI tenancies and VCNs. So, we need to configure VCN peering so that the two SQL servers in two different OCI tenancies can talk to each other. The configuration of log shipping between SQL servers in the same OCI tenancy and VCN is simpler and doesn’t need VCN peering.
Our architecture uses the following the components:
OCI tenancy A:
-
VCN-A
-
DRG-A
-
SQLServer-A
OCI tenancy B:
-
VCN-B
-
DRG-B
-
SQLServer-B
Getting started
We’re following the steps documented in Using SQL Server Management Studio for configuring log shipping. Before we start, we must configure VCN peering between the two OCI tenancies.
As you configure log shipping, you must create a shared directory to make the transaction log backups available to the secondary server. In this shared directory, the transaction log backups are generated. For example, if you back up your transaction logs to the directory c:\data\tlogs\, you can create the \\primaryserver\tlogs share of that directory.
Also ensure that the SQL Server version of secondary database is same or higher than the primary database. The primary database must use the full or bulk-logged recovery model. Switching the database to simple recovery causes log shipping to stop functioning.
Configuring security
The SQL Server service accounts and SQL Server Agent service accounts of both the SQL Servers need read/write permissions on the shared directory. The log shipping stored procedures require membership in the sysadmin fixed server role.
VCN peering between tenancies
When configuring VCN peering between two tenancies, we must ensure that appropriate Identity and Access Management (IAM) permissions are applied to the VCNs in the different tenancies. We must also configure a DRG in both tenancies and attach the VCNs to these DRGs. When you have created the DRGs using the steps provided in the article, follow the steps in Peering VCNs in different regions through a DRG.
Before task C, we must configure the IAM policies. In Tenancy A, you need a group (requestor-group) that has permissions to manage DRGs and VCNs in the tenancy. Tenancy A acts as the requestor and tenancy B acts as the acceptor. Run the following IAM policies through the requestor:
-
Define tenancy Acceptor as <acceptor-tenancy-ocid>
-
Allow group <requestor-group-name> to manage remote-peering-from in compartment <<requestor-compartment-name>>
-
Endorse group <requestor-group-name> to manage remote-peering-to in tenancy Acceptor
Run the following IAM policies by the acceptor:
-
Define tenancy Requestor as <requestor-tenancy-ocid>
-
Define group <requestor-group-name> as <requestor-group-ocid>
-
Admit group <requestor-group-name> of tenancy Requestor to manage remote-peering-to in compartment <acceptor-compartment-name>
Configuring log shipping between two tenancies
Use the steps in Using SQL Server Management Studio.
In step 5, provide the UNC path of the shared directory that you created. SQL Server and SQL Agent Service accounts from both the tenancies need read/write permissions in this directory.
If you’re using a network path to the backup folder, you don’t need to follow step 6. If you want to use a local path for backup, provide the local path of the backup folder.
In step 9, you can enable backup compression so that the backup file is compressed. Enabling backup compression can help you save disk space.
In step 17, you can select No Recovery or Standby mode. Standby mode supports limited, read-only operations on the secondary server. If you choose Standby mode, ensure you follow step 18. If you choose Standby mode, read operation doesn’t work when restoration is in progress.
Server monitoring is optional so you can skip the step.
When you have completed the configuration, a full database backup occurs, which creates and restores on the secondary server and then every 15 minutes afterwards.
You have now successfully configured log shipping between two SQL servers. You can use log shipping as a disaster recovery solution to have a standby read-Only copy to offload read request to the standby database or to migrate the databases from one server to another with reduced downtime.
If you want to failover to your disaster recovery server or complete the migration, during the cutover period, you can stop the application connectivity and follow the steps in Fail Over to a Log Shipping Secondary (SQL Server) to failover the primary database server to your secondary database server. When the failover is complete, you must redirect your applications to connect to the new primary SQL server.
Conclusion
In this blog post, you learned how to configure SQL Server log shipping between SQL Server databases from one OCI tenancy to SQL Server databases in another OCI tenancy. If you want to configure log shipping between SQL Server databases in same tenancy, you can use the same steps and skip the VCN peering step. Using SQL Server log shipping, you can not only migrate the database with less downtime, but also create a disaster recovery solution for a SQL Server database or you can have a read-only copy of the database in another tenancy.
To learn more about SQL Server on OCI, visit Microsoft SQL Server on OCI. As an alternative to log shipping, you can also use SQL Server AlwaysOn for a disaster recovery solution or read scalability. See SQL Server Always On availability groups on Oracle Cloud Infrastructure. Feel free to explore OCI with a Free Trial.
Stay tuned for our next blog on how to configure SQL Server database mirroring on Oracle Cloud Infrastructure.
For more information on VCN peering and IAM policies, see the following resources: