How to become an author
Search
Write a publication
Pull to refresh

Вариант клонирования БД для разработки/тестирования

Reading time6 min
Views9.2K
Database Administration*
Вкратце опишу как организовал клонирование БД (создание нескольких экземпляров БД из одного бэкапа) на текущем проекте. Способ позволяет сэкономить время и место на жестком диске.

Ситуация: есть толстая БД (скажем, сотня Гб). Хочется иметь эту базу со всеми данными отдельно для каждого разработчика и не тратить на это терабайтный диск. Далее приведено решение для MSSQL под windows с использованием powershell.

Наткнулся на утилиту SQL Clone от Redgate. На сайте есть описание как она работает. Самая суть в использовании такой штуки: differencing virtual hard disk. На русский это переводится как «разностный виртуальный диск» — диск на котором хранится только разница относительно «родительского» диска.

Подробности под катом

Схема работы выглядит следующим образом:

  1. Создаем и подключам обычный виртуальный диск (он потом станет родительским).
  2. Создаем один экземпляр БД, от которого будут делаться клоны. Вычищаем продовские данные, подготавливаем базу полностью к работе в тестовой среде. Файлы БД размещаем на виртуальном диске.
  3. Отключаем БД от сервера. Отключаем виртуальный диск.
  4. Создаем differencing disk. Подключаем к системе. Подключаем БД с этого диска к sql серверу.
  5. Повторяем пункт 4 до достижения гармоничного числа БД.

Создание родительского диска описано не будет, т.к. это можно сделать вручную через графический интерфейс disk management. Ну или загуглить команды и дополнить скрипты, приведенные в статье.

Примечание раз:

В windows 10 и windows server 2016 есть powershell commandlet New-VHD. Для тех же, кто использует предыдущие версии сервера есть утилита diskpart. Автоматизировать работу с ней не совсем удобно, т.к. на вход она принимает файл с командами для выполнения.

Примечание два:

Т.к. файлы БД размещаются на differencing disk, то производительность такого решения далеко не на высоте. Получается несколько уровней косвенности: запись идет в базу, которая лежит на виртуальном диске, который хранит разницу, в доме который построил Джек. Конкретных цифр по производительности у меня нет (т.к. на нашем тестовом контуре это далеко не первый вопрос в любом случае). Буду признателен, если кто-то замерит на сколько просаживается скорость записи/чтения.

Примечание три:

Т.к. скрипты не предполагались для широкого использования и приводятся исключительно для примера, наблюдается повышенная криворукость и жесткая привязка к MSSQL.

Проинициализируем кое-какие переменные:
$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;


Т.к. запускается скрипт на машине разработчика, а все действия выполняются на машине с sql сервером, предполагается, что настроен powershell remoting. Все команды выполняются в открытой сессии.

Get-StoredCredential — это commandlet для сохранения credential на локальной машине (устанавливается отдельно). В принципе можно обойтись и без него, поэтому он и завернут в try/catch.

Далее код выполнения скрипта diskpart:
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
    }
}


Sql команды я выполняю через SQLCMD:
function run_sql([string]$sql)
{
    Write-Host $sql
    SQLCMD -S $server -d master -U $($db_cred.UserName) -P $($db_cred.Password) -Q $sql
}


Создание differencing disk:
    run_script "create vdisk file=`"$root_path\$name.vhdx`" parent=`"$root_path\parent_disk.vhdx`""


Далее подключение диска и БД:
        $disk_letter = Invoke-Command -Session $session -ScriptBlock {
            ls function:[d-z]: -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"


Вот этот кусок «ls function:[d-z]: -n» — это просто какая-то магия для получения списка букв дисков. Как работает — без понятия, скопировал со stackoverflow.

В приведенном коде самая большая трудность — получить полученный виртуальный диск и посадить его на конкретную букву. Еще предварительно ему надо сделать «online».

Отключение диска несколько проще:
        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


Собираем все в кучу:
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:[d-z]: -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


Ахтунг раз:

Если ребутнуть сервер, то замучаешься объяснять sql серверу, что этих баз нет, и надо их заново подключить.

Ахтунг два:

Автор конечно проверил команды на своем тестовом контуре, но гарантировать ничего (тем более их работоспособность) не намерен. At yout own risk.

Итого:

Запуск дополнительной тестовой БД занимает пару минут и 40Мб на диске. Таким образом гораздо удобнее организовать каждому разработчику свой экземпляр БД.

Дополнительно:

Тот же самый скрипт можно использовать для поднятия БД под интеграционные тесты.

Надеюсь, окажется кому-то полезным.
Tags:
Hubs:
Total votes 24: ↑18 and ↓6+12
Comments11
3
Karma
0
Rating
@dartNNN

User

Send message

Articles

Your account

Sections

Information

Services

Support
© 2006–2024, Habr