Transcript Powerpoint slides for module 6

Module 6: Implementing
SQL Server Replication
in an Enterprise
Environment
Overview

Planning for Disaster Recovery in a Replicated
Environment

Upgrading and Applying Service Packs in a Replicated
Environment

Understanding Enterprise Options in a Replicated
Environment

Understanding Replication with Heterogeneous
Database Systems
Lesson: Planning for Disaster Recovery in a
Replicated Environment

How to Back Up the Publisher

How to Back Up the Publisher and Distributor

How to Back Up the Publisher, Distributor, and Subscriber

Backing Up Snapshot Replication

Backing Up and Restoring Transactional Replication


Using sync with backup
Forcing the Distributor to retain content until the Subscriber has
backed up

Recovery Strategies for Merge Replication

Disaster Recovery Best Practices
How to Back Up the Publisher
Published
database
master
msdb
Backup
How to Back Up the Publisher and Distributor
Published
database
master
Backup
msdb
Backup
How to Back Up the Publisher, Distributor, and
Subscriber
Published
database
master
Backup
msdb
Backup
Published
database
master
msdb
Backup
Backing Up Snapshot Replication
Databases
Use SQL Server backup
.sch
.dri
.bcp
Use Windows NT backup
for snapshot folder
REPLDATA
Backing Up and Restoring Transactional Replication
Using the sync with backup Option

Using sync with backup option



Ensures that Log Reader Agent will not send
transactions to the distribution database
Allows restoration of databases
Restoring the publication database without using sync
with backup option

Avoids increase in latency
Forcing the Distributor to Retain Content Until the
Subscriber Has Backed Up
Recovery Strategies for Merge Replication

Restore Publisher and resynchronize with each
Subscriber

Restore Publisher and Distributor and resynchronize
with each Subscriber

Restore Distributor only

Drop and recreate subscriptions

Restore Subscriber database and resynchronize with
each Publisher
Disaster Recovery Best Practices

Keep a script for replication

Link recovery to validation

Use reinitialization cautiously

Back up concurrently

Restore databases to their original server
Lesson: Upgrading and Applying Service Packs in a
Replicated Environment

Configuring Replication with Multiple SQL Server
Versions

Upgrading a Replicated System

Applying Service Packs to SQL Server 2000 Replicated
Servers
Configuring Replication with Multiple SQL Server
Versions
Snapshot or Transactional Replication
Role
Combination 1
Combination 2
SQL Server 7.0
Combination 3
Publisher
SQL Server 6.5
Distributor
SQL Server 6.5 or 7.0 SQL Server 7.0 or 2000 SQL Server 2000
Subscriber
SQL Server 6.x - 2000 SQL Server 6.x - 2000
Merge Replication
Role
Combination 1
Combination 2
Publisher
SQL Server 7.0
SQL Server 2000
Distributor
SQL Server 2000
SQL Server 2000
Subscriber
SQL Server 7.0
SQL Server 7.0
SQL Server 2000
SQL Server 6.x - 2000
Upgrading a Replicated System
Data Flow
2
Publisher
1
2
3
Data Flow
1
Distributor
3
Subscriber
Applying Service Packs to SQL Server 2000
Replicated Servers

Running in a mixed environment


You can replicate between a SQL Server with a service
pack installed and a SQL Server without that service
pack installed
Restoring after service pack upgrade

After applying a service pack, back up the log or full
database
Lesson: Understanding Enterprise Options in a
Replicated Environment

Choosing a High Availability Solution

Subscribing from the Internet

Replicating over a Dial-up Networking Connection

Replicating with Microsoft Pocket PC
Choosing a High Availability Solution

Standby features

Failover qualities

Metadata support

Transactional consistency and current transactions

Performance impact
Subscribing from the Internet
Replicating through ISA Replicating with Virtual
Server
Private Networks
(VPNs)
VPN
Secure Internet
Connection
ISA Server
Replicating Over a Dial-up Networking Connection

Configuring replication

Creating a scheduled job
Replicating with Microsoft Pocket PC

Microsoft SQL Server 2000 Windows CE Edition

Replicating with a Microsoft Pocket PC Subscriber

Row-level tracking

Using SQL Server CE in a programming environment

Subscribing to a publication through an application

The role of Microsoft Internet Information Services in
SQL Server CE applications
Lesson: Understanding Replication with
Heterogeneous Database Systems

Publishing to Heterogeneous Subscribers

Replicating with Microsoft Access Subscribers

Replicating with Oracle Subscribers

Replicating with IBM DB2 Subscribers

Publishing from Heterogeneous Sources
Publishing to Heterogeneous Subscribers
Oracle
SQL Server
SQL Server
IBM DB2
Microsoft
Access
Replicating with Microsoft Access Subscribers


SQL Server 2000 Desktop Engine

Based on SQL 2000 technology

Most Access projects can run without revision
Microsoft Jet

Does not support case-sensitive sort order

Push and anonymous pull subscriptions

Supports rowcount validation only

Uses reserved column names
Replicating with Oracle Subscribers

Oracle subscription support



SQL Server has ODBC driver and OLE DB provider
(Intel)
Must install Oracle’s SQL*Net driver on Publisher and
Distributor
Replication restrictions

Table names cannot have spaces

date data type maps to char(4)

Only one column of text or image data (long raw)

SQL ranges for float and real differ from Oracle
Replicating with IBM DB2 Subscribers

DB2/AS400



SQL Server Books Online has data mapping table
DB2/MVS


OLE DB provider and ODBC driver are included with
Microsoft Host Integration Server 2000
SQL Server Books Online has data mapping table
Other DB2 Systems

May require third-party ODBC drivers or OLE DB
providers
Publishing from Heterogeneous Sources
Build applications with:

SQL Distributed Management Objects

Replication Distributor Interface

Third-party tools
Review

Planning for Disaster Recovery in a Replicated
Environment

Upgrading and Applying Service Packs in a Replicated
Environment

Understanding Enterprise Options in a Replicated
Environment

Understanding Replication with Heterogeneous
Database Systems
Course Evaluation