Transcript Module 9: Replication - Rajib Kundu SQL DBA Blog

Module 9:
SQL Server 2005
Replication
Overview
Overview of Replication Enhancements
New Types of Replication
Configuring Replication
Lesson: Overview of Replication Enhancements
User Interface Enhancements
Replication Management
Performance and Scalability
Replication Monitor
Other Enhancements
User Interface Enhancements
Replication wizards
 40% fewer pages
 Improved default values and auto-detection
 Less branching
New Subscription Wizard
 Replaces the Push Subscription Wizard and Pull
Subscription Wizard
 Allows you to define multiple subscriptions with different
properties at the same time
Replication Management
Initializing a subscriber
 Backup and restore
 Copying a database
 Dynamic snapshots
Resuming snapshot transfers
Replicating DDL statements
Allowing anonymous subscriptions for all publications
Performance and Scalability
Precomputed partitions
Updates to unique keys
Transactional parallelism — independent agent option
used by default
Download only articles — to reduce change tracking
metadata
Reduced contention on reporting subscribers
 Use snapshot isolation level
 Use snapshots
Replication Monitor
Independent from SQL Server Management Studio
 Passive monitoring
Define acceptable performance figures
Minimize monitoring impact
Merge replication
 Article level statistics
 Upload and download statistics
Transactional replication — real-time monitoring with
tracer tokens
Other Enhancements
Merge replication concurrency enhancements —
guaranteed delivery of logical records
Programming changes — release of RMO
Replication of more types of objects
 .NET UDTs
 Assemblies
Lesson: New Types of Replication
Heterogeneous Replication
Peer-to-Peer Transactional Replication
HTTP Replication
Heterogeneous Replication
Use Oracle v8 and later databases as the publisher in a
replication system
Snapshot replication — no impact on Oracle database
Transactional replication
 Triggers and tracking tables added to Oracle database
 Log Reader Agent transfers changes to SQL Server Distributor
and Distribution Agent transfers changes to subscribers
Potential issues because of:
 Differences between SQL Server and Oracle
 Use of Oracle tools
Peer-to-Peer Transactional Replication
London
Distribution
Agent
Sydney
Log Reader
Agent
Distribution
Agent
New York
Distribution
Agent
Log Reader
Agent
Log Reader
Agent
HTTP Replication
XML over HTTP/HTTPS
Subscriber
Firewall
Binary over TCP/IP
IIS
https://Repl.AdventureWorks.com
Publisher
Lesson: Configuring Replication
How to Configure Replication Using SQL Server
Management Studio
How to Configure Replication Using RMO
How to Configure Replication Using SQL Server
Management Studio
Creating a publication — New Publication Wizard
Creating a subscription — New Subscription Wizard
Synchronizing a subscription
Practice: Creating a Publication Using SQL Server
Management Studio
In this practice, you will see how to:
Start the New Publication Wizard
Configure a publication
How to Configure Replication Using RMO
1 Reference the SMO and RMO libraries
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Replication
2 Import the SMO and RMO namespaces
3 Connect to the replications server(s)
4 Perform replication tasks
publications
Dim Create
Imports
subCon
Microsoft.SqlServer.Management.Common
As
ServerConnection
Imports=Microsoft.SqlServer.Replication
subCon
New ServerConnection("MySubscriber")
Create subscriptions
subCon.Connect()
Practice: Creating a Subscription Using RMO
In this practice, you will see how to create a
subscription using RMO
Course Evaluation