SQL Server to SQL Server replication

Option 1: SQL Server to SQL Server Replication

If you choose the first option, [SQL Server] to [SQL Server] replication, you will arrive at the following screen:

Option 1. Continuous replication w/ automatic seeding and change tracking activation

Replicate entire MS SQL Server or a single database w/ automatic seeding (no backup/restore required; destination/read-replica databases are created automatically). Upon successful completion of the seeding, a change tracking schedule (under Replication Schedules) will be configured automatically.

This option is suitable for replicating smaller size databases. It can be utilized to replicate large size databases if MS SQL Server backup/restore is not available.

All RDS and EC2 SQL Server version 2008 and above, and including Web Edition are supported.

Replication scenarios include but are not limited to continuous replication of:

  • On-Premise, RDS or EC2 SQL Server to the pre-installed (contact support for other MS SQL Server edition options) on this server. The Read-Replicas created locally on this server can be used for Reporting or DR.
  • On-Premise, RDS or EC2 SQL Server to one or multiple RDS or EC2 SQL Server instances for Reporting or DR.
  • RDS to one or multiple RDS Multi-AZ Readable-Replica instances for HA and/or to offload primary
  • RDS to one or multiple RDS Cross-Region (Multi-AR) Readable-Replica instances located in same or multiple regions for DR and/or Data-Locality.

Learn more about supported Use Cases.

Option 2. Continuous replication w/ semi-automatic seeding (backup/restore) and change tracking activation

Replicate a single database w/ semi-automatic seeding. Manual database backup and restore on the replica MS SQL Server is required, before change tracking can be activated.

For more information regarding how to backup an RDS SQL Server database and restore it into an RDS SQL Server click here.

This option is suitable for replicating large size databases or migrations without downtime. Very large backup files can be delivered to AWS utilizing AWS Snowball.

Learn more about supported Use Cases.

Option 3. One-time replication w/ automatic seeding without change tracking activation

One-time replication of all MS SQL Server databases or a single database without change tracking activation (cannot be activated at a later time). Destination replica database(s) are created automatically.

For information regarding integrating this process into DevOps DLM tools, see API Documentation.

On the next screen, you would only need to enter the source and destination connection details.

If your destination MS SQL Server is RDS, we recommend that the root RDS account provided by AWS to not be used for replication. You can create a new user to be used for replication, and also another read-only user to be created under the replication user, to be used for read-only access to the destination RDS SQL Server.

Click here to obtain the recommended scripts to be used to create the above mentioned users.

Enter the source and destination connection details (Basic settings)

Depending on your use case, you may need to switch to the advanced connections configuration screen, where you can select:

  • To encrypt the MS SQL Server connections.
  • Select to not replicate the compatibility level of the source database.
  • Enter a custom destination database creation script.
  • Enter a post-initial replication SQL script.
Advanced connections configuration

Upon successful initiation of the replication process, you will be redirected to the Runtime logs page, where you should see the your initial replication process running along with other seeding and continuous replications processes.

Runtime logs

Clicking on "Tabular" will take you to a page where you can monitor the table seeding progress.

Table seeding progress

Clicking on "Runtime" in the list of running replication processed will take you to a detailed runtime logs console windows, where you can monitor (or see complete detailed about the seeding process upon completion) detailed runtime logs information about the seeding process.

Detailed runtime logs console windows

If the replication process finishes successfully, the status will change from "Running"  to "Success".

if during the seeding process non-critical errors occur, the status will change from "Running" to "RunningWithErrors" or  "RunningWithWarnings". In this case the seeding process will finish with status "CompletedWithErrors" or "CompletedWithWarnings" respectively.

Possible non-critical errors are i.e. failure to create a stored procedure or a view due to non-existing referenced table.

If a critical error occurs, the process will end with status "Failed". You would need to review the logs for detailed error description or contact support. You have the option to [Email Logs to Support] with a single click (after configuring SMTP).

If your replication was initiated under the default replication option ("Replicate entire database and schedule continuous data replication /structure and data plus scheduled CDC/"), and it completes successfully (completion statuses "Success", "CompletedWithWarnings", "CompletedWithErrors"), a schedule to execute replication processes will be created under [AdvancedSchedules] with the default parameters for the server instance type (Compare Instance Types).

.

.

.