SQL Server Link/Ingest Environment dSource
For the Window Target environment, the dSource delphixdb in MSSQLSERVER instance will be linked/ingested into the Delphix Engine. It will appear in the Windows_Source group below.
Filename: link_sqlserver.ps1
PS> . .\link_sqlserver.ps1
Authenticating on http://172.16.160.195/resources/json/delphix
Login Successful ...
group reference: GROUP-34
sourceconfig reference: MSSQL_SINGLE_CONFIG-26
env reference: WINDOWS_ HOST_ENVIRONMENT-7
repository reference: MSSQL_INSTANCE-4
database link API Results: {"type":"OKResult","status":"OK","result":"MSSQL_DB_CONTAINER-114","job":"JOB-819","action":
"ACTION-1659"}
DB Container: MSSQL_DB_CONTAINER-114
Job # JOB-819 ***** waiting for status ***** Current status as of 09/05/2016 11:41:13 : COMPLETED : 100.0% Completed Job COMPLETED Succesfully.
JOB JOB-820 waiting for status ***** Current status as of 09/05/2016 11:41:23 : RUNNING : 5.0% Completed Current status as of 09/05/2016 11:41:44 : RUNNING : 9.0% Completed
Current status as of 09/05/2016 11:41:54 : RUNNING : 56.0% Completed Job COMPLETED Succesfully. Done ...
Successful dSource linked/ingested into the Delphix Engine.
SQL Server Provision
The example below is done from the command line once you know the parameters and reference object names.
Filename: windows_sqlserver_provision.txt
Create these 3 JSON text files:
session.json
{
"type": "APISession",
"version": {
"type": "APIVersion",
"major": 1,
"minor": 5,
"micro": 3
}
}
login.json
{
"type": "LoginRequest",
"username": "delphix_admin",
"password": "delphix"
}
provision.json
{
"type": "MSSqlProvisionParameters",
"container": {
"type": "MSSqlDatabaseContainer",
"name": "Vbitt00",
"group": "GROUP-36",
"sourcingPolicy": {
"type": "SourcingPolicy",
"loadFromBackup": false,
"logsyncEnabled": false
},
"validatedSyncMode": "TRANSACTION_LOG"
},
"source": {
"type": "MSSqlVirtualSource",
"operations": {
"type": "VirtualSourceOperations",
"configureClone": [],
"postRefresh": [],
"postRollback": [],
"postSnapshot": [],
"preRefresh": [],
"preSnapshot": []
}
},
"sourceConfig": {
"type": "MSSqlSIConfig",
"linkingEnabled": false,
"repository": "MSSQL_INSTANCE-1",
"databaseName": "Vbitt00",
"recoveryModel": "SIMPLE",
"instance": {
"type": "MSSqlInstanceConfig",
"host": "WINDOWS_HOST-1"
}
},
"timeflowPointParameters": {
"type": "TimeflowPointSemantic",
"container": "MSSQL_DB_CONTAINER-23",
"location": "LATEST_SNAPSHOT"
}
}
This works on Windows Powershell Command Prompt
Use curl, curl.exe or modify the default alias.
curl --insecure -c cookies.txt -i -X POST -H "Content-Type: application/json" -d "@session.json" http://172.16.160.153/resources/json/delphix/session curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@login.json" http://172.16.160.153/resources/json/delphix/login curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@provision.json" http://172.16.160.153/resources/json/delphix/database/provision
Plug in the returned JOB #
curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.153/resources/json/delphix/notification?channel=JOB-428
Get Example
curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.153/resources/json/delphix/system
Complete example.
Provision the newly created delphixdb dSource in the Windows_Source group to a virtual database VBITT in the Windows_Target group.
Filename: provision_sqlserver.ps1
Variables ...
$nl = [Environment]::NewLine $BaseURL = " http://172.16.160.195/resources/json/delphix " $cookie = "cookies.txt" $delphix_user = "delphix_admin" $delphix_pass = "delphix" . . .
Required for Provisioning Virtual Database ...
$SOURCE_SID="delphixdb" # dSource name used to get db container reference value $VDB_NAME="VBITT" # Delphix VDB Name $TARGET_GRP="Windows_Target" # Delphix Engine Group Name $TARGET_ENV="Window Target" # Target Environment used to get repository reference value $TARGET_REP="MSSQLSERVER" # Target Environment Repository / Instance name ############################################# # NO CHANGES REQUIRED BELOW THIS POINT # #############################################
Sample Run Output
PS> . .\provision_sqlserver.ps1
Authenticating on http://172.16.160.195/resources/json/delphix
Login Successful ...
group reference: GROUP-37
container reference: MSSQL_DB_CONTAINER-114
env reference: WINDOWS_HOST_ENVIRONMENT-7
repository reference: MSSQL_INSTANCE-4
database provision API Results: {"type":"OKResult","status":"OK","result":"MSSQL_DB_CONTAINER-115","job":"JOB-822","act
ion":"ACTION-1664"}
DB Container: MSSQL_DB_CONTAINER-115
Job # JOB-822
jobState RUNNING
percentComplete 0.0
***** waiting for status *****
Current status as of 09/05/2016 11:43:51 : RUNNING : 0.0% Completed
Current status as of 09/05/2016 11:44:01 : RUNNING : 3.0% Completed
Current status as of 09/05/2016 11:44:12 : RUNNING : 11.0% Completed
Current status as of 09/05/2016 11:44:22 : RUNNING : 18.0% Completed
Current status as of 09/05/2016 11:44:32 : RUNNING : 18.0% Completed
Current status as of 09/05/2016 11:44:52 : RUNNING : 75.0% Completed Job COMPLETED Succesfully. Done ...
SQL Server Refresh
The following are curl commands that can be issued from the Powershell command line. For inclusion within a Powershell script, see the masking example, masking.ps1.
Filename: windows_sqlserver_refresh.txt
MS SQL Server Refresh Example ...
Session ...
curl --insecure -c cookies.txt -i -X POST -H "Content-Type: application/json" -d "@session.json" http://172.16.160.179/resources/json/delphix/session
Filename: session.json
{
"type": "APISession",
"version": {
"type": "APIVersion",
"major": 1,
"minor": 5,
"micro": 3
}
}
PS> *curl --insecure -c cookies.txt -i -X POST -H "Content-Type: application/json" -d "@session.json" http://172.16.160.179/resources/json/delphix/session*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Set-Cookie: JSESSIONID=8DE0362F5BBD73E6BFA9E13FF111E78C; Path=/resources/; HttpOnly
Content-Type: application/json
Content-Length: 179
Date: Thu, 16 Jun 2016 07:24:34 GMT
{"type":"OKResult","status":"OK","result":{"type":"APISession","version":{"type":"APIVersion","major":1,"minor":5,"micro":3},"locale":null,"client":null},"job":null,"action":null}
PS>
Login ...
curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@login.json" http://172.16.160.179/resources/json/delphix/login
Filename:login.json
{
"type": "LoginRequest",
"username": "delphix_admin",
"password": "delphix"
}
PS> *curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@login.json" http://172.16.160.179/resources/json/delphix/login*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Content-Length: 76
Date: Thu, 16 Jun 2016 07:25:39 GMT
{"type":"OKResult","status":"OK","result":"USER-2","job":null,"action":null}
PS C:\Users\Administrator>
List Databases ...
curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.179/resources/json/delphix/database
PS> *curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.179/resources/json/delphix/database*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Content-Length: 4062
Date: Thu, 16 Jun 2016 07:27:14 GMT
{"type":"ListResult","status":"OK","result":[...
...
{"type":"MSSqlDatabaseContainer","reference":"MSSQL_DB_CONTAINER-37","namespace":null,"name":"Vdelphix_demo","group":"GR
OUP-35","provisionContainer":"MSSQL_DB_CONTAINER-36","creationTime":"2016-06-16T07:09:06.222Z","currentTimeflow":"MSSQL_
TIMEFLOW-38","previousTimeflow":"MSSQL_TIMEFLOW-37","description":null,"runtime":
...
{"type":"So{"type":"MSSqlDatabaseContainer","reference":"MSSQL_DB_CONTAINER-36","namespace":null,"name":"delphix_demo","
group":"GROUP-35","provisionContainer":null,"creationTime":"2016-06-16T07:07:49.939Z","currentTimeflow":"MSSQL_TIMEFLOW-
36","previousTimeflow":null,"description":"","runtime":
...
...}],"job":null,"action":null,"total":6,"overflow":false}
PS>
Need Reference Object from Database Information ...
For Parent Source Database delphix_demo, reference object is MSSQL_DB_CONTAINER-36
For Virtual Database Vdelphix_demo, reference object is MSSQL_DB_CONTAINER-37
[Optional: Get Database Info ...]
curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.179/resources/json/delphix/database/MSSQL_DB_CONTAINER-37
PS> *curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.179/resources/json/delphix/database/MSSQL_DB_CONTAINER-37*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Content-Length: 696
Date: Thu, 16 Jun 2016 07:35:42 GMT
{"type":"OKResult","status":"OK","result":{"type":"MSSqlDatabaseContainer","reference":"MSSQL_DB_CONTAINER-37","namespac
e":null,"name":"Vdelphix_demo","group":"GROUP-35","provisionContainer":"MSSQL_DB_CONTAINER-36","creationTime":"2016-06-1
6T07:09:06.222Z","currentTimeflow":"MSSQL_TIMEFLOW-38","previousTimeflow":"MSSQL_TIMEFLOW-37","description":null,"runtim
e":{"type":"MSSqlDBContainerRuntime","logSyncActive":false,"preProvisioningStatus":null,"lastRestoredBackupSetUUID":null
},"os":"Windows","processor":"x86","sourcingPolicy":{"type":"SourcingPolicy","logsyncEnabled":false,"loadFromBackup":fal
se},"performanceMode":"DISABLED","delphixManaged":true,"masked":false},"job":null,"action":null}
PS>
Refresh Vdelphix_demo using parent delphix_demo (MSSQL_DB_CONTAINER-36) with the latest timecard ...
curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@refresh.json" http://172.16.160.179/resources/json/delphix/database/MSSQL_DB_CONTAINER-37/refresh
=== POST /resources/json/delphix/database/MSSQL_DB_CONTAINER-37/refresh ===
refresh.json
{
"type": "RefreshParameters",
"timeflowPointParameters": {
"type": "TimeflowPointSemantic",
"container": "MSSQL_DB_CONTAINER-36"
}
}
PS> *curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@refresh.json" http://172.16.160.179/resources/json/delphix/database/MSSQL_DB_CONTAINER-37/refresh*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Content-Length: 82
Date: Thu, 16 Jun 2016 07:40:44 GMT
{"type":"OKResult","status":"OK","result":"","job":"JOB-60","action":"ACTION-167"}
PS>
[ Observer Delphix GUI Action ]
Done with SQL Server VDB Refresh ...