Professional SQL Server High Availability and Disaster Recovery E-Book

Ahmad Osama

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 351

Veröffentlichungsjahr: 2019

Beschreibung

Leverage powerful features of the SQL Server and watch your infrastructure transform into a high-performing, reliable network of systems.

Key Features

• Explore more than 20 real-world use cases to understand SQL Server features
  
  


• Get to grips with the SQL Server Always On technology
  
  


• Learn how to choose HA and DR topologies for your system

Book Description

Professional SQL Server High Availability and Disaster Recovery explains the high availability and disaster recovery technologies available in SQL Server: Replication, AlwaysOn, and Log Shipping. You'll learn what they are, how to monitor them, and how to troubleshoot any related problems. You will be introduced to the availability groups of AlwaysOn and learn how to configure them to extend your database mirroring. Through this book, you will be able to explore the technical implementations of high availability and disaster recovery technologies that you can use when you create a highly available infrastructure, including hybrid topologies. Note that this course does not cover SQL Server Failover Cluster Installation with shared storage.

By the end of the book, you'll be equipped with all that you need to know to develop robust and high performance infrastructure.

What you will learn

• Configure and troubleshoot Replication, AlwaysOn, and Log Shipping
  
  


• Study the best practices to implement HA and DR solutions
  
  


• Design HA and DR topologies for the SQL Server and study how to choose a topology for your environment
  
  


• Use T-SQL to configure replication, AlwaysOn, and log shipping
  
  


• Migrate from On-Premise SQL Server to Azure SQL Database
  
  


• Manage and maintain AlwaysOn availability groups for extended database mirroring

Who this book is for

Professional SQL Server High Availability and Disaster Recovery is for you if you are a database administrator or database developer who wants to improve the performance of your production environment. Prior experience of working with SQL Server will help you get the most out of this book.

Leseprobe

Professional SQL Server High Availability and Disaster Recovery

Implement tried-and-true high availability and disaster recovery solutions with SQL Server

Ahmad Osama

Professional SQL Server High Availability and Disaster Recovery

Copyright © 2019 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Author: Ahmad Osama

Reviewer: Mohit Gupta

Managing Editor: Taabish Khan

Acquisitions Editor: Aditya Date

Production Editor: Nitesh Thakur

Editorial Board: David Barnes, Ewan Buckingham, Shivangi Chatterji, Simon Cox, Manasa Kumar, Alex Mazonowicz, Douglas Paterson, Dominic Pereira, Shiny Poojary, Saman Siddiqui, Erol Staveley, Ankita Thakur, and Mohita Vyas.

First Published: January 2019

Production Reference: 1300119

ISBN: 978-1-78980-259-7

Published by Packt Publishing Ltd.

Livery Place, 35 Livery Street

Birmingham B3 2PB, UK

Table of Contents

Preface   i

Lesson 1: Getting Started with SQL Server HA and DR   1

Introduction   2

What is High Availability and Disaster Recovery?   2

High Availability   2

Disaster Recovery   2

HA and DR Terminologies   6

Availability   6

Recovery Time Objective   8

Recovery Point Objective   8

SQL Server HA and DR Solutions   9

Windows Server Failover Cluster Installation   9

Log Shipping   9

AlwaysOn Availability Groups   9

Replication   9

Hybrid Scenarios   10

Introduction to SQL Server Replication   10

Replication Agents   12

Types of Replication   13

Transactional Replication   14

Merge Replication   14

Snapshot Replication   14

Configuring Snapshot Replication Using SQL Server Management Studio   15

Exercise 1: Creating a Publication   16

Exercise 2: Exploring the Distribution Database   33

Database Snapshot   38

Snapshot Agent Job   41

Replication Snapshot (snapshot.exe)   44

Modifying an Existing Publication   48

Exercise 3: Creating a Subscription   56

Exercise 4: Exploring the Distribution Database (Metadata)   69

Distribution Agent Job   71

Exercise 5: Running the Job   76

Exercise 6: Distribution Agent Process (distrib.exe)   80

Optimizing Snapshot Replication   84

Snapshot Replication Best Practices   84

Modifying Agent Parameters   86

Activity 1: Troubleshooting Snapshot Replication   86

Summary   91

Lesson 2: Transactional Replication   93

Introduction   94

Understanding Transactional Replication   94

Configuring Transactional Replication   95

Exercise 7: Creating the Publication   95

Exercise 8: Creating the Subscription   105

Transactional Replication Agents   112

Exercise 9: Modifying the Existing Publication   120

Stopping Transactional Replication   126

Exercise 10: Removing Transactional Replication   131

Azure SQL Database as a Subscriber in Transaction Replication   132

Exercise 11: Configuring and Verifying Azure SQL Database as a Subscriber for an On-Premises Transactional Replication   132

Understanding Peer-To-Peer Transactional Replication   140

Configuring Peer-To-Peer Transactional Replication   142

Exercise 12: Creating the Publication, Adding Articles, Adding the Subscription, and Pushing the Subscription Agent to the Publication on Server 1   143

Exercise 13: Taking the Backup of the Publication Database on Server 1   150

Exercise 14: Restoring the Database on the Subscriber Server   151

Exercise 15: Creating the Publication, Adding Articles, Adding the Subscription, and Pushing the Subscription Agent to the Publication on Server 2   152

Exercise 16: Verifying the Replication   157

Modifying an Existing Publication   161

Exercise 17: Removing an Article   161

Exercise 18: Adding an Article   166

Activity 2: Configuring Transactional Replication   169

Summary   170

Lesson 3: Monitoring Transactional Replication   173

Introduction   174

The Replication Monitor   174

Exercise 19: Setting Up the Replication Monitor   175

Monitoring Replication with the Replication Monitor   181

Exercise 20: Configuring Replication Alerts   186

Activity 3: Configuring an Agent Failure Error   190

Real-World Transactional Replication Problems and Solutions   190

Configuration Issues   191

Exercise 21: Problem 1 – Unable to Connect to Distributor   191

Exercise 22: Problem 2 – Inactive Subscriptions   205

Exercise 23: Problem 3 - Missing Replication Stored Procedures   213

Data Issues   218

Exercise 24: Problem 4 – Row Not Found at the Subscriber   218

Performance Issues   232

Exercise 25: Problem 5 – Log Reader Agent Takes Time to Scan the Transaction Log   232

Conflicts in Peer-to-Peer Transactional Replication   241

Exercise 26: Problem 6 – Conflicts in P2P Transactional Replication   241

Activity 4: Troubleshooting Transactional Replication   247

Summary   248

Lesson 4: AlwaysOn Availability Groups   251

Introduction   252

AlwaysOn Availability Group Concepts and Terminology   252

Availability Groups Concepts and Components   252

Data Synchronization   261

AlwaysOn Availability Groups   266

Prerequisites   267

Creating Hyper-V VMs   271

Exercise 27: Enabling Hyper-V   271

Exercise 28: Downloading Windows Server 2012 R2 Evaluation Edition and Creating the Base VM   272

Exercise 29: Installing the Windows Server 2012 R2 Evaluation Edition   274

Exercise 30: Provisioning VMs Using the Base VM   284

Active Directory Domain Controller   290

Exercise 31: Configuring Active Directory Domain Controller   291

Installing the SQL Server Developer Edition   292

Exercise 32: Downloading and Installing the SQL Server 2016 Developer Edition   293

Exercise 33: Restoring Sample Databases   299

Windows Server Failover Cluster   302

Exercise 34: Configuring Windows Server Failover Cluster   302

Configuring AlwaysOn Availability Groups   304

Exercise 35: Enabling the AlwaysOn AG Feature   304

Exercise 36: Creating the Availability Group   306

Exercise 37: Reviewing an AlwaysOn AG Configuration   326

AlwaysOn Availability Groups on Microsoft Azure   331

Exercise 38: Configuring an AlwaysOn Availability Group on Microsoft Azure   331

Exercise 39: Reviewing Our Availability Group Configuration   350

Exercise 40: Deleting the AlwaysOn Configuration   359

Summary   361

Lesson 5: Managing AlwaysOn Availability Groups   363

Introduction   364

AlwaysOn AG Failover   364

Automatic Failover   364

Exercise 41: Automatic Failover   366

Manual Failover   371

Exercise 42: Manual Failover without Data Loss   371

Exercise 43: Manual Failover with Data Loss   374

Managing AlwaysOn Availability Groups   379

Removing a Database from an Existing Availability Group   380

Exercise 44: Removing a Database from the Secondary Replica   381

Exercise 45: Removing a Database from the Primary Replica   383

Exercise 46: Adding a Database to an Availability Group   384

Exercise 47: Removing a Replica from an Availability Group   387

Exercise 48: Adding a Replica to an Availability Group   388

Exercise 49: Changing the Availability Mode of a Replica   391

Exercise 50: Changing the Failover Mode of a Replica   393

Exercise 51: Creating Multiple Listeners for the Same Availability Group   395

Exercise 52: Configuring Backups on the Secondary Replica   407

Exercise 53: Configuring Readable Secondaries   411

Exercise 54: Read-Only Routing   416

Exercise 55: Configuring the Flexible Failover Policy   422

Availability Database Synchronization States   426

Monitoring AlwaysOn Availability Groups   427

The AlwaysOn AG Dashboard   427

Exercise 56: Collecting Latency Data Using the AlwaysOn AG Dashboard   429

SQL Server Agent Job   430

Exercise 57: Configuring Reports   432

Monitoring AlwaysOn AG Using T-SQL   437

Monitoring an AlwaysOn AG Using PowerShell   443

Troubleshooting AlwaysOn Availability Groups   445

Exercise 58: Problem 1 - DDL Queries Block the Redo Thread on the Secondary Replica   446

Exercise 59: Problem 2 - Transaction Log is Growing   454

Exercise 60: Problem 3 - Replica Database in the Resolving State   461

Activity 5: Manual Failover   466

Activity 6: Adding a New Database to an Existing Availability Group   466

Summary   467

Lesson 6: Configuring and Managing Log Shipping   469

Introduction   470

Log Shipping Use Cases   471

Configuring Log Shipping   472

Exercise 61: Configuring Log Shipping   472

Managing and Monitoring Log Shipping   490

Exercise 62: Adding a New Secondary Instance to an Existing Log Shipping Configuration   491

Exercise 63: Removing a Secondary Instance from an Existing Log Shipping Configuration   492

Exercise 64: Adding a Database to a Log Shipping Configuration   493

Exercise 65: Removing a Log Shipped Database   495

Monitoring Log Shipping   497

Exercise 66: Configuring Email Alerts for the Log Shipping SQL Agent Jobs   497

Exercise 67: Using T-SQL to Set Up Custom Monitoring   500

Exercise 68: Changing the Database State at the Secondary   506

Exercise 69: Performing a Failover from the Primary Instance to the Secondary Instance   508

Troubleshooting Common Log Shipping Issues   512

Exercise 70: Problem 1 – Middle of a Restore Error   512

Exercise 71: Problem 2 – Directory Lookup for File Failed   515

Comparing AlwaysOn, Replication, and Log Shipping   518

Activity 7: Adding a New Data File to a Log Shipped Database   519

Activity 8: Troubleshooting a Problem – Could Not Find a Log Backup File that Could be Applied to Secondary Database 'Sales'   520

Summary   521

Appendix   523

Preface

About

This section briefly introduces the author, the coverage of this book, the technical skills you'll need to get started, and the hardware and software required to complete all of the included activities and exercises.

About the Book

Professional SQL Server High Availability and Disaster Recovery explains the high availability and disaster recovery technologies available in SQL Server: replication, AlwaysOn, and log shipping. You'll learn what they are, how to monitor them, and how to troubleshoot any related problems. You will be introduced to the availability groups of AlwaysOn and learn how to configure them to extend your database mirroring. Through this book, you will be able to explore the technical implementations of high availability and disaster recovery technologies that you can use when you create a highly available infrastructure, including hybrid topologies.

By the end of the book, you'll be equipped with all that you need to know to develop robust and high performance infrastructures.

About the Authors

Ahmad Osama works for Pitney Bowes Pvt Ltd as a database engineer and is a Microsoft Data Platform MVP. In his day to day job at Pitney Bowes, he works on developing and maintaining high performance on-premises and cloud SQL Server OLTP environments, building CI/CD environments for databases and automation. Other than his day to day work, he regularly speaks at user group events and webinars conducted by the Dataplatformlabs community.

Objectives

Understand how to choose high availability and disaster recovery topologies for your environmentDesign high availability and disaster recovery for SQL ServerConfigure replication, AlwaysOn, and log shipping in a production environmentUse best practices to apply and troubleshoot replication, log shipping, and AlwaysOnUse T-SQL to configure replication, AlwaysOn, and log shippingUse transactional replication to migrate from an on-premises SQL Server to Azure SQL database

Audience

This book is for you if you're a database administrator or database developer who wants to improve the performance of your production environment. Prior experience of working with SQL Server will help you get the most out of this book.

Approach

Professional SQL Server High Availability and Disaster Recovery is a fast-paced, practical, hands-on book aimed at experienced administrators. As you progress, you'll find helpful tips and tricks, and useful self-assessment material, exercises, and activities to help benchmark your progress and reinforce what you've learned.

Hardware Requirements

For an optimal student experience, we recommend the following hardware configuration:

Processor: 1.8 GHz or higher Pentium 4 (or equivalent)Memory: 8 GB RAMHard disk: 200 GB free spaceInternet connection

Software Requirements

You'll also need the following software installed in advance:

Windows 8 or aboveThe latest version of Google ChromeAn Azure subscriptionSQL Server Management Studio 17.2 or abovePowerShell 5.1SQL Server 2014 SP2 or above installed as named instance Server\SQL2014SQL Server 2016 SP2 installed as named instance Server\SQL2016

Conventions

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "As discussed earlier, there is an option to compress snapshot files in the .cab format."

A block of code is set as follows:

REM -- Change the variable values as per your environment

SET Publisher=WIN2012R2\SQL2016

SET PublisherDB=WideWorldImporters

New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "In the View Synchronization Status window, select Start to start the distributor agent."

Installation

You will need an Azure account for a few exercises in this book. If you don't have one, create a free account here: https://azure.microsoft.com/en-in/free/.

The latest version of SQL Server Management Studio can be downloaded from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017. Installation instructions are also available on the same web page.

Installing the Code Bundle

Download the code bundle for the book from the GitHub repository and copy it to the C:\Code folder.

Additional Resources

The code bundle for this book is also hosted on GitHub at https://github.com/TrainingByPackt/Professional-SQL-Server-High-Availability-and-Disaster-Recovery.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Lesson 1

Getting Started with SQL Server HA and DR

Learning Objectives

By the end of this lesson, you will be able to:

Describe high availability and disaster recoveryExplain the different HA and DR concepts and terminologyDescribe the different HA and DR solutions available in SQL ServerDescribe replication concepts and terminologyDescribe the different types of replicationConfigure and troubleshoot snapshot replication

This lesson will discuss the common concepts related to high availability and disaster recovery. We will then see how to configure snapshot replication.

Introduction

Business continuity is of utmost importance in today's world. An application downtime that's even as low as a few minutes may result in potential revenue loss for companies such as Amazon and Flipkart. Downtime not only results in direct revenue loss as transactions are dropped as and when downtime happens, it also contributes to a bad user experience.

Often, application downtime because of programming or functional issues doesn't affect the entire application and can be fixed by the developers quickly. However, downtime caused by infrastructure or system failure affects the entire application and can't be controlled functionally (by the developers).

This is where high availability (HA) and disaster recovery (DR) are required. In this lesson, you'll learn about high availability and disaster recovery concepts and terminology, and the different solutions that are available in Microsoft SQL Server to achieve HA and DR.

The type of HA and DR solution implemented by a business depends majorly on the service level agreement (SLA). The SLA defines the recovery point objective (RPO) and recovery time objective (RTO), which will be discussed in detail later in this lesson.

What is High Availability and Disaster Recovery?

High Availability

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.

Disaster Recovery

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:

System upgrades: Critical system upgrades such as software, hardware, network, or storage require the system to be rebooted and may even cause application downtime after being upgraded because of configuration changes. If there is an HA setup present, this can be done with zero downtime.Human errors: As it's rightly said, to err is human. We can't avoid human errors; however, we can have a system in place to recover from human errors. An error in deployment or an application configuration or bad code can cause an application to fail. An example of this is the GitLab outage on January 31, 2017, which was caused by the accidental removal of customer data from the primary database server, resulting in an overall downtime of 18 hours.

