Chapter 15: Using SQL Server Replication

1
Chapter 15 Using SQL Server Replication
2
Chapter Overview
P513

• Introducing Replication
• Planning for Replication
• Implementing Replication
• Monitoring and Administering Replication

3
Reasons to Replicate Information
P514-515

• Reducing network traffic between remote locations
  and central server
• Separating OLTP and OLAP operations for
  performance
• Combining data from multiple locations
• Data redundancy
• Supporting mobile users
• Scaling out

4
Types of Replication
P515

• Snapshot
• Transactional
• Merge

5
Multimedia Presentation SQL Server Replication
6
Replication Terminology
P515-517
7
Snapshot Replication
P517-519
8
Transactional Replication
P519-520
9
Merge Replication
P520-521
10
Physical Replication Models
P521-522
11
Replication Implementation Tools
P522

• SQL Server Enterprise Manager and Replication
  Monitor
• ActiveX controls, SQL-DMO, and Replication
  Distributor Interface
• System stored procedures
• Windows Synchronization Manager
• Active Directory

12
Replication Security Planning
P523-524

• Only sysadmin members can create and administer
  replication.
• Sysadmin and db_owner members can create and
  configure publications.
• Publisher uses the distributor_admin SQL Server
  account to connect to remote Distributor.
• Snapshot folder uses hidden admin share by
  default.
• Agents run in security context of SQL Server
  Agent by default and must have appropriate access
  rights.

13
Filtering Data
P525
14
Initial Snapshot Options
P525

• Stored in Repldata folder on the Distributor by
  default.
• Alternate locations include network drives or
  compact disc, and files can be compressed using
  CAP file format.
• Maintaining snapshot files is optional, unless
  anonymous Subscribers are permitted.

15
Distributor Configuration
P526-533

• Share with Publisher or use remote Distributor.
• Use hidden admin share for snapshot folder, or
  create explicit share.
• Specify distribution database location, such as
  RAID.
• Enable Publishers.

16
Publisher Configuration
P535-545

• Default vs. alternate snapshot folder.
• Security context for replication agents.
• Enable security for remote Distributor.
• Enable databases for replication.
• Enable Subscribers.

17
Subscriber Configuration

• Configure properties for replication agents
  connecting to Subscribers.
• Specify security context for Subscriber
  connection.
• Specify schedule for Distribution and Merge
  Agents.

18
Publication Creation
P535

• Specify updatable or transformable subscriptions.
• Specify Subscriber types.
• Specify articles and their properties.
• Choose whether or not to list in Active
  Directory.
• Configure horizontal and/or vertical filters.
• Set schedule.

19
Push Subscriptions
P556-562

• Choose Subscribers, and select destination
  database.
• Define Distribution or Merge Agent location and
  schedule.
• Define subscription initialization properties.
• Configure updatable or transformable subscription
  properties.
• For merge publications, define subscription
  priority for conflict resolution.
• Start required services.

20
Pull Subscriptions
P565-571

• Locate publication using Active Directory, or
  locate on registered servers.
• Specify destination database.
• Specify named or anonymous subscription.
• Specify updatable subscription properties,
  including authentication.
• Specify snapshot delivery information.
• Specify Distribution or Merge Agent schedule,
  including on demand.

21
Replication Monitor
P573-577
22
Distributor, Publisher, and Publication Properties
P578-583

• Distributor properties, including retention
  periods
• Publication properties, including subscription
  expiration setting and status
• New and existing replication agent profiles
• Subscription properties, including agent location
  and status

23
Replication Conflicts and Pull Subscription
Properties
P584-589

• Microsoft Replication Conflict Viewer
• Windows Synchronization Manager
• Alternate synchronization partners
• Subscriber, Publisher, and Distributor login
  properties
• Enabling interactive resolution

24
Chapter Summary

• Replication uses the Publisher Distributor
  Subscriber metaphor.
• Choose snapshot, transactional or merge
  replication.
• Understand security context issues for
  replication agents.
• Choose subscription type based on performance
  requirements and administrative convenience.
• Consider enabling interactive conflict resolution
  for merge replication.

25
Lab

• Lab 12 Using SQL Server Replication
• 75 minutes