With the complex SQL database development and production infrastructure there comes an issue with database provisioning. The issue implies that all development and testing instances should have proper version of a database in the proper environments. So that means that database development teams, client application teams, QA and testing teams need to work on proper database version which is usually the one in production but naturally cannot work on the production database itself. This is why those environments have to have a provisioned database and there comes the problem for database DevOps teams how to perform database provisioning, which is repetitive task, with optimal time invested.
The usual way to provision a QA/Staging/Test database is to create a Production database backup and restore it in desired environment using the SQL Server backup functionality. Let’s briefly review how the process should work.
First we need a database backup. Sometimes a regular overnight backup is available but other times the backup needs to be performed on the spot. For the latter case we will create a backup of a medium size database (around 10GBs size). The backup statistics reports the time of slightly below 11 minutes.
What is left to do to provision a database to a target environment is to restore the backup to a SQL Server instance hosted in environment. The restore operation can take slightly more time than the backup operation as can be seen in the log record after finishing the restoration process. The process in this example took slightly more than 12 minutes compared to little less than 11 minutes for the backup.
The restore time can be even greater if the restore is done on a remote instance which is common case.
This is pretty straightforward for database provisioning of one instance. The problem starts when if the restore process has to be performed to multiple instances where cumulative time for every restore can become significant. Additional problem is that restored database consumes storage space on the target machine hosting the SQL Server instance.
This means if we have a 10GB database and it needs to be placed on three different locations we would have to reserve 35+ minutes (disregarding the time for transfer over network) and total of 30GB of space.
What if there is a method to accomplish database provisioning in just a fraction of time and space?
This is where the concept of database cloning can resolve the database provisioning problem for complex infrastructure.
The concept of creating a database clone is based on restoring a backed up database to a virtual drive and simply mounting that drive to a target location and the database on it to a hosted SQL Server. The PSDatabaseClone is a PowerShell module that install cmdlets to perform this task.
Basic prerequisites for the PSDatabaseClone clone are:
- Powershell version 5
- Dbatools module for PowerShell
- PSFramework module for PowerShell
- Hyper-v module for PowerShell
- A network shared folder for storing virtual drive images
- Working SQL Server instance (SQL 2000 or later) for the PSDatabaseClone specific data
When all requirements are met database provisioning can begin.
With the first initialization of the PSDatabaseClone module, which means using cmdlets associated with the module, Set-PSDCConfiguration command will be executed automatically. This command will guide through configuration setup for this PowerShell utility:
-
First, it will ask the user to select where the configuration data will be stored. It is recommended to store data in a small local database as it is less prone to corruption comparing to locally stored JSON file
-
After that the database location and credentials will be required:
-
When this is done and everything is in order the confirmation message will appear:
As previously stated, this Set-PSDCConfiguration command will be executed automatically on first initialization of main PSDatabaseClone commands but it can be activated manually at any time in order to change the setup.
In order to create a database clone first a database image has to be created. The command with the example parameters should look like this:
|
1 |
New-PSDCImage -SourceSqlInstance Win-10\sqlexpress -Database AdventureWorks2017 -DestinationSqlInstance Win-10\sqlexpress -ImageNetworkPath \\Win-10\images -CreateFullBackup |
The parameters used in the example are:
-SourceSqlInstance: the argument for this parameter is a SQL Server instance where the database that requires provisioning is located
-Database: parameter that specifies the name of the database
-DestinationSqlInstance: This parameter requires the same argument as for the source SQL instance. This will be used as temporary SQL Server instance in order to restore database backup to a virtual drive which will be explained later in more details.
-ImageNetworkPath: This argument for this parameter will be the location where to store the virtual drive file (also considered as the parent drive which will be explained later). This location was created earlier with the rest of the PSDatabaseClone requirements. It will be the source location for database clone creation so in order for database provisioning to remote location to work, it is expected to locate the image in a shared folder.
-CreateFullBackup: This is a switch parameter which instructs to create new backup file for the database that requires provisioning. The location for the database backup will be read from SQL Server configuration. Alternative to this parameter value will be -UseLatesFullBackup which instructs the command to search for existing backups and use the latest one by its timestamp.
The execution of this command will go through several phases. First, the database backup will be created (if -CreateFullBackup is used):
After that a virtual drive image file (carrying the source database name and a timestamp in its name) will be created and temporarily mounted as device.
In the next phase the source database will be restored on a mounted virtual drive.
When the process is finished the message with created virtual drive information will appear:
Now, lets briefly look at the process time statistics. The following command will bring out the latest used command execution time:
|
1 |
(Get-History)[-1].EndExecutionTime - (Get-History)[-1].StartExecutionTime |
Here we can see that the process took something more than 20 minutes to execute. This is roughly the same time required for conventional method for backup/restore operations which this process actually did.
When this phase is finished the virtual drive will be unmounted and ready for database provisioning.
This takes us to next database provisioning phase and that is creating a database clone. Database clone is exactly what the target environment will use as working database. The PSDatabaseClone command with its parameters used to achieve that will look like this:
|
1 2 |
New-PSDCClone -SqlInstance Win-10\Dev1 -SqlCredential $cred -Database AdventureWorks2017 -CloneName AdventureWorks2017_clone -Destination C:\Clone -LatestImage |
The parameters used in this example are:
-SqlInstance: The argument for this parameter is the SQL Server instance where the database clone should be attached
-Database: The argument for this parameter is the name of the source database stored in the virtual drive and will be used to recognize appropriate image name
-CloneName: The argument for this parameter is the name of the database clone which will be attached to the selected SQL Server instance
-Destination: The argument for this parameter is a location in the database provisioning target environment where the virtual drive will be copied in order to mount it. If this command is executed in that environment the path should be local. Otherwise the target environment should provide shared folder location and then the UNC path can be used as argument.
-LatestImage: This parameter is mandatory and will give instructions to use the latest image created for the selected database
-SqlCredential: This is the optional parameter in case where SQL Authentication method is used for the target SQL instance which is usually the case when the target is on a remote location. For the argument a secured string should be used. Following commands will create one and store it in internal PowerShell configuration:
|
1 2 3 |
$password = ConvertTo-SecureString “MySQLPassword” -AsPlainText -Force $cred = New-Object System.Management.Automation.PSCredential (“sa”, $password) |
Executing the New-PSDCClone command will extract only the image information from previously created virtual drive (the parent drive), use that information to create new child local virtual drive with the reference to a database stored in the parenting virtual drive and attach the database to a local SQL Server instance. This means that the local drive does not require space for the entire database but only for the image information. This is how the storage space issue for database provisioning gets resolved. Also, all additional data introduced to created database clone will be stored as delta in the child virtual drive thus unaffecting another database clone that is referred to the same original image. As can be seen in the screenshot below, the virtual drive file takes only around 300MB compared to the size of the database of about 10GB:
After successful execution of the process the message with the database clone information will appear:
We can now find a database clone by its designated name attached on the target instance:
And also the presence of the mounted virtual drive where image information is held:
Let’s review the time needed for the database provisioning process with a database clone for one instance:
It took only 18 seconds to set the target environment for work with a database clone.
After that database provisioning can continue to other locations with the New-PSDCClone command and just a small change of target parameters.
Since this is a PowerShell concept with some basic scripting skills the whole database provisioning process can be automated thus reducing the invested time for the database DevOps operators.
Current occupation is with ApexSQL LCC as Support Engineer helping customers with technical issues and does quality assurance for a set of SQL database continuous integration and continuous delivery solutions in a form of ApexSQL DevOps toolkit product.
View all posts by Milan Rancic
- PowerShell encrypt password techniques for SQL Server - April 18, 2019
- How to setup image based SQL Server database provisioning with PSDatabaseClone - March 22, 2019
- SQL database provisioning via Database clone using PSDatabaseClone PowerShell module - February 4, 2019