Technology: Setting up a Merge Replication Gateway Server

Note: I didn't write this document, credit goes to Lucy Yao - but it's very useful information to have and is frequently helpful during server setup.

New Server Setup

1. Install SQL Server x64 (Standard – Server with a GUI)

2. Create Windows accounts

1. My_Replication_Merge

2. My_Replication_Dist

3. My_Replication_Log

4. My_Replication_Snapshot

5. My_SQL_Agent (Agent Service)

6. My_SQL_DBEngine (Server Service)

7. My_Admin account

3. Rename computer to SYNCGATEWAY

4. Install .NET 3.5

1. In Server Manager, click Manage (upper right), then click Add Roles and Features

2. Push Next until you reach the features page. Select .Net Framework 3.5 and click Next

3. On the confirmation page, click Specify Alternate Source Path. Type D:\sources\sxs at the bottom (if the ISO from 1a was mounted in drive D).

4. Complete the installation

5. Install IIS

1. In Server Manager, click Manage (upper right), then click Add Roles and Features

2. Click Next until you reach Server Roles, then select IIS. Click Add Features at the pop-up (if applicable).

3. At the Features page, expand .NET Framework 4.5 and select ASP.NET 4.5

4. Click Next until you reach Role Services. Select the following

1. Basic Authentication

2. Client Certificate Mapping Authentication

3. IIS Management Console

4. IIS 6 Management Compatibility

5. Install

6. In Server Manager, click Manage (upper right), then click Add Roles and Features

7. Click Next until you reach Server Roles

8. Select the following in the Application Development submenu

1. ISAPI Extensions

2. ASP.NET (3.5 and 4.5)

3. .NET Extendibility (3.5 and 4.5)

9. Install

6. Install SQL Server 2014 

1. In Feature selection, choose

1. Database Engine Services

2. SQL Server Replication

3. Client Tools Connectivity

4. Management Tools –Basic

5. Management Tools –Complete

2. In Server Configuration, set the account name to

1. SSE_Agent for SQL Server Agent and set Startup Type to Automatic

2. SSE_DBEngine for SQL Server Database Engine and set Startup Type to Automatic

3. In Database Engine Configuration, click Add Current User

4. Install

7. Enable Mixed Mode Authentication (In SSMS right click SYNCGATEWAY, then properties. Go to the Security tab) 

8. Add relevant user permissions in SQL (a custom SQL script helps here)

9. Give all users full permissions to the C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\repldata

10. Restore the database (MYDATABASE)

11. Add users to distribution database

1. Select Security then My_Replication_Merge on the left-hand pane. Right-click and select properties

2. In User Mapping, select distribution. In Default Schema, type in dbo

3. In the check box below, select db_owner

4. Repeat the process for My_Replication_Snapshot and My_Replication_Dist

12. IIS Setup – Replication Website

1. Certificate

1. Open IIS and click SYNCGATEWAY. Click Server Certificates

2. On the right-hand pane, select Create Self-Signed Certificate

3. Pick a name and follow the creation process

2. Replication Site

1. On the left-hand pane, under Sites, right-click Default Website and rename it Replication

2. With the Replication Website selected, click Bindings

3. Add a binding with Type: https. For SSL certificate, select the one created in 12a (Alternatively, use a non-self signed SSL certificate)

4. Remove the default http port 80 site

3.  Installing the SQL Server Replication Listener

1.  Create a new file directory for replisapi.dll on the computer that is running IIS.  Create the directory under the C:\Inetpub\SQLReplication directory

2. Copy replisapi.dll from the directory C:\Program Files\Microsoft SQL Server\120\com\ to the file directory that you created in step 1.

3. Click Start, and then click Run. In the Open box, enter cmd, and then click OK.

4. In the directory created in step 1, execute the following command: regsvr32 replisapi.dll

5. In Internet Information Services (IIS) Manager, in the Connections pane, right-click Replication, and then select Add Virtual Directory.

6. For Alias, enter SQLReplication.

7. For Physical Path, enter C:\Inetpub\SQLReplication\, and then click OK. 

8. In Internet Information Services (IIS) Manager, click Replication

9. In the center pane, click Handler Mappings.

10. In the Actions pane, click Add Module Mapping.

11. For Request Path, enter replisapi.dll.

12. From the Module drop-down list, select IsapiModule.

