18.02.2019 21:30
dartNNN 11 3800 Источник
Вкратце опишу как организовал клонирование БД (создание нескольких экземпляров БД из одного бэкапа) на текущем проекте. Способ позволяет сэкономить время и место на жестком диске.

Ситуация: есть толстая БД (скажем, сотня Гб). Хочется иметь эту базу со всеми данными отдельно для каждого разработчика и не тратить на это терабайтный диск. Далее приведено решение для 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Мб на диске. Таким образом гораздо удобнее организовать каждому разработчику свой экземпляр БД.

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

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

Надеюсь, окажется кому-то полезным.

Комментарии (11)


  1. Sleuthhound
    19.02.2019 07:09
    #19772582

    Сэкономили 1 ТБ места (хотя это еще под вопросом, т.к. если разраб удалит в своей бд скажем таблички на 20-30 gb и создаст новые, то весь профит от разностного диска улетучится) и обеспечили разработчиков болью и страданием из-за снижения скорости работы их БД -> разработчики будут тратить больше времени из-за тормозящей бд -> не успеваем выкатить обновления проекта в срок -> лишение премии -> разбор полетов -> вас гонят в шею как истинного виновника

    Надеюсь сценарий будет другой, но за статью спасибо.


    1. dartNNN Автор
      19.02.2019 09:14
      #19772858

      Ну и если разработчику надо удалять таблицы, то может ему просто пустая база нужна? Но замечание верное.
      Такой драматичный сценарий. Мне аж самому страшно стало. Нет, чисто визуально не заметил снижения производительности. Тем более такого, чтобы кого-то увольнять.
      Ну и может так:
      Используем одну БД -> изменения разработчиков накладываются -> мешает отладке и тестированию -> не успеваем выкатить обновления проекта в срок -> лишение премии -> разбор полетов -> меня гонят в шею как истинного виновника.
      Или еще так:
      Делаем полноценные копии БД -> запрашиваем дополнительные ресурсы на сервера -> пока заявку рассматривают, задачи стоят -> не успеваем выкатить обновления проекта в срок -> лишение премии -> разбор полетов -> меня гонят в шею как истинного виновника.


  1. Alexus819
    19.02.2019 08:51
    #19772786
    -1

    уважаемый, а как быть в случае если разработчиков больше чем букв в англ. алфавите?


    1. dartNNN Автор
      19.02.2019 09:23
      #19772880

      Можно поднять копию сервера и часть команды пересадить на него. Но в любом случае, если у нас больше 20 программистов, стоило бы конкретно под такой проект подумать, как лучше сделать инфраструктуру для разработки.


  1. ggo
    19.02.2019 09:47
    #19772944

    За какое время создается клон базы?
    Управление клонами — в каком туле происходит?


    1. dartNNN Автор
      19.02.2019 11:57
      #19773488

      Точное время не замерял /вообще ничего не замерял:(/, но кажется что-то около 30-40 секунд.
      Управление исключительно приведенным скриптом, т.к. в нашем случае задача достаточно простая: поднять 4 копии и пусть живут. Стуктура БД у нас почти статична. Изначально вообще поднял 4 клона руками, но потом ребутнули сервак:(


  1. Temmokan
    19.02.2019 09:56
    #19772980

    Чисто из любопытства — сколько это стоит? На самом сайте вместо ценника традиционное сейчас «get in touch». На случае, если уже «got in touch» с ними.


    1. dartNNN Автор
      19.02.2019 12:28
      #19773632

      Имеется в виду стоимость SQL Clone? Я тоже не особо понял. На сайте можно скачать 14-дневную триал. Если добрать до пунктов покупки продуктов, то там какие-то безумные цены. Сам SQL Clone я даже не пробовал, просто привел как пример такого же подхода.


      1. Temmokan
        21.02.2019 08:43
        #19783020

        «Нуегонафиг», такой пример, если безумные цены. В том смысле, что лучше не доводить до состояния, когда придётся платить состояние за починку подобными инструментами.


  1. reallord
    19.02.2019 10:24
    #19773062

    Я надеюсь Вы делаете копию с продуктива и потом одну DEV копию делите на всех разработчиков? Или прямо с продуктива делаем DEV diff копии?


    1. dartNNN Автор
      19.02.2019 12:00
      #19773504

      Ужас-ужас, мне бы такое в голову не пришло — делать diff сразу с прода. Базу сначала надо подготовить под DEV. У меня это даже отдельно в статье написано:

      2. Создаем один экземпляр БД, от которого будут делаться клоны. Вычищаем продовские данные, подготавливаем базу полностью к работе в тестовой среде. Файлы БД размещаем на виртуальном диске.

      Может быть не совсем ясно написал. Имеется в виду, создание БД из бекапа и очистка от всех чувствительных данных.