Prerequisites

Prerequisites

1. See Supported MS SQL Server Versions.

2. In order for a continuous replication (a replication which upon completion of the initial replication/seeding is transitioned to a continuous data and schema change tracking) mode to work, all tables in the source database need to have Primary Keys (PK). Before a continuous replication is started, the Quick Setup wizard analyzes all tables and lists tables without PKs. At this point you can create PKs for the listed tables, or choose to proceed as is. Note however that after the initial replication/seeding is completed, the tables without PKs will be ignored for continuous data replication.

Below is a sample script which can be used to create PKs:

alter table TableName
add PKColumnName bigint not null primary key identity(1,1)

3. MS SQL Server login/user accounts used for replication need to be granted sufficient permissions (applicable in the case when the root RDS login is not used for replication). The user used to replicate from the source database(s) is required to be granted db_owner permission . MS SQL Server user used to replicate into the read-replica database is required to be granted create database permission. For more information see the recommended scripts (listed in the RDS/SQL Server User Management section) to create users to be used against source and read-replica for replication, and read-only user to be used by reporting applications.

4. UPDATETEXT | READTEXT | WRITETEXT cannot be used with Change Tracking. It is recommended to use UPDATE | SELECT instead. You need to correct scripts (stored procedures, functions etc.) to not include UPDATETEXT | READTEXT | WRITETEXT (also application programming code must not use those functions) before initiating a replication. The replication will be aborted and reported as failed if UPDATETEXT | READTEXT | WRITETEXT are found in stored procedures or functions. But if UPDATETEXT | READTEXT | WRITETEXT are used in programming code, those cannot be detected and Change Tracking will be activated. Then your program ming code using those functions will error out.
Note: Microsoft will not support those functions in future releases of MS SQL Server.

5. When replicating to RDS, Varbinary(max) FileStream fields stored in file system will be converted to Varbinary(max) stored in databse.

6. Assemblies are supported by RDS, but the RDS instance has to be created with a custom parameter group with CLR enabled. Follow the steps below to create a custom parameter group (in the given example the RDS being created is MS SQL Server 2014 Web Edition).
Note: Assemblies can be created without enabling CLR but by default the RDS root account does not have sufficient permissions to activate them. For more details, see sample test scripts here.

  • 6.1 Create a custom parameter group by inheriting the default parameter group for your version of MS SQL Server

  • 6.2 Enter CLR in the filter, set value to enabled (value=1).

7. If Change Tracking is not enabled for some or all tables in a database being replicated by other systems, it will be enabled for the tables with primary keys which are not excluded from replication. Sample alter script:

ALTER DATABASE [EzSystem] SET CHANGE_TRACKING=ON (CHANGE_RETENTION=4 DAYS, AUTO_CLEANUP =ON);
ALTER TABLE [dbo].[table1] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=ON);
...
ALTER TABLE [dbo].[tableN] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=ON);

(1-N) above will be applied for all tables with Primary Keys, except those excluded for replication in the \Advanced section of Quick Setup.

8. Applies to product version 9.10 and below:

The following NIC (CloudBasic EC2 Network Adapter & EC2 Network Adapters of any EC2 SQL Servers involved in replication) changes are recommended, in order to prevent initial replication seeding or change tracking failures with error "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired".

RDP (Remote Desktop) to the CloudBasic or SQL Server EC2 server(s), and make below change to the NIC (EC2 Network Adapter), then reboot the EC2 server(s):

Turn all "AWS PV Network Device #0" properties to disabled. See below screenshot. Then reboot the server.