API Actions: CreateRedshiftReplication

Request Syntax for AccessType=AccessKeys:

XML

POST /api/CreateRedshiftReplication 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
 
<CreateRedshiftReplicationRequest>
    <ParallelTablesLimit>5</ParallelTablesLimit>
	<RebuildIndexes>false</RebuildIndexes> 
	<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>
	</Source>
	<Staging>
		<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>
		<ReplicateCompatibilityLevel>true</ReplicateCompatibilityLevel>\
	</Staging>
	<Redshift>
		<AccessType>AccessKey</AccessType> 
		<AccessKey>AKIAIK5IR2D555AV4RFA</AccessKey>
		<SecretKey>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</SecretKey>
		<BucketName>cbr-test</BucketName>
		<BucketFolderName>/SCD/11232017/TestDBAll_1</BucketFolderName>
		<RegionEndpoint>us-east-1</RegionEndpoint>
		<RetentionDays>5</RetentionDays>
		<Url>your-url</Url>
		<DatabaseSchema>test1</DatabaseSchema>
		<DatabaseName>TestDBAll_3</DatabaseName>
		<Username>user-name</Username>
		<Password>********</Password>
		<ScdType>Type1</ScdType>
	</Redshift>
	<AddUpdateTimestamp>false</AddUpdateTimestamp> 
	<TablesToInclude>[dbo].[Departments],[dbo].[Managers],[dbo].[Employees]</TablesToInclude>
	<ReseedingSchedule>
		<NoPkTablesToInclude>[dbo].[NoPkTable1], [dbo].[NoPkTable2]</NoPkTablesToInclude>
		<PkTablesToInclude>[dbo].[PkTable1], [dbo].[PkTable2]</PkTablesToInclude>
		<FromTime>12:00:00 AM</FromTime>
		<ToTime>11:59:59 PM</ToTime>
		<RepeatIntervalInMinutes>120</RepeatIntervalInMinutes>
	</ReseedingSchedule>	
</CreateRedshiftReplicationRequest>

JSON

POST /api/CreateRedshiftReplication 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
 
{
	"parallelTablesLimit": 5,
	"rebuildIndexes": false,
	"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": true,
		"changeTrackingRetentionPeriod": "2 DAYS",
		"resetChangeTracking": false
	},
	"staging": {
		"connectionString": "Data Source=your-source-db-server;Initial Catalog=your-source-db;Persist Security Info=False;User ID=user-name;Password=******;Connect Timeout=12800",
		"encryptDataInTransit": true,
		"replicateCompatibilityLevel": true
	},
	"redshift": {
		"accessType": "AccessKey",
		"accessKey": "AKIAIK5IR2D555AV4RFA",
		"secretKey": "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",
		"bucketName": "cbr-test",
		"bucketFolderName": "/SCD/11232017/TestDBAll_1",
		"regionEndpoint": "us-east-1",
		"retentionDays": 5,
		"url": "your-url",
		"databaseSchema": "test1",
		"databaseName": "TestDBAll_3",
		"username": "your-user-name",
		"password": "******",
		"scdType": "Type1"
	},
	"addUpdateTimestamp": false,
	"tablesToInclude": "[dbo].[Departments],[dbo].[Managers],[dbo].[Employees]",
	"reseedingSchedule": {
		"noPkTablesToInclude": "[dbo].[NoPkTable1], [dbo].[NoPkTable2]",
		"pkTablesToInclude": "[dbo].[PkTable1], [dbo].[PkTable2]",
		"fromTime": "12:00:00 AM",
		"toTime": "11:59:59 PM",
		"repeatIntervalInMinutes": "120"
	}
}

Request Syntax for AccessType=IAMRole:

XML

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

<CreateRedshiftReplicationRequest>
   	<ParallelTablesLimit>5</ParallelTablesLimit>
	<RebuildIndexes>false</RebuildIndexes> 
	<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> 
	</Source>
	<Staging>
		<ConnectionString>Data Source=your-source-db-server;Initial Catalog=your-source-db;Persist Security Info=False;User ID=user-name;Password=******;Connect Timeout=12800</ConnectionString>
		<EncryptDataInTransit>true</EncryptDataInTransit>
		<ReplicateCompatibilityLevel>true</ReplicateCompatibilityLevel>
	</Staging>
	<Redshift>
		<AccessType>IAMRole</AccessType> 
		<AccountId>AKIAIK5IR2D555AV4RFA</AccountId>
		<RoleName>XrMEaWGywbZtPbtWm1TBmwUxkV5Ovm6tJEsuy+4X</RoleName>
		<BucketName>cbr-test</BucketName>
		<BucketFolderName>/SCD/11232017/TestDBAll_1</BucketFolderName>
		<RegionEndpoint>us-east-1</RegionEndpoint>
		<RetentionDays>5</RetentionDays>
		<Url>your-url</Url>
		<DatabaseSchema>test1</DatabaseSchema>
		<DatabaseName>TestDBAll_3</DatabaseName>
		<Username>your-user-name</Username>
		<Password>********</Password>
		<ScdType>Type1</ScdType>
	</Redshift>
	<AddUpdateTimestamp>false</AddUpdateTimestamp> 
	<TablesToInclude>[dbo].[Departments],[dbo].[Managers],[dbo].[Employees]</TablesToInclude>
	<ReseedingSchedule>
		<NoPkTablesToInclude>[dbo].[NoPkTable1], [dbo].[NoPkTable2]</NoPkTablesToInclude>
		<PkTablesToInclude>[dbo].[PkTable1], [dbo].[PkTable2]</PkTablesToInclude>
		<FromTime>12:00:00 AM</FromTime>
		<ToTime>11:59:59 PM</ToTime>
		<RepeatIntervalInMinutes>120</RepeatIntervalInMinutes>
	</ReseedingSchedule>
