Database cloning option for development / testing
Briefly I will describe how I organized the cloning of the database (creating several copies of the database from one backup) on the current project. The method saves time and hard disk space.
Situation: there is a thick database (say, a hundred GB). I would like to have this database with all the data separately for each developer and not spend a terabyte disk on it. The following is a solution for MSSQL under windows using powershell.
Stumbled upon the SQL clone utility from Redgate. The site has a description of how it works . The very essence of using such a thing: differencing virtual hard disk. In Russian, this translates as "differential virtual disk" - the disk on which is stored only the difference relative to the "parent" disk.
Details under the cut
The scheme of work is as follows:
Creating a parent disc will not be described, because This can be done manually through the disk management graphical user interface. Well, or google the commands and supplement the scripts given in the article.
Note times:
In windows 10 and windows server 2016 there are powershell commandlet New-VHD . For those who use previous versions of the server there is a utility diskpart. Automate working with it is not very convenient, because at the input, it takes a file with commands to execute.
Note two:
Since if the database files are located on the differencing disk, then the performance of such a solution is far from perfect. It turns out several levels of indirection: the record goes to the database, which lies on a virtual disk that stores the difference, in the house that Jack built. I have no specific figures on performance (since on our test loop this is not the first question anyway). I would be grateful if someone would measure by how much the write / read speed drops.
Note three:
Since the scripts were not intended for wide use and are given solely for example, there is an increased curvature and tight binding to MSSQL.
Since the script is run on the developer’s machine, and all actions are performed on the machine with the sql server, it is assumed that powershell remoting is configured. All commands are executed in an open session.
Get-StoredCredential is the commandlet for storing the credential on the local machine (installed separately). In principle, you can do without it, so it is wrapped in try / catch.
This piece of “ls function: [dz]: -n” is just some kind of magic for getting a list of drive letters. How it works - no idea, copied from stackoverflow.
In the above code, the biggest difficulty is to get the resulting virtual disk and put it on a specific letter. He also needs to do online.
Achtung times:
If you reboot the server, you will forget to explain the sql server that these databases are not there, and you need to reconnect them.
Achtung two:
The author, of course, checked the teams on his test loop, but does not intend to guarantee anything (especially their performance). At yout own risk.
Total:
Running an additional test database takes a couple of minutes and 40MB on disk. Thus, it is much more convenient for each developer to organize their own copy of the database.
Additionally:
The same script can be used to raise the database for integration tests.
I hope it will be useful to someone.
Situation: there is a thick database (say, a hundred GB). I would like to have this database with all the data separately for each developer and not spend a terabyte disk on it. The following is a solution for MSSQL under windows using powershell.
Stumbled upon the SQL clone utility from Redgate. The site has a description of how it works . The very essence of using such a thing: differencing virtual hard disk. In Russian, this translates as "differential virtual disk" - the disk on which is stored only the difference relative to the "parent" disk.
Details under the cut
The scheme of work is as follows:
- Create and connect a regular virtual disk (it will later become the parent).
- Create one instance of the database from which clones will be made. We clean out prodovskiy data, prepare the database completely to work in a test environment. Database files are placed on a virtual disk.
- Disconnect the database from the server. Disable virtual disk.
- Create a differencing disk. We connect to the system. We connect the database from this disk to the sql server.
- Repeat point 4 to achieve a harmonious number of databases.
Creating a parent disc will not be described, because This can be done manually through the disk management graphical user interface. Well, or google the commands and supplement the scripts given in the article.
Note times:
In windows 10 and windows server 2016 there are powershell commandlet New-VHD . For those who use previous versions of the server there is a utility diskpart. Automate working with it is not very convenient, because at the input, it takes a file with commands to execute.
Note two:
Since if the database files are located on the differencing disk, then the performance of such a solution is far from perfect. It turns out several levels of indirection: the record goes to the database, which lies on a virtual disk that stores the difference, in the house that Jack built. I have no specific figures on performance (since on our test loop this is not the first question anyway). I would be grateful if someone would measure by how much the write / read speed drops.
Note three:
Since the scripts were not intended for wide use and are given solely for example, there is an increased curvature and tight binding to MSSQL.
We initialize some variables:
$server = "server"; $db_file_name = "db_file_name"; $root_path = "path to folder with disks"; $cred = try { Get-StoredCredential -Target "$server\Administrator"; } catch { Get-Credential -Message "server windows user" -UserName "$server\Administrator" } $db_cred = $(try { Get-StoredCredential -Target "$server\sa"; } catch { Get-Credential -Message "sql server user" -UserName "sa" }).GetNetworkCredential(); $session = New-PSSession -ComputerName $server -Credential $cred; Since the script is run on the developer’s machine, and all actions are performed on the machine with the sql server, it is assumed that powershell remoting is configured. All commands are executed in an open session.
Get-StoredCredential is the commandlet for storing the credential on the local machine (installed separately). In principle, you can do without it, so it is wrapped in try / catch.
Next, the diskpart script execution code:
function run_script([string]$script, [bool]$suppress_output = $false) { $result = Invoke-Command -Session $session -ArgumentList $script -ScriptBlock { param($script) $script.Split("`r`n") | % { Write-Host $_.Trim() }; Out-File -FilePath "tmp" -InputObject $script -Encoding ascii return diskpart /s "tmp" } if($suppress_output) { return $result; } else { $result | ? { !$_.Contains("Microsoft") -and $_ -ne "" } | Write-Host } } I execute the sql commands via SQLCMD:
function run_sql([string]$sql) { Write-Host $sql SQLCMD -S $server -d master -U $($db_cred.UserName) -P $($db_cred.Password) -Q $sql } Creating a differencing disk:
run_script "create vdisk file=`"$root_path\$name.vhdx`" parent=`"$root_path\parent_disk.vhdx`"" Next, connect the disk and database:
$disk_letter = Invoke-Command -Session $session -ScriptBlock { ls function:[dz]: -n | ?{ !(test-path $_) } | select -Last 1; } $volumes = run_script "list volume" $true $disks = run_script "list disk" $true $script = " sel vdisk file=`"$current_path\$db_name.vhdx`" attach vdisk"; run_script $script; $disks_after = run_script "list disk" $true $new_disk = $($disks_after | ? { $_ -notin $disks } ) Write-Host $new_disk $new_disk -match "\d+" $diskId = $Matches[0] $script = " select disk $diskId online disk"; run_script $script $volumes_after = run_script "list volume" $true # get added disk $new_volume = $($volumes_after | ? { $_ -notin $volumes } ) Write-Host $new_volume $new_volume -match "\d+" $volumeId = $Matches[0] $script = " select volume $volumeId assign letter=$disk_letter"; run_script $script run_script "list volume"; run_script "list vdisk"; $atach_script = "CREATE DATABASE $db_name ON (FILENAME = '$disk_letter\$db_file_name.mdf'),(FILENAME = '$disk_letter\$db_file_name.ldf') FOR ATTACH"; run_sql "$atach_script" This piece of “ls function: [dz]: -n” is just some kind of magic for getting a list of drive letters. How it works - no idea, copied from stackoverflow.
In the above code, the biggest difficulty is to get the resulting virtual disk and put it on a specific letter. He also needs to do online.
Disconnecting a disk is somewhat simpler:
run_sql " ALTER DATABASE $name SET OFFLINE WITH ROLLBACK IMMEDIATE GO sp_detach_db $name"; $script = "select vdisk file=`"$root_path\$name.vhdx`" detach vdisk "; run_script $script Putting it all together:
param( [ValidateSet("detach_all", "attach_all_available", "create_new", "attach_db", "detach_db", "remove_file")][Parameter(mandatory=$true)][string] $mode, [string] $name ) function run_sql([string]$sql) { Write-Host $sql SQLCMD -S $server -d master -U $($db_cred.UserName) -P $($db_cred.Password) -Q $sql } function run_script([string]$script, [bool]$suppress_output = $false) { $result = Invoke-Command -Session $session -ArgumentList $script -ScriptBlock { param($script) $script.Split("`r`n") | % { Write-Host $_.Trim() }; Out-File -FilePath "tmp" -InputObject $script -Encoding ascii return diskpart /s "tmp" } if($suppress_output) { return $result; } else { $result | ? { !$_.Contains("Microsoft") -and $_ -ne "" } | Write-Host } } function attach_disk([string]$db_name, [string]$current_path) { $disk_letter = Invoke-Command -Session $session -ScriptBlock { ls function:[dz]: -n | ?{ !(test-path $_) } | select -Last 1; } $volumes = run_script "list volume" $true $disks = run_script "list disk" $true $script = " sel vdisk file=`"$current_path\$db_name.vhdx`" attach vdisk"; run_script $script; $disks_after = run_script "list disk" $true $new_disk = $($disks_after | ? { $_ -notin $disks } ) Write-Host $new_disk $new_disk -match "\d+" $diskId = $Matches[0] $script = " select disk $diskId online disk"; run_script $script $volumes_after = run_script "list volume" $true # get added disk $new_volume = $($volumes_after | ? { $_ -notin $volumes } ) Write-Host $new_volume $new_volume -match "\d+" $volumeId = $Matches[0] $script = " select volume $volumeId assign letter=$disk_letter"; run_script $script run_script "list volume"; run_script "list vdisk"; $atach_script = "CREATE DATABASE $db_name ON (FILENAME = '$disk_letter\$db_file_name.mdf'),(FILENAME = '$disk_letter\$db_file_name.ldf') FOR ATTACH"; run_sql "$atach_script" } $server = "server"; $db_file_name = "db_file_name"; $cred = try { Get-StoredCredential -Target "$server\Administrator"; } catch { Get-Credential -Message "server windows user" -UserName "$server\Administrator" } $db_cred = $(try { Get-StoredCredential -Target "$server\sa"; } catch { Get-Credential -Message "sql server user" -UserName "sa" }).GetNetworkCredential(); $session = New-PSSession -ComputerName $server -Credential $cred; $root_path = "path to folder with disks"; $files = Invoke-Command -Session $session -ArgumentList $root_path -ScriptBlock { param($root_path) Get-ChildItem -Filter "*.vhdx" -Path $root_path } switch ($mode) { "detach_all" { $files ` | % { Write-Host $("*"*40) `r`n $_.FullName `r`n; $_ } ` | % { " ALTER DATABASE $($_.Name.Replace('.vhdx', '')) SET OFFLINE WITH ROLLBACK IMMEDIATE GO sp_detach_db $($_.Name.Replace('.vhdx', ''))" } ` | % { run_sql "$_" } $files ` | % { Write-Host $("*"*40) `r`n $_.FullName `r`n; $_ } ` | % { run_script "select vdisk file=`"$($_.FullName)`" detach vdisk " } break; } "attach_all_available" { $files | % { $_.Name.Replace('.vhdx', '') } | ? { $_ -ne "parent_disk" } | % { attach_disk $_ $root_path } break; } "attach_db" { attach_disk $name $root_path break; } "detach_db" { run_sql " ALTER DATABASE $name SET OFFLINE WITH ROLLBACK IMMEDIATE GO sp_detach_db $name"; $script = "select vdisk file=`"$root_path\$name.vhdx`" detach vdisk "; run_script $script break; } "create_new" { $script = "create vdisk file=`"$root_path\$name.vhdx`" parent=`"$root_path\parent_disk.vhdx`"" run_script $script attach_disk $name $root_path; break; } "remove_file" { Invoke-Command -Session $session -ArgumentList $name,$root_path -ScriptBlock { param($name, $root_path) Remove-Item -Path "$root_path\$name.vhdx" } } } Remove-PSSession $session Achtung times:
If you reboot the server, you will forget to explain the sql server that these databases are not there, and you need to reconnect them.
Achtung two:
The author, of course, checked the teams on his test loop, but does not intend to guarantee anything (especially their performance). At yout own risk.
Total:
Running an additional test database takes a couple of minutes and 40MB on disk. Thus, it is much more convenient for each developer to organize their own copy of the database.
Additionally:
The same script can be used to raise the database for integration tests.
I hope it will be useful to someone.
Source: https://habr.com/ru/post/440804/