API Actions: CreateReplication
Request Syntax:
JSON
POST /api/CreateReplication HTTP/1.1 Host: use.your.host.name:82 X-Amz-Content-Sha256: e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 Content-Type: application/json X-Amz-Date: 20171115T202130Z Authorization: AWS4-HMAC-SHA256 Credential= UQOPWUVNBALABCABCABC/20171115/us-east-1/cloudbasic/aws4_request, SignedHeaders=content-length;content-type;host;x-amz-content-sha256;x-amz-date, Signature=995374189c189e8e68ed3de82c1764ca11971711fb5179eeab2b19edd883dd74 { "replicationMethod": "ContinuousWithAutoSeeding", "replicationName": "Job Name (Primary)", // Not required. This is job's friendly name, seen in UI console, Runtime Logs, Reports, Dashboard, API logs etec. //The job would have a system ID/GUID assigned. If not populated, the job will be named with a system name equal to the source DB name "replicateLoginsUsersAndRoles": false, "replicateTablesOnly": false, //default=false, true would cause only tables to be replicated (no views, stored procedures functions etc) "replicateViews": true, //default = true, false would cause views only to not be replicated, appear as excluded in UI Console. "replicateStoredProcedures": true, //Version 13.5 and later; default = true, false would cause stored procedures only to not be replicated, appear as excluded in UI Console. "parallelTablesLimit": 5, "rebuildIndexes": false, "stagingBufferType": "TempDb" //Defauult=TempDb for non-AzureSQL targets. Valid values (TempDb, ReplicaDb) "tde": { "certificateName": null, "keyAlgorithm": null }, "source": { "connectionString": "Data Source=your-source-db-server;Initial Catalog=your-source-db;Persist Security Info=False;User ID=user-name;Password=******;Connect Timeout=1280", "encryptDataInTransit": false, "changeTrackingMethod": "ChangeDataCapture", "changeTrackingRetentionPeriod": "2 DAYS", "resetChangeTracking": true, "userApprovalToEnableChangeTracking": true //required if ChangeTracking or CDC is not enabled yet, or if enabled but with a different retention period }, "destination": { "connectionString":"ConnectionString>Data Source=you-server-name.database.windows.net;Initial Catalog=your-database-name;Persist Security Info=False;User ID=user-name;Password=******;Connect Timeout=12800", "encryptDataInTransit": false, "ValidateCrossDbDependencies": true, //13.7 and above; Default = false "azureServerTier": "Standard S0", //applicable to target AzureSQL only "azureMaxDbSize": "100 MB", //applicable to target AzureSQL only "replicateCompatibilityLevel": true, "customDbCreateScript": " CREATE DATABASE [AdventureWorksLT_replica] ON PRIMARY ( NAME = AdventureWorksLT_replica1, FILENAME = \"D:\\RDSDBDATA\\DATA\\AdventureWorksLT_replicadat1.mdf\", SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), ( NAME = AdventureWorksLT_replica2, FILENAME = \"D:\\RDSDBDATA\\DATA\\AdventureWorksLT_replicadat2.ndf\", SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON ( NAME = AdventureWorksLT_replicalog1, FILENAME = \"D:\\RDSDBDATA\\DATA\\AdventureWorksLT_replicalog1.ldf\", SIZE = 100000KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%), ( NAME = AdventureWorksLT_replicalog2, FILENAME = \"D:\\RDSDBDATA\\DATA\\AdventureWorksLT_replicalog2.ldf\", SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS --RDS default collation ", "preSeedingCustomAction": " ALTER DATABASE CURRENT COLLATE SQL_Latin1_General_CP1_CI_AI; ", //bellow script is recommended to be executed when target is RDS, as the initial seeding creates the replica DB, but the root RDS admin login does not automatically become db_owner, as it is not sysadmin "postSeedingCustomAction": " CREATE USER rdsAdmin FOR LOGIN admin; EXEC sp_addrolemember ''db_owner'',''admin''; " } }, //Below tag has been deprecated in 12.322 //"tablesToExclude": "[dbo].[table1], [myschema].[table2], [dbo].[tableN]", //deprecated in 12.322, backward compatibility maintained. //Replaced by “addSourceObjectsToExclusionList”: {...“tables”: “[dbo].[Departments],[dbo].[Employees]“...} //deprecated - to be removed - never published //"tablesToExcludeWithoutPk": "true", -- 12.318 and above; //incompatible with //“tablesToExcludeWithoutUniqueIndexWithoutPk”,“tablesToExcludeWithUniqueIndexWithoutPk” //12.322 and above //Optional Tag Group; All tags within the group are optional “addSourceObjectsToExclusionList”: { //object=tables “tables”: “[dbo].[table1],[dbo].[table2],[dbo].[tableN]“, “tablesWithoutPk”: “true”, //incompatible with “tablesWithoutUniqueIndexWithoutPk”,“tablesWithUniqueIndexWithoutPk” “tablesWithoutUniqueIndexWithoutPk”: “true”, //incompatible with tablesWithoutPk” “tablesWithUniqueIndexWithoutPk”: “true”, //incompatible with tablesWithoutPk” “tablesWithBinaryColumnTypes”: “true”, //allows exclusion of tables with column types (varbinary, binary, image, geometry) “tablesMatchingRules”: “^\\[dbo\\].\\[archived_*]$, ^\\[dbo\\].\\[OLD_*]$, ^\\[dbo\\].\\[temp_*]$“, //object=views “views”: “[dbo].[view1], [dbo].[vw_view2], [dbo].[vw_viewN]“, “viewsMatchingRules”: “^\\[dbo\\].\\[vw_archived_*]$, ^\\[dbo\\].\\[vw_old_*]$, ^\\[dbo\\].\\[vw_temp_*]$“, //object=SP “storedProcedures”: “[dbo].[sp_storedproc1],[dbo].[sp_storedproc2], [dbo].[sp_storedprocN]" } //Optional tag group "reseedingSchedule": { "tablesToIncludeWithoutPk": "[dbo].[TableWithoutpk1], [dbo].[TableWithoutpk2], [dbo].[TableWithoutPkN]", "tablesToIncludeWithPk": [dbo].[TableWithPk1], [dbo].[TableWithPk2], [dbo].[TableWithPkN]", "fromTime": "10:00:00 AM", "toTime": "10:59:59 PM", "repeatIntervalInMinutes": "100", "processTimeoutInMinutes": "40", "serializedProcessTimeoutInMinutes": "40", "autoSerializedProcessTimeoutInMinutes": "40" }, //Optional tag group "RebuildDbReplicaIndexesSchedule: { "enabled": true, "recurrence": "Weekly", "weekDays": "Saturday", "fromTime": "12:00:00 AM", "toTime": "12:15:59 AM", "parallelIndexesRebuildLimit": 10 } }
Request Parameters
Parameter | Description | Required | |||
---|---|---|---|---|---|
ReplicationMethod | Possible values are:
|
Yes | |||
ContinuousWithAutoSeeding | Continuous replication w/ automatic seeding and change tracking activation. Replicate an entire SQL Server or a single database with automatic seeding. No backup/restore operation is required because the destination/read-replica databases are created automatically. Upon successful completion of the seeding, a change tracking schedule will be configured automatically. This option is suitable for replicating smaller size databases. It can be utilized to replicate large size databases if SQL Server backup/restore is not available. All RDS and EC2 SQL Server version 2008 and above, including Web Edition, are supported. Replication scenarios include, but are not limited to, continuous replication of: · 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 the primary · RDS to one or multiple RDS Cross-Region (Multi-AR) Readable-Replica instances located in the same or multiple regions for DR and/or Data-Locality. |
||||
ContinuousWithSemiAutoSeeding | Replicate a single database with semi-automatic seeding. Manual database backup and restore on the replica SQL Server is required before change tracking can be activated.
This option is suitable for replicating large size databases or when replicating under the fully automated seeding option fails due to large tables or other reasons. Very large backup files can be delivered to AWS utilizing AWS Snowball. |
||||
OneTimeWithAutoSeeding | One-time replication of all SQL Server databases or a single database without change tracking activation (cannot be activated at a later time). The destination replica databases are created automatically. | ||||
Type: String Default: None |
|||||
ReplicateLoginsUsersAndRoles | Indicates whether the users and roles will be replicated from the source database instance to the destination database instance.
Note: |
No | |||
Type: String Default: None Valid value: true/ false |
|||||
ReplicateTablesOnly | When set to true, only database tables and their data will be replicated. Views, Stored Procedures, Triggers and Functions will not be replicated.
Note: |
No | |||
Type: String Default: None Valid value: true/ false |
|||||
ParallelTablesLimit | Indicates the maximum number of parallel table replications at any given moment. Instances with insufficient performance can get saturated if a large number of parallel table replications is requested.
Note: |
No | |||
Type: Numeric Default: None |
|||||
RebuildIndexes | Indicates whether the indexes should be rebuilt in the destination database instance.
Note: |
No | |||
Type: String Default: None Valid value: true/ false |
|||||
StagingBufferType | Controls what storage is used for staging data. By default CloudBasic will use the standard SQL Server TempDb to store staging data before merging it into the destination database. Use the ReplicationDB value to force CloudBasic to use the destination database for staging storage. | No | |||
Type: String Default: TempDb Valid values: TempDb, ReplicaDB |
|||||
TDE | Note: Transparent Data Encryption (TDE) is applicable to SQL Server Enterprise Edition only. |
No | |||
CertificateName | The name of the server certificate to be used for encryption. | Yes | |||
Type: String Default: None |
|||||
KeyAlgorythm | Valid values are: - AES 128 - AES 256 |
Yes | |||
Type: String Default: None |
|||||
Source | Yes | ||||
ConnectionString | A valid connection string to the source database instance. | Yes~ | |||
Type: String Default: NoneEncryptDataInTransit: Indicates whether data in transit should be encrypted.Note: TLS 1.2 is activated for SQL Server 2016 and prior versions that have been updated to support TLS 1.2. TLS 1.1, 1.0 or SSL are activated if the SQL Server version does not support TLS 1.2. |
|||||
ChangeTrackingMethod | Designates a supported method for tracking of changes. | No~ | |||
Type: String Default: ChangeTracking Valid value: ChangeDataCapture/ ChangeTracking |
|||||
ChangeTrackingRetentionPeriod
Note: applies to CDC (ChangeTrackingMethod=ChngeDataCapture as well ) |
Specifies the period that Change Tracking data (applies to both Change tracking and CDC) is saved for before being automatically purged. | Yes~ | |||
Type: String Default: None Valid values: 12 Hours, 1 Day, 2 Days, 3 Days, 4 Days, 5 Days, 6, Days, 7 Days, 8 Days |
|||||
ResetChangeTracking
Note: applies to CDC (ChangeTrackingMethod=ChngeDataCapture as well ) |
When set to true, all accumulated change tracking history will be deleted.
Note: Important: If change tracking is reset and the source database is involved in other replications, those will be invalidated after the reset as well. |
Yes~ | |||
Type: String Default: None Valid value: true/ false |
|||||
Destination | Yes | ||||
ConnectionString | A valid connection string to the source database instance. | Yes~ | |||
Type: String Default: None |
|||||
AzureServerTier | Type of Azure Tier. Valid values are:
Plus all supported types as of Oct-1-2022 |
No~ | |||
Type: String Default: None |
|||||
AzureMaxDbSize | Indicates the maximum size of the database on Azure. Possible valid values are:
|
No~ | |||
Type: String Default: None |
|||||
EncryptDataInTransit | Indicates whether data in transit should be encrypted.
Note: |
Yes~ | |||
Type: String Default: None Valid value: true/ false |
|||||
ReplicateCompatibilityLevel | Replicate the compatibility level of the source database.
Note: |
Yes~ | |||
Type: String Default: None Valid value: true/ false |
|||||
CustomDbCreateScript | Optional SQL script that will change the parameters of the destination database.
Note: |
No~ | |||
Type: String Default: None |
|||||
PreSeedingCustomAction | A SQL script that will be executed after the initial database creation and before any seeding has started.
Note: |
No~ | |||
Type: String Default: None |
|||||
PostSeedingCustomAction | A SQL script that will be executed after the initial seeding has finished and before the first change tracking process begins.
Note: |
No~ | |||
Type: String Default: None |
|||||
AddUpdateTimestamp | Indicates whether to add a field named [cb_sys_update_timestamp] to each destination/replica table. | No | |||
Type: String Default: None Valid value: true/ false |
|||||
TablesToExclude | A list of tables that will be excluded from replication.
Note: |
No | |||
Type: String Default: None |
|||||
StoredProceduresToExclude | A list of Stored Procedures to be excluded from replication. | No | |||
Type: String Default: None |
|||||
ReseedingSchedule | Tables w/ No Primary Keys Reseeding Schedule List. Note: Tables with No Primary Keys (PKs) will be seeded during replication but will not be tracked for changes. If adding PKs is not an option, you can include those tables in the reseeding schedule job for reseeding. See http://GetStarted.cloudbasic.net for more information. |
No | |||
NoPkTablesToInclude | Any table without a Primary Key (No PK), regardless of whether it is excluded in TablesToExclude list or not, can be included here. | No~ | |||
Type: String Default: None |
|||||
PkTablesToInclude | Only tables with Primary Keys (Pk) excluded in the TablesToExclude list can be included here. | No~ | |||
Type: String Default: None |
|||||
FromTime | Indicates the time when the replication should start. | Yes~ | |||
Type: Time Default: None Format: HH:MM:SS AM/PM |
|||||
ToTime | Indicates the time when the replication should stop. | Yes~ | |||
Type: Time Default: None Format: HH:MM:SS AM/PM |
|||||
RepeatIntervalInMinutes | Indicates the reseeding frequency in minutes. | No~ | |||
Type: Numeric Default: None |
|||||
ProcessTimeoutInMinutes | Specifies the period of time that the Change Tracking process is expected to finish in. If this period of time is exceeded, the process will be canceled. | No~ | |||
Type: Numeric Default: 30 |
|||||
SerializedProcessTimeoutInMinutes | This attribute is applicable only when Serialization (Serialize Schedule Execution) is activated manually.Specifies the period of time that the Change Tracking process is expected to finish in. If this period of time is exceeded, the process will be canceled and the next process in the queue will be started. | No~ | |||
Type: Numeric Default: 30 |
|||||
AutoSerializedProcessTimeoutInMinutes | This attribute is applicable only when Serialization (Serialize Schedule Execution) is activated by the system.Specifies the period of time that the Change Tracking process is expected to finish in. If this period of time is exceeded, the process will be canceled and the next process in the queue will be started. | No~ | |||
Type: Numeric Default: 30 |
|||||
RebuildDbReplicaIndexesSchedule | Configures a scheduled rebuild of all indexes on the replica DB in order to improve database performance and optimize execution plans. Please note that this is applicable only in SQL Server to SQL Server replication scenarios. Default: By default, the rebuilding of indexes is scheduled as a Daily task and is left Disabled. |
No | |||
Enabled | If only this tag is provided, schedule will be defaulted to [Weekly, Saturday, From 12:00:00 AM To 12:15:00 AM, 10 indexes to rebuild in parallel]. | No~ | |||
Type: String Default: None Valid value: true/ false |
|||||
Recurrence | The frequency that indexes should be rebuilt with. | No~ | |||
Type: String Default: None Valid values are: - Daily - Weekly |
|||||
WeekDays | This tag is applicable only when Recurrence has been set Weekly. Multiple days must be separated with comma, i.e. <WeekDays>Wednesday, Saturday</WeekDays>. | No~ | |||
Type: String Default: Saturday |
|||||
FromTime | Indicates the time when the replication should start. | Yes~ | |||
Type: Time Default: None Format: HH:MM:SS AM/PM |
|||||
ToTime | Indicates the time when the replication should stop. | Yes~ | |||
Type: Time Default: None Format: HH:MM:SS AM/PM |
|||||
ParallelIndexesRebuildLimit | Limits the number of indexes to be rebuilt in parallel to avoid overloading of the replica SQL Server. | No~ | |||
Type: Numeric Default: None |
~ Within the parent element
Sample Request Structure - RDS/EC2 SQL Server to AzureSQL database replication example:
POST /api/CreateReplication HTTP/1.1 Host: use.your.host.name:82 X-Amz-Content-Sha256: e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 Content-Type: application/xml X-Amz-Date: 20171115T202130Z Authorization: AWS4-HMAC-SHA256 Credential= UQOPWUVNBALABCABCABC/20171115/us-east-1/cloudbasic/aws4_request, SignedHeaders=content-length;content-type;host;x-amz-content-sha256;x-amz-date, Signature=995374189c189e8e68ed3de82c1764ca11971711fb5179eeab2b19edd883dd74 <?xml version="1.0" encoding="utf-8"?> <CreateReplicationRequest> <ReplicationMethod>ContinuousWithAutoSeeding</ReplicationMethod> <ReplicateLoginsUsersAndRoles>true</ReplicateLoginsUsersAndRoles> <ReplicateTablesOnly>false</ReplicateTablesOnly> <ParallelTablesLimit>5</ParallelTablesLimit> <RebuildIndexes>false</RebuildIndexes> <Tde> <CertificateName>my_tde_certificate</CertificateName> <KeyAlgorithm>AES 128</TdeKeyAlgorithm> </Tde> <Source> <ConnectionString>Data Source=your-source-db-server;Initial Catalog=your-source-db;Persist Security Info=False;User ID=user-name;Password=******;Connect Timeout=1280</ConnectionString> <EncryptDataInTransit>true</EncryptDataInTransit> <ChangeTrackingRetentionPeriod>2 DAYS</ChangeTrackingRetentionPeriod> <ResetChangeTracking>false</ResetChangeTracking> </Source> <Destination> <ConnectionString>Data Source=you-server-name.database.windows.net;Initial Catalog=your-database-name;Persist Security Info=False;User ID=user-name;Password=******;Connect Timeout=12800</ConnectionString> <AzureServerTier>Basic</AzureServerTier> <AzureMaxDbSize>500 MB</AzureMaxDbSize> <EncryptDataInTransit>true</EncryptDataInTransit> <ReplicateCompatibilityLevel>true</ReplicateCompatibilityLevel> <CustomDbCreateScript> <![CDATA[ CREATE DATABASE [mydb] ON PRIMARY ( NAME = DBNAME1, FILENAME = 'D:\RDSDBDATA\DBNAMEdat1.mdf', SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), ( NAME = DBNAME2, FILENAME = 'D:\RDSDBDATA\DBNAMEdat2.ndf', SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), ( NAME = DBNAME3, FILENAME = 'D:\RDSDBDATA\DBNAMEdat3.ndf', SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON ( NAME = DBNAMElog1, FILENAME = 'D:\RDSDBDATA\DBNAMElog1.ldf', SIZE = 100000KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%), ( NAME = DBNAMElog2, FILENAME = 'D:\RDSDBDATA\DBNAMElog2.ldf', SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), ( NAME = DBNAMElog3, FILENAME = 'D:\RDSDBDATA\DBNAMElog3.ldf', SIZE = 100000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS --RDS default collation] ]]> </CustomDbCreateScript> <PreSeedingCustomAction> <![CDATA[ ALTER TABLE [dbo].[table1] ALTER COLUMN [column11] CHAR(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL GO ALTER TABLE [dbo].[table2] ALTER COLUMN [column21] VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL GO ]]> </PreSeedingCustomAction> <PostSeedingCustomAction> </PostSeedingCustomAction> </Destination> <AddUpdateTimestamp>false</AddUpdateTimestamp> <StoredProceduresToExclude>spProcedure1,spProcedure2</StoredProceduresToExclude> <TablesToExclude>[dbo].[Table1],[myschema].[Table2]</TablesToExclude> <ReseedingSchedule> <NoPkTablesToInclude>[dbo].[NoPkTable1],[myschema].[NoPkTable2]</NoPkTablesToInclude> <PkTablesToInclude>[dbo].[PkTable1],[myschema].[PkTable2]</PkTablesToInclude> <FromTime>12:00:00 AM</FromTime> <ToTime>11:59:59 PM</ToTime> <RepeatIntervalInMinutes>120</RepeatIntervalInMinutes> <ProcessTimeoutInMinutes>10</ProcessTimeoutInMinutes> <SerializedProcessTimeoutInMinutes>5</SerializedProcessTimeoutInMinutes> <AutoSerializedProcessTimeoutInMinutes>5</AutoSerializedProcessTimeoutInMinutes> </ReseedingSchedule> </CreateReplicationRequest>
Request Headers
This implementation uses only request headers that are common to all operations. For more information please see the section Common Request Headers
Response Syntax:
JSON
HTTP/1.1 200 status: 200 { "replicationId": "594ed5c8-e0aa-4fd0-987f-fcfd2aaf1763", "status": "Running", "note": "" }
Response Parameters
Parameter | Description | Optional |
---|---|---|
ReplicationId | This is a GUID that uniquely identifies the newly created Replication. | No |
Status | The current status of the Replication process. Possible values include:
|
No |
Note | If the replication was created with ReplicateLoginsUsersAndRoles=true, the following message will be included in the Note: For security reasons random passwords are assigned to any replicated logins and users. Passwords need to be manually reset on the replica SQL Server after the initial database replication seeding completes. Password changes are also not automatically replicated as part of the schema replication process. |
Yes |
Error Response Syntax:
JSON
HTTP/1.1 400 status: 400 { "errors": [ "{Destination\ConnectionString}:The database must not exist. It will be created by the replication process. Delete the database or use a different name." ], "requestId": "21" }