RuleSoft-SQL Merge Replication Instructions

Get 1 valid CPS setup created on the same company profile (from CPS) for EACH SERVER that will partake in the system.

Do Install from the CPS Client for each separate Server and make sure both have different database names and are identifiable like “ShoeCompany_CPS_Master” and “ShoeCompany_CPS_Rep1” to keep names unique but clear.

An existing single CPS master database can be used if already populated on a server but do not attempt to use 2 partially data-filled CPS servers as the Merge could result in unusual behavior (the second and subsequent databases should be new/blank setups)


 

 

Steps to perform on ALL SQL Servers in the setup

In the SQL manager right click on the server itself and go to properties.

SET the below “Max Text Replication Size” to “-1”.
 This allows all data to be replicated regardless of size. Most CPS data is < 200,000 bytes in size in a single field but this is not always the case.


 

 

Login to both/all servers involved and try to ensure they are setup to run the SQL Server and Agent and Browser Services using the same DOMAIN account (two identical local accounts can potentially work as well but its not tested fully).
 This is done via the SQL Server Management Console plug-in.

Also ensure this domain account is an SQL Server sys_admin in SQL Server manager.
 This simplifies allot of the security issues that can occur.


 

 

Publisher Steps

In the SQL Management studio on the “master” server expand the nodes as follows…

Right click and select “New Publication”

Select the database you created that is the MASTER to publish and click NEXT


 

 

IMPORTANT ! CPS uses datatypes in 2008 SQL Server and is NOT compatible with older versions!


 

 

Minimum requirement for CPS replication should ONLY be the NON-History tables.
However History Tables can be replicated but it will increase overhead of replication by more then 2x.
 Views should already be present in both servers and are not needed.


 

 

This is normal message at this point… click NEXT


 

 

No Filters for full server Merge Replication… click NEXT


 

 

Defaults work fine here as well but snapshot schedule can be altered if desired to be more frequent … click next


 

 

Enter “Security Settings” on this page …


 

 

Enter the account that is running the SQL Server Services (if the SQL server is using default service account this may not work as the account is local to the machine, though not required to simplify the process the account should be an ADMIN of SQL and an ADMIN of the local machine)


 

 

Defaults are fine… click NEXT


 

 

Enter a Name .. click FINISH


 

 

Successful creation of Publication …


 

 

SUBSCRIBER CONFIGURATION

Login to the second machine with the SQL Server Management Studio and find the subscriptions node…

Right Click and select “New Subscriptions”

Select “Find SQL Server” in the drop down box … Enter proper credentials for the MASTER server that was configured as the Publisher in the first portion of this document.


 

 

Select the Publish you previously created … Click NEXT


 

 

Select the method of control you want, CPS was tested using PULL SUBSCRIPTIONS, but both formats should work if configured correctly.


 

 

Select the Subscriber database that the CPS master data will be pulled to (this is the SECOND CPS database that was configured on the subscriber server)


 

 

Select the ellipsis button to enter the security information the “…” button …


 

 

Use the same account for the server process to keep things simple…

The secondary fields can then be left as is.


 

 

Choose your replication schedule. It is perfectly acceptable to use “Run Continuously” or “Define a schedule”. Note that longer schedule times (e.g. 1 hour) indicate how long between merges of the data. Data Changed on one system will not update on the other system as fast. Shorter times does not mean less efficient as less data needs to be merged. This was tested with “Run Continuously” as well as a defined schedule as short as 1 minute. But the decision is totally up to you (do NOT use Manual unless you want a person to have to kick off the synchronization)


 

 

Click NEXT…


 

 

Click NEXT … do not use “Server” option unless you have valid reasons to do so as it creates extra overhead on your subscriber.


 

 

Select NEXT …


 

 

Click FINISH


 

 

HOW TO CHECK YOUR REPLICATION

Select the server/publisher and right click on Publications and select “Launch Replication Monitor”

This should be the initial view…


 

 

After a few minutes the view should change to the below…


 

 

Note about “Replication Snapshot Share”

There is a chance you may be required to open a share folder on the publishing server to allow initial synchronization. Search online for more information regarding this.

Troubleshooting

Troubleshooting issues beyond this point is too far out of scope to cover all issues.

Utilize Google.

Inspect SQL Agent Job History for error messages.

Try to ensure both servers are setup to run the SQL Server and Agent and Browser Services using the same DOMAIN account.
Also ensure this domain account is an SQL Server sys_admin in SQL Server manager.
 This simplifies allot of the security issues that can occur.