API Actions: AlterReplication

API Actions: AlterReplication

 

Request Syntax:

JSON

POST /api/AlterReplication 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

{
	"replicationId": "594ed5c8-e0aa-4fd0-987f-fcfd2aaf1763",
        "replicationName": "",
	"sourceConnectionString": "Data Source=your-source-db-server;Initial Catalog=your-source-db;Persist Security Info=False;User ID=user-name;Password=******",
	"destinationConnectionString": "Data Source=your-destination-db-server;Initial Catalog=your-destination-db;Persist Security Info=False;User ID=user-name;Password=******",
	"dataReplication": "true",
	"schemaReplication": "false",
        "dynamicTablesDiscovery": "true", -- 12.318 and above
        "dynamicViewsDiscovery": "true",  -- 12.318 and above
        "addNewTablesToExcusionList": "false", -- 12.318 and above
        "dynamicTablesExclusionRules" : "^\\[dbo\\].\\[archived_*]$, ^\\[dbo\\].\\[OLD_*]$", -- 12.318 and above 
        "replicateTablesOnly": false, // 12.320 and above 
        "dropTablesFoundOnReplicaOnly": "true" // 12.202 and above. /api/CreateReplication sets this value to default=false
        "replicateLoginsUsersRoles": "true" // 12.202 and above 
        "dropLoginsUsersRolesFoundOnReplicaOnly: "true" // 12.202 and above. /api/CreateReplication sets this value to default=false
	"parallelTablesLimit": "10", //Deprecated in 12.51 and above (replaced by both parallelReplicationLimit and parallelDetectionLimi)
        "parallelReplicationLimit": "10", //Supported in 12.51 and above. Pass this parameter only if altering parallel tables limit (max number of tables replicated concurrently; lower number decreases workload on source & destination servers but might increase latency
        "parallelDetectionLimit": "10", //Supported in 12.51 in above. Pass this parameter only if altering parallel tables change detection limit (max number of tables change detected concurrently; lower number decreases workload on source & destination servers but might increase latency
	"changeTrackingBatchLimit": "500000",
        "bulkCopyBatchSize": "100000", -- 12.318 and above
        "maxOperationRetries": "3", -- 12.320 and above; default = 3 
   	"stagingBufferType": "ReplicaDb", //default = TempDb

        "replicateTablesInOrderFollowingDependencies": true,
        "tablesOrderingTimeoutInSeconds": "1",

        "reseedTableIfAmbiguousSchemaChange": true, //12.321 and above
        "reseedTableIfAmbiguousSchemaChangeRowsCountThreshold": "500000", // 12.231 and above

	"ctProcessTimeoutInMinutes": "60",
        "changesDetectQueryTimeoutInSeconds": "1800", -- 12.318 and above
        "changesExtractQueryTimeoutInSeconds": "1800", -- 12.318 and above
        "changesMergeQueryTimeoutInSeconds": "1800", -- 12.318 and above
	"serializedCtProcessTimeoutInMinutes": "60",
	"autoSerializedCtProcessTimeoutInMinutes": "60",
	"isContinuous": "false",
	"repeatIntervalInMinutes": "5",
	"scheduleIntervalTimeFrom": "12:20:00 AM",
	"scheduleIntervalTimeTo": "11:59:59 PM",
	"logErrorsOnly": "false",
        "schemaCompareIntervalInMinutes": 0, //default value set by /api/CreateReplication is 60 mins. 0 = continuous.
        "clusterAffinity": "ExecuteOnLocal", //applicable when the instance is joined to HA cluster. Default value set by /api/CreateReplication is LoadBalanced. Valid values are (LoadBalanced, ExecuteOnLocal, ExecuteOnRemote), where Local is the HA cluster instance against which the api call is submitted. 

        //Below tags have been depricated in 12.322, moved to a different tag group with tag name adjustments
	//"tablesToExclude": "[dbo].[table1], [myschema].[table2], [dbo].[tableN]", 
        //"tablesToExcludeDisableCt": true, 
        //"tablesToExcludeDisableCdc": true, 
        //Replaced by “addSourceObjectsToExclusionList”: {“tables”: “[dbo].[table1], [myschema].[table2], [dbo].[tableN]“, "tablesDisableCt": true,"tablesDisableCdc": true} 

        //12.322 and above (below tag group replaces a list of deprecated tags)
        “addSourceObjectsToExclusionList”: {
           //object=tables
           //tablesDisableCt & tablesDisableCdc below are mutually exclusive, not required. Default values = False
           "tablesDisableCt": false, // default = False. Value=True would reqeuest disabling of Change Tracking (for Change Tracking based job) for all tables lited below or selected based on the the RegEx name selection rules. Incompatible with "tablesDisableCdc".
           "tablesDisableCdc": false, // default = False. Value=True would reqeuest disabling of Change Data Capture (CDC), for CDC based jobs) for all tables lited below or selected based on the the RegEx name selection rules. Incompatible with "tablesDisableCt".

           “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=StoredProcedure
           “storedProcedures”: “[dbo].[sp_storedproc1],[dbo].[sp_storedproc2], [dbo].[sp_storedprocN]",

           //object=trigger
           "triggers" : "[dbo].[trigger1], [dbo].[trigger1]"
        }

        //12.322 and above
        “removeSourceObjectsFromExclusionList”: {
           //object=tables
           //tablesDisableCt & tablesDisableCdc below are mutually exclusive, not required. Default values = False
           "tablesDisableCt": false, // default = False. Value=True would request disabling of Change Tracking (for Change Tracking based job) for ALL TABLES REMAINING in exclusion list. Incompatible with "tablesDisableCdc".
           "tablesDisableCdc": false, // default = False. Value=True would request disabling of Change Data Capture (CDC), for CDC based jobs, for ALL TABLES REMAINING in exclusion list. Incompatible with "tablesDisableCt".

           “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=StoredProcedure
           “storedProcedures”: “[dbo].[sp_storedproc1],[dbo].[sp_storedproc2], [dbo].[sp_storedprocN]",

           //object=trigger
           "triggers" : "[dbo].[trigger1], [dbo].[trigger1]"
        }

        //12.322 and above
        "addReplicaObjectsToExclusionList" : {
           "views": "view3",
           "storedProcedures" : "storedproc3",
           "triggers" : "trigger3",
           "indexes" : "index3",
           "functions" : "func3",
           "roles" : "role3",
           "users" : "user3"
        }

        //12.322 and above
        "removeReplicaObjectsFromExclusionList" : {
           "views": "view3",
           "storedProcedures" : "storedproc3",
           "triggers" : "trigger3",
           "indexes" : "index3",
           "functions" : "func3",
           "roles" : "role3",
           "users" : "user3"
        }

         // Below tag group (introduced in 12.318) is not compatible with “addSourceObjectsToExclusionList”, "removeSourceObjectsFromExclusionList", "addReplicaObjectsToExclusionList", "removeReplicaObjectsFromExclusionList" tag groups
        "moveTablesToReplicationJob" :{ 
          "targetReplicationId": "XXXXX-bca1-4c60-b225-3bb739990f88", 
          "createSecondaryJob" : "false",
          "secondaryJobName" : "PrimaryJobName (secondary)",
          "tables" : "[dbo].[table1], [myschema].[table1], [dbo.tableN]",
          "tablesWithoutPk" : "true", //incompatible with"tablesWithoutUniqueIndexWithoutPk", "tablesWithUniqueIndexWithoutPk" 
          "tablesWithoutUniqueIndexWithoutPk" : "true", //incompatible with "tablesWithoutPk"
          "tablesWithUniqueIndexWithoutPk" : "true", //incompatible with "tablesWithoutPk"
          "tablesWithBinaryColumnTypes" : "true",// allows moving of all tables containing column of binary types (varbinary, binary, image, geometry ..)
          "tablesMatchingRules" : "^\\[dbo].\\[archived.*]$, ^\\[dbo].\\[old_.*]$, ^\\[dbo].\\[tmp.*]$" 
        } 

	"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"
	},
	"rebuildDbReplicaIndexesSchedule: {
		"enabled": true,
		"recurrence": Weekly,
		"weekDays": Saturday,
		"fromTime": 12:00:00 AM,
		"toTime": 12:15:59 AM,
		"parallelIndexesRebuildLimit": 10
	}
}

 

