Professional SQL Server High Availability and Disaster Recovery: Implement tried-and-true high availability and disaster recovery solutions with SQL Server

High availability refers to providing an agreed level of system or application availability by minimizing the downtime caused by infrastructure or hardware failure.

When the hardware fails, there's not much you can do other than switch the application to a different computer so as to make sure that the hardware failure doesn't cause application downtime.

Business continuity and disaster recovery, though used interchangeably, are different concepts.

Disaster recovery refers to re-establishing the application or system connectivity or availability on an alternate site, commonly known as a DR site, after an outage in the primary site. The outage can be caused by a site-wide (data center) wide infrastructure outage or a natural disaster.

Business continuity is a strategy that ensures that a business is up and running with minimal or zero downtime or service outage. For example, as a part of business continuity, an organization may plan to decouple an application into small individual standalone applications and deploy each small application across multiple regions. Let's say that a financial application is deployed on region one and the sales application is deployed on region two. Therefore, if a disaster hits region one, the finance application will go down, and the company will follow the disaster recovery plan to recover the financial application. However, the sales application in region two will be up and running.

High availability and disaster recovery are not only required during hardware failures; you also need them in the following scenarios:

Let's look at an example of how high availability and disaster recovery work to provide business continuity in the case of outages.

Consider the following diagram:

Figure 1.1: A simple HA and DR example

The preceding diagram shows a common HA and DR implementation with the following configuration:

Let's say there is a motherboard failure on node 1 and it crashes. This causes node 2 to be active automatically and it starts serving user transactions. This is shown in the following diagram:

Figure 1.2: A simple HA and DR example – Node 1 crashes

This is an example of high availability where the system automatically switches to the secondary node within the same data center or a different data center in the same region (Virginia here).

The system can fall back to the primary node once it's fixed and up and running.

Now, let's say that while the primary server, node 1, was being recovered, there was a region-wide failure that caused the secondary server, node 2, to go down. At this point, the region is down; therefore, the system will fail over to the DR server, node 3, and it'll start serving user transactions, as shown in the following diagram:

Figure 1.3: A simple HA and DR example – Nodes 1 and 2 crash

This is an example of disaster recovery. Once the primary and secondary servers are up and running, the system can fall back to the primary server.

Availability or uptime is defined as the percentage that a system or an application should be available for in a given year. Availability is expressed as Number of Nines.

For example, a 90%, one nine, availability means that a system can tolerate a downtime of 36.5 hours in a year, and a 99.999%, five nines, availability means that a system can tolerate a downtime of 5.26 minutes per year.

The following table, taken from https://en.wikipedia.org/wiki/High_availability#"Nines", describes the availability percentages and the downtime for each percentage:

Figure 1.4: Availability table

In the preceding table, you can see that as the Number of Nines increases, the downtime decreases. The business decides the availability, the Number of Nines, required for the system. This plays a vital role in selecting the type of HA and DR solution required for any given system. The higher the Number of Nines, the more rigorous or robust the required solution.

Recovery time objective, or RTO, is essentially the downtime a business can tolerate without any substantial loss. For example, an RTO of one hour means that an application shouldn't be down for more than one hour. A downtime of more than an hour would result in critical financial, reputation, or data loss.

The choice of HA and DR solution depends on the RTO. If an application has a four-hour RTO, you can recover the database using backups (if backups are being done every two hours or so), and you may not need any HA and DR solution. However, if the RTO is 15 minutes, then backups won't work, and an HA and DR solution will be needed.

Recovery point objective, or RPO, defines how much data loss a business can tolerate during an outage. For example, an RPO of two hours would mean a data loss of two hours won't cost anything to the business; however, if it goes beyond that, it would have significant financial or reputation impacts.

Essentially, this is the time difference between the last transaction committed before downtime and the first transaction committed after recovery.

The choice of HA and DR solution also depends on the RPO. If an application has 24 hours of RPO, daily full backups are good enough; however, for a business with four hours of RPO, daily full backups are not enough.

To differentiate between RTO and RPO, let's consider a scenario. A company has an RTO of one hour and an RPO of four hours. There's no HA and DR solution, and backups are being done every 12 hours.

In the case of an outage, the company was able to restore the database from the last full backup in one hour, which is within the given RTO of one hour; however, they suffered a data loss as the backups are being done every 12 hours and the RPO is of four hours.

Commonly known as FCI, this requires SQL Server to be installed as a cluster service on top of the Windows failover cluster.

The SQL Server service is managed by a Windows cluster resource. The example we took to explain HA and DR earlier in this lesson was largely based on this.

