1. Real-Time replication of tables without Primary Keys (PKs) but with Unique Indexes is supported but need to select CDC (Change Data Capture) under [Advanced] options in UI during initial job configuration (specify CDC in API call parameters if creating new jobs over API).
2. Replication of tables without both Primary Keys (PKs) and Unique Indexes is supported, but need to select CDC (Change Data Capture) under [Advanced] options in UI during initial job configuration (specify CDC in API call parameters if creating new jobs over API).
To lower latency, adding of basic LongInt Identity field Primary Key or adding of Unique Index is recommended.
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)
Note: CDC (Change Data Capture) is not supported by SQL Server Web Edition and low end AzureSQL DB classes (below Standard S4).
3. Table truncate when the replication job is configured with CDC (Change Data Capture) is supported, but integration with CloudBasix API is required. Contact Support for more information.
4. SQL Server Table partition truncate, partition switch, partition split are supported operation, but selection of CDC (Change Data Capture) under [Advanced] replication options, and integration with CloudBasix API is required. Contact Support for more information. The following is the process/steps required:
4.1 CloudBasix Jobs must be created with CDC, which is also required for tables without PKs.
4.2 Partition operations are fully compatible with CloudBasix CDC jobs, but need to follow below rules:
4.2.1 Partition Truncate
(i) PartitionTruncate must be substituted with PartitionSwitchToDummyTable operation to avoid the need to disable CDC, which is handled in a fully automated manner by CloudBasix but triggers table reseed and is not an acceptable operation for very large tables.
(ii) PartitionTruncate is subject to full automation via placing of a single CloudBasix API call, if willing to substitute PartitionTruncate against source DB with calling CloudBasixAPI(PartitionTruncate) and trust CloudBasix to convert PartitionTruncate to PartitionSwitch, including generating script and creating of a temporary blank table to facilitate the partition switch and then drop or truncate the table.
(iii) If preferred to convert the partition truncate to partition switch in your application or batch process script, then after the operation, a call to CloudBasix API to provide the truncated partition ID is required.
4.2.2 Partition Switch:
After performing the partition switch, from the batch process must place a call to CloudBasix API to provide the switched partition ID.
4.2.3 Partition Split:
A call to CloudBasix API is required following the split operation.
Contact Support for more information
5. 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.
6. 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.
7. When replicating to RDS, Varbinary(max) FileStream fields stored in file system will be converted to Varbinary(max) stored in database.
8. 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.
- 8.1 Create a custom parameter group by inheriting the default parameter group for your version of MS SQL Server
- 8.2 Enter CLR in the filter, set value to enabled (value=1).
9. If Change Tracking (or CDC, depending on whether the default change tracking option is changed to CDC under /New Replication/[Advanced]) is not enabled for some or all tables in a database being replicated by other systems, it will be automatically enabled for the tables which are not excluded from replication. Sample alter script (applies to the default change tracking option):
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.
10. Applies to replication scenarios where the source is non-RDS (on-premise or EC2 SQL Server):
If NIC card settings in (8) above are applied, and you still experience errors of type "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired", then it is recommended that Jumbo Frames/Packets (MTU=9000, Checksum=false) are enabled on the source server hosting SQL Server's NIC, plus if the SQL Server is hosted on-premise, same is to be applied to related switches.
11. CloudBasix instance connectivity requirements:
Ensure that the CloudBasix instance has access to any Source and Destination SQL Server instances over the designated SQL Server TCP/IP port (default 1433, Azure SQL Server Managed instance public port is 3342). This will require proper configuration of VPCs, Security Groups and any firewalls the traffic is expected to pass through. In addition, when using the Redshift related features of CloudBasix, ensure that traffic is allowed on port 5439 from the CloudBasix instance to the Redshift cluster.