Request Parameters

Parameter Description Required
ReplicationId The ReplicationID returned in the CreateReplication response. Yes
Type: String
Default: None
ReplicationName A friendly name of the replication job. It cannot be provided in CreateReplication. The name is defaulted to source DB name during initial creation of the job. No
Type: String
Default: None
SourceConnectionString A valid ConnectionString to the source database.

Note:
Populate this parameter only when altering the source connection string; i.e. changing login and/or password.

 No
Type: String
Default: None
DestinationConnectionString A valid ConnectionString to the destination database.

Note:
Populate this parameter only when altering the destination/ replica connection string; i.e. changing login and/or password.

 No
Type: String
Default: None
DataReplication Populate this parameter only when turning off/on the data replication.Note:
Important: if you need to stop/start all replications, use the StopReplicationService/ StartReplicationService commands instead.		 No
Type: String
Default: None
Valid value: true/ false
SchemaReplication Populate this parameter only when turning off/on the schema replication. No
Type: String
Default: None
Valid value: true/ false
AddNewTablesToExcusionList Setting this parameter to thru would cause newly discovered tables to be added to exclusion list. /API/CreateReplication sets this to default=false. No
Type: String
Default: False
Valid value: true/ false
DropTablesFoundOnReplicaOnly (12.202 and above) Setting this parameter to True would cause tables dropped on the source to be dropped on the replica too. /API/CreateReplication sets this to default=false (default/recommended for DR use cases, unless there is frequent dropping and creating/recreating of tables). No
Type: String
Default: False
Valid value: true/ false
AddNewTablesToExcusionList Setting this parameter to thru would cause newly discovered tables to be added to exclusion list. /API/CreateReplication sets this to default=false. No
Type: String
Default: False
Valid value: true/ false
AddNewTablesToExcusionList Setting this parameter to thru would cause newly discovered tables to be added to exclusion list. /API/CreateReplication sets this to default=false. No
Type: String
Default: False
Valid value: true/ false
ParallelTablesLimit Populate this parameter only when altering the number of tables being replicated in parallel.Note:
A lower number decreases the workload on source & destination servers, but it might increase latency.		 No
Type: Integer
Default: None
ChangeTrackingBatchLimit Populate this parameter only when altering the change tracking batch limit.Note:
This is the number of records per table extracted at once. This parameter controls the workload on the source server when there is a high volume of changes (i.e. 1mil per min bulk load).		 No
Type: Integer
Default: 500,000
CtProcessTimeoutInMinutes Change Tracking (Ct) Time out (in minutes). Populate this parameter only when altering the change tracking time out.Note:
If a Ct process runs longer than the specified time, it will be canceled. If Ct is canceled due to an exceeded timeout, the next Ct processes will run with doubled previous timeout. If canceled again, timeout will be doubled again. If timeout is exceeded for a 3rd time, then the Ct process will be launched without a timeout. Upon successful Ct run, the timeout will be adjusted back to the originally specified value.		 No
Type: Integer
Default: 30
SerializedCtProcessTimeoutInMinutes When Serialization is activated manually, this parameter indicates the number of minutes the Ct process is allowed to run by itself before another Ct process is allowed to start in parallel.Note:
For more information, please see the <SerializeReplications> and <ParallelDatabaseReplicationsLimit> parameters in the AlterConfiguration API method.		 No
Type: Integer
Default: 30
AutoSerializedCtProcessTimeoutInMinutes When Serialization is system activated, this parameter indicates the number of minutes the Ct process is allowed to run by itself before another Ct process is allowed to start in parallel.Note:
In scenarios where a large number of concurrent DB replications are running, the CloudBasic service can become over saturated and go through a recycle. This will restart the Ct process in a Serialized mode.		 No
Type: Integer
Default: 30
IsContinuous Controls how the Ct process is scheduled.Note:
By default, all replications run in a continuous mode in order to minimize latency. In scenarios where the replication process must run on a schedule, this value must be set to false and the frequency of execution must be provided in the RepeatIntervalInMinutes parameter.		 No
Type: String
Default: true
Valid value: true/ false
RepeatIntervalInMinutes Specifies the frequency of execution of the Ct process in minutes.Note:
Applicable only if IsContinuous is set to false.		 No
Type: Integer
Default: None
ScheduleIntervalTimeFrom Starting time of the period when the Ct process is allowed to run.Note:
Set this parameter only if the replication process should not run continuously.		 No
Type: String
Default: None
ScheduleIntervalTimeTo Ending time of the period when the Ct process is allowed to run.Note:
Set this parameter only if the replication process should not run continuously.		 No
Type: String
Default: None
IsLogErrorsOnly Controls the granularity of the run-time logs.Note:
By default only errors and warnings are logged. Set the value to false when you want detailed logging or when troubleshooting performance and latency issues.		 No
Type: String
Default: true
Valid value: true/ false
SchemaCompareIntervalInMinutes Controls whether schema change tracking is continual or occurs at specified interval.

