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.
随着复杂SQL数据库开发和生产基础架构，数据库配置出现了问题。 该问题意味着所有开发和测试实例均应在适当的环境中具有适当版本的数据库。 因此，这意味着数据库开发团队，客户端应用程序团队，QA和测试团队需要使用正确的数据库版本，该版本通常是生产版本，但自然无法在生产数据库本身上运行。 这就是为什么这些环境必须具有预配置的数据库的原因，而数据库DevOps团队面临的问题是如何执行数据库预配置，这是重复的任务，需要花费最佳的时间。
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.
设置QA / Staging / Test数据库的通常方法是创建生产数据库备份，并使用SQL Server备份功能在所需的环境中还原它。 让我们简要回顾一下该过程应如何进行。
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.
首先，我们需要数据库备份。 有时可以进行常规的夜间备份，但其他时候则需要当场执行备份。 对于后一种情况，我们将创建一个中等大小的数据库（大约10GB）的备份。 备份统计信息报告的时间略低于11分钟。
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.
将数据库置备到目标环境所要做的就是将备份还原到环境中托管SQL Server实例。 完成还原过程后，在日志记录中可以看到，还原操作可能比备份操作花费更多的时间。 此示例中的过程花费了略多于12分钟的时间，而备份花费了不到11分钟的时间。
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.
对于一个实例的数据库配置而言，这非常简单。 当必须对多个实例执行还原过程时，问题就开始了，因为每个实例的累积时间可能变得很长。 另一个问题是，还原的数据库会占用托管SQL Server实例的目标计算机上的存储空间。
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.
这意味着，如果我们有一个10GB的数据库，并且需要将其放置在三个不同的位置，那么我们将不得不保留35分钟以上的时间（不考虑通过网络传输的时间），并且总共需要30GB的空间。
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.
创建数据库克隆的概念基于将备份的数据库还原到虚拟驱动器，然后将该驱动器简单地安装到目标位置，并将其上的数据库安装到托管SQL Server。 PSDatabaseClone是一个PowerShell模块，该模块安装cmdlet来执行此任务。
Basic prerequisites for the PSDatabaseClone clone are:
PSDatabaseClone克隆的基本前提条件是：
- Powershell version 5 Powershell版本5
- Dbatools module for PowerShell
- 适用于PowerShell的Dbatools模块
- PSFramework module for PowerShell
- 适用于PowerShell的PSFramework模块
- Hyper-v module for PowerShell Hyper-v模块
- A network shared folder for storing virtual drive images 网络共享文件夹，用于存储虚拟驱动器映像
- Working SQL Server instance (SQL 2000 or later) for the PSDatabaseClone specific data PSDatabaseClone特定数据的有效SQL Server实例（SQL 2000或更高版本）
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:
首次初始化PSDatabaseClone模块时，这意味着使用与该模块关联的cmdlet，将自动执行Set-PSDCConfiguration命令。 此命令将指导完成此PowerShell实用程序的配置设置：
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.
如前所述，此Set-PSDCConfiguration命令将在主PSDatabaseClone命令的首次初始化时自动执行，但可以随时手动将其激活以更改设置。
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:
为了创建数据库克隆，首先必须创建数据库映像。 带有示例参数的命令应如下所示：
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
-SourceSqlInstance ：此参数的参数是一个需要配置的数据库所在SQL Server实例
-Database: parameter that specifies the name of the database
-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.
-DestinationSqlInstance ：此参数需要与源SQL实例相同的参数。 这将用作临时SQL Server实例，以便将数据库备份还原到虚拟驱动器，稍后将对此进行详细说明。
-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.
-ImageNetworkPath ：此参数的此参数将是存储虚拟驱动器文件（也将被视为父驱动器，将在后面说明）的存储位置。 此位置是与PSDatabaseClone其余要求一起创建的。 这将是创建数据库克隆的源位置，因此为了使向远程位置的数据库配置工作正常进行，应将其定位在共享文件夹中。
-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.
-CreateFullBackup ：这是一个切换参数，指示为需要配置的数据库创建新的备份文件。 数据库备份的位置将从SQL Server配置中读取。 -UseLatesFullBackup替代此参数值，它指示命令搜索现有备份并在其时间戳之前使用最新的备份。
The execution of this command will go through several phases. First, the database backup will be created (if -CreateFullBackup is used):
该命令的执行将经历几个阶段。 首先，将创建数据库备份（如果使用-CreateFullBackup ）：
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:
现在，让我们简要地查看处理时间统计信息。 以下命令将带出最近使用的命令执行时间：
(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.
在这里，我们可以看到该过程花费了超过20分钟的时间来执行。 常规方法执行备份/还原操作所需的时间大约与此过程实际上相同。
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:
这将带我们进入下一个数据库供应阶段，即创建数据库克隆。 数据库克隆正是目标环境将用作工作数据库的克隆。 PSDatabaseClone命令及其用于实现该目标的参数将如下所示：
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
-SqlInstance：此参数的参数是应在其中附加数据库克隆SQL Server实例
-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
-CloneName：此参数的参数是将附加到所选SQL Server实例的数据库克隆的名称。
-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.
-Destination：此参数的参数是数据库供应目标环境中将复制虚拟驱动器以进行安装的位置。 如果在该环境中执行此命令，则路径应位于本地。 否则，目标环境应提供共享文件夹的位置，然后UNC路径可用作参数。
-LatestImage: This parameter is mandatory and will give instructions to use the latest image created for the selected database
-LatestImage：此参数是强制性的，将指示如何使用为所选数据库创建的最新图像
-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:
-SqlCredential：在目标SQL实例使用SQL身份验证方法的情况下（通常是目标位于远程位置时的情况），这是可选参数。 对于自变量，应使用安全字符串。 以下命令将创建一个并将其存储在内部PowerShell配置中：
$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:
执行New-PSDCClone命令将仅从先前创建的虚拟驱动器（父驱动器）中提取图像信息，使用该信息来参考存储在父虚拟驱动器中的数据库创建新的子本地虚拟驱动器。本地SQL Server实例。 这意味着本地驱动器不需要用于整个数据库的空间，而只需要用于图像信息。 这就是解决数据库配置的存储空间问题的方法。 另外，引入到创建的数据库克隆中的所有其他数据都将作为增量存储在子虚拟驱动器中，因此不会影响引用同一原始映像的另一个数据库克隆。 从下面的屏幕快照中可以看出，虚拟驱动器文件仅占用约300MB的空间，而数据库的大小约为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.
设置目标环境以使用数据库克隆仅用了18秒。
After that database provisioning can continue to other locations with the New-PSDCClone command and just a small change of target parameters.
之后，可以使用New-PSDCClone命令并仅对目标参数进行少量更改，即可继续进行数据库配置。
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.
由于这是具有某些基本脚本技能的PowerShell概念，因此整个数据库供应过程可以自动化，从而减少了数据库DevOps运营商的投资时间。
436
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
