Friday, January 20, 2017

White Paper - SQL Server 2012- Merge Replication between SQL Server Enterprise with Express edition

Abstract

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet. SQL Server Enterprise, Standard & BI editions having the full replication capabilities whereas Express edition can be utilize for subscription only. This whitepaper provides a way of implementing MERGE Replication between Enterprise edition with Express edition in SQL Server 2012.


About the Author
Vishal Jharwade – Product Specialist (SQL Server/MSBI)
.

About the Domain
This solution is best suite for Sales/Marketing domain where business people has to bring their laptops with them while visiting several clients to show updated matrix/statistics with respect to the business. Also it is useful when they need to update the data in system after business meeting. This solution will reduce the latency in getting the recent data which adds value to business.

  1. Types of Replication in SQL Server 2012

Following are the types of Replication available in SQL Server:
Snapshot Replication
Transactional Replication
Merge Replication
  • Generates point in time Snapshots of the data.
  • Mostly used when the amount of data to be replicated is small
  • It is uni directional replication

  • Permits immediate replication of data modifications.
  • Minimal latency is required.
  • Publisher and the subscriber are always in synchronization and should always be connected.
    • Merges the updates between sites when they are connected.
    • Multiple Subscribers need to update at various times.
    • Subscribers need to receive data, make changes offline and later synchronize changes with the Publisher and other Subscribers.
    Replication agents required:

    • Snapshot Agent
    • Distribution Agent
    Replication agents required:
    • Snapshot Agent
    • Distribution Agent
    • Log Reader Agent
    • Queue Reader Agent
    Replication agents required:
    • Snapshot Agent
    • Merge Agent
    Note: Peer-to-peer replication built on the foundation of transactional replication,  provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodespeer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

    1. Replication Features Supported by the Editions of SQL Server 2012

    Feature Name
    Enterprise
    Business Intelligence
    Standard
    Web
    Express with Advanced Services
    Express with Tools
    Express
    SQL Server change tracking
    Yes
    Yes
    Yes
    Yes
    Yes
    Yes
    Yes
    Merge replication
    Yes
    Yes
    Yes
    Yes (Subscriber only)
    Yes (Subscriber only)
    Yes (Subscriber only)
    Yes (Subscriber only)
    Transactional replication
    Yes
    Yes
    Yes
    Yes (Subscriber only)
    Yes (Subscriber only)
    Yes (Subscriber only)
    Yes (Subscriber only)
    Snapshot replication
    Yes
    Yes
    Yes
    Yes (Subscriber only
    Yes (Subscriber only)
    Yes (Subscriber only)
    Yes (Subscriber only)
    Heterogeneous subscribers
    Yes
    Yes
    Yes




    Oracle publishing
    Yes






    Peer to Peer transactional replication
    Yes






    1. Architecture of MERGE Replication

    SQL Server Replication uses various Agents which are the processes that are responsible for copying and distributing data between publisher and subscriber. MERGE Replication uses two types of replication agents- Snapshot Agent & Merge Agent.
    • Publisher maintains source databases and makes data available for Replication.
    • Snapshot agent makes initial copy of Publisher
    • Distributor agent stores metadata, history also receives/store changes
    • Merge Agent send initial snapshot to subscriber
    • It also merges incremental data changes that occurred at the Publisher or Subscribers after the initial snapshot was created
    • Merge Agent detects and resolves any conflicts according to rules you configure.
    • Merge Agent runs on Subscriber if Pull Subscription and it runs on Distributor if Push Subscription

                           Fig showing Architecture of Merge Replication

    1. Prerequisite to implement Replication between Enterprise and Express edition

    Following are the prerequisites in order to implement Replication between SQL Server Enterprise edition and SQL Server Expression edition:
    • SQL Server Enterprise or Standard or  BI edition should installed in an instance of SQL Server
    • SQL Server Express edition should installed in another  instance of SQL Server
    • User should have administrator permission in both the servers to implement Replication
    • SQL Server Agent account should be administrator access to SQL Server Express edition instance
    Note: For this whitepaper, we are using SQL Server 2012 Enterprise Edition and SQL Server 2012 Express edition

    1. Solution Approach

    To implement MERGE Replication between SQL Server 2012 Enterprise edition and Express edition, we are going to use PUSH subscription in which we will have centralized control and administration in SQL Server Enterprise edition.
    Following are the reasons to choose PUSH subscription:
      • Centralize administration at one place (Enterprise edition)
      • SQL Server Express edition does not support SQL Agent.
    .                                                             
                                                   Fig showing workflow direction of PUSH and PULL subscription

    1. Step by Step procedure to implement Publication in Enterprise edition:

    Below is the list of SQL Server(s) and Database that will be use for implementing Replication:
    Type
    Server Name
    Edition
    Database
    Publisher/Distributor
    IND-SQL-01
    Enterprise Edition
    AdventureWorks2012
    Subscriber
    IND-SQL-01\SQLExpress
    Express Edition
    AdventureWorks2012
    1. Open the SQL Server Management studio and connect SQL Server instance of publisher.
    2. Take the backup of the database that you want to add in the Replication e.g AdventureWorks2012
    3. Connect to Subscriber instance and restore the database backup(adventureworks2012) to subscriber instance so that both the instances have the same database copy
    4. In the Publisher’s instance, go to the Replication folder➔New➔Publisher:









    1. Now it will start the Publication Wizard
    1. Now select the database that you want to add in the Replication:
    Click Next
    1. Select your publication type as MERGE Publication:
    Click Next
    1. Specify the SQL Server versions for Subscriber. In our case , it is SQL Server 2008 or later:
    2. Now select the articles that you want to make part of replication. Here we are selecting all the tables for Replication:
    Click Next







    1. Once we add the tables for Replication, Replication wizard will add an unique identifier column to all the tables
    Click Next
    1. If you want, you can add some filter on the table. Treat it as optional steps
    1. Now Wizard will create initial snapshot of the articles
    Click Next
    1. Now Provide credential by which Replication will work. Here we are using SQL Agent account:
    Click ok.
    1. At the End, Wizard will create the Publication
    Click Next
    1. Give the Name of the Publication e.g Publisher_AdventureWorks2012
    Click Finish
    1. It will add all the selected articles into the Publication:
    1. Go to the Replication Folder➔Local Publications. Here we can see the newly created publication

    1. Steps to create Subscriber in Express edition

    1. Connect to SQL Server Enterprise edition (IND-SQL-01) and go to the Replication folder➔New➔Subscriptions. It will start new subscription wizard
    Click Next
    1. Select the publisher server for this subscriber, it should be newly created publisher- IND-SQL-01 Publisher_AdventureWorks2012
    Click Next
    1. Now select the type of subscription, in our case, it is PUSH subscription because we need centralized administration at Publisher side (enterprise edition)
    Click Next
    1. Now select your subscriber Server. It should be our SQL Server Express edition. To add the Subscriber, click on Add SQL Server Subscriber
    1. Choose your SQL Server Express Edition and Connect it
    1. Now we can see that Subscriber server has been added. Here we can add multiple Subscribers.
    Click Next
    1. Now we need to set Merge Agent Security, Click on the Connect to Subscriber button
    1. We can use any specific account for Replication or current SQL Agent account (Enterprise Edition). Here we are using SQL Server Agent Account
    Click ok
    1. Select the Synchronization Schedule as Run on demand
    Click Next
    1. Now you can initialize subscription immediately or at First synchronization.
    Click Next
    1. Select subscription type as Server
    Click Next
    1. Click on finish to create the Subscription
    Click Next
    1. Check the details of subscription and click Finish
    1. After Subscription, Synchronization has been also done
    Click ok
    1. Check your publisher and Subscriber details in Enterprise edition instance
    So MERGE Replication with PUSH subscription has been implemented.

    1. Things to check after implementing Replication

    1. Check whether unique identifier column has been added or not in all the articles at publisher and subscriber end
    Fig showing rowguid in the publisher
    Check same GUID should be in the Subscriber end
    1. Check whether Replication agent jobs has been created in SQL Server Enterprise edition instance(IND-SQL-01) or not
    Note: By default, SQL Replication agent runs at every 5 mins, we can change it

    1. Steps to test Replication

    Following are the steps to test the MERGE Replication:
    • Perform  Insertion/Updation / Deletion operations in Publisher and check whether same changes reflected in Subscriber
    • Perform Insertion/Updation / Deletion operations in Subscriber and check whether same changes reflected in Publisher.

      1. Insertion at Publisher and verify data in Subscriber

    Take a table which is part of Replication and perform the insertion operation at publisher
    1. Check the data in the [HumanResources].[Department] table in Publisher and Subscriber
    Fig showing data in publisher
    Fig showing data in Subscriber
    So Publisher and Subscriber are having the same data.
    1. Now insert new data in publisher

    1. Check the data into the publisher
    Fig showing data in publisher after insertion operation
    1. Verify the data at subscriber
      
                                           Fig showing the data in Subscriber
    Result: Data in Publisher and Subscriber is in sync after performing insertion operation in publisher

      1. Insertion in Subscriber and verify data in Publisher
    1. Now do the insertion operation at Subscriber side
    1. Check the data in subscriber  after data insertion:
    1. Now check the data in Publisher:
    Result: Data in Publisher and Subscriber is in sync after performing insertion operation in subscriber

      1. Update operation in publisher and verify data in subscriber
    1. Update some data in publisher

    1. Check the data in Publisher after update operation:
    1. Now check the data into Subscriber
    Result: Data in Publisher and Subscriber is in sync after performing update operation in publisher

      1. Update operation in subscriber and verify data in publisher
    1. Update some record in the subscriber
    1. Check the data in subscriber
    1. Now check the data in publisher
    Result: Data in Publisher and Subscriber is in sync after performing update operation in subscriber.

      1. Delete operation in publisher and verify data in subscriber
    1. Now delete some records from the Publisher
    1. Check the data in Publisher after deleting the records
    1. Check the data in Subscriber
    Result: Data in Publisher and Subscriber is in sync after performing delete operation in publisher.

      1. Delete operation in subscriber and verify data in publisher

    1. Perform delete operation in subscriber
    1. Now see the data at subscriber
    1. Now see the data Publisher
    Result: Data in Publisher and Subscriber is in sync after performing delete operation in subscriber.

    1. Conclusion

    By using above steps, MERGE Replication can be implemented between SQL Server Enterprise/Standard/BI editions with SQL Server Express edition.

    1. References

    1 comment:

    1. JhonJuly 23, 2022 at 9:41 AM


      Twitter Card validator obviously used in Twitter and to know more about the validator. It is merely important to understand about the Twitter Card. In this article, we will come up with details of Twitter Card and Twitter Card validator and different type of cards available to make you understand better along with its usage. twitter card validator Twitter Card goes gives a link between your tweets by adding designed structured look and attracting more audience. In simple terms, the code must add to webpage and when someone shares your tweets link will display as Twitter Card.

      ReplyDelete

    Subscribe to: Post Comments (Atom)