Note

You can read more about the GitLab outage post-mortem here: https://about.gitlab.com/2017/02/10/postmortem-of-database-outage-of-january-31/.

Security breaches: Cyber-attacks are a lot more common these days and can result in downtime while you find and fix the issue. Moving the application to a secondary database server may help reduce the downtime while you fix the security issue in most cases.

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:

The primary and secondary servers (SQL Server instance) are in Virginia. This is for high availability (having an available backup system).The primary and secondary servers are in the same data center and are connected over LAN.A DR server (a third SQL Server instance) is in Ohio, which is far away from Virginia. The third SQL Server instance is used as a DR site.The DR site is connected over the internet to the primary site. This is mostly a private network for added security.The primary SQL Server (node 1) is active and is currently serving user transactions.The secondary and DR servers are inactive or passive and are not serving user transactions.

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.

Note

A data center is a facility that's typically owned by a third-party organization, allowing customers to rent or lease out infrastructure. A node here refers to a standalone physical computer. A disaster recovery site is a data center in a different geographical region than that of the primary site.

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.

Note

Organizations periodically perform DR drills (mock DR) to make sure that the DR solution is working fine and to estimate downtime that may happen in the case of an actual DR scenario.

HA and DR Terminologies

The following terms are important in the world of HA and DR so that you can correctly choose the best possible HA and DR solutions and for the better understanding of HA and DR concepts.

Availability

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, describes the availability percentages and the downtime for each percentage:

Note

This link also talks about how this is calculated. You can look at it, but a discussion on calculation is out of the scope of this book.

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

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

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.

SQL Server HA and DR Solutions

The following are the most commonly used HA and DR solutions available in Microsoft SQL Server.

Windows Server Failover Cluster Installation

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

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.

AlwaysOn Availability Groups

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

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.

Hybrid Scenarios

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.

Note

There are other solutions such as database mirroring and third-party solutions. Database mirroring is deprecated and will be removed in future SQL Server versions. This book only talks about SQL Server features and not any third-party HA and DR solutions.

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.

Introduction to SQL Server Replication

Replication is a SQL Server feature that synchronizes data from a database (known as a publisher) to one or more databases (known as subscribers) on the same or different SQL Server instances.

Consider the following diagram:

Figure 1.5: Replication example

The preceding diagram depicts a typical implementation of replication. A replication has a number of components that work together to synchronize data between databases.

Let's look at these components in detail:

Publisher: A publisher is a database that facilitates the data for replication.Publication: A publication is a set of objects and data to replicate. A publisher (database) can have one or more publications. For example, a database has two schemas, finance and sales. There's one publication that has objects and data for the finance schema and another publication that has objects and data for the sales schema.Articles: Articles are the database objects that are to be replicated such as tables and stored procedures. A publication can include one or more selected database objects and data.Distributor: A distributor is a database (distribution database) that stores the data to be replicated from one or more publishers. The distribution database can be on the same instance as the publisher (which happens in most cases) or can be on a different SQL Server instance. Created as part of the replication database, it also stores the replication metadata such as publisher and subscriber details.

A better understanding of distribution databases is crucial in troubleshooting replication.

Subscriber: A subscriber is a database that subscribes to one or more publications from the one or more publishers in order to get the replicated data. A subscriber can also update the publisher data in case of merge or peer-to-peer transactional replication. A subscriber database can be on the same SQL Server instance as the publisher or on a different SQL Server instance.Subscription: Subscription is the opposite of publication. The subscriber connects to the publisher by creating a subscription for the given publication.

There are two types of subscriptions, push and pull subscriptions. In the case of a push subscription, the distributor updates the subscriber as and when data is received (distribution agent is at distributor). In a pull subscription, the subscriber asks the distributor for any new data changes, as scheduled (distribution agent is at the subscriber).

If we now look at the preceding diagram, the publisher database has two publications, one for finance and one for the sales schema. The replication agent gets the changes from the publisher and inserts them into the distribution database.

The distribution agent then applies the changes to the relevant subscribers. There are two subscribers: one has a subscription to the finance publication and another subscribes to the sales publication.