This book covers creating a Windows Server Failover Cluster; however, it doesn't cover troubleshooting a failover cluster.

Log shipping is one of the oldest SQL Server solutions, and is mostly used for DR and SQL Server migration. It takes transaction log backups from the primary server and restores them on one or more secondary servers. It is implemented using SQL Agent jobs.

Log shipping is covered in more detail later in this book.

Introduced in SQL Server 2012, AlwaysOn AG is one of the newest and most impressive HA and DR features in SQL Server. When launched, it worked on top of Windows Server Failover Cluster; however, this restriction has been removed in Windows Server 2016 and SQL Server 2016.

AlwaysOn Availability Groups allows you to manually or automatically fail over one or more databases to a secondary instance if the primary instance is unavailable. This book talks about AlwaysOn in detail in a later lesson.

Replication is one of the oldest SQL Server features that replicates data from one database (commonly known as a publisher) to one or more databases (known as subscribers) in the same or different SQL Server instances.

Replication is commonly used for load balancing read and write workloads. The writes are done on the publisher and reads are done on the subscriber. However, as it replicates data, it is also used as an HA and DR solution.

The solutions described here can be used together as well. Using one feature doesn't restrict you from using others. Consider a scenario where a company has a transactional database and logging database. The transactional database is of more importance and has stringent RTO and RPO compared to the logging database. A company can choose AlwaysOn for the transactional database and log shipping/replication for the logging database.

In this section, you have learned about high availability and disaster recovery concepts and terminology.

The next section talks about replication and how it can be used as an HA and DR solution.

Replication is one of the oldest features in SQL Server. It allows you to sync or replicate data from one or more databases on the same or different SQL Server instances. In this section, we will cover replication concepts and terminology. We will also talk about the different types of replications available in SQL Server. We will then cover snapshot replication in detail.

Replication agents are the standalone executables that are responsible for replicating the data from a publisher to a subscriber. In this section, we will cover replication agents in brief, and we will look at them in detail later in this book.

Snapshot Agent

The snapshot agent creates the selected articles and copies all of the data from the publisher to the subscriber whenever executed. An important thing to note here is that the subsequent execution of the agent doesn't copy the differential data; rather, each run clears out the existing schema and data at the subscriber and copies the schema and data from the publisher.

The snapshot agent is run at the distributor and is used via snapshot replication. It is also used in transactional and merge replication to initialize the subscriber with the initial data.

Log Reader Agent

The log reader agent scans the transaction log for the transactions marked for replication and inserts them into the distribution database. It is used only in transactional replication and provides continuous replication from the publisher to the subscriber.

Each publication has its own log reader agent; that is, if there are two different databases with transactional replication, there will be two log reader agents, one for each database.

The log reader agent runs at the distributor.

Distribution Agent

As the name suggests, the distribution agent distributes (applies) the data that's inserted into the distribution database by the log reader agent to the subscribers.

The distribution agent runs at the subscriber if it's a pull subscription and at the distributor if it's a push subscription.

Merge Agent

Used in merge replication, the merge agent applies the initial snapshot to the subscriber (generated by the snapshot agent) and then replicates the changes from the publisher to the subscriber and from the subscriber to the publisher as and when they occur, or when the subscriber is online and available for replication.

There is one merge agent for one merge subscription.

Transactional replication, as the name suggests, replicates the transactions as and when they are committed at the publisher to the subscribers.

It's one of the most commonly used replications to load balance read-write workloads. The writes are done at the publisher and the reads (or reporting) are done at the subscriber, thereby eliminating read-write blocking. Moreover, the subscriber database can be better indexed to speed up the reads and the publisher database can be optimized for Data Manipulation Language (DML) operations.

The log reader and distribution agent carry out the transactional replication, as stated earlier. The agents are implemented as SQL agent jobs, that is, there's a SQL agent job for a log reader agent and a SQL agent job for the distribution agent.

There are two other transactional replications that allow changes to flow from subscriber to publisher: transactional replication with updatable subscription (bidirectional transactional replication) and peer-to-peer transactional replication.

Transaction replication is discussed in detail in Lesson 2, Transactional Replication.

Merge replication, as the name suggests, replicates changes from publishers to subscribers and from subscribers to publishers. This sometimes results in conflict in cases where the same row is updated with different values from the publisher and subscriber.

Merge replication has a built-in mechanism to detect and resolve conflicts; however, in some cases, it may get difficult to troubleshoot conflicts. This makes it the most complex replication type available in SQL Server.

Merge replication uses the merge agent to initialize subscribers and merge changes. Unlike transaction replication, where the snapshot agent is used to initialize subscribers, in merge replication, the snapshot agent only creates the snapshot. The merge agent applies that snapshot and starts replicating the changes thereafter.