Default value set by /api/CreateReplication is 60 mins.

Set the value to 0 (zero) to make schema change tracking continuous.

 No
Type: String
Default: 60 mins
Valid value: 0 (continuous) to 1440 (24 hours)
ClusterAffinity Applicable to instances which are included in HA cluster. Controls whether the replication job is executed LoadBalanced (default) on both instances in the cluster, or if it is assigned to one primary  instance in the cluster No
Type: String
Default: LoadBalanced
Valid value: LoadBalanced, ExecuteOnLocal, ExecuteOnRemote
TablesToExclude A list of tables to be excluded from replication.Note:
Excluded tables will be removed only from the ongoing change tracking and will not be removed from the respective destination database.		 No
Type: String
Default: None
StoredProceduresToExclude A list of stored procedures to be excluded from the existing replication. No
Type: String
Default: None
IndexesToExclude A list of indexes to be excluded from the existing replication. No
Type: String
Default: None
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 ReplicaDB value to force CloudBasic to use the destination database for staging storage. No
Type: String
Default: TempDb
Valid values are:
- ReplicaDb
- TempDb
ReseedingSchedule Creates or modifies an existing reseeding schedule.Note:
Note: Tables w/ 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 The list of tables with no Primary Keys that will be included in the reseeding.

Note:
Any table without a Primary Key (No PK), regardless of whether it is excluded in the <TablesToExclude> list or not, can be included here.
Type: String
Default: None
PkTablesToInclude The list of tables with Primary Keys that will be included in the reseeding.

