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

    Вкратце опишу как организовал клонирование БД (создание нескольких экземпляров БД из одного бэкапа) на текущем проекте. Способ позволяет сэкономить время и место на жестком диске.

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

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

      Надеюсь сценарий будет другой, но за статью спасибо.
        0
        Ну и если разработчику надо удалять таблицы, то может ему просто пустая база нужна? Но замечание верное.
        Такой драматичный сценарий. Мне аж самому страшно стало. Нет, чисто визуально не заметил снижения производительности. Тем более такого, чтобы кого-то увольнять.
        Ну и может так:
        Используем одну БД -> изменения разработчиков накладываются -> мешает отладке и тестированию -> не успеваем выкатить обновления проекта в срок -> лишение премии -> разбор полетов -> меня гонят в шею как истинного виновника.
        Или еще так:
        Делаем полноценные копии БД -> запрашиваем дополнительные ресурсы на сервера -> пока заявку рассматривают, задачи стоят -> не успеваем выкатить обновления проекта в срок -> лишение премии -> разбор полетов -> меня гонят в шею как истинного виновника.
        –2
        уважаемый, а как быть в случае если разработчиков больше чем букв в англ. алфавите?
          +1
          Можно поднять копию сервера и часть команды пересадить на него. Но в любом случае, если у нас больше 20 программистов, стоило бы конкретно под такой проект подумать, как лучше сделать инфраструктуру для разработки.
          0
          За какое время создается клон базы?
          Управление клонами — в каком туле происходит?
            0
            Точное время не замерял /вообще ничего не замерял:(/, но кажется что-то около 30-40 секунд.
            Управление исключительно приведенным скриптом, т.к. в нашем случае задача достаточно простая: поднять 4 копии и пусть живут. Стуктура БД у нас почти статична. Изначально вообще поднял 4 клона руками, но потом ребутнули сервак:(
            +2
            Чисто из любопытства — сколько это стоит? На самом сайте вместо ценника традиционное сейчас «get in touch». На случае, если уже «got in touch» с ними.
              0
              Имеется в виду стоимость SQL Clone? Я тоже не особо понял. На сайте можно скачать 14-дневную триал. Если добрать до пунктов покупки продуктов, то там какие-то безумные цены. Сам SQL Clone я даже не пробовал, просто привел как пример такого же подхода.
                0
                «Нуегонафиг», такой пример, если безумные цены. В том смысле, что лучше не доводить до состояния, когда придётся платить состояние за починку подобными инструментами.

              0
              Я надеюсь Вы делаете копию с продуктива и потом одну DEV копию делите на всех разработчиков? Или прямо с продуктива делаем DEV diff копии?
                0
                Ужас-ужас, мне бы такое в голову не пришло — делать diff сразу с прода. Базу сначала надо подготовить под DEV. У меня это даже отдельно в статье написано:
                2. Создаем один экземпляр БД, от которого будут делаться клоны. Вычищаем продовские данные, подготавливаем базу полностью к работе в тестовой среде. Файлы БД размещаем на виртуальном диске.

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

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое