Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Professional SQL Server High Availability and Disaster Recovery
Professional SQL Server High Availability and Disaster Recovery

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

Arrow left icon
Profile Icon Ahmad Osama
Arrow right icon
R$50 per month
Paperback Jan 2019 564 pages 1st Edition
eBook
R$80 R$196.99
Paperback
R$245.99
Subscription
Free Trial
Renews at R$50p/m
Arrow left icon
Profile Icon Ahmad Osama
Arrow right icon
R$50 per month
Paperback Jan 2019 564 pages 1st Edition
eBook
R$80 R$196.99
Paperback
R$245.99
Subscription
Free Trial
Renews at R$50p/m
eBook
R$80 R$196.99
Paperback
R$245.99
Subscription
Free Trial
Renews at R$50p/m

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Professional SQL Server High Availability and Disaster Recovery

Chapter 2. Transactional Replication

Learning Objectives

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

  • Configure and troubleshoot transactional replication

  • Configure Azure SQL Database as a subscriber

  • Configure and troubleshoot peer-to-peer transactional replication

This lesson will teach us how to configure transactional replication and P2P transactional replication. We will also see how to troubleshoot, monitor, and optimize the same.

Introduction


In the previous lesson, we were introduced to replication and its types. We also took a deep dive into how to configure snapshot replication. In this lesson, we will continue our tour of replication and look at transactional replication and peer-to-peer transactional replication.

This lesson teaches you how to configure, troubleshoot, monitor, and optimize transactional replication. Transactional replication is the most common type of replication used to synchronize data between two databases. It is often used to load-balance read and write operations and is also a cost-effective solution for high availability.

Understanding Transactional Replication


In this section, we'll look at how transactional replication works. The following diagram describes how transactional replication works:

Figure 2.1: Transactional replication example

This diagram shows the AdventureWorks database being replicated using transactional replication. Once transactional replication is configured, the snapshot agent, log reader agent, and distribution agent are created as part of the replication. The agents then work together, as described here, to replicate the transactions.

The snapshot agent is used once to initialize the subscriber database schema with that of the publisher. This isn't shown in the diagram, however, it's the first thing that happens when the transactional replication starts.

The subscriber database can be initialized using the publisher database backup, which is covered later in this lesson.

Once initialized, the transactions are replicated as follows:

  • Steps 1 and 2: The log reader agent continuously scans...

Configuring Transactional Replication


We'll now look at how to configure or set up transactional replication between two databases using T-SQL.

Exercise 7: Creating the Publication

The first step in configuring replication is to create the publication. Follow these steps to create a publication for transactional replication:

  1. Navigate to C:\Code\Lesson02\Transactional and open 1_CreatePublication.sql in SQL Server Management Studio.

  2. In SSMS, from the top menu, select Query and then select SQLCMD. This changes the mode of the 1_CreatePublication.sql query to SQLCMD from SQL.

  3. Modify the parameters in the script (shown in the following screenshot) as per your environment:

    Figure 2.2: Parameters in creating a publication

    The preceding screenshot shows the parameters being used in the script. The parameters are self-explanatory. The publisher and distributor are the same, as the publisher acts as its own distributor.

  4. To run the script and create the publication, change the parameters as specified earlier...

Azure SQL Database as a Subscriber in Transaction Replication


An Azure SQL database is a database-as-a-service offering from Microsoft Azure. An Azure SQL database can be configured as a subscriber to an on-premises SQL Server publisher. This is one of best methods to use for migrating an on-premises database to an Azure SQL database. Moreover, the Azure SQL database subscriber can also be used as a backup or to offload reads from the publisher.

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

To configure Azure SQL database as a subscriber for an on-premises transactional replication, follow these steps:

Note

Open a new PowerShell console (running as administrator) and execute the Install-Module AzureRm command to install the AzureRm PowerShell module. The AzureRm module is a prerequisite for this exercise.

  1. The first step is to create a new blank Azure SQL database. Open a new PowerShell console window and execute the following...

Understanding Peer-To-Peer Transactional Replication


Peer-to-peer replication, also referred to as master-master replication, is built upon transactional replication in which changes at the subscriber are also replicated to the publisher.

