Бекап баз данных через SQL VDI

    Хочу рассказать о том, как мы захотели сделать собственный бекап баз данных для Microsoft SQL Server, и что из этого получилось.

    Проблема


    Мы в компании используем Microsoft SQL Server, и вполне ожидаемо, что нам надо делать бекапы баз данных для него, что мы успешно делаем. Делается это следующим образом:
    • Первый скрипт делает бекапы нужных баз данных
    • Второй скрипт проходит по папкам с бекапами и архивирует их в 7zip
    • Третий скрипт уносит эти бекапы на внешнее хранилище


    Вроде всё логично на мой взгляд, но есть некая странность во всём этом. Так как некоторое время на сервере находятся файлы, которые активно занимают место, а потом данные файлы удаляются за ненадобностью. Хотелось всё это оптимизировать. Кроме того, у нас в комании есть продукт, одной из задач которого является синхронизация базы данных, и ему подобный код, оптимизирующий задачу, тоже очень пригодился бы.



    Варианты решения



    Бекап со сжатием средствами SQL


    Хорошая идея, но не каждая версия SQL поддерживает его, к тому же размер данного бекапа выходит всё равно не самый мелкий. А с учётом того, что в большинстве случаев, бекапы никогда не пригождаются, хотелось бы более сильного сжатия. Зато отличный вариант в плане удобства.

    Бекап в Azure


    SQL Server, начиная с 2012 поддерживает бекап в Azure. Была идея в качестве URL указать собственный сервис и в нём всё делать. Но уж очень сложная и грязная технология выходила. Да и версия SQL должна быть достаточно свежей.

    Бекап в pipe


    Отличнейшая идея, но с 2005-ого SQL уже не поддерживается.

    Бекап через VDI


    Секретная технология от Microsoft, которая пришла на смену pipe. Почему секретная? А попробуйте найти документацию на неё. Это тот ещё квест. Есть документация на сайте Microsoft, которая вроде понятная, но ничего не понятно. Потому что вроде про VDI, а вроде про VSS. Есть её перепечатки и плюшевые примеры использования на различных сайтах.
    По факту, самая понятная документация находится в chm-файле по данной ссылке
    После всей этой документации возникло ощущение какой-то сугубо служебной технологии, но т.к. ничего другого не нашлось, остановились на ней. К тому же она работает на всех SQL с 2005-ой версии (даже с 2000, но с некоторыми ограничениями), на любой редакции.

    Используем VDI


    Делать нечего, взяли документацию, взяли Аню, выдали документацию, гугл и отправили изучать эту технологию.


    Аня изучает документацию

    Если бы я придумывал эту технологию, я бы сделал так:
    • Зарегистрировал виртуальное устройство в SQL
    • Сделал бы команду бекапа в данное устройство с параметрами
    • Написал бы инструкцию пользователю, как использовать
    • ?????
    • PROFIT!


    Это было бы логично, но Microsoft не ищет лёгких путей. Они придумали следующую схему
    • Регистрируем одноразовое устройство в SQL со случайным именем (обычно все используют гуиды)
    • Ждём данных от SQL
    • В то же самое время, другим потоком делаем бекап в это устройство (имя мы сами придумали, так что только мы и можем вызвать)
    • Получаем данные от SQL кусочками
    • Всё получили, закрываем устройство и заканчиваем команду бекапа базы

    Конечно же, всё написано на «замечательном» COM'е, чтобы жизнь мёдом не казалось и приправлено следующими ограничениями:
    • SQL и приложение должны быть на одном и том же сервере (слава богу, поддерживаются разные инстансы SQL)
    • Пользователь SQL должен быть sysadmin (скромненько)
    • Пользователь, от которого запущено приложение должен быть администратором или пользователем с волшебными правами Create Global Objects

    По последнему пункту подробнее: в документации есть такая шедевральная фраза:
    The CreateEx (and Create) calls will modify the security DACL on the process handle in the client process. Because of this, any other modification of the process handle must be serialized with invocation of CreateEx. CreateEx will serialize with other calls to CreateEx, but is unable to serialize with external processing. Access is granted to the account running the SQL Server service.
    Типичная фраза вида: почувствуй себя лузером. Т.е. все слова понятны, а смысла нет. По факту, как я уже написал выше, нужно право Create Global Objects, которое по умолчанию доступно администраторам, Network Service и Local Service. Т.е. в принципе, из сервиса запускать всё можно, а не администратором (пользователь, который делает бекапы, обычно не администратор), нельзя. Но можно выдать право ручками, через групповую политику (мы просто сделали команду в утилите, которая выдаёт права указанному пользователю, т.е. раз выдал под админом, дальше можно работать). К сожалению, так и не смогли разобраться с UAC. Если забыть запустить программу явно под администратором, то права не получить никаким боком. Можно было в манифесте явно указать, что нужны админские права, но тогда в принципе не получится использовать программу не администратором. А хочется.

    С этим разобрались, идём дальше. Я не буду писать куски кода и расписывать детально что делать, ибо это никому не интересно, а кому интересно, те сами разберутся. :) В целом, там обычный цикл вида GetCommand, CompleteCommand, в середине которого обработка результатов. Я просто расскажу некоторые нюансы, которые вылезли при использовании данного COM в .NET

    Лирическое отступление
    Совсем забыл рассказать, что же за утилиту мы хотели сделать (и сделали в итоге). Мы хотели делать бекап базы сразу в архив. Обычный, zip. Ну и восстановление из него, конечно же. Т.е. никаких своих особых форматов файлов. Можно распаковать и восстановить бекап без этой утилиты, или запаковать существующий бекап и потом его восстановить утилитой. Конечно же, первая итерация столкнулась с проблемой: не все библиотеки для сжатия нормально переваривают файлы больше 2Гб, в результате остановились на 7zip и обёртке для неё SevenZipLib. В результате получили возможность архивировать в 7z, zip, bz2, xz, gz. Правда 7zip в таком режиме тоже работает через COM… что доставило нам дополнительной радости.


    COM Великий и ужасный


    Да, мы решили писать всё на .NEТ. Собственно, маршаллинг в COM там вполне сносный (половина системных библиотек по сути обёртки над COM'ом), и казалось, что проблем не будет. Но они возникали буквально на каждом шагу.
    Данная часть будет, наверное, очевидной, для тех, кто уже сталкивался с этим, но тем, кто не сталкивался, может быть интересная.

    При декларации объектов, названия методов не имеют значения


    Важен только порядок (в этом отличие от WinAPI методов с атрибутом DllImport). Т.е. если вы случайно перепутали два метода местами (особенно похожих, типа Create и CreateEx), можете ловить замечательные ошибки, когда всё вроде бы работает, но как-то не совсем так, как надо.
    Следите за этим.

    .NET за вас будет кидать эксепшены


    В COM методы обычно возвращают HRESULT, т.е. код ошибки. Ну или 0-ой код, если всё хорошо. .NET пользуется этим и в случае ненулевого кода кидает COMException, в котором уже можно посмотреть на код и обработать его. Вроде удобно, но не совсем. К тому же, на одном из серверов со старым .NET обнаружилось замечательная бага. При возникновении данного эксепшена (например, когда обращаемся к закрытому устройству, что в общем-то по документации нормальная ситуация), вылезало ExecutionEngineException с фатальным крешем приложения. Гугл в качестве решения предлагал устанавливать апдейты, но в целом, это не решение для утилиты, поэтому обошли проблему следующим образом: Добавили к каждому COM-методу атрибут PreserveSig, который в случае ошибки не будет кидать эксепшен а просто вернёт код ошибки. В этом случае, никаких страшных проблем не возникало. Ну и код стал логичнее.

    STAThread и MTAThread


    Для разработчиков на .NET, это некий магический атрибут, который студия добавляет к методу Main. Если кто-то попытается понять, что это, он получит следующий шикарный в своей простоте текст:
    STAThreadAttribute indicates that the COM threading model for the application is single-threaded apartment. This attribute must be present on the entry point of any application that uses Windows Forms; if it is omitted, the Windows components might not work correctly. If the attribute is not present, the application uses the multithreaded apartment model, which is not supported for Windows Forms.
    Который в переводе означает: чувак, так надо.

    Ну, естественно, так, конечно надо, и когда наше приложение архивировало в zip, всё было нормально, а при попытке использовать 7z, всё эпично зависало. В чём же проблема, и что на самом деле значат эти атрибуты?

    Вкратце, смысл в следующем. COM из коробки многопоточный. Т.е. разные объекты получают и возвращают данные в разных потоках. Но криворукий код (или Windows Forms), не могут осилить такую сложную концепцию, поэтому они все COM-вызовы заворачивают во внутреннюю очередь и выполняют в одном потоке. Никаких проблем с многопоточностью! Зато мы эпично встряли в Dead Lock. Сколько стоило понять, что это дедлок, и дедлок где-то в кишках .NET… ууух…

    Но нам повезло, у нас консольное приложение, мы просто повесили атрибут MTAThread и успокоились. Если вам это не подойдёт… что же страдайте. Есть пути обхода.

    Создание объектов может быть странным


    .NET создаёт удобные врапперы, так что инстанциирование объектов выглядит простым и очевидным:
    var obj = new ClientVirtualDeviceSet();
    

    И всё у нас работало, пока не попробовали запустить всё на 32х-битной винде. И при создании объекта вылез эксепшен. Логично было предположить, что проблема с типами данных и маршаллингом (не тот размер), но всё было в порядке. В чём конкретно проблема, так и не выяснили, просто создали объект как в старые добрые времена, через CoCreateInstance.

    object comObject;
    var errorCode = CoCreateInstance(
    	CLSID_MSSQL_ClientVirtualDeviceSet,
    	IntPtr.Zero,
    	CLSCTX_INPROC_SERVER,
    	CLSID_MSSQL_ClientVirtualDeviceSet,
    out comObject);
    


    Прочее


    Всякие мелочёвки с маршаллингом, правильным лейаутом структур расписывать не буду, в принципе, они один раз доводятся до ума и больше не трогаются. Просто совет, если тяжело разобраться с указателями, отдавайте IntPtr и ручками конвертите его в нужный тип. Может быть проще, чем подбирать правильные атрибуты для .NET

    Немного про 7z


    Точнее про SevenZipSharp, но т.к. это в целом обёртка (очень хорошая), то и про него тоже.

    Первое, что может смутить, так это то, что библиотека заточена прод работу с файлами (а не со стримами), поэтому логика там наизнанку. Т.е. в .NET компрессор обычно из себя представляет из себя обёртку над writeable-стримом, вы в него пишите, он сжимает и передаёт сжатые данные во внутренний поток. В SevenZipSharp наоборот, он читает из вашего стрима данные и записывает их в свой внутренний стрим. Что с учётом того, что из SQL мы в целом тоже читаем, доставило Ане много приятных минут разбирательств с асинхронными стримами, блокировками, эвентами… В общем на любителя :)

    Второе, опять же связанное с файлами, это то, что библиотека очень любит бегать по потоку в разные стороны, что в целом неплохо, но если поток не позволяет это сделать (например, stdout), начинаются проблемы. В принципе, мы сумели это обойти (тоже отдельный квест, после которого Аня стала меня ненавидеть) для gzip, xz, gzip, 7z, а вот с zip'ом возникли сложности. Структура потока не позволяет это сделать. С чтением из stdin всё ещё хуже, библиотека в принципе не может это сделать. Нам пришлось пока отказаться от этой возможности, и оставить только возможность читать из stdin только несжатые бекапы.

    Итоги


    После всего этого увлекательного квеста у нас получилась утилита с оригинальным названием PackDb, которая может делать бекапы баз данных сразу в архив, восстанавливать их оттуда, и бонусом, ещё одна увлекательная фича: копирование базы данных без создания временных файлов. Зачем это надо? Для тестовых баз данных, когда нужно скопировать эталонную базу в новую и протестировать на данной базе, не трогая основную. Ну и всякие мелочи вида выдачи прав, валидации корректности бекапа или возможности не использовать архивы, если не надо а просто брать bak файлы.



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

    Метки:
    Поделиться публикацией
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 16
    • +5
      Аня вообще няшка у вас )
      • 0
        Расскажите, как вы сделали прогрессбар на cmd?
        • 0
          А что именно вас интересует? Сам прогресс — псевдографикой, перерисовка с помощью \r
          • 0
            Да. Хотелось сделать сценарий, который ставит ОП, с помощью WinRS. Процесс состоит из копирования дистрибутива с помощью xcopy и запуске инсталлятора в тихом режиме setup.exe /S.
            Хотелось бы сделать прогресс бар для этих действий.
        • 0
          Доброго времени суток.
          Скажите, пожалуйста, под какой лицензией распространяется PackDb?
          • 0
            Я уточню насчёт конкретного текста, но можете использовать без ограничений для бизнеса.
          • 0
            Я правильно понял, что поддерживается также и восстановление?
            Проводили ли вы сравнение с процедурой сохранения\восстановления стандартными средствами (+сжатие)? Конечно же интересует время выполнения.
            • 0
              Да, конечно, восстановление поддерживается. Время выполнения примерно равно сжатию, сам бекап не оказывает существенного влияния на скорость операции.
              • 0
                а конкретные цифры и примеры?

                попробовал протестировать на паре баз 10-20Гб (сравнивал с бэкапом и восстановлением стандартными средствами SQL с включенным сжатием):
                — бэкап 7z (5-6 мин) дольше стандартного (2.5-3 мин) в 2 раза, размер меньше в 1.5-2
                — бэкап zip дольше в 2 раза, размер сопоставим со стандартным
                — восстановление 7z с уровнем компрессии fast (3 мин) в 2 раза медленнее стандартного (1.5 мин)

                Параметр сжатия пробовал только fast\low, все что выше выдавало неприемлемую оценку по времени в 30-40 мин и я не дождался.
                Восстановление из архива zip завершилось с ошибкой и открыть архив тоже не получилось.
                • 0
                  Смотрите, тут дело в том, что вы можете получить 7z быстрее и проще чем стандартными средствами (и не тратить место на диске). А потом этот 7z восстановить не распаковывая. А т.к. 7z жмёт гораздо лучше чем zip и стандартные средства, для долговременного хранения лучше использовать его (если есть время). Ну или для быстрых бекапов уменьшить качество сжатия.
                  По цифрам.
                  Вариант раз на слабом сервере. Стандартные средства 74 секунды бекап + 391 секунда сжатия в 7z = 465. Тот же эффект через PackDb — 446 секунд.
                  Варинат два на мощном сервере (база другая). 192 + 399 = 591 против 413.

                  Размер базы для примера: без сжатия 8.3Gb, сжатие средствами SQL — 1.9Gb, сжатие в 7z — 0.46Gb. Т.е. выигрыш на 1.5Gb для одной базы.

                  По поводу проблем с зипом — обнаружили, виновные будут наказаны. Есть проблемы со сжатием в zip при размерах базы более 2Gb (у зипа постоянно с этим проблемы лезут).
                  • 0
                    это цифры для какой компрессии?
                    и что насчет времени при компрессии high\ultra — может тоже проблема с базами больше 2гб?

                    проводили ли тесты на промышленных масштабах 200-300-500Гб? я бы попробовал на 300гб базе, но поскольку база в кластере и утилита может работать только с локальным сервером, увы не получится...(

                    в общем, идея интересная, но для меня выгода сомнительна, поскольку в приоритете время сохранения и еще важнее восстановления — если база допустим 300гб будет восстанавливаться не полчаса, а час-два, и мне это уже критично.

                    пожелания — добавить больше опций, как разные виды бэкапов (dif, log), так и например очень важный параметр COPY_ONLY (пригодилось бы если текущие бэкапы делать стандартно, а иногда запускать сжатые через утилиту для архива)
                    • 0
                      это цифры для какой компрессии?

                      normal
                      и что насчет времени при компрессии high\ultra — может тоже проблема с базами больше 2гб?

                      Да с этим вроде нет проблем, это просто 7z такой медленный.

                      проводили ли тесты на промышленных масштабах 200-300-500Гб? но поскольку база в кластере и утилита может работать только с локальным сервером, увы не получится...(

                      Тут не должно быть никаких проблем, только долго (ну и обычный бекап долгий в данном случае). А с кластером — если на одном из узлов локально запустить?

                      Пожелания записали, будут ресурсы — сделаем.

            • 0
              В статье ни слова про differential backup и transaction log backup
              Среди CLI свичей нет ничего, что намекало бы на восстановление из backup set-а с реплаем транзакшн лога к определенной точке.
              Сжимать бэкапы файлами нет никакого смысла — если вы бесстрашные, то блочная дедубликация решает гораздо круче, а если аккуратные и консервативные — то ваш LTO5\6 накопитель уже умеет компрессить всё на лету, за него второй раз это делать нет никакого смысла. Бесстрашная стратегия круто работает на development средах, консервативная — в продакшне.

              Ну и вместо сжатия файлов всегда можно использовать сжатие тома. Работает чуть похуже, зато 17 лет обратной совместимости.

              Коль уж мы разобрались с бессмысленностью сжатия файла бэкапа (которая в общем-то есть в виде аттрибута в 2008м+ сервере больше девяти лет, правда только в Enterprise-версиях), то с остальным будет всё ещё проще. Права sysadmin вполне себе дают xp_cmdshell, и при аккуратном его использовании можно добиться того же эффекта (и даже сжимать бэкапный файл 7зипом после архивации, если вдруг прям очень сильно надо).

              В общем, пока я был юн и неопытен — баловался скриптами для архивации СУБД. Как только пришло время точных сроков восстановления — сразу же symantec\ms dpm\yosemite — выбирайте по бюджету. Последняя, кстати, точно стоит дешевле месяца работы разработчика.

              • 0
                Интересно было бы узнать, ради каких объемов данных это все затевалось и какова частота бэкапов?
                • 0
                  Для любых. Результат всегда радует, а опции выбираются под конкретную ситуацию.
                • 0
                  Ребят, в bareos, имеется отличный плагин, который умеет сжатие, инкремент, и готовые тулзы для админства. :) Всё это free and opensource

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