13. For Executable, select C:\Inetpub\SQLReplication\replisapi.dll

14. For Name, enter Replisapi.

15. Click the Request Restrictions button, click the Access tab, and then click Execute.

16. Click OK to close the Request Restrictions dialog box, and then click OK again to close the Add Module Mapping dialog box. When you are prompted to allow the ISAPI extension, click Yes to add the extension.

17. Verify that Replisapi.dll is listed under the Enabled handler mappings. If it is in the Disabled list, right-click the Replisapi entry and then click Edit Feature Permissions. Check the Execute box, and then click OK.

4. To Configure IIS Authentication

1. In Internet Information Services (IIS) Manager, click Replication.

2. In the middle pane, double-click Authentication.

3. Right-click Anonymous Authentication, and then choose Disable.

4. Right-click Basic Authentication, and then choose Enable.

5. To require SSL security for a Web site

1. In the middle pane, double-click SSL Settings.

2. Check the Require SSL option. Under Client certificates, verify that the Ignore button is selected.

3. Click Apply

6. To test the certificate

1. From the Actions pane, click Browse *:443(https).

2. Internet Explorer will open and display a message that "There is a problem with this website's security certificate." This warning tells you that the associated certificate was not issued by a recognized CA and might not be trustworthy. This is an expected warning, so click Continue to this website (not recommended).

3. If you are prompted to Connect to localhost, enter a user name and password to proceed. You should see the default page for the Web site. (The Windows administrator username and password).

7. Add My_Replication_Merge to the IIS_IUSRS group

1. In Server Manager, click Tools then expand Computer Management, expand Local Users and Groups, and then click Groups.

2. Right-click IIS_IUSRS, and then click Add to Group.

3. In the IIS_IUSRS Properties dialog box, click Add.

4. In the Select Users, Computers, or Groups dialog box, add SYNCGATEWAY/My_Replication_Merge.

5. In the Select Users dialog box and the IIS_IUSRS Properties dialog box, click OK.

8. Grant minimum account permissions on the folder that contains replisapi.dll

1. In Windows Explorer, right-click the folder that you created for replisapi.dll, and then click Properties.

2. On the Security tab, click Edit.

3. In the Permissions for <foldername> dialog box, add SYNCGATEWAY\My_Replication_Merge

4. Verify that the account is granted only Read, Read & Execute, and List Folder Contents permissions.

9. Create an application pool in Internet Information Services (IIS) Manager

1. In Internet Information Services (IIS) Manager, in the Connections pane, expand the local server node.

2. Right-click Application Pools, and then click Add Application Pool.

3. Enter Replications as the name for the application pool, leave the default values for the remaining fields, and then click OK.

10. Associate the account with the application pool

1. In Internet Information Services (IIS) Manager, expand the local server node, and then click on Application Pools.

2. Right-click the application pool that you created, and then click Set Application Pool Defaults.

3. In the Application Pool Defaults dialog box, scroll down to the Process Model section, and then click the Identity field.

4. Click the ellipsis button on the right side of the Identity row.

5. Click the Custom Account radio button, and then click Set.

6. In the User name and Password fields,enter My_Replication_Merge for the user name and the windows password as the password.

7. Click OK to close the Application Pool Identity dialog box, and then click OK again to close the Application Pool Defaults dialog box.

11. Associate the application pool with the replication Web site

1. In Internet Information Services (IIS) Manager, expand the local server node, and then click on the Replication website

2. In the Actions pane, under Manage Web Site, click Advanced Settings.

3. In the Advanced Settings dialog box, click on the ellipsis button to the right of Application Pool.

4. From the Application pool drop-down list, select Replications

5. Click OK again to close Advanced Settings.

12. To test the connection to replisapi.dll

1. In Microsoft Internet Explorer, on the Tools menu, click Internet Options.

2. On the Connections tab, click LAN Settings.

3. If a proxy server is not used on the LAN, clear Automatically Detect Settings and Use a proxy server for your LAN.

4. If a proxy server is used, click Use a proxy server for your LAN and Bypass proxy server for local addresses, and then click OK.

5. Go to https://(your URL)/SQLReplication/replisapi.dll?diag

6. Log in with the Administrator account

7. The page should look like this

1. 

You now have a working sync gateway! A separate post will cover how to sync to it from the merge subscriber.