Replication Agents

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.

Note

There's also a queue reader agent that's used in bidirectional transactional replication. Bidirectional transactional replication is now obsolete.

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.

Types of Replication

SQL Server has snapshot, transactional, and merge replication. Each replication type is best suited for one or more sets of scenarios. This section discusses different types of replication and scenarios in which they should be used.

Transactional Replication

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

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

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:

A publication for the finance database is created at the publisher.The snapshot agent creates the snapshot (.sch files for object schema and .bcp files for data). The snapshot files are kept at a shared folder that's accessible by the publisher and the distributor.A subscription for the finance publication is created at the subscriber.The distribution agent applies the snapshot at the subscriber's finance database.

Configuring Snapshot Replication Using SQL Server Management Studio

Throughout this book, we will be using SQL Server Management Studio. You should already be familiar with this. Installation instructions are available in the preface, and all exercises can be completed on the free tier.

Configuring snapshot replication is a two-step process: the first step is to create the publication and the second step is to create the subscription. We will first create the publication.

Exercise 1: Creating a Publication

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

Open SQL Server Management Studio and connect to the Object Explorer (press F8 to open and connect to Object Explorer).Find and expand the Replication node and right-click on the Local Publication node. In the context menu, select New Publication:

Figure 1.7: Select New Publication

In the New Publication Wizard introduction page, click Next to continue:

Figure 1.8: The New Publication Wizard window

The New Publication Wizard | Distributor page defines where the distribution database will be created. The first option specifies that the publisher server will act as the distribution server and will host the distribution database and distributor jobs.

The second option allows you to add a new server to act as the distribution server.

Leave the first option checked and click Next to continue:

Figure 1.9: The Distributor window

In the New Publication Wizard | Snapshot Folder window, specify the location where snapshots (schema: .sch files and data) are stored. This needs to be a network path, and both the distributor and the subscriber should have access to this path.

Create a new folder in any location on your computer and share it with everyone by modifying the sharing settings of the new folder that's been created.

Copy the shared path in the Snapshot folder box, as shown in the following screenshot. Click Next to continue:

Figure 1.10: The Snapshot Folder window

In the New Publication Wizard | Publication Database window, choose the database you wish to publish or replicate. Click Next to continue:

Figure 1.11: The Publication Database window

In the New Publication Wizard | Publication Type window, select Snapshot publication and click Next to continue:

Figure 1.12: The Publication Type window

In the New Publication Wizard | Articles window, select the database objects to be replicated.

Expand Tables and select the required tables, as shown in the following screenshot:

Figure 1.13: The Articles window

Note

Make sure you don't select any temporal tables. Temporal tables aren't supported for replication at the time of writing this book. Temporal tables are the ones with the _archive postfix, for example, Customers_Archive.

Do not select any other objects for now.

Select the BuyingGroups table and then click Article Properties. Then, select Set properties of Highlighted Table Article.

The Article Properties window lists multiple article properties that you may have to change as and when required. For example, you can change the table name and owner at the subscriber database or you can copy the non-clustered Columnstore index from the publisher to the subscriber. This property is disabled by default:

Figure 1.14: The Articles Properties window

The New Publication Wizard | Filter Table Rows window allows you to filter out data to be replicated to the subscriber:

Figure 1.15: The Filter Table Rows window

In the Filter Table Rows window, click on the Add button to add filters. In the Add Filter window, add the filter, as shown in the following screenshot:

Figure 1.16: The Add Filter window

The shown filter will filter out any order with an order year of less than 2016 and will replicate all orders made after the year 2016.

Click OK to go back to the Filter Table Rows window:

Figure 1.17: The Filter Table Rows window

Observe that the filter has been added.

You can't add filters by joining one or more tables. The filter only works on a single table. It is therefore advised that you add the filter to other tables as well. Otherwise, all tables other than the Orders table will have data for all the years. This example, however, applies the filter on the Orders table only.

Click Next to continue.

In the New Publication Wizard | Snapshot Agent window, check the Create a snapshot immediately and keep the snapshot available to initialize subscriptions option.

This will generate the snapshot in the snapshot folder that's specified:

Figure 1.18: The Snapshot Agent window