A peer-to-peer topology consists of two or more databases where a change in each node (database) gets replicated at every other node (database) in the topology. Peer-to-peer replication therefore provides a scalable high availability solution.

A typical peer-to-peer transactional replication topology is shown in the following diagram:

Figure 2.36: P2P replication example

Node A and Node B are in a peer-to-peer replication. Any changes made at Node A are replicated to Node B and any changes made at Node B are replicated to Node A.

This topology has the following benefits:

  • Reads are load-balanced between two nodes, thereby optimizing the read performance.

  • Writes are load-balanced between two nodes. For example, let's say there is a customer table that stores customer...

Configuring Peer-To-Peer Transactional Replication


In this section, we'll configure peer-to-peer transactional replication. The topology is as follows:

Figure 2.37: P2P Replication example

The preceding diagram illustrates peer-to-peer topology with two nodes with a push subscription:

  • There isn't any additional replication agent involved.

  • Each server acts as a publisher and subscriber.

  • The log reader agent picks up the transactions from AdventureWorks and inserts them into the distribution database. The push subscription agent (distribution agent) pushes the transactions to the Server 2 AdventureWorks database.

Configuring P2P replication consists of the following steps:

  1. Creating the publication and adding articles to the publication on Server 1.

  2. Creating the subscription and then pushing the subscription agent (distribution agent) on Server 1.

  3. Taking the backup of the AdventureWorks database on Server 1. Unlike standard transactional replication, P2P transactional replication is initialized using...

Modifying an Existing Publication


The following steps show how the publication properties can be changed, as discussed for the standard transactional replication. We'll now look at how to remove and add an article in a P2P replication.

Exercise 17: Removing an Article

The process of removing an article can be broken down into four main sections:

  1. Stopping the distribution agent at each node

  2. Dropping the article at each node

  3. Verifying that the article has been dropped at each node

  4. Starting the distribution agent at each node

Step 1 - Stopping the Distribution Agent at Each Node

Stopping a distribution agent can be easily done by stopping the distribution agent job or by stopping the agent from the View Synchronization window.

Stopping the distribution agents at each node will stop the transactions from being applied to the published database. To stop a distribution agent, follow these steps:

  1. In SSMS, open Object Explorer and connect to Node 1 (Neptune\SQL2014).

  2. Expand Replication | Local Subscriptions...

Summary


In this lesson, we have learned how to configure transactional replication and peer-to-peer transactional replication.

We learned how the different agents, that is, snapshot agent, log reader agent, and distributor agent, work together to replicate the data from the publisher database to the subscriber database.

We then saw how to modify the properties of an existing publication and subscription and how to add and remove articles to/from an existing publication.

In the next lesson, we'll learn how to monitor and troubleshoot transactional replication.

Left arrow icon Right arrow icon

Key benefits

  • 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

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.

Who is this book 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.

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

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Jan 31, 2019
Length: 564 pages
Edition : 1st
Language : English
ISBN-13 : 9781789802597
Vendor :
Microsoft
Category :
Languages :
Concepts :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Jan 31, 2019
Length: 564 pages
Edition : 1st
Language : English
ISBN-13 : 9781789802597
Vendor :
Microsoft
Category :
Languages :
Concepts :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
R$50 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
R$500 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just R$25 each
Feature tick icon Exclusive print discounts
R$800 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just R$25 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total R$ 858.97
SQL Server 2017 Administrator's Guide
R$339.99
Professional SQL Server High Availability and Disaster Recovery
R$245.99
SQL Server 2019 Administrator's Guide
R$272.99
Total R$ 858.97 Stars icon
Banner background image

Table of Contents

6 Chapters
Getting Started with SQL Server HA and DR Chevron down icon Chevron up icon
Transactional Replication Chevron down icon Chevron up icon
Monitoring Transactional Replication Chevron down icon Chevron up icon
AlwaysOn Availability Groups Chevron down icon Chevron up icon
Managing AlwaysOn Availability Groups Chevron down icon Chevron up icon
Configuring and Managing Log Shipping Chevron down icon Chevron up icon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.