13.06.2019 08:11
fadeinmad 12 1800 Источник

Предыстория


Как-то раз для воспроизведения бага мне потребовался бэкап production-базы.

К моему удивлению я столкнулся со следующими ограничениями:

  1. Бэкап базы был сделан на версии SQL Server 2016 и не был совместим с моей SQL Server 2014.
  2. На моем рабочем компьютере в качестве ОС использовалась Windows 7, поэтому я не мог обновить SQL Server до версии 2016
  3. Поддерживаемый продукт был частью более крупной системы с сильно связанной легаси-архитектурой и также обращался к другим продуктам и базам, поэтому его развертывание на другой станции могло занять очень много времени.

Учитывая вышеизложенное, я пришел к выводу, что настало время костылей нестандартных решений.

Восстановление данных из бэкапа


Я решил использовать виртуальную машину Oracle VM VirtualBox с Windows 10 (можно взять тестовый образ для браузера Edge отсюда). На виртуальную машину был установлен SQL Server 2016 и на нем из бэкапа была восстановлена база данных приложения (инструкция).

Настройка доступа к SQL Server на виртуальной машине


Далее было необходимо предпринять некоторые шаги, чтобы появилась возможность доступа к SQL Server извне:

  1. Для фаервола добавить правило пропускать запросы на порт 1433.
  2. Желательно, чтобы доступ к серверу шел не через windows-аутентификация, а через SQL по логину и паролю (проще настроить доступ). Однако в этом случае нужно не забыть включить в свойствах SQL Server возможность SQL-аутентификации.
  3. В настройках пользователя на SQL Server на вкладке User Mapping указать для восстановленной базы роль пользователя db_securityadmin.

Перенос данных


Собственно сам перенос данных состоит из двух этапов:

  1. Перенос схемы данных (таблицы, представления, хранимые процедуры и т.д.)
  2. Перенос самих данных

Перенос схемы данных


Выполняем следующие операции:

  1. Выбираем Tasks -> Generate Scripts для переносимой базы.
  2. Выбираем нужные для переноса объекта или оставляем значение по умолчанию (в этом случае будут созданы скрипты для всех объектов базы).
  3. Указываем настройки для сохранения скрипта. Удобнее всего сохранить скрипт в единый файл в кодировке Unicode. Тогда при сбое не понадобится заново повторять все шаги.

После сохранения скрипта его можно выполнить на исходном SQL Server (старой версии), чтобы создать требуемую базу.

Внимание: после выполнения скрипта необходимо проверить соответствие настроек базы из бэкапа и базы, созданной скриптом. В моем случае в скрипте отсутствовала настройка для COLLATE, что приводило к сбою при переносе данных и танцам с бубном пересозданию базы с помощью дополненного скрипта.

Перенос данных


Перед переносом данных необходимо отключить проверку всех ограничений на базе:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Перенос данных осуществляем с помощью мастера импорта данных Tasks -> Import Data на SQL Server, где находится созданная скриптом база:

  1. Указываем настройки подключения к источнику (SQL Server 2016 на виртуальной машине). Я использовал Data Source SQL Server Native Client и вышеупомянутую SQL-аутентификацию.
  2. Указываем настройки подключения к месту назначения (SQL Server 2014 на хост-машине).
  3. Далее настраиваем маппинг. Необходимо выбрать все не read-only объекты (например, представления выбирать не нужно). В качестве дополнительных опций следует выбрать «Разрешить вставку в identity-столбцы», если такие используются.
    Внимание: если при попытке выделить несколько таблиц и проставить им свойство «Разрешить вставку в identity-столбцы» свойство уже было ранее установлено хотя бы для одной из выделенных таблиц, в диалоге будет отмечено, что свойство уже установлено для всех выделенных таблиц. Данный факт может сбить с толку и привести к ошибкам переноса.
  4. Запускаем перенос.
  5. Восстанавливаем проверку ограничений:
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Если возникли какие-либо ошибки, проверяем настройки, удаляем созданную с ошибками базу, заново создаем ее из скрипта, вносим исправления и повторяем перенос данных.

Заключение


Данная задача встречается довольно редко и возникает только из-за вышеуказанных ограничений. Чаще всего решение заключается в обновлении SQL Server или подключению к удаленному серверу, если это позволяет архитектура приложения. Однако от легаси-кода и кривых рук некачественной разработки никто не застрахован. Надеюсь, что Вам эта инструкция не понадобится, а если все же в ней возникнет необходимость, то поможет сэкономить кучу времени и нервов. Спасибо за внимание!