Click Next to continue.

Note

It is recommended that you schedule the snapshot agent during off business hours so as to avoid performance degradation that occurs as a result of snapshot generation.

In the New Publication Wizard | Agent Security window, specify the service account under which the snapshot agent process will run and how it connects to the publisher:

Figure 1.19: The Agent Security window

Click on Security Settings to continue.

In the Snapshot Agent Security window, choose options, as shown in the following screenshot:

Figure 1.20: The Snapshot Agent Security window

Note

Running the snapshot agent process under a SQL agent service account isn't a good practice on production environments as a SQL agent service account has more privileges than required by the snapshot agent. However, we are only using it for demonstrative purposes.

The minimum permissions required by the Windows account under which the snapshot agent process runs are db_owner rights on the distribution database, db_owner rights on the publisher database, and read, write, and modify rights on the shared snapshot folder.

Click OK to continue. You'll be redirected to the Agent Security window. In this window, click Next to continue:

Figure 1.21: The Agent Security window with account selected

In the Wizard Actions window, select Create the publication, and then click Next to continue:

Figure 1.22: The Agent Security window with account selected

In the Complete the Wizard window, provide the publication name, as shown in the following screenshot, and then click on Finish to complete the wizard and create the publication:

Figure 1.23: The Complete the Wizard window

The wizard creates the publication, adds the selected articles, and creates and starts the snapshot agent job:

Figure 1.24: The Creating Publication window

After the publication is created, click on Close to exit the wizard.

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

Exercise 2: Exploring the Distribution Database

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:

To get publication information, run this query:

SELECT [publisher_id]

      ,[publisher_db]

      ,[publication]

      ,[publication_id]

      ,[publication_type]

      ,[thirdparty_flag]

      ,[independent_agent]

      ,[immediate_sync]

      ,[allow_push]

      ,[allow_pull]

      ,[allow_anonymous]

      ,[description]

      ,[vendor_name]

      ,[retention]

      ,[sync_method]

      ,[allow_subscription_copy]

      ,[thirdparty_options]

      ,[allow_queued_tran]

      ,[options]

      ,[retention_period_unit]

      ,[allow_initialize_from_backup]

      ,[min_autonosync_lsn]

  FROM [distribution].[dbo].[MSpublications]

You should see the following output:

Figure 1.26: Publication information

You can get the publication details from Object Explorer as well:

Figure 1.27: Publication details from the Object Explorer

To get the article information, run this query:

SELECT [publisher_id]

      ,[publisher_db]

      ,[publication_id]

      ,[article]

      ,[article_id]

      ,[destination_object]

      ,[source_owner]

      ,[source_object]

      ,[description]

      ,[destination_owner]

FROM [distribution].[dbo].[MSarticles]

You should get the following output:

Figure 1.28: Article information

To get the snapshot agent information, run this query:

SELECT [id]

      ,[name]

      ,[publisher_id]

      ,[publisher_db]

      ,[publication]

      ,[publication_type]

      ,[local_job]

      ,[job_id]

      ,[profile_id]

      ,[dynamic_filter_login]

      ,[dynamic_filter_hostname]

      ,[publisher_security_mode]

      ,[publisher_login]

      ,[publisher_password]

      ,[job_step_uid]

  FROM [distribution].[dbo].[MSsnapshot_agents]

You should get the following output:

Figure 1.29: Snapshot agent information

Note

The preceding output is also useful in identifying which snapshot agent job belongs to which publication when there are multiple snapshot agent jobs configured on a SQL Server instance.

To get the snapshot agent's execution history, run this query:

SELECT [agent_id]

      ,[runstatus]

      ,[start_time]

      ,[time]

      ,[duration]

      ,[comments]

      ,[delivered_transactions]

      ,[delivered_commands]

      ,[delivery_rate]

      ,[error_id]

      ,[timestamp]

FROM [distribution].[dbo].[MSsnapshot_history]

You should get the following output:

Figure 1.30: Snapshot agent execution history

You can also get the snapshot agent and its history from the Object Explorer under the SQL Server Agent node:

Figure 1.31: Snapshot agent history from the object explorer

Right-click on the snapshot agent job and select View History from the context menu.

Database Snapshot

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