CLI cookbook: provisioning the SQL Server AG VDB

This topic describes how to provision a SQL Server AG VDB using the command line interface. 

Prerequisites

You will need the following information: 

• The name of the AG VDB you want to create 

• The group in which to create the AG VDB 

• The SQL Server database name for the AG VDB 

• The source dSource or VDB from which you wish to provision

• The semanticLocation, LSN, or timestamp of the point you want to provision from. You can run these commands to get the list of snapshots or timeflow ranges: :

  CODE

  snapshot list database=dexample 
  snapshot list timeflow=dexample 
  snapshot list fromDate="2020-03-01T00:00:00.000Z" toDate="2020-03-04T11:31:27.883Z" 

• The target environment on which you want to create the AG VDB. You can list the environments with the /environment list command.

• The target repository (SQL Server Availability Group on the target environment) in which to create the AG VDB. These can be listed with the /repository list command.

Procedure

1. Execute the database provision command.

   CODE

   delphix> database provision

2. Execute the defaults command.

   CODE

   delphix database provision> defaults

3. Set the timeflow point parameters such as parent SQL server container and location for AG VDB provision.

   CODE

   delphix database provision defaults *> set type=TimeflowPointSemantic 
   delphix database provision defaults *> set container=dexample 
   delphix database provision defaults *> set location=LATEST_SNAPSHOT 

4. Commit the operation to populate the defaults, as the browser interface provides. At this point, the operation can be committed, though you will likely need to change the defaults to match the information. 

   CODE

   delphix database provision defaults *> commit

5. Set the name and group for the new AG VDB. 

   CODE

   delphix database provision *> set container.name=vexample 
   delphix database provision *> set container.group=""

6. Set the SQL Server instance for the new AG VDB to MSSqlAvailabilityGroupDBConfig. 

   CODE

   delphix database provision *> set sourceConfig.type=MSSqlAvailabilityGroupDBConfig 

7. Set the database name for the AG VDB on the target SQL Server AG cluster instance.

   CODE

   delphix database provision *> set sourceConfig.databaseName=vexample 

8. Set the target repository.

   CODE

   delphix database provision *> set sourceConfig.repository=targetAg 

9. Set the database recovery model to FULL for the AG VDB.

   CODE

    delphix database provision *> set sourceConfig.recoveryModel=FULL 

10.  Set the allowAutoVDBRestartOnHostRebootattribute to true.

    CODE

    delphix database provision *> set source.allowAutoVDBRestartOnHostReboot=true 

11. Set the agProvisionConfig attribute for the AG VDB.

    CODE

    delphix database provision *> set source.agProvisionConfig.backupBased=true 

For more details on ` agProvisionConfig.backupBased` please refer here. 

12. Commit the result.

    CODE

    delphix database provision *> commit