没有合适的资源?快使用搜索试试~ 我知道了~
首页SQL Server 2005 Replication Step by Step
SQL Server 2005 Replication Step by Step
需积分: 15 10 下载量 143 浏览量
更新于2023-07-06
收藏 3.3MB PDF 举报
SQL Server 2005 Replication Step by Step
资源详情
资源推荐
![](https://csdnimg.cn/release/download_crawler_static/2800099/bg1.jpg)
Page 1 of 85
©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
Microsoft SQL Server 2005 Replication
Step by Step
Table of Contents
Introduction .................................................................................................................................................. 3
Eager Replication ...................................................................................................................................... 3
Lazy Replication ........................................................................................................................................ 4
Components in Replication ........................................................................................................................... 4
Distributor ................................................................................................................................................. 5
Publisher ................................................................................................................................................... 5
Author Name
Account/Business Group
Author(s)
Mahesh Kumar Chudamani
Employee ID:-11003447
Email ID:-mahesh.chudamani@wipro.com
Satish Kumar Vemulakonda
Employee ID:-11003436
Email ID:-satish.vemulakonda@wipro.com
Microsoft/TMTS
Reviewer(s)
Microsoft/TMTS
![](https://csdnimg.cn/release/download_crawler_static/2800099/bg2.jpg)
Page 2 of 85
©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
Subscriber ................................................................................................................................................. 6
Publication ................................................................................................................................................ 6
Article ........................................................................................................................................................ 6
Subscriptions ............................................................................................................................................. 6
Agents ....................................................................................................................................................... 7
Snapshot Agent ..................................................................................................................................... 8
Log Reader Agent .................................................................................................................................. 8
Distribution Agent ................................................................................................................................. 8
Merge Agent ......................................................................................................................................... 8
Queue Reader Agent ............................................................................................................................. 9
Purpose of Replication .................................................................................................................................. 9
Replication in SQL Server .............................................................................................................................. 9
Snapshot Replication .............................................................................................................................. 10
Transactional Replication ........................................................................................................................ 12
Merge Replication ................................................................................................................................... 13
Physical Replication Models........................................................................................................................ 15
Publisher/Distributor–Subscriber Model ................................................................................................ 15
Central Publisher–Multiple Subscribers Model ...................................................................................... 15
Central Subscriber–Multiple Publishers Model ...................................................................................... 17
Multiple Publishers–Multiple Subscribers Model ................................................................................... 18
Configuring Replication ............................................................................................................................... 19
Step 1 Configuring Distribution ............................................................................................................... 20
Step 2 Configuring Publication ................................................................................................................ 33
Step 3 Verifying the Status of Replication Agents .................................................................................. 52
Step 4 Configuring Permissions for the Distribution Agent .................................................................... 56
![](https://csdnimg.cn/release/download_crawler_static/2800099/bg3.jpg)
Page 3 of 85
©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
Step 5 Creating a Subscription ................................................................................................................ 60
Best Practices in Replication ....................................................................................................................... 74
Best Practices in Snapshot Replication ................................................................................................... 74
Best Practices in Transactional Replication ............................................................................................ 77
Best Practices in Merge Replication........................................................................................................ 82
Conclusion ................................................................................................................................................... 85
References .................................................................................................................................................. 85
Introduction
Replication is a ―set of technologies‖ that can move data and database objects from
one database to another and across different platforms and geographic locales. Using
replication you create copies of the Database and share the copy with different users so that
they can make changes to their local copy of database and later synchronize the changes to the
source database.
The advantages of replicating databases are the physical separation of the databases
and the normal latency of the data. For example, sales staff working in the field can enter their
orders or changes on their portable devices and transfer the data automatically to the head
office while maintaining database consistency at each site.
The question arises is how to distribute data using replication, and the answer depends
on when, where, and how data is propagated.
There are two kinds of replication:
Eager Replication.
Lazy Replication.
Eager Replication
Eager replication is also known as synchronous replication. In this method, an
application can update a local replica of a table, and within the same transaction it can also
update other replicas of the same table. No concurrency anomalies occur, since synchronous
replication gives serializable execution. Any anomaly in concurrency is detected by the locking
method. If any of the nodes are disconnected, eager replication prevents the update from taking
![](https://csdnimg.cn/release/download_crawler_static/2800099/bg4.jpg)
Page 4 of 85
©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
place. However, there is a compromise in performance as a result of all the updates being
carried in a single transaction.
Eager replication consists of the following steps: execute, transmit, notify, and either
commit or rollback. An executed transaction is transmitted to different nodes, and in the event
of failure in one node, the transaction is rolled back and all the other nodes are notified of the
failure. The transaction is then aborted in all nodes. If replication is successful in all the nodes, a
commit is broadcast and a copy of the committed transaction is then sent to all the nodes.
You may wonder why you would want to use Eager replication. Suppose you want to
have a real-time copy of the master database so that you have a ready backup in the event of a
failure. The synchronous nature of data transfer in eager replication facilitates real-time data
transfer and in this situation it would be useful. However, eager replication is not a good choice
for a remote or mobile environment, since it reduces update performance. Also, in a mobile
environment the nodes are not always connected.
Lazy Replication
Lazy replication is also known as asynchronous replication. In this case, if the
transactions are committed, they are sent to the different sites for the updates to occur.
However, if they are rolled back, the changes will not be transmitted to the different sites. Thus,
the very nature of asynchronous replication allows the updates of committed transactions to be
sent to disconnected sites, as in the case of handheld sets or mobile devices.
With this type of replication, it is possible for two different sites to update the same data
on the same destination site. This will lead to a conflict in the updating of the data. Such update
conflicts need to be resolved in lazy replication and this is done by associating timestamps with
each of the transaction objects. Each object carries the timestamp associated with the previous
update of that data. So when a transaction is sent to the destination site, it first checks to see
whether the timestamp associated with the local copy of the replicated data matches the
incoming transaction‘s old timestamp for that data. Only if they match will the changes,
including the transaction‘s new timestamp, be applied. If the timestamps do not match at the
initial stage, the updated transaction is rejected. SQL Server has a conflict resolution viewer that
deals with updates, inserts, and deletes.
Components in Replication
The below are different components in SQL Server Replication.
Distributor
Publisher
Subscriber
Publication
![](https://csdnimg.cn/release/download_crawler_static/2800099/bg5.jpg)
Page 5 of 85
©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
Article
Subscriptions
Agents
Distributor
The Distributor server is the common link that enables all the components involved in
replication to interact with each other. It contains the distribution database, and it is responsible
for the smooth passage of data between the Publisher servers and the Subscriber servers.
If the Distributor server is located on the same machine as the Publisher server, it is
Known as the local Distributor server, but if it is on a separate machine from the Publisher
server, it is called the remote Distributor server. In large-scale replication, it is better to house
the Distributor server on a remote server. This will not only improve performance but will also
reduce I/O processing and reduce the impact of replication on the Publisher server.
The role of the Distributor server varies depending on the type of replication:
In snapshot and transactional replication, the distribution database in the Distributor
server stores the replicated transactions temporarily and also stores the metadata and
the job history. The replication agents are also stored in the Distributor server, except in
cases where the agents are configured remotely or pull subscriptions are used. (A pull
subscription is one in which the Subscriber server asks for periodic updates of all
changes made at the publishing server).
In merge replication, unlike in snapshot and transactional replication, the distribution
database in the Distributor server stores the metadata and the history of the
synchronization. It also contains the Snapshot Agent and the Merge Agent for push
subscriptions. (A push subscription is a subscription in which the Publisher server
propagates the changes to the subscribing servers without any specific request from the
subscribing server).
The distribution database is a system database that is created when the
Distributor server is configured. You should not drop the distribution database unless
you want to disable it. It not only stores information about replication but also the
metadata, job history, and transactions.
Publisher
While the Distributor server manages the data flow, the Publisher server ensures that
data is available for replication to other servers. The Publisher is the server that contains the data
to be replicated. It can also identify and maintain changes in data. Depending on the type of
剩余84页未读,继续阅读
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
安全验证
文档复制为VIP权益,开通VIP直接复制
![](https://csdnimg.cn/release/wenkucmsfe/public/img/green-success.6a4acb44.png)