Transcript Document

DAT201: Introduction To SQL
™
Server Replication
Matt Hollingsworth
Program Manager
Microsoft Corporation
Agenda
•
SQL Server Replication Review
– Concepts, terminology
•
•
Snapshot Replication
Transactional Replication
– Change tracking and forwarding
– Deployment examples
•
Merge Replication
– Change tracking and forwarding
– Deployment examples
•
Summary
What Is SQL Server Replication?
•
•
•
•
•
•
Replicates database objects
Keeps the data synchronized
Maintains 1 to 1 data row relationship
Optionally subsets or transforms data
Can push or pull data to destination
Utilizes a Publish and Subscribe paradigm
Publish/Subscribe Paradigm
• Publication:
– Defines a group of source database objects to replicate
– The individual database objects are identified as
publication “articles”
• Subscription:
– Defines subscriber as a destination for data flowing
from the specified source publication
• Filters – row and column
– Static filters define data partition during publication
creation and apply to all future subscriptions
– Dynamic filters are calculated during synchronization
and allow data filtering on a per subscriber basis.
Publishers And Subscribers
Servers (SQL Server, Oracle, Other)
Laptops (SQL Server, MSDE)
odbc/ole-db
Handhelds (SQL Server CE)
Publisher/Distributor
(SQL Server)
Subscribers
Common Replication Scenarios
• Offloading query workload
–
–
–
–
Web data caching
Data warehouse staging
Reporting
Warm standby/geographic fail-over
• Mobile clients
• Data consolidation and distribution
• Heterogeneous system integration
Types Of Replication
• Snapshot Replication
– Point in time set of data created using bulk copy API
– Used for replicating non-volatile data sets like price
lists
– Delivers initial data for transactional and merge
replication
• Transactional Replication
– Targets well connected, server to server, low latency
replication of data
– Row changes are replayed in original order, preserving
transactional consistency
• Merge Replication
– Designed for occasionally connected applications
– Delivers net data changes with row by row conflict
resolution
Agenda
• SQL Server Replication Review
– Concepts, terminology
• Snapshot Replication
• Transactional Replication
– Change tracking and forwarding
– Deployment examples
• Merge Replication
– Change tracking and forwarding
– Deployment examples
• Summary
Snapshot Replication
Snapshot
Agent
Publishing
Database
Published
Tables
Files containing
schema and data
Distribution
Agent
• Subscriber gets a complete copy of published data
• Snapshot can be scheduled during off peak hours
Subscribing
Database
Snapshot Replication
• Options
–
–
–
–
Configure a custom pre or post-snapshot script
Specify CAB compression of files
Specify FTP for delivering snapshot files
The snapshot step for transactional and merge
publications can be bypassed if the initial data
already exists at the subscriber (nosync)
demo
Snapshot Replication
Agenda
•
SQL Server Replication Review
– Concepts, terminology
•
•
Snapshot Replication
Transactional Replication
– Change tracking and forwarding
– Deployment examples
•
Merge Replication
– Change tracking and forwarding
– Deployment examples
•
Summary
Transactional Replication
Snapshot
Agent
Publishing
Database
DB
log
•
•
•
•
Published
Tables
Files containing
schema and data
Distribution
Agent
Subscribing
Database
Logreader
Agent
Distribution
Database
Distribution Agent delivers initial snapshot of data prepared by
Snapshot Agent
Logreader Agent reads changes from database log
Logreader Agent stores changes in the Distribution database
Distribution Agent forwards changes to subscribers
Transactional Replication
Change Tracking – Log Reader
• SQL Server marks transactions in log for replication
• Log Reader reads last transaction id processed for a
publisher from distribution database
• Retrieves next set of transactions and SQL statements
from publisher log
• Writes SQL statements to distribution database
• Advances replication watermark in the log to coordinate
with the log manager of the database engine
• Writes history and error information into the distribution
database
Transactional Replication
Forwarding – Distribution Agent
• The Distribution Agent reads the last
transaction received by the subscriber
• Retrieves the next set of transactions and
SQL statements from the distribution
database
• Applies changes to subscriber
• Updates the last transaction received
• Note: agent can run on the distributor (“push”)
or on the subscriber (“pull”)
Transactional Replication
Change Tracking - Updating Subscribers
Publisher
2PC, RPC
Distributor
Queue
Queue Reader Agent
NETWORK
Queue
Immediate Updating
Subscriber
Read-Only
Subscriber
Queued Updating
Subscriber
Transactional Replication
Change Tracking And Forwarding - Features
•
•
•
•
Low overhead at publisher
Partial updates to BLOBS
Built in validation option
Automatic cleanup of store and forward
queues
• Options for managing constraints at
subscriber (NFR)
• Identity range management
• Indexed view support
demo
Transactional Replication
Agenda
•
SQL Server Replication Review
– Concepts, terminology
•
•
Snapshot Replication
Transactional Replication
– Change tracking and forwarding
– Deployment examples
•
Merge Replication
– Change tracking and forwarding
– Deployment examples
•
Summary
Transactional Replication
Reporting Server/DW Staging
OLAP
Cube
Tokyo
Subscriber
Redmond
OLTP Server
Reporting Server
London
Publisher
Subscriber and
Re-Publisher
Subscriber
• 75%+ of typical online DB activity is selecting data!
• Will reduce load and contention on OLTP server
• Improves adhoc query performance and tuning options
Transactional Replication
Web Data Caches
Updates
Load Balancing
Replication
Publisher
Distributor
Internet Clients
App Server Tier Read Only Data Tier
(Subscribers)
Updatable Data Tier
(Publisher)
Transactional Replication
Data Consolidation And Distribution
Subscriber
Publisher
London
Subscriber
Publisher
Subscriber
Publisher
Tokyo
Redmond
Subscriber
Publisher
New Delhi
Transactional Replication
Creating A Warm Standby Server
• Why replication and not log shipping?
– Continuous read access to the replica
– Lower latency
– Mirroring a subset of the original DB
• Otherwise use log shipping
– First choice for maintaining warm standby
– All databases changes replicated
– Less overhead and easier to fail back
Agenda
• SQL Server Replication Review
– Concepts, terminology
• Snapshot Replication
• Transactional Replication
– Change tracking and forwarding
– Deployment examples
• Merge Replication
– Change tracking and forwarding
– Deployment examples
• Summary
Merge Replication
Change Tracking
Publishing
Database
Merge
Process
Subscribing
Database
Default or Custom
Resolver
•
•
•
Net row changes tracked by system triggers in publishing
and subscribing databases
Merge Agent propagates changes between publishing and
subscribing databases
Conflicts are detected and resolved automatically
Merge Replication
Change Tracking Metadata
•
•
Generation – watermark that groups changes
Row Lineage: Maintains sequence of changes to a row
– Represents the identity of all replicas that modified the row, and
which version they made
– Detect simultaneous changes, subsequent changes and dampen
sync loops
•
Column Versions: Who updated the column, and when
– For every column, stores identity or replica that made last update,
and the version
– Attribute level tracking – merge changes to disjoint columns
Merge Replication
Key Features
•
•
•
•
•
Dynamic subscriber data partitions
Customizable conflict resolution
HTTP sync with SQL Server CE
In-line validation of data
Synchronization to alternate publishers
Merge Replication
Dynamic Subscriber Partitions
Goals
• Eliminate or reduce the possibility of conflicts.
• Minimize the volume of data maintained at client
• Improve performance over a slow link
• Prevent clients from receiving sensitive data
Implementation
• Vertical partitions - Column filters on tables
• Horizontal partitions
– Subset filters on individual tables
– Join filters according to relationship between tables
– Both static and dynamic partitioning can be supported
Merge Replication
Conflict Resolution
• Source Wins
– Changes sent to destination
• Destination wins
– Discard changes and rely on next phase to propagate
winner
• Generate new row
– E.g., Additive Resolver
– Update destination and propagate changes during next
phase to source
– Customizable
Merge Replication
HTTP sync with SQL Server CE
SQL Server CE
App
Client
Agent
Device
DB
SERVER
I
I
S
SQL
Server
Reconciler
Server
Agent
SQL Server
Replication
Provider
Message
Replication
Provider
.IN
.OUT
SQL
DB
demo
Merge Replication
Agenda
• SQL Server Replication Review
– Concepts, terminology
• Snapshot Replication
• Transactional Replication
– Change tracking and forwarding
– Deployment examples
• Merge Replication
– Change tracking and forwarding
– Deployment examples
• Summary
Merge Replication
Deployment
• Mobile applications
– Sales Force Automation
– Distribution Applications
– Inventory Tracking
• Catalog Servers
– Regional servers that make offline local updates with
shared data
• Incident tracking/Call Center applications
– Example: Microsoft RAID bug database
Merge Replication Example
Sales Force Automation Topology
Central Publisher
West Coast
East Coast
Regional
Server
HQ Regional Managers
Regional
Server
Regional
Server
Mobile Users
Mobile Users
Sales Office
Sales Office
Mobile Users
Merge Replication
Server To Server - MSN Search Example
Catalog 2
Catalog 1
Catalog 3
• Typically data is not partitioned
• Will provide better connectivity to client applications
in a given region
• Servers are mostly offline and sync regularly over a fast reliable link
Merge Replication
Distribution Apps
SQL Server
Connection
http
Publisher
IIS Server
Firewall
Firewall
• HTTP synchronization
• Typically data is partitioned for each user
• Number of devices syncing with the server is large
Putting It All Together…
Internet customers
Eastern sales force
Web data caches
East region server
HQ
Western sales force
OLTP Server
West region server
Suppliers with heterogeneous databases
Reporting
Server
DW staging
server
Warm Standby
server
Related TechEd Sessions
• DAT412 Transactional Replication Internals
– Wednesday 16:45-18:00, Room 2
• DAT411 Programming and Deploying
Microsoft SQL Server 2000 Replication:
Lessons Learned
– Thursday 15:00 - 16:15, Room 3
Online Resources
• Books Online – SQL Server 2000
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
• Middle Tier Application Data Caching with SQL Server 2000
http://www.microsoft.com/sql/techinfo/development/2000/
middletierdatacaching.asp
• Transactional Replication Performance Tuning and
Optimization
http://www.microsoft.com/SQL/techinfo/administration/2000/ReplPerf.asp
• Diagnosing and Troubleshooting Slow Partitioned Merge
Processes
http://www.microsoft.com/sql/techinfo/development/2000/
slowpartitionedmerge.asp
Don’t forget to complete the
on-line Session Feedback form
on the Attendee Web site
https://web.mseventseurope.com/teched/
© 2002 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.