1 / 23

Module 3: Implementing Snapshot Replication

Module 3: Implementing Snapshot Replication. Overview. Understanding Snapshot Replication Architecture Replicating Snapshot Publications Efficiently Troubleshooting Snapshot Replication. Lesson: Understanding Snapshot Replication Architecture. How the Snapshot Agent Prepares the Snapshot

umed
Download Presentation

Module 3: Implementing Snapshot Replication

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 3:Implementing Snapshot Replication

  2. Overview • Understanding Snapshot Replication Architecture • Replicating Snapshot Publications Efficiently • Troubleshooting Snapshot Replication

  3. Lesson: Understanding Snapshot Replication Architecture • How the Snapshot Agent Prepares the Snapshot • How the Distribution Agent Applies the Snapshot • How SQL Server Agents Clean Up Snapshot Replication

  4. Distribution database REPLDATA Records History and Errors Publishing database Articles 2 1 3 .sch.dri Schema How the Snapshot Agent Prepares the Snapshot Releaseslocks 5 Reads 1 Logs activity 4 Snapshot Agent Writes schema 2 Writes data 3 Data .bcp

  5. REPLDATA Subscribing database .sch.dri.bcp Tables 2 1 3 distribution database MSrepl_commands MSrepl_transactions How the Distribution Agent Applies the Snapshot 6 Adds triggers, procedures, views to subscribing database 5 Creates replication system objects 4 Inserts data 3 Creates objects Determines whichsubscriptions to apply Determines location of snapshot folder Distribution Agent 1 2 History Errors 7 Logs activity

  6. How SQL Server Agents Clean Up Snapshot Replication • Agent History Cleanup Agent • Distribution Cleanup Agent • Expired Subscription Cleanup Agent • Reinitialize Subscriptions Having Data Validation Failures Agent • Replication Agents Checkup Agent

  7. Lesson: Replicating Snapshot Publications Efficiently • Methods of Deploying Snapshots • Locating the Snapshot Folder • Choosing Appropriate Bulk Copy Program Options • Resolving Name Conflicts from Existing Tables at the Subscriber • Using Scripts Effectively Before and After Snapshot Application • Replicating Transformable Subscriptions

  8. Methods of Deploying Snapshots

  9. Locating the Snapshot Folder • Benefits of alternate locations for the snapshot folder • Increased accessibility • Reduced disk space and Distributor overhead • Enhanced snapshot administration • Easy topology for Centralized Distributor with Multiple Publishers • Enhanced file compression

  10. Practice: Configuring an Alternate Snapshot Location • Create a publication of the Suppliers table • Configure an alternate snapshot location • Subscribe to the SuppliersSnapshot publication and browse the snapshot files

  11. Choosing Appropriate Bulk Copy Program Options • Native and character mode formats • -MaxBcpThreads • -BcpBatchSize • -UseInProcLoader

  12. New New New New Old Old Old Old Resolving Name Conflicts from Existing Tables at the Subscriber • DROP the existing table and re-create it (default) • Delete data in the existing table that matches the row filter statement • Delete all data in the existing table • Keep the existing table unchanged

  13. Practice: Resolving Name Conflicts from Existing Tables at the Subscriber • Modify a table during replication • Create a push subscription

  14. 1 You create the script 2 Snapshot Agent links the script to the snapshot description 3 Snapshot Agent creates the snapshot 5 4 6 2 1 3 Run before script Run after script Apply the snapshot Replication occurs Using Scripts Effectively Before and After Snapshot Application • The Distribution Agent and Merge Agent run the scripts • Agents use the osql to run the scripts • Scripts run in the context of the subscribing database

  15. Practice: Using Before and After Snapshot Scripts • Create a snapshot publication • Review scripts • Create the SQLAssist login • Create a subscription and modify the .sch file • Modify the publication • Create a push subscription to the publication • Confirm the replication of the Shippers table and the application of the scripts • Locate errors

  16. two, two, two… 2, 2, 2… Replicating Transformable Subscriptions Data Transformation Services

  17. Practice: Replicating Transformable Subscriptions • Use DTS to transform data • Create a DTS package • Locate the DTS package

  18. Lesson: Troubleshooting Snapshot Replication • Troubleshooting Agents from the Command Prompt • Troubleshooting the Snapshot Agent • Troubleshooting the Distribution Agent

  19. Troubleshooting Agents from the Command Prompt • Tests the replication agents without using the SQL Server Agent • Only way to test ActiveX Pull Subscriptions • Snapshot Agent is snapshot.exe • Distribution Agent is distrib.exe "C:\Program Files\Microsoft SQL Server\80\COM\snapshot" -Publisher [VANCOUVER] -PublisherDB [Northwind] -Publication [ShippersSnapshot] -DistributorSecurityMode 1

  20. Troubleshooting the Snapshot Agent • Check the snapshot folder for the snapshot files • Check for an alternate snapshot location for the publication • Check whether the SQL Server Agent has write permission to the snapshot folder • Check when the snapshot will be created • Check scheduled run time for SQL Server Agent • Check for errors • Run as sa • Run Snapshot Agent from the command line

  21. Troubleshooting the Distribution Agent • Check whether Subscriber has write permission to the snapshot folder • Check scheduled run time for the Distribution Agent • Check for errors • Check whether the subscribing user has access • Check whether the subscribing server is listed as an enabled Subscriber • Run the job owner as sa • Check whether SQL Server Agent can create tables on the Subscriber • Run the Distribution Agent from the command line

  22. Review • Understanding Snapshot Replication Architecture • Replicating Snapshot Publications Efficiently • Troubleshooting Snapshot Replication

  23. Lab 3: Implementing Snapshot Replication • Exercise 1: Use Snapshot Replication with Custom Properties

More Related