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

CODE


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

CODE


Job # JOB-819*****    waiting for status    *****Current status as of 09/05/2016 11:41:13 : COMPLETED : 100.0% CompletedJob COMPLETED Succesfully.

CODE


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

CODE


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:

CODE


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.

CODE


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 #

CODE


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

CODE


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 ...

CODE


$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 ...

CODE


$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

CODE


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

CODE


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 ...

CODE


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

CODE


{    "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 ...

CODE


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 ...]

CODE


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 ...

CODE


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 ...