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.

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

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

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

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

6. 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:

5. Now it will start the Publication Wizard

6. Now select the database that you want to add in the Replication:

Click Next

7. Select your publication type as MERGE Publication:

Click Next

8. Specify the SQL Server versions for Subscriber. In our case , it is SQL Server 2008 or later:
9. Now select the articles that you want to make part of replication. Here we are selecting all the tables for Replication:

Click Next

10. Once we add the tables for Replication, Replication wizard will add an unique identifier column to all the tables

Click Next

11. If you want, you can add some filter on the table. Treat it as optional steps

12. Now Wizard will create initial snapshot of the articles

Click Next

13. Now Provide credential by which Replication will work. Here we are using SQL Agent account:

Click ok.

14. At the End, Wizard will create the Publication

Click Next

15. Give the Name of the Publication e.g Publisher_AdventureWorks2012

Click Finish

16. It will add all the selected articles into the Publication:

17. Go to the Replication Folder➔Local Publications. Here we can see the newly created publication

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

2. Select the publisher server for this subscriber, it should be newly created publisher- IND-SQL-01 Publisher_AdventureWorks2012

Click Next

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

4. Now select your subscriber Server. It should be our SQL Server Express edition. To add the Subscriber, click on Add SQL Server Subscriber

5. Choose your SQL Server Express Edition and Connect it

6. Now we can see that Subscriber server has been added. Here we can add multiple Subscribers.

Click Next

7. Now we need to set Merge Agent Security, Click on the Connect to Subscriber button

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

9. Select the Synchronization Schedule as Run on demand

Click Next

10. Now you can initialize subscription immediately or at First synchronization.

Click Next

11. Select subscription type as Server

Click Next

12. Click on finish to create the Subscription

Click Next

13. Check the details of subscription and click Finish

14. After Subscription, Synchronization has been also done

Click ok

15. Check your publisher and Subscriber details in Enterprise edition instance

So MERGE Replication with PUSH subscription has been implemented.

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

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

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

2. Now insert new data in publisher

3. Check the data into the publisher

Fig showing data in publisher after insertion operation

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

2. Insertion in Subscriber and verify data in Publisher

1. Now do the insertion operation at Subscriber side

2. Check the data in subscriber  after data insertion:

3. Now check the data in Publisher:

Result: Data in Publisher and Subscriber is in sync after performing insertion operation in subscriber

3. Update operation in publisher and verify data in subscriber

1. Update some data in publisher

2. Check the data in Publisher after update operation:

3. Now check the data into Subscriber

Result: Data in Publisher and Subscriber is in sync after performing update operation in publisher

4. Update operation in subscriber and verify data in publisher

1. Update some record in the subscriber

2. Check the data in subscriber

3. Now check the data in publisher

Result: Data in Publisher and Subscriber is in sync after performing update operation in subscriber.

5. Delete operation in publisher and verify data in subscriber

1. Now delete some records from the Publisher

2. Check the data in Publisher after deleting the records

3. Check the data in Subscriber

Result: Data in Publisher and Subscriber is in sync after performing delete operation in publisher.

6. Delete operation in subscriber and verify data in publisher

1. Perform delete operation in subscriber

2. Now see the data at subscriber

3. Now see the data Publisher

Result: Data in Publisher and Subscriber is in sync after performing delete operation in subscriber.

10. Conclusion

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

11. References

[1]  http://msdn.microsoft.com/en-IN/library/ms151198%28v=sql.110%29.aspx