SQL server API use cases
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.ps1Authenticating on http://172.16.160.195/resources/json/delphixLogin 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% CompletedJob COMPLETED Succesfully.
JOB JOB-820waiting for status *****Current status as of 09/05/2016 11:41:23 : RUNNING : 5.0% CompletedCurrent 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% CompletedJob 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/sessioncurl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@login.json" http://172.16.160.153/resources/json/delphix/logincurl --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.ps1Authenticating on http://172.16.160.195/resources/json/delphixLogin 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","action":"ACTION-1664"}DB Container: MSSQL_DB_CONTAINER-115 Job # JOB-822 jobState RUNNINGpercentComplete 0.0***** waiting for status *****Current status as of 09/05/2016 11:43:51 : RUNNING : 0.0% CompletedCurrent status as of 09/05/2016 11:44:01 : RUNNING : 3.0% CompletedCurrent status as of 09/05/2016 11:44:12 : RUNNING : 11.0% CompletedCurrent status as of 09/05/2016 11:44:22 : RUNNING : 18.0% CompletedCurrent 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% CompletedJob 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 OKServer: Apache-Coyote/1.1Set-Cookie: JSESSIONID=8DE0362F5BBD73E6BFA9E13FF111E78C; Path=/resources/; HttpOnlyContent-Type: application/jsonContent-Length: 179Date: 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 OKServer: Apache-Coyote/1.1Content-Type: application/jsonContent-Length: 76Date: 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/databasePS> *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 OKServer: Apache-Coyote/1.1Content-Type: application/jsonContent-Length: 4062Date: 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":"GROUP-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 OKServer: Apache-Coyote/1.1Content-Type: application/jsonContent-Length: 696Date: Thu, 16 Jun 2016 07:35:42 GMT {"type":"OKResult","status":"OK","result":{"type":"MSSqlDatabaseContainer","reference":"MSSQL_DB_CONTAINER-37","namespace":null,"name":"Vdelphix_demo","group":"GROUP-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":"MSSqlDBContainerRuntime","logSyncActive":false,"preProvisioningStatus":null,"lastRestoredBackupSetUUID":null},"os":"Windows","processor":"x86","sourcingPolicy":{"type":"SourcingPolicy","logsyncEnabled":false,"loadFromBackup":false},"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 OKServer: Apache-Coyote/1.1Content-Type: application/jsonContent-Length: 82Date: 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 ...