Merge Replication: Server Side
To setup merge replication in SQL studio perform the following:
- Right click "Local Publications"
- New Publication
- Next
- Select: [Server Name] will act as its own distributor.SQL Server will create a distribution database and log. NEXT
Snapshot Folder (first time only) Create a folder and share it as "RepoData", copy the path including RepoData and paste it into "Screenshoot Folder". NEXT
- Choose the database that contains the data or objects you wish to publish: Select database, NEXT.
- Publication Type: Merge Publication, NEXT.
- Subscriber Types: SQL Server 2008 (or as appropriate).
- Select Tables and Views etc. as needed, NEXT.
- Filters: (NONE), NEXT, NEXT
- Agent Security: First Create a new user with a password that never expires (of course you could let it, but... maintenance). Give the user full controll of the share you created for the snapshot folder in step 5. Run under the following account: Process Account = [Created User] and give password. Use "By impersonating..." and click OK.
- Make agent user from 11 an owner of the database to be replicated.
- Create the publication... Finish.
- Security, Logins, [created user], right click, properties: User Mapping: Select "distribution" and check db_owner of distribution.
- Replication, Local Publications, [New publication created] right click properties
- Click Publication Access List, Add [created user]
- SQL Server Agent, jobs, [Newly created job], right click and start job.
You can check the progress in the Replication Monitor.
Merge Replication: Client Side
Pre-reqs: Make sure that the user has access to the publication: On the Publication Server SSMS, Replication, Local Publication, Archaeology - right click and select properties, Publication Access List, Add the new user if needed.
In local sql server
- [SQLExpress], Replication, Local Subscriptions, Right Click, New Subscriptions...
- Walk your way through the wizard answering questions as we did above (they are different, but much easier).
- Make sure the SQL Browser etc. services are running on the client and server
Run the two scripts "CLR_INTEGRATION_ON.sql" and "ConcatAggr.sql" before you synchronize
- You MAY need to reboot (helped me once, but I didn't have to a different time).
- Run Mobsync, you should see the database listed and click sync all. That's it!