Module 6: Implementing SQL Server Replication in an Enterprise Environment

1
Module 6 Implementing SQL Server Replication in
an Enterprise Environment
2
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

3
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

4
How to Back Up the Publisher
Backup
Published database
msdb
master
5
How to Back Up the Publisher and Distributor
Backup
Published database
msdb
master
Backup
6
How to Back Up the Publisher, Distributor, and
Subscriber
Backup
Published database
msdb
master
Backup
Published database
Backup
msdb
master
7
Backing Up Snapshot Replication
Use SQL Server backup
Use Windows NT backup for snapshot folder
8
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

9
Forcing the Distributor to Retain Content Until
the Subscriber Has Backed Up
10
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

11
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

12
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

13
Configuring Replication with Multiple SQL Server
Versions
Snapshot or Transactional Replication
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
14
Upgrading a Replicated System
Data Flow
Data Flow
1
2
3
Publisher
Distributor
Subscriber
15
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

16
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

17
Choosing a High Availability Solution

• Standby features
• Failover qualities
• Metadata support
• Transactional consistency and current
  transactions
• Performance impact

18
Subscribing from the Internet
Replicating with Virtual Private Networks (VPNs)
Replicating through ISA Server
VPN
ISA Server
19
Replicating Over a Dial-up Networking Connection

• Configuring replication
• Creating a scheduled job

20
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

21
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

22
Publishing to Heterogeneous Subscribers
Oracle
SQL Server
SQL Server
IBM DB2
Microsoft Access
23
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

24
Replicating with Oracle Subscribers

• Oracle subscription support
• SQL Server has ODBC driver and OLE DB provider
  (Intel)
• Must install Oracles SQLNet 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

25
Replicating with IBM DB2 Subscribers

• DB2/AS400
• OLE DB provider and ODBC driver are included with
  Microsoft Host Integration Server 2000
• SQL Server Books Online has data mapping table
• DB2/MVS
• SQL Server Books Online has data mapping table
• Other DB2 Systems
• May require third-party ODBC drivers or OLE DB
  providers

26
Publishing from Heterogeneous Sources

• Build applications with
• SQL Distributed Management Objects
• Replication Distributor Interface
• Third-party tools

27
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

28
Course Evaluation