Note:
Only tables with Primary Keys (Pk) excluded in the <TablesToExclude> list can be included here.
Type: String
Default: None
FromTime Starting time of the period when the reseeding process is allowed to run.
Type: String
Default: None
ToTime Ending time of the period when the reseeding process is allowed to run.
Type: String
Default: None
RepeatIntervalInMinutes Specifies the frequency of execution of the reseeding process in minutes.
Type: Integer
Default: None
ProcessTimeoutInMinutes Specifies the maxim amount of time (in minutes) the reseeding process is allowed to run for.
Type: Integer
Default: None
SerializedProcessTimeoutInMinutes When Serialization is activated manually, this parameter indicates the number of minutes the reseeding process is allowed to run by itself before another reseeding process is allowed to start in parallel.

Note:
For more information, please see the <SerializeReplications> and <ParallelDatabaseReplicationsLimit> parameters in the AlterConfiguration API method
Type: Integer
Default: None
AutoSerializedProcessTimeoutInMinutes When Serialization is system activated, this parameter indicates the number of minutes the Ct process is allowed to run by itself before another Ct process is allowed to start in parallel.

Note:
In scenarios where a large number of concurrent DB replications are running, the CloudBasic service can become oversaturated and go through a recycle. This will restart the Ct process in a Serialized mode.
Type: Integer
Default: None
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

 

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:

HTTP/1.1 200
status: 200

 

Response Parameters

None

 

Error Response Syntax:

JSON

HTTP/1.1 400
status: 400

{
   "errors": [
        "{Source\ConnectionString}:Source connection string status:InaccessibleDatabase",
		"{Destination\ConnectionString}:Destination connection string status:InaccessibleDatabase"
	],
	"requestId": 20
}