Список использованных источников


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


  1. osipov_dv
    13.06.2019 11:16
    #20276058
    +1

    по-моему про права и пользователей забыли, или ваш замечательный legacy, работает как это «принято» из под sa?
    и еще, вот так вы включаете обратно ограничения, но чтобы они стали trusted надо делать
    Восстанавливаем проверку ограничений:
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CHECK CONSTRAINT all'


    1. DrPass
      13.06.2019 11:20
      #20276090

      У него же в задаче не миграция продакшен-сервера на предыдущую версию, а просто поднятие бэкапа на более старом серваке. Я, например, со вторым кейсом встречался не раз, а необходимости в первом никогда не видел, и даже не представляю, зачем оно могло бы понадобиться.


      1. fadeinmad Автор
        13.06.2019 12:06
        #20276380

        Да, именно второй кейс и был описан. В данной задаче требовались только данные с продакшен-сервера, чтобы найти причину возникновения бага. Сам сервер так и продолжил работу, как и раньше. А воспроизведение ситуации для поиска ошибки велось уже на компьютере с локально установленной базой, на которой ты сам себе админ. Поэтому проблем с правами доступа не было.


      1. osipov_dv
        13.06.2019 14:01
        #20276954

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


  1. gotch
    13.06.2019 11:47
    #20276264

    Я решил использовать виртуальную машину Oracle VM VirtualBox с Windows 10 (можно взять тестовый образ для браузера Edge отсюда). На виртуальную машину был установлен SQL Server 2016 и на нем из бэкапа была восстановлена база данных приложения (инструкция).

    Вот на этом и стоило остановиться. А вдруг баг вопроизводится только на родной платформе.


    1. DrPass
      13.06.2019 12:04
      #20276360

      В случае с MS SQL чаще бывает наоборот. Количество говнозапросов, которые успешно переваривает 2016, больше, чем те, с которыми справляются 2014, 2012 и 2008.


    1. fadeinmad Автор
      13.06.2019 12:12
      #20276412

      Такая вероятность была минимальна. Основное подозрение было на сами данные в базе. Некий неучтенный кейс поведения приложения, возможно нарушающий консистентность данных. Остановиться на Server 2016 я не мог, так как для базы требовалось дополнительное окружение (другие базы, сильная связанность данных), настройка которого потребовала бы гораздо большего времени.
      Однако, в статье описан не сам поиск бага, а способ, как можно вытащить данные из бэкапа новой версии на сервер более старой версии. Поиск бага — всего лишь причина, по которой пришлось это сделать.
      Поводов для использования этого способа немного, но они могут возникнуть. И, как написано в заключении, надеюсь, что это никому не понадобится.


  1. speshuric
    13.06.2019 20:35
    #20278944

    Как обрабатываются поля с timestamp?
    Как обрабатываются триггера? А DDL? А Logon?
    Как переносить BLOBы? А с учетом хранения в filestream?
    Нормально ли обрабатываются дурацкие set ansi nulls off?
    Что делать, если объекты содержат синтаксические ошибки?
    … И еще вагон граблей


    1. fadeinmad Автор
      14.06.2019 17:20
      #20282218

      Перенос с учетом локализации, согласен, довольно сложный. Однако я заострил внимание на скрипте генерации базы. В него необходимо внести дополнительные настройки, если это потребуется.
      Насчет остального, опять же, я описал основное направление и те проблемы, с которыми столкнулся. Если кто-то поделится своим опытом и дополнит эту инструкцию, будет просто замечательно.


  1. W001fer
    14.06.2019 17:15
    #20282200

    В принципе, описан единственный способ скопировать базу с новой версии скуля на более старую. Был в свое время такой опыт — разработка велась на версии 2008, а боевая площадка была на 2005, страшно матерились постоянно по этому поводу :) То разработчики «забудут», что каких-то инструкций в 2005 нет и приходилось переделывать, то вот так же надо быстро базу с дева в бой запустить. Весело было. В целом все верно, забыли только хранимые процедуры, они таким методом не переносятся, их надо из студии Create To и вперед :)


    1. fadeinmad Автор
      14.06.2019 17:22
      #20282226

      Хранимые процедуры переносятся: код их создания будет присутствовать в скрипте генерации базы. Есть возможность выгрузки только скриптов хранимых процедур.
      Проверено для Sql Server 2014 и Microsoft SQL Server Management Studio 12.0.


      1. W001fer
        14.06.2019 17:47
        #20282320

        Эххх, совсем старый стал, отстал от жизни :) В 2008 часть БД, которая относится к Programmability не переезжала в процессе генерации скрипта создания базы, в современных не проверял, всегда придерживаюсь правила, что дев, тест и прод — одной версии. Во избежание, так сказать.