</CreateRedshiftReplicationRequest>

JSON

POST /api/CreateRedshiftReplication 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
 
{
	"parallelTablesLimit": 5,
	"rebuildIndexes": false,
	"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": true,
		"changeTrackingRetentionPeriod": "2 DAYS",
		"resetChangeTracking": false
	},
	"staging": {
		"connectionString": "Data Source=your-source-db-server;Initial Catalog=your-source-db;Persist Security Info=False;User ID=user-name;Password=******;Connect Timeout=12800",
		"encryptDataInTransit": true,
		"replicateCompatibilityLevel": true
	},
	"redshift": {
		"accessType": "IAMRole",
		"accountId": "AKIAIK5IR2D555AV4RFA",
		"roleName": "XrMEaWGywbZtPbtWm1TBmwUxkV5Ovm6tJEsuy+4X",
		"bucketName": "cbr-test",
		"bucketFolderName": "/SCD/11232017/TestDBAll_1",
		"regionEndpoint": "us-east-1",
		"retentionDays": 5,
		"url": "your-url",
		"databaseSchema": "test1",
		"databaseName": "TestDBAll_3",
		"username": "your-user-name",
		"password": "******",
		"scdType": "Type1"
	},
	"addUpdateTimestamp": false,
	"tablesToInclude": "[dbo].[Departments],[dbo].[Managers],[dbo].[Employees]",
	"reseedingSchedule": {
		"noPkTablesToInclude": "[dbo].[NoPkTable1], [dbo].[NoPkTable2]",
		"pkTablesToInclude": "[dbo].[PkTable1], [dbo].[PkTable2]",
		"fromTime": "12:00:00 AM",
		"toTime": "11:59:59 PM",
		"repeatIntervalInMinutes": "120"
	}
}

Request Parameters

Parameter		Description	Required
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: Higher value will decrease the total time to complete the seeding, but might affect the performance of the source RDS/SQL Server depending on server type and size. For AWS RDS, value of 10 is recommended for RDS type *.Large or below.	Yes
ParallelTablesLimit		Type: Integer Default: None	Yes
RebuildIndexes		Indicates whether the indexes should be rebuilt in the destination database instance. Note: Rebuilding of indexes upon completion of seeding might be necessary when deadlocks occur in the destination database.	Yes
RebuildIndexes		Type: String Default: None Valid value: true/ false	Yes
Source
	ConnectionString	A valid connection string to the source database instance.	Yes
	ConnectionString	Type: String Default: None	Yes
	EncryptDataInTransit	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	Yes
	EncryptDataInTransit	Type: String Default: None Valid value: true/ false	Yes
	ChangeTrackingRetentionPeriod	Specifies the period that Change Tracking data is saved for before being automatically purged.	Yes
	ChangeTrackingRetentionPeriod	Type: String Default: None Valid values: 12 Hours, 1 Day, 2 Days, 3 Days, 4 Days, 5 Days, 6, Days, 7 Days, 8 Days	Yes
Staging
	ConnectionString	A valid connection string to the source database instance.	Yes
	ConnectionString	Type: String Default: None	Yes
	EncryptDataInTransit	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	Yes
	EncryptDataInTransit	Type: String Default: None Valid value: true/ false	Yes
	ReplicateCompatibilityLevel	Replicate the compatibility level of the source database. Note: Valid values for SQL Server 2014 are 100, 110 and 120. Valid values for SQL Server 2016 are 100, 110, 120 & 130.	Yes
	ReplicateCompatibilityLevel	Type: String Default: None Valid value: true/ false	Yes
