MSSQL Replication

由 datahunter 在五, 25/03/2016 - 18:41 發表

最後更新: 2020-06-22

Replication Model

Model

[Source[                    [Destination]
Publisher -> Distributor -> Subscriber1
                         -> Subscriber2
                         -> ...

Publisher

Replication 的 Source Server

Subscriber

The Subscriber is the database which is going to receive the DML as well as DDL schema changes which are performed on the publisher.

Distributor

The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers.

Each Publisher is associated with a single database (known as a distribution database) at the Distributor.

The Distributor is a server that contains the distribution database,

which stores metadata and history data for all types of replication and transactions for transactional replication.

To set up replication, you must configure a Distributor.

Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor.

Local Distributor

A single database server instance acts as both the Publisher and the Distributor.

Distributor Database

The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers.

Default:

Name: distribution

Location C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data

A database which contains all the Replication commands.

Whenever any DML or DDL schema changes are performed on the publisher,

the corresponding commands generated by SQL Server are stored in the Distribution database.

This database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance.

Article

An article identifies a database object that is included in a publication.

(tables, views, stored procedures, and other objects. )

Publication

A publication is a collection of one or more articles from one database.

Subscription

A subscription is a request for a copy of a publication to be delivered to a Subscriber.

The subscription defines what publication will be received, where, and when. (push and pull)

Publisher, Distributor, Subscriber DB Version

A Publisher can be any version as long as it less than or equal to the Distributor version

A Distributor can be any version as long as it is greater than or equal to the Publisher version

A Subscriber to a transactional publication can be any version within two versions of the Publisher version.

Types of Replication

* By default, Subscribers to transactional publications should be treated as read-only

("Transactional publication with updatable subscriptions" feature will be removed in a future (2014))

Snapshot Replication Architecture

each time a snapshot is applied, it completely overwrites the existing data.

commonly used to provide the initial set of data and database objects for transactional and merge publications

All types of replication use a snapshot to initialize Subscriber

Diagram

Snapshot Agent -> Snapshot folder(schema & data) -> Distribution Agent

Transactional Replication Architecture

* uni-directionally

* tracks changes through the SQL Server transaction log

Diagram

Publisher (LOG) -> LogReaderAgent -> DistributionDB -> DistributionAgent -> Subscriber

Merge Replication Architecture

tracks changes through triggers and metadata tables

Agent

Snapshot Agent
Log Reader Agent
Distribution Agent
Merge Agent
Queue Reader Agent

By default, replication agents run as jobs scheduled under SQL Server Agent

Snapshot Agent

* It prepares schema and initial data files of published tables and other objects, stores the snapshot files
(runs at the Distributor.)

* After the snapshot files have been generated, you can view them in the snapshot folder using Microsoft Windows Explorer.
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData"

Log Reader Agent

* It moves transactions marked for replication from the transaction log on the Publisher to the distribution database
(runs on the Distributor and connects to the Publisher)

Distribution Agent

* It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers.

* runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

* Agent also checks whether data at the Publisher and Subscriber match

Merge Agent

* Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.
* The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions.

Queue Reader Agent

transactional replication with the queued updating option. (runs at the Distributor)

Replication Maintenance Jobs

perform scheduled and on-demand maintenance

Install Transactional Replication On Server

Implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent.

Installing

select "SQL Server Replication" on the "Feature Selection" <- Replication Management Objects (RMO)

* SQL Server Management Studio 要 Enable IP connection 先可以用

* Start -> "All Programs" -> "Microsoft SQL Server 2008 R2" -> "SQL Server Installation Center (64-bit)" <-- Install 不用 restart sql engine

* replication 要用 hostname 去設定

Error

"Sql Server replication requires the actual server name to make a connection to the server.
Connections through a server alias, IP address or any other alternate name are not supported. 
specify the actual server name"

找出 hostname

select @@SERVERNAME
go

設定 hosts 檔

C:\Windows\System32\drivers\etc\hosts

* table cannot be published because it does not have a primary key column

Remark

* Peer-to-peer replication is available only in Enterprise versions of SQL Server.

Log Reader Agent

The Log Reader Agent runs at the Distributor;

it typically runs continuously, but can also run according to a schedule you establish.

When executing, the Log Reader Agent first reads the publication transaction log
(the same database log used for transaction tracking and recovery during regular SQL Server Database Engine operations)
and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data in transactions that have been marked for replication.
Next, the agent copies those transactions in batches to the distribution database at the Distributor.
The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log.
The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers.
Only committed transactions are sent to the distribution database.

After the entire batch of transactions has been written successfully to the distribution database, it is committed.
Following the commit of each batch of commands to the Distributor,
the Log Reader Agent calls sp_repldone to mark where replication was last completed.
Finally, the agent marks the rows in the transaction log that are ready to be purged.
Rows still waiting to be replicated are not purged.

Transaction commands are stored in the distribution database until they are propagated to all Subscribers or
until the maximum distribution retention period has been reached.

Subscribers receive transactions in the same order in which they were applied at the Publisher.

Security Role Requirements for Replication

at the Publisher: db_owner database role on the publication database at the Publisher

at the Subscriber: db_owner database role on the subscriber database at the Subscriber

Mark a subscription for reinitialization: db_owner database role on the subscription database at the Subscriber

sysadmin <-- 咩都做得

Start or stop a replication agent: Owner of the agent job or sysadmin

建立 Push Replication (Step by Step)[SQL2008]

Steps:

[1] On Source

1. Modify "C:\Windows\System32\drivers\etc\hosts" <-- add remote MSSQL instanse name

2. "Add features - SQL Server Replication" to Installed MSQL (setup.exe) [no need reboot]

2. Configuring the Distribution Database

[1] Management Studio

[2] Right Click on the "Replication node" and Select "Configure Distribution.."

[N]Once done, a new database named "distribution" gets created.

3. Creating the publisher

[1] Right Click on "Local Publications" and select "New Publications"

[2] Select "Transactional Replication" from the available publication type and Click on the "Next>"

[3] Select the Objects that you want to publish.

[4] Create a snapshot immediately ...

[5] 設定 Snapshot Agent (Select "Run under the SQL Server Agent service account as the account")

4. Creating the subscriber

a. Right Click on the "publisher created" and select "New Subscriptions"

b. Run all agents as the Distributor

c. Add Subscriber -> Add SQL Server Subscriber ...

d. Set agent permission

("Run under SQL Server Agent", "By impersonating the process account", "Using the following SQL Server login")

The subscriber database can be created by restoring the publisher database at the start itself or by creating a new database

e. Schedule

5. Final

Checking

Replication Monitor

sqlmonitor.exe

INDEX with Replication

Indexes can be added at the Publisher or Subscribers with no special considerations for replication

(be aware that indexes can affect performance).

CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example,

the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

Remark

CREATE INDEX index_name ON table_name (column_name)

i.e.

CREATE INDEX IDX_CUSTOMER_LAST_NAMEON Customer (Last_Name);

Columns & Truncate Replication

Columns with Replication

SQL Server supports a wide variety of schema changes on published objects, including adding and dropping columns.

For example, execute ALTER TABLE … DROP COLUMN at the Publisher, and the statement is replicated to Subscribers and then executed to drop the column.

Subscribers running versions of SQL Server prior to SQL Server 2005 support adding and dropping columns through the stored procedures sp_repladdcolumn and sp_repldropcolumn. For more information, see Making Schema Changes on Publication Databases.

Truncate with Replication

TRUNCATE TABLE is a non-logged operation that does not fire triggers.

It is not permitted because replication cannot track the changes caused by the operation:

transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.

Transaction retention period

Also known as the distribution retention period (The length of time transactions are stored for transactional replication)

Subscriptions can be deactivated or can expire if they are not synchronized within a specified retention period.

Over "maximum distribution retention period"

The action that occurs depends on the type of replication and the retention period that is exceeded.

If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours) and

there are changes in the distribution database that have not been delivered to the Subscriber,

the subscription will be marked deactivated by the Distribution clean up job that runs on the Distributor.

The subscription must be reinitialized.

Over "publication retention period"

If a subscription is not synchronized within the publication retention period (default of 336 hours),

the subscription will expire and be dropped by the Expired subscription clean up job that runs on the Publisher.

The subscription must be recreated and synchronized.

If a push subscription expires, it is completely removed, but pull subscriptions are not.

You must clean up pull subscriptions at the Subscriber.

History retention period

The length of time history metadata is stored for all types of replication.

Validation

Use "validation"

Validation reports on whether a given Subscriber is synchronized with the Publisher.

Validation does not provide information on which rows if any are not synchronized correctly, but the tablediff utility does.

原理

Distribution Agent or Merge Agent will validate data the next time it runs:

Row count only.

This validates whether the table at the Subscriber has the same number of rows as the table at the Publisher,

but does not validate that the content of the rows matches.

Row count validation provides a lightweight approach to validation that can make you aware of issues with your data.

Row count and binary checksum.

In addition to taking a count of rows at the Publisher and Subscriber,

a checksum of all the data is calculated using the checksum algorithm.

If the row count fails, the checksum is not performed.

Troubleshot

Error1:

在 "View Synchronization Status" 內見到

The initial snapshot for publication is not yet available

出現情況

If the subscription gets reinitialised, a new snapshot must be generated.

The distribution agent will give that error until a fresh snapshot is available.

原因

After the snapshot was generated, the Distribution Agent began applying the snapshot,

which is why you then saw the messages

Bulk copying data into table '????'

一段時間後

No replicated transactions are available.

This is normal and by design.

Verify the snapshot was successfully applied at the subscriber. You can do this by examining the data at the subscriber.

P.S.

重新建立會快 D

Disable Publishing and Distribution

You can do the following:

Delete all distribution databases on the Distributor.

Disable all Publishers that use the Distributor and delete all publications on those Publishers.

Delete all subscriptions to the publications. Data in the publication and subscription databases will not be deleted;

however, it loses its synchronization relationship to any publication databases.

If you want the data at the Subscriber to be deleted, you must delete it manually.

瀏覽次數： 1446

夢想家