1 / 28

Module 6: Implementing SQL Server Replication in an Enterprise Environment

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

tab
Download Presentation

Module 6: Implementing SQL Server Replication in an Enterprise Environment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Published database Backup msdb master

  5. How to Back Up the Publisher and Distributor Published database Backup msdb master Backup

  6. How to Back Up the Publisher, Distributor, and Subscriber Published database Backup msdb master Backup Published database Backup msdb master

  7. REPLDATA Databases .sch.dri .bcp 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. Role Combination 1 Combination 2 Combination 3 Publisher SQL Server 6.5 SQL Server 7.0 SQL Server 2000 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 SQL Server 6.x - 2000 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. 2 1 3 Upgrading a Replicated System Data Flow Data Flow 2 1 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. Secure Connection Internet Subscribing from the Internet Replicating through ISA Server Replicating with Virtual Private Networks (VPNs) 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 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

  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

More Related