Merge replication isn't covered in this book as it's not used as an HA and DR solution anymore.

Snapshot replication generates a snapshot of the articles to be replicated and applies it to the subscriber. The snapshot replication can be run on demand or as per schedule. It's the simplest form of replication and is also used to initialize transactional and merge replication.

Consider the following diagram:

Figure 1.6: Snapshot replication example

The preceding diagram demonstrates how snapshot replication works. The finance database is replicated from publisher to subscriber. Here's how it works:

In this exercise, we will create a publication for our snapshot replication:

Now, let's look at the objects or components that are created as part of creating the publication.

In step 4 of the previous exercise, we specified that the publisher will act as its own distributor. This results in the creation of a distribution database and snapshot agent job on the publisher itself. We can also use a different instance for distribution, however, let's keep it simple for this demonstration.

In SQL Server Management Studio, connect to the Object Explorer, expand Databases, and then expand System Database. Observe that a new system database distribution has been added as a result of the previous exercise:

Figure 1.25: System tables

The distribution database has system tables that keep track of replication metadata. Let's explore the metadata tables that are related to snapshot replication.

Open a new query window in SSMS and execute the following queries one by one:

Navigate to the snapshot folder (WideWorldImporters-Snapshot) that was provided in step 5 of the previous exercise. This contains the snapshot files for the articles that were selected for replication.

Observe that this folder acts as a base and has a subfolder named WIN2012R2$SQL2016_WIDEWORLDIMPORTERS_WWI-SNAPSHOT. The subfolder is named by concatenating the SQL Server instance name and the publication name to the base snapshot folder. This is done to separate out snapshots for different publications; the base snapshot folder can therefore have snapshots from multiple publications.

Every time a snapshot agent is run, a new folder is created inside WIN2012R2$SQL2016_WIDEWORLDIMPORTERS_WWI-SNAPSHOT. This is named by the timestamp when the snapshot agent ran and contains the schema and data files. This is shown in the following screenshot:

Figure 1.32: Snapshot folder

Observe that the snapshot folder has .sch, .idx, .pre, and .bcp files. Let's see what these files are used for.

.pre Files

The .pre files are the pre-snapshot scripts that drop the object at the subscriber if it exists. This is because every time a snapshot runs, it initializes the tables from scratch. Therefore, the objects are first dropped at the subscriber. Let's look at an example:

SET QUOTED_IDENTIFIER ON
go
if object_id('sys.sp_MSrestoresavedforeignkeys') < 0 exec sys.sp_MSdropfkreferencingarticle @destination_object_name = N'CustomerTransactions', @destination_owner_name = N'Sales'
go
drop Table [Sales].[CustomerTransactions]
go

The preceding query is from the customertransactions.pre file. It first drops foreign keys, if any, for the CustomerTransactions table and then drops the CustomerTransactions table.

.sch Files

The .sch files contain the creation script for the articles to be replicated. Let's look at an example:

drop Table [Sales].[CustomerTransactions]
go
SET ANSI_PADDING ON
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sales].[CustomerTransactions](
  [CustomerTransactionID] [int] NOT NULL,
  [CustomerID] [int] NOT NULL,
  [TransactionTypeID] [int] NOT NULL,
  [InvoiceID] [int] NULL,
  [PaymentMethodID] [int] NULL,
  [TransactionDate] [date] NOT NULL,
  [AmountExcludingTax] [decimal](18, 2) NOT NULL,
  [TaxAmount] [decimal](18, 2) NOT NULL,
  [TransactionAmount] [decimal](18, 2) NOT NULL,
  [OutstandingBalance] [decimal](18, 2) NOT NULL,
  [FinalizationDate] [date] NULL,
  [IsFinalized]  AS (case when [FinalizationDate] IS NULL then CONVERT([bit],(0)) else CONVERT([bit],(1)) end) PERSISTED,
  [LastEditedBy] [int] NOT NULL,
  [LastEditedWhen] [datetime2](7) NOT NULL
)
GO

This query is from the customertransactions.sch file.

.idx Files

The .idx files contain the indexes and constraints on the tables to be created at the subscriber. Let's look at an example:

CREATE CLUSTERED INDEX [CX_Sales_CustomerTransactions] ON [Sales].[CustomerTransactions]([TransactionDate])
go
ALTER TABLE [Sales].[CustomerTransactions] ADD CONSTRAINT [PK_Sales_CustomerTransactions] PRIMARY KEY NONCLUSTERED ([CustomerTransactionID])
Go

