SQL SERVER – How to Create a Readable Secondary Server in SQL Server Standard – Notes from the Field #107

[Notes from Pinal]: The basic nature of human is greedy. When we get one thing which we desire the next thing. In the early world of SQL Server we got a secondary server as a backup or high availability. In earlier times the secondary server was not readable, it just served as a backup. At this point of time our human nature kicked in and we want to get more from the server, which was just sitting there most of the time. We wanted to make our secondary server readable. This is when I reached out to Kenneth and asked him what can we do to make our secondary server as a readable.

In this 107th episode of the Notes from the Fields series database expert Kenneth Urena (partner at Linchpin People) shares very interesting conversation related to how to create readable secondary server in SQL Server standard edition.

AlwaysOn Availability groups are a great technology to create up-to-date, readable secondary’s and distribute read-only load to servers that are not involved in read/write operations. There is one main license requirement, though: your servers need to be running SQL Server Enterprise edition.

So, what happens if you need this functionality, but you are running SQL Server Standard Edition? Transactional Replication is a tool you might want to look to for answers.

This post will demystify some of the misconceptions about transactional replication, and review the considerations and tips you need to successfully configure this technology:

Understanding Transactional Replication

Transactional replication is composed of 3 main roles: The Publisher, The Distributor and The Subscriber.

This is how it works – The Publisher tracks what Objects of the database (Articles) are going to be published, then the Distributor get the changes from the Publisher and makes it available for the Subscriber to consume.

Transactional Replication Requirements

The physical implementation of this technology requires at least 3 databases:

• The Source Database: This database is your actual production database, and it is going to hold the publication on the Publisher.
• The Distribution Database: This database will host all the articles modifications per subscriber per database. It also has a timeframe to keep this information available.
• The Destination database: This database is where all the data will get replicated, and potentially you can redirect the read only queries to take place. This database is hosted on the subscriber host.

Configuring Transactional Replication Properly

In replication the roles can be hosted by the same server. But this choice may actually cause a worse problem than the one we are trying to solve (load balancing). It is because of this that you should keep the following tips in mind before configuring transactional replication:

1. Make sure all of the tables on the source database have primary keys, otherwise that article can’t be include in the publication.
2. Since the goal is take load out of the primary server, make sure to configure the distribution role on a different server than the primary. If you don’t have the budget to configure a distribution server, you can use the subscriber as distributor. Otherwise you will be overloading the server during high traffic.
3. The configuration can be done as follows:
   

4. If the subscriber is going to host the distributor database, make sure the secondary server has the same version (or greater) of SQL server than the publisher.
5. If you are planning to host publications from different servers, with multiple databases on each server, on the same distributor server, it is possible to configure one distribution database per publisher server as follows:

USE MASTER
EXEC sp_adddistributor @distributor = N'DESKTOP-QVDC9JE\SQL2016', @password = N'secret'
GO
EXEC sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
USE [distribution]
IF (NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'UIProperties' AND TYPE = 'U '))
CREATE TABLE UIProperties(id INT)
IF (EXISTS (SELECT * FROM:: fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', NULL, NULL)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
ELSE
EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO
EXEC sp_adddistpublisher @publisher = N'DESKTOP-QVDC9JE\SQL2016', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

6. If you plan to have multiple subscribers on different servers to the same publication, make sure to configure that publication as pull, so the distribution database doesn’t get overloaded.

7. If your publication contains most of the tables of the source database, it will be quicker to initialize the subscriber from backups. Just make sure the publication is configured to allow initialization from backups as follows:

• Expand the Replication folder
• Expand the Local Publication folder
• Right click over the Publication
• Click on Properties
• Select Subscription Options
• Make sure Allow Initialization from Backup Files is on true

Readable Secondary’s and More Balanced Loads – voila!

Although it may require a bit more setup, transactional replication provides a satisfactory replacement for Enterprise level AlwaysOn Availability Groups in the Standard Edition of SQL Server. So long as you take care to configure and maintain the tool properly, this technology should go a long way towards helping you create up-to-date, readable secondary’s and distribute load balancing in a workable manner.

Reference: Pinal Dave (https://blog.sqlauthority.com)