Redshift
	AccessType	The type of access can be either AccessKey or IAMRole.	Yes
	AccessType	Type: String Default: None Valid value: AccessKey/ IAMRole	Yes
	AccessKey	When the AccessType is set to AccessKey this field must contain the AWS AccessKey that the CloudBasic process will use to write to the specified S3 bucket.	Yes
	AccessKey	Type: String Default: None	Yes
	SecretKey	When the AccessType is set to AccessKey this field must contain the AWS SecretKey that the CloudBasic process will use to write to the specified S3 bucket.	Yes
	SecretKey	Type: String Default: None	Yes
	AccountId	When the AccessType is set to IAMRole this field must contain the AWS AccountID that the CloudBasic process will use to write to the specified S3 bucket.	Yes
	AccountId	Type: String Default: None	Yes
	RoleName	When the AccessType is set to IAMRole this field must contain the AWS RoleName that the CloudBasic process will use to write to the specified S3 bucket.	Yes
	RoleName	Type: String Default: None	Yes
	BucketName	The AWS S3 bucket name that the CloudBasic process will write to.	Yes
	BucketName	Type: String Default: None	Yes
	BucketFolderName	The folder in the AWS S3 bucket where the CloudBasic process will write.	Yes
	BucketFolderName	Type: String Default: None	Yes
	RegionEndpoint	The name of the AWS Region where the S3 bucket is located.	Yes
	RegionEndpoint	Type: String Default: None	Yes
	RetentionDays	Number of the change tracking files will be kept for before the CloudBasic process deletes them.	Yes
	RetentionDays	Type: Integer Default: None	Yes
	Url	The AWS Redshift cluster end point.	Yes
	Url	Type: String Default: None	Yes
	DatabaseSchema	The Schema name of the Redshift database where data should be loaded.	Yes
	DatabaseSchema	Type: String Default: None	Yes
	DatabaseName	The Redshift database name where data should be loaded.	Yes
	DatabaseName	Type: String Default: None	Yes
	Username	The username that the CloudBasic service will use when connecting to the AWS Redshift service.	Yes
	Username	Type: String Default: None	Yes
	Password	The password that the CloudBasic service will use when connecting to the AWS Redshift service.	Yes
	Password	Type: String Default: None	Yes
	ScdType	The Slowly Changing Dimension Type that this replication is implementing.	Yes
	ScdType	Type: String Default: None Valid value: Type1/ Type2	Yes
AddUpdateTimestamp		Indicates whether to add a field named [cb_sys_update_timestamp] to each destination/replica table.	Yes
AddUpdateTimestamp		Type: String Default: None Valid value: true/ false	Yes
TablesToInclude		A list of tables that have no Primary Keys in the source database but have to be replicated in the destination database. Pass 'All' to include all tables <tablestoinclude>All</tablestoinclude>.	Yes
TablesToInclude		Type: String Default: None	Yes
ReseedingSchedule		Tables with 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.
	NoPkTablesToInclude	Any table without a Primary Key (No PK), regardless of whether it is excluded in <tablestoinclude> list or not, can be included here.	Yes
	NoPkTablesToInclude	Type: String Default: None	Yes
	PkTablesToInclude	Only tables with Primary Keys (Pk) NOT included in the <tablestoinclude> list can be included here.	Yes
	PkTablesToInclude	Type: String Default: None	Yes
	FromTime	Starting time of the period when the reseeding process is allowed to run.	Yes
	FromTime	Type: String Default: None	Yes
	ToTime	Ending time of the period when the reseeding process is allowed to run.	Yes
	ToTime	Type: String Default: None	Yes
	RepeatIntervalInMinutes	Specifies the frequency of execution of the reseeding process in minutes.	Yes
	RepeatIntervalInMinutes	Type: Integer Default: None	Yes

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:

XML

HTTP/1.1 200
status: 200
 
<?xml version="1.0" encoding="utf-8"?>
<CreateRedshiftReplicationResponse>
	<ReplicationId>594ed5c8-e0aa-4fd0-987f-fcfd2aaf1763</ReplicationId>
	<Status>Running</Status>
	<Note />
</CreateRedshiftReplicationResponse>

JSON

HTTP/1.1 200
status: 200
 
{
	"replicationId": "594ed5c8-e0aa-4fd0-987f-fcfd2aaf1763",
	"status": "Running",
	"note": ""
}

Response Parameters

Parameter	Description	Optional
ReplicationId	A GUID that uniquely identifies the newly created Replication.	No
Status	The current status of the Replication process. Possible values include: Running RunningWithErrors RunningWithWarnings Failed Success CompletedWithErrors CompletedWithWarnings	No
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

Parameter

Description

Optional

ReplicationId

A GUID that uniquely identifies the newly created Replication.

Status

The current status of the Replication process. Possible values include:

Running
RunningWithErrors
RunningWithWarnings
Failed
Success
CompletedWithErrors
CompletedWithWarnings

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:

XML

HTTP/1.1 400
status: 400
 
<?xml version="1.0" encoding="utf-8"?>
<ErrorResponse> 
  <RequestId>request-id</RequestId> 
  <Errors> 
    <Error> <Message>Error message 1</Message> </Error> 
    <Error> <Message>Error message 2</Message> </Error> 
  </Errors> 
</ErrorResponse>

JSON

HTTP/1.1 400
status: 400

{
   "errors": [
       "{Redshift\BucketName}: Invalid value for BucketName"
 	],
	"requestId": 20
}