Enable transactional replication renewable subscriptions in SQL Server 2019

Page creation date : Sunday, May 29, 2022

Introduction and precautions

This article is intended for replication in SQL Server 2019. One of the functions in it is "renewable subscription" is enabled.

This feature was enabled by default in previous versions of SQL Server, but has been disabled since (probably) 2017.

Future versions of SQL Server may remove this feature. Except for upgrading an existing environment, we recommend that you substitute it for new construction with merge replication etc.

reference

environment

Use two servers, one server and two SQL Server. Windows Server is in a state where no additional settings have been made immediately after installation.

1st unit (publication side)

Windows Server 2019 Standard Edition
SQL Server 2019 Developer Edition (Standard Edition or higher required)
SQL Server Management Studio 18.9.1

Second unit (subscriber side)

Windows Server 2019 Standard Edition
SQL Server 2019 Express Edition
SQL Server Management Studio 18.9.1

purpose

Even if table records in either the first or second server databases are updated Ensure that table records on each other's servers are synchronized.

By the way, one on the publication side is fine, but the subscriber side can have any number of units. It also works with multiple instances of SQL Server on a single server.

Installing SQL Server

Installing SQL Server is not the main part of these Tips, so I won't go into details. Only those relevant to replication are listed.

Installing the publication side requires Standard Edition or higher. This Tips installs Developer Edition as a test environment.

Subscribers are fine with Express Edition. Of course, other editions are fine, depending on the DB configuration.

For Feature Selection, select Database Engine Service and SQL Server Replication. This is the same for both the publication side and the subscriber side.

Instance Configuration defaults to both the publication side and the subscriber side. Even if you change the instance name, the difference is whether or not you specify the instance name in the server specification later.

On the publication side, configure SQL Server Agent to start automatically. You can change this setting later.

This tip authenticates replication with the SQL Server sa account. Therefore, enable SQL Server authentication. If you do not want to use the sa account for security reasons, replace the SQL Server authentication part of the explanation later with Windows authentication or another account.

Make this setting the same on both servers.

Installing SQL Server Management Studio

Install the SQL Server Management Studio that you downloaded. Since the installation screen is only to install, the explanation is omitted.

Restart Windows if you are prompted to restart after installation. Also, install any patches in Windows Update.

Create a publish-side database and tables

If you create a table on the publication side, the subscriber side automatically replicates the table or record.

The method of creation is the same as the normal procedure, so I will omit the details.

You are creating a database named Publication DB in SQL Server Management Studio.

Create a table named "Users" and register the columns as follows: All you need to do is check the synchronization of records, so the name, number, and column contents of the table can be anything. However, only the primary key is required.

Add a few records to make sure that the records are synchronized after the replication is created.

Configuring Remote Connections

Configure them to allow remote access to each other's servers' databases. Details are summarized in the following tips.

Server-side configuration for connecting to SQL Server with SQL Server authentication

Setting folder permissions

On the server on the publication side, open the properties of the following folder: The folder path depends on the version of SQL Server and the instance name.

C:\Program Files\Microsoft SQL Server\MSSQL15. MSSQLSERVER\MSSQL\ReplData

From the Security tab, add the "SERVICE" account and set it to full control.

Configuring the Distribution

This time, the distribution is created on the same server as the publication.

Start SQL Server Management Studio, right-click Replication, and select Configure Distribution.

Click Next.

Check "Make your own server > your own distributor" <.

After that, click "Next" as it is to complete it.

Creating a publication

Originally, it is created from the wizard as shown in the following figure, In SQL Server 2019, creating from a wizard does not enable "renewable subscriptions" and does not allow the synchronization of records between tables.

Therefore, we use T-SQL to create the publication here.

The following is SQL for creating a publication with SQL Server authentication: Please change the password according to your settings.

-- トランザクションパブリケーションを追加するための宣言
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'パブリケーションDB'; 
SET @publication = N'パブリケーション'; 
SET @login = 'sa'; 
SET @password = 'saパスワード'; 

USE [パブリケーションDB]

-- パブリケーションデータベースでトランザクションレプリケーションを有効にする。
EXEC sp_replicationdboption 
    @dbname=@publicationDB, 
    @optname=N'publish',
    @value = N'true';

-- sp_addlogreader_agent を実行して、エージェントジョブを作成します。
EXEC sp_addlogreader_agent 
    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;

-- 即時更新、キュー更新、およびプルサブスクリプションをサポートするトランザクションパブリケーションを作成します。
EXEC sp_addpublication 
    @publication = @publication, 
    @status = N'active',
    @allow_sync_tran = N'true', 
    @allow_queued_tran = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true',
    -- 関連するデフォルトのプロパティを明示的に宣言する
    @conflict_policy = N'pub wins';

-- デフォルトのスケジュールを使用して、パブリケーションの新しいスナップショットジョブを作成します。
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;
GO

When using Windows authentication, change the "sp_addlogreader_agent" and "sp_addpublication_snapshot" parts as follows.

SQL Server Authentication

    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;

Windows Authentication

    @job_login = @login, 
    @job_password = @password,
    @publisher_security_mode = 1;

If it is created successfully, you will see the following:

Also, open the properties of the publication created and make sure that "Renewable subscription" is enabled.

Specifying Articles

Originally, if you create it with a wizard, you will specify the article at the same time, Since the above script does not include article processing, it is specified from the property.

Select "Articles" from the menu on the left, check the table you want to synchronize, and click the OK button.

A msrepl_tran_version is added to the columns of the table to be synchronized, and a table trigger is added at the same time.

Create a subscription

Right-click the publication you just created and select New Subscription.

Click Next.

Select the publication you created.

Check Run all agents on Distributor <server name>.

Click the Add SQL Server Subscriber button.

Specifies the server (+ instance name) to register as a subscriber.

If you do not have a subscriber database, create a new one. If it already exists, select it because the selection item is displayed.

After you specify the database, click Next.

If you have more than one subscriber, you can register as many as you need here.

Click the "..." button.

Here, the agent runs under the sa account, but please set it according to your environment.

Select Run Continuously.

The "Renewable Subscription" setting should be operational.

In the case of "commit changes at the same time", it is synchronized immediately, but all servers must be running.

"Queue changes and commit if possible" accumulates changes in the queue, so they are committed when the server is running, even if the server is not running. However, the synchronization timing is slow.