1 / 11

Replication

Replication. What is Replication?. Replication is the process of copying database information Replication is used for: Backing up your database Migrating to a new server Mirroring Syncing with Subscriber servers. Types of Servers Used in Replication. PUBLISHER

pearly
Download Presentation

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. Replication

  2. What is Replication? • Replication is the process of copying database information • Replication is used for: • Backing up your database • Migrating to a new server • Mirroring • Syncing with Subscriber servers

  3. Types of Servers Used in Replication • PUBLISHER • Server that stores source data • SUBSCRIBER • Server that receives replicated data • DISTRIBUTOR • Server that monitors changes and updates Publisher. • Can be on Publisher side (push), or Subscriber side (pull), or on it’s own machine. • The distributor should be placed on the most powerful server.

  4. Types of Replication • SNAPSHOT • Copy of the database from a specific time. Similar to creating a system image of a hard drive. • MERGE • Starts as Snapshot, then changes are tracked with triggers. • Subscribers are synched as they connect. • Used when Subscribers are often working offline. • TRANSACTIONAL • Starts with Snapshot, then Distributor updates Subscribers in near real-time. • PEER-TO-PEER • Based on Transactional foundation, but both servers are at the same hierarchical level. • ORACLE PUBLISHER • Used when migrating from Oracle server to SQL Server

  5. Replication Agents • Programs or services that manage the replication process • SNAPSHOT AGENT • All replication processes use this agent. • Generates snapshot and writes information to the file system. • Is located on the Publisher server • DISTRIBUTION AGENT • Applies snapshot to Subscribers in Snapshot Replication, applies changes in Transactional replication • Runs on Distributor server • MERGE AGENT • Detects changes on Publisher and Subscriber and pushes changes from Publisher to Subscriber. • Contains conflict resolution processes, and created a conflict table to store conflicting values. • LOG READER • Used only by Transactional replication. • Moves transactions from transaction log to Distributor server.

  6. Setting Up Replication on SQL SERVER • It’s All Wizards!!!

  7. Configuring the Distributor • Inside SSMS, connect to instance of server and right click the Replication folder inside “Databases” • Select “Configure Distributor” • If Distributor is on remote server, you must indicate the UNC path • On the Distribution Database Page, change the name of the database and specify a location • You can enable other Publisher servers to use this Distributor from the Publishers page • If you like, you can create a script of all the actions from the Wizards Action page • Configuration Begins!!!

  8. Configure Transactional Publisher • Right click Local Publications folder inside Replication folder and select “New Publication” • Select the database and choose the type of Publication you want to set up • Replicated objects are referred to as articles. Select which objects to replicate. • Under Set Properties, you can choose to replicate constraints, triggers, indexes, etc. • You may specify only certain rows to replicate by utilizing a WHERE clause • Select when to create the Snapshot and how often • For security, you can specify which account the Snapshot Agent and Log Reader Agent will run. Be careful with this, as the selected account may have authority beyond replication • The new Publisher will be shown under Replication => Local Publications

  9. Configure Transactional Subscriber • Expand replication folder and right click Local Subscriptions folder. Select New Subscriber. • You may then select whether to run the Distributor from the Subscriber machine or the Publisher machine. In general, it is best to run it from the more powerful machine • Select your Subscriber Database • As with the Publisher, you may select the account under which the Subscriber runs. • You may configure the synchronization timing from the Synchronization Schedule window. You can run continuously, on demand, or set a schedule • Select whether to initialize immediately or at first synchronization

  10. Replication Monitoring • SQL Server allows you to monitor all Publishers, Subscribers, and Distributors from a graphical interface • This interface reports on the health of the various machines and all agents that are running. • SQL Server Agent also allows you to start and stop each individual agent.

  11. You Have Now Successfully Replicated!!!

More Related