Transcript Transactional Replication

Presented by Paul Ibison
IT Technical Specialist
creator of www.replicationanswers.com
responsible for PayPal and EBay replication implementation
© 2012 Paul Ibison. All rights reserved.

What is Replication?
◦ “Replication is the process of sharing information between
databases (or any other type of server) to ensure that the
content is consistent between systems. ”

Transactional Replication
◦ Server to Server solution

Merge Replication
◦ Offline Database Client solution

Snapshot Replication
◦ Point in time copy of objects from one database to another

Other “competing” options are
◦
◦
◦
◦
◦
◦
◦
◦
◦
Distributed Transactions using MSDTC
Backup/Restore
Log Shipping
Linked Servers
Triggered Solution
SSIS
Clustering
Database Mirroring
HADR in SQL 2012
◦ Remote users at distant locations need quick
access to data – links are slow to head-office
◦ Mobile disconnected users – links are temporary
to head office but people need to work
◦ Disaster recovery???
◦ Need quick access to reporting data
◦ Need to refresh test environments
Reporting databases are
synchronized in near realtime with one or more
source OLTP databases
Management reports
don’t need to wait for a
backup and restore
•Central headquarters
data replicated to 16
vessels
•Poor satellite links –
slow and sometimes
disconnect, but the
vessel staff need to
continue working!
• Republishing
scenario for
regional
veterinary
practices
• Most complex
setup I
worked with!
• A magazine publisher produces
one or more publications
• A publication contains articles
• The publisher either distributes
the magazine directly or uses a
distributor
• Subscribers receive
publications to which they have
subscribed

Publications

Articles
◦ One or more articles
◦ Basis of subscription
◦ One or more per database
◦
◦
◦
◦
Tables or parts of tables
Views
Stored procedures
User-defined functions
Subscriber
Publication
Subscriber
Publisher/
Distributor
Subscribers
Publisher
Distributor
Subscribers
Microsoft SQL Server supports the following
types of replication:
 Snapshot Replication
 Transactional Replication (updatable
subscriptions for transactional replication
deprecated)
 Merge Replication



Distributes data as it appears at a moment in
time
This type is mostly used when the amount of
data to be replicated is small and data/DB
objects are static or does not change
frequently
Doesn’t require any schema changes or prerequisites

Permits low latency replication of data
modifications
◦ [Never synchronous]





Requires Primary Keys
Quick to process
Easy to troubleshoot
Subscriber data generally read-only
Doesn’t deal with conflicts well






Merges the updates between sites when
they are connected
Each Subscriber often has a different
partition of data
Adds a GUID to each table
Allows for conflict resolution
Slower to process
Can be difficult to troubleshoot
Snapshot
Transactional
Snapshot Agent
Snapshot Agent
Distribution Agent
Distribution Agent
Merge
Snapshot Agent
Log Reader Agent
Merge Agent
[Queue Reader
Agent]
Agents are the processes (jobs) that are responsible for copying
and distributing data between publisher and subscriber. There are
different types of agents supporting different types of replication.

Focused on your requests
◦ Monitoring & diagnostics
◦ Oracle Publishing
◦ Transactional
 Better high availability
 Initialization from backup
◦ Merge
 Performance
 Synchronization over the web
◦ Schema changes made using the following data definition
language (DDL) statements are automatically replicated
◦ Wizard simplicity
Designed specifically for Oracle Publishers

–
v8+ on any operating system
Administered like SQL Server, from SQL Server

–
No Oracle side software install necessary
–
Requires minimal knowledge of Oracle

Benefits
◦
◦
◦
◦

High availability & scale out of DB applications
No single point of failure
Allows maintenance without taking applications offline
Can scale queries across databases
Setup
◦ Using wizard is quite simple and requires no downtime to
existing nodes
◦ Publications must all have the same name

Benefits

Setup
◦ Restore a publisher DB backup to deliver the
initial data to a subscriber (instead of snapshot)
◦ Subscriber automatically picks up subsequent
changes on the publisher that occurred after the
backup
◦ Set the “allow initialization with backup”
publication option
◦ Restore a subsequent publisher backup to the
subscriber
Binary over TCP/IP
XML over HTTPS
Publisher/Distributor
IIS
Subscriber
Replisapi.dll
Firewall
•No VPN or port required
•Eases deployment of mobile applications
•Off by default
•Secure by default
• Only authenticated users can send
request
• Requires HTTPS


Microsoft SQL Server 2008 Express Edition can serve as a Subscriber
for all types of replication, providing a convenient way to distribute
data to client applications that use this edition of SQL Server.
When using SQL Server 2008 Express Edition in a replication
topology, keep the following considerations in mind:
◦ SQL Server 2008 Express Edition cannot serve as a Publisher or Distributor.
However, merge replication allows changes to be replicated in both
directions between a Publisher and Subscriber.
◦ SQL Server 2008 Express Edition does not include SQL Server Agent, which
is typically used to run replication agents. If you use a pull subscription (in
which agents run at the Subscriber), you must synchronize the
subscription using Windows Synchronization Manager or RMO.



Microsoft Resources:
◦ msdn.microsoft.com/sqlserver/
◦ www.microsoft.com/sql/community
Contact me:
◦ [email protected]
Websites:
◦ www.replicationanswers.com
◦ http://blogs.msdn.com/b/repltalk/