This query is from the customertransactions.idx file.

.bcp Files

The .bcp files contain the table data to be inserted into the tables at the subscriber. There can be multiple .bcp files, depending on the table size.

The snapshot agent job is a SQL Server agent job that executes snapshot.exe to generate the snapshot (these are the files we discussed earlier: .sch, .pre, .idx, and .bcp).

The snapshot agent job is created as part of creating the snapshot publication. You can locate the job in Object Explorer under the SQL Server Agent | Jobs node:

Figure 1.33: Snapshot agent job location

Double-click on the job to open it.

In the Job Properties window, click on Steps on the left-hand side in the Select a page pane:

Figure 1.34: The Job Properties window

This job has three steps.

This completes the objects and components that were created as part of creating a publication.

We'll now look into the snapshot.exe process in detail.

The snapshot.exe process is installed with SQL Server (if you choose to install replication when installing SQL Server) and is stored in the C:\Program Files\Microsoft SQL Server\130\COM folder (for SQL Server 2016 installation).

The snapshot.exe process accepts quite a few parameters that are used to generate snapshots and to tune snapshot generation.

The snapshot.exe process can be run independently from the command line by passing the relevant parameters.

Execute the following commands in a command-line window to run snapshot.exe with the default parameters, as specified in step 2 of the snapshot agent job that we discussed previously:

REM -- Change the variable values as per your environment
SET Publisher=WIN2012R2\SQL2016
SET PublisherDB=WideWorldImporters
SET Publication=WWI-Snapshot
"C:\Program Files\Microsoft SQL Server\130\COM\SNAPSHOT.EXE" -Publisher %Publisher% -PublisherDB %PublisherDB% -Distributor %Publisher% -Publication %Publication% -DistributorSecurityMode 1

You will have to change the Publisher variable with the SQL Server instance you created in the snapshot publication. You may have to change the PublisherDB and Publication parameters if you chose a different database and publication name when creating the snapshot publication in Exercise 1: Creating a Publication.

Once it runs successfully, it generates the following output:

Figure 1.38: Running snapshot.exe

The snapshot agent also logs the progress status in the distribution.dbo.MSsnapshot_history table. You can query the table to verify these steps.

An important thing to note here is that the snapshot agent locks the published tables (schema lock and exclusive locks) while generating the snapshot. This is to make sure that no changes are made to the schema when a snapshot is being generated. Any changes to the schema during snapshot generation will leave the snapshot in an inconsistent state and it will error out when applying the snapshot to the subscriber.

For example, when the snapshot agent generated a .sch file, a table had two columns; however, when it exports the data for that table, another transaction modifies the table by adding one more column to it. Therefore, the table creation script, .sch, has two columns and .bcp, which is the data file, has three columns. This will error out when applied to the subscriber.

This also means that for large databases, generating snapshots may result in blocking issues.

To review the locks being applied during snapshot generation, run the snapshot.exe process, as described earlier. Then, quickly switch to SQL Server Management Studio and execute the following query to get lock details:

select 
  resource_type,
  db_name(resource_database_id) As resource_database_name,
  resource_description,
  request_mode,
  request_type,
  request_status,
  request_session_id
from sys.dm_tran_locks

You should get an output similar to what is shown in the following screenshot:

Figure 1.39: Reviewing the applied locks

Observe that the IX, X, and Sch-S locks are applied on the WideWorldImporters database during snapshot generation.

This section explores existing publication properties and how to modify an existing publication so that you can add or remove articles, change agent security settings, and more.

To view publication properties, connect to Object Explorer, expand Replication, and then expand Local Publications.

Right-click on the snapshot (WWI-Snapshot) and select Properties from the context menu:

Figure 1.40: Selecting Properties from the context menu

This opens the Publication Properties window:

Figure 1.41: The Publication Properties | General window

The Publication Properties window has different options on the left-hand side (Select a page) pane to modify or change publication properties. We'll now look at these options in detail.

Articles

The Articles page allows you to add or remove an article to or from the publication. Once you add or remove one, you'll have to generate a snapshot for the changes to take effect:

Figure 1.42: The Articles window

Adding or removing an article is very simple. Uncheck the Show only checked articles in the list option to display all database objects.

Then, select or remove the objects as required and click on OK to apply the changes.

Filter Rows

The Filter Rows page allows you to add or remove row filters to the tables. This is similar to step 9 of Exercise 1: Creating a Publication:

Figure 1.43: The Filter Rows window

Snapshot

The Snapshot page allows you to do the following:

FTP Snapshot

The FTP Snapshot page allows subscribers to download snapshots using FTP. You'll have to specify the FTP folder as the snapshot folder:

Figure 1.45: The FTP Snapshot window

You'll have to set up the FTP by specifying the connection details so that the snapshot agent can then push and pull snapshot files to and from the specified FTP folder.

Subscription Options

The Subscription Options page lets you control subscriber-level settings:

Figure 1.46: The Subscription Options window

The important settings for snapshot replication are as follows:

Publication Access List

The Publication Access List (PAL) page is a list of logins that have permission to create and synchronize subscriptions. Any login that has access to the publication database (WideWorldImporter) and is defined in both the publisher and distributor can be added to PAL:

Figure 1.47: The Publication Access List window

Agent Security

In the Agent Security page, you can modify the agent security settings, as defined in step 11 of Exercise 1: Creating a Publication:

Figure 1.48: The Agent Security window

To change or modify security settings, click on the Security Settings button and change as required.

In this exercise, we'll create the subscription at the subscriber end. A subscriber is usually a separate SQL Server instance.

To create a new subscription for the publication, follow these steps:

Now, let's look at the objects that were created as part of creating the subscription.

Let's look at the changes that are made to the distribution tables after creating the subscription.

Open SSMS and connect to the distribution database on the publisher server. You can execute the following queries to find out how the subscription details are stored:

The distribution agent job that was created at the subscriber (pull subscription) runs the distribution.exe process to apply the snapshot that was created by the snapshot agent on the subscriber database.

To view the job, you can open SSMS and connect to the subscriber server using Object Explorer. Then, you can expand the SQL Server Agent node.

You should see a job similar to what is shown in the following screenshot:

Figure 1.66: The distribution agent job

You can double-click on the job to open its Job Properties window:

Figure 1.67: The Job Properties window

The Job Properties window lists out the general information about the job. You can select the Steps page from the Select a page pane on the left-hand side of the Job Properties window:

Figure 1.68: The Steps page

This job has only one step. You can double-click on the step name or select Edit at the bottom of the window to check the step details:

Figure 1.69: The Run agent window

Observe that the Run agent step calls the distributor.exe executable with a set of parameters.

The job wasn't run until now as we opted for Run on Demand when configuring the subscription. Close the Job Properties window.

Now, let's run the job and replicate the data from the publisher to the subscriber. To run the job manually, navigate to the job on the Object Explorer, as mentioned earlier:

distrib.exe is the executable that does the actual work of replicating the data from the publisher to the subscriber:

The changes are therefore replicated.

Let's look at a few of the best practices you should consider when working with snapshot replication.

Minimizing Logging at Subscriber

Snapshot replication uses bcp to bulk insert data from the publisher to the subscriber database. It's therefore advised to switch to a bulk-logged or simple recovery model to minimize logging and optimize bulk insert performance.

Minimizing Locking

As we discussed earlier, snapshot generation applies exclusive locks on tables until the snapshot is generated. This stops any other applications from accessing the tables, resulting in blocking. You can look at the following options to minimize blocking:

Replicating Only Required Articles

Understand the business requirements and replicate only what is required. Replicating all articles in a large database will take time and resources for snapshot generation.

Using Pull Subscription

Consider using pull subscription. In pull subscription, the distribution agent is on the subscriber and not on the distributor. This reduces workload on the distributor. Moreover, if the publisher is acting as its own distributor, its workload is reduced.

Compressing the Snapshot Folder

As discussed earlier, there is an option to compress snapshot files in the .cab format. This reduces the size of the snapshot files and speeds up network transfer. However, it takes time to compress the files by the snapshot agent and decompress by the distribution agent.

The following table discusses snapshot and distribution agent parameters that can be modified so as to optimize snapshot replication:

Figure 1.78: The Agent Parameters table

In this activity, we'll troubleshoot snapshot replication.

You have been asked to set up snapshot replication for the WideWorldImporters database. The subscriber database will be used to run daily reports. You configured the replication so that the initial snapshot is applied successfully. You schedule it to occur daily at 12:00 AM, as directed by the business. However, the next day you are informed that the data isn't synced.

In this activity, you'll find and fix the issue.

Setup Steps

To simulate the error, follow these steps:

Generating a New Snapshot

Follow these steps to generate a new snapshot:

Applying the Snapshot to the Subscriber

You'll now apply the generated snapshot to the subscriber. To apply the snapshot, follow these steps:

An easy solution to this problem is to drop the view at the subscriber database. However, the business tells you that the view can't be dropped as it's being used by the daily report.

Another solution to this problem is to modify the publication properties to include pre- and post-snapshot scripts so that you can delete and create the view, respectively.