Зеркалирование баз данных на MS SQL

    Доброго дня. Решил я описать здесь свой опыт настройки зеркалирования БД. Не имея, до недавнего времени, подобного профита, я начал сёрфить интернет в поисках информации на этот счёт. И постараюсь оформить пост как пошаговая инструкция рассказать об основных моментах, в общем что бы ничего лишнего.

    Интро
    Для резервирования БД рассматривали 2 варианта:
    — репликации
    — зеркалирование
    Репликации отпали, потому, как некоторые таблицы не могут реплицироваться и вообще специально под это дело надо сразу предусматривать структуру базу данных.
    Зеркалирование работает на ура! В результате тестов клал основную базу, переводил зеркальную в главную. После того как поднимал главную — та автоматом становилась зеркальной, менял их местами. Всё прошло без сучка, без задоринки. (Дай бог ей долгого здравия!)

    Вообще есть 3 режима зеркалирования:
    — защищённый с автоматическим восстановлением
    — защищённый с ручным восстановлением
    — не защищённый/асинхронный
    Защищённый отличаются от асинхронного тем, что не ждут подтверждения принятия транзакции на зеркальном сервере, а продолжают работать и набрасывают в очередь новые и новые транзакции.
    Защищённый с автоматическим восстановлением требует для автоматического восстановления использовать 3-й сервер (следящий) и в принципе полезен только если у вас в приложении можно указать резервный сервер для переключения в случае когда не работает основной. Поскольку мне было жалкао засарять следящими серверами информационное пространство и приложения работающие с базой тоже не имело возможности переключаться самостоятельно.
    Я настраивал базы на работу в защищённом режиме с ручным восстановлением.

    Вот хорошая инструкция на TechNet'е.
    А здесь в картинках показано как это сделать через GUI.

    Часть 1. Настройка связи сервера.
    Для связи серверов друг с другом на обоих машинах создаются контрольные точки, открываются порты на соединение, создаются пользователи, сертификаты и пр.
    Создадим контрольные точки, для авторизации мы будем использовать сертификат сгенерированный MS SQL сервером (так же можно использовать и другие сертификаты).

    1. Создаём сертификат на главном сервере и сохраним его в паку D:\Certs
    USE MASTER
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'секретный пароль'
    GO
    IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1)
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'PrincipalServerCert')
    CREATE CERTIFICATE PrincipalServerCert
    WITH SUBJECT = 'Principal Server Certificate',
    START_DATE = '08/15/2011',
    EXPIRY_DATE = '08/15/2021';
    GO
    BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'D:\Certs\PrincipalServerCert.cer'


    2. Создадим контрольную точку DBMirrorEndPoint на главном сервере.
    USE MASTER
    GO
    IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
    CREATE ENDPOINT DBMirrorEndPoint
    STATE = STARTED AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE PrincipalServerCert, ENCRYPTION = REQUIRED
    ,ROLE = ALL
    )


    3. Создаём сертификат и контрольную точку DBMirrorEndPoint на зеркале, по аналогии с главным.
    USE MASTER
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'секретный пароль'
    GO
    IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1)
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'MirrorServerCert')
    CREATE CERTIFICATE MirrorServerCert
    WITH SUBJECT = 'Mirror Server Certificate',
    START_DATE = '08/15/2011',
    EXPIRY_DATE = '08/15/2021';
    GO
    BACKUP CERTIFICATE MirrorServerCert TO FILE = 'D:\Certs\MirrorServerCert.cer'

    IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
    CREATE ENDPOINT DBMirrorEndPoint
    STATE=STARTED AS TCP (LISTENER_PORT = 5023)
    FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MirrorServerCert, ENCRYPTION = REQUIRED
    ,ROLE = ALL
    )


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

    4. Копируем сертификаты с одного на другой сервак, чтобы в папке D:\Certs лежало по 2 сертификата.


    5. Создадим на главном сервере пользователя MirrorServerUser, этого пользователь привязываем к сгенерированному и скопированному с зеркального сервера сертификату MirrorDBCertPub
    USE MASTER
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'MirrorServerUser')
    CREATE LOGIN MirrorServerUser WITH PASSWORD = 'секретныйпароль2'
    IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'MirrorServerUser')
    CREATE USER MirrorServerUser;
    IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'MirrorDBCertPub')
    CREATE CERTIFICATE MirrorDBCertPub AUTHORIZATION MirrorServerUser
    FROM FILE = 'D:\Certs\MirrorServerCert.cer'
    GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerUser
    GO


    6. Создадим на резервном сервере пользователя PrincipalServerUser, этого пользователь привязываем к сгенерированному и скопированному с главного сервера сертификату PrincipalDBCertPub
    USE MASTER
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'PrincipalServerUser')
    CREATE LOGIN PrincipalServerUser WITH PASSWORD = 'секретныйпароль2'
    IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'PrincipalServerUser')
    CREATE USER PrincipalServerUser;
    IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'PrincipalDBCertPub')
    CREATE CERTIFICATE PrincipalDBCertPub AUTHORIZATION PrincipalServerUser
    FROM FILE = 'D:\Certs\PrincipalServerCert.cer'
    GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerUser
    GO


    Связь между серверами настроена!

    Часть 2. Настройка баз данных.
    Здесь нам надо будет снять бэкап с рабочей базы, поднять его на зеркальном сервере в режиме NORECOVERY и включить режим зеркалирования.
    Зеркалируемая база данных должна иметь модель восстановления FULL.

    1. Снимаем бэкап рабочей БД.
    BACKUP DATABASE [MIRROR_TEST] TO DISK = N'D:\MIRROR_TEST.bak'
    WITH FORMAT, INIT, NAME = N'MIRROR_TEST-Full Database Backup',STATS = 10


    2. Поднимаем его на зеркальном (скрипт подразумевает, что файл бэкапа перенесён на зеркальный сервак на диск D)
    RESTORE DATABASE [MIRROR_TEST]
    FROM DISK = 'D:\MIRROR_TEST.bak' WITH NORECOVERY
    ,MOVE N'MIRROR_TEST' TO N'D:\MSSQL_DB\MIRROR_TEST.mdf'
    ,MOVE N'MIRROR_TEST_log' TO N'D:\MSSQL_DB\MIRROR_TEST_log.ldf'


    3. Для запуска зеркалирования на зеркальном сервере выполняем:
    ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMAINSERV:5022'

    4. Затем на главном:
    ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://MSSQLMIRRORSERV:5023'

    Если вылезет ошибка типа:

    The mirror database, “MIRROR_TEST”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

    или

    The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

    Сделайте бэкап журнала с базы на главном сервере и восстановите его на зеркальном (опять же в режиме NORECOVERY).
    Бэкап:
    BACKUP LOG MIRROR_TEST TO DISK = 'D:\MIRROR_TEST.trn'

    Восстановление:
    RESTORE LOG MIRROR_TEST
    FROM DISK = 'D:\MIRROR_TEST.trn' WITH NORECOVERY


    Часть 3. Восстановление после сбоев. Изменение ролей.
    Изменить роли сервера, чтобы зеркальный стал главным и наобород можно через GUI кликнов правой кнопкой по базе — Task Mirror Failover или же через команду T-SQL
    ALTER DATABASE MIRROR_TEST SET PARTNER FAILOVER

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

    Если же грохнулась главная база, то чтобы оживить резервную нужно выполнить принудительное восстановление
    ALTER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    правда в этом случае существует риск потерять некоторые данные (про это много написано здесь)

    При выполнении принудительного восстановления зеркальная база становится главной, а бывшая главная после восстановления автоматически станет зеркальной, ожидающей разрешения продолжить сеанс зеркалирования. Для чего нужно выполнить
    ALTER DATABASE MIRROR_TEST SET PARTNER RESUME
    Вот вроде и всё! Пока работает 8-)
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 36

      0
      «Не имея, до недавнего времени, подобного профита» — wat
        0
        Возможно имелось ввиду другое иноземное слово «экспирианс».
          +12
          Т.е. слово «опыт» отменили… Как всё быстро летит ^_^
            0
            Там в двух предложениях подряд получилось бы слово «опыт».
            Экспирианс тоже ничего, но профит на ум быстрей пришло.
            Слово как слово…
              0
              I weep for the future of IT.
              И, да, как сказали ниже, с сертификатами что-то перемудрили. Т.е. дисбаланс какой-то технологический вышел: подняли простенькое зеркало, но с использованием сертификатов. Зачем? Чем обусловлен выбор?
              И хотелось бы побольше дополнительной информации: что за база, размеры, какова нагруженность, тип нагрузки (транзакции или хранилище данных), что «получилось» потерять при обрушении мастера, замеряли ли трафик между серверами при зеркалировании, зачем вообще зеркалирование сделали. А то получилось какое-то зеркалирование ради зеркалирования и мануал который гуглится за 10 минут. :\
                0
                Почему простенький? Обычное зеркало. С сертификатами мне показалось более изящное решение. Не каких регламентных требований не было установлено, я сделал сертификаты :-) Базы master были идентичные, так что после переключения клиентов проблем в общем-то не было. Вы можете назвать много причин для зеркалирования, кроме обеспечения отказоустойчивости? Трафик, там незначительно увеличился, потому как ижет передача журнала физически плюс сжатие, в отличие от той же репликации, я до работы доберусь могу скинуть значаний если интересно.
                  +3
                  >Почему простенький?
                  Потому что вы не вдавались в подробности. Не сообщили параметры защищаемой БД, не описали почему был выбран этот вариант для повышения отказоустойчивости и даже не потрудились накидать схемку для иллюстрации топологии решения и как зеркалирование поможет в повышении этой самой отказоустойчивости в ваших условиях (Одним зеркалированием ведь дело не ограничивается, в смысле, вот принципал упал, теперь у вас есть зеркало на другом сервере — и что? Смогут его юзеры использовать?). Не рассказали про дополнительные параметры зеркалирования, просто тут по-дефолту, там по-дефолту, пока работает, миссия выполнена. Поэтому и выглядит ваша работа как зеркалирование ради зеркалирования от нечего-делать и вряд ли кому-то будет полезно, поскольку невозможно сравнить ситуации.

                  Решение с использованием сертификатов не изящное. Оно накладыват определённые ограничения, ответственность и увеличивает стоимость поддержки из-за необходимости их периодического обновления (а если ещё и не задокументировано должным образом, то может обернуться неприятным сюрпризом для следующего человека, которому достанется обслуживание инфраструктуры). Обычно оно применяется когда серверы находятся в разных доменах или вообще разнесены географически.
              +3
              Можно ещё смайл обсудить в конце повествования.
          0
          Кстати, срок действия сертификатов по умолчанию — год, что может стать неприятным сюрпризом. И настройка для Windows Authentication немного проще — позволяет первые три пункта сделать через UI.
            0
            Ну можно дату начала действия и конца действия указать (START_DATE, EXPIRY_DATE).
            А вообще да, можно и так и так кому как нравится.
              0
              >кому как нравится
              Are you f*ckin' kiddin' me? Решение о выборе варианта фейловера для БД принимается на основе личных предпочтений администратора? Это плохой сон. Ущипните меня Т_Т
                0
                Метода авторизации в регламентах прописано не было, почему бы не принять решение самому. Быстродействию, безопасности от использования сертификатов хуже ведь не делается.
                  0
                  аутентификация притормаживает быстродействие
                    0
                    Аутентификация для mirroring endpoint в любом случае будет, но она же на скорость работы установленного соединения не влияет. Может притормаживать шифрование, но оно задается отдельной опцией.
                      0
                      правильно, но аутентификация происходит при каждом обращении одной базы к другой и в результате обращения проишодит обмен сертификатами. Вот этот обмен и проверка сертификата и притормаживает. Это происходит быстро но на общее время у нас мелкие задержки. Это некритично при малом количестве транзакций.
                      Это как в примере:
                      аутентификация Ты кто? — Я Вася, а пароль? — Пупкин, Проходи…
                      аутентификация с проверкой сертификата: Ты кто? -Я Вася, а пароль? — Пупкин, Номер паспорта? — 2344241313131232, Подожди я проверю если номер совпадает… Слышь? У тебя с номером все в порядке, проходи…
                      Ну пример может не совсем так показывает общую картину но думаю что идея понятна.
                        +1
                        Идея понятна, но где подтверждение, хоть одной ссылкой?

                        Да, я настраивал зеркалирование на практике, для большого количества баз одновременно. И точно знаю, что на самом деле все не так, как вы описали. Базы не обращаются друг к другу. Все отзеркалированные базы используют те же 5-6 постоянных соединений.

                        Аутентификация происходит при установлении соединения, один раз, это явно сказано в msdn.microsoft.com/en-us/library/ms186360.aspx: Connection requests from a partner or witness, if any, must be authenticated.

                        Соединения устанавливается один раз, при старте первой сессии. Попробуйте настроить зеркала для нескольких баз и сделать выборку из sys.dm_db_mirroring_connections. Соединения постоянные, никуда не пропадают. Из описания dmv тоже достаточно очевидно, что аутентификация — часть connection handshake, а не самого протокола передачи транзакций.
                  0
                  «Are you f*ckin' kiddin' me?» — говорите вам слово «профит» не угодило??
                    +1
                    Моя фраза означает «вы что, бл*дь, шутите?» и органично вписывается в контекст моего комментария. Слово «профит» означает «выгода» — и либо я не понял, что вы хотели сказать, либо вы не знали значения слова.
                    0
                    а разве администратор не отдает предпочтение чему либо? есть администраторы которые действуют только по книжке и выделенный текст скопируют правым кликом мышки и есть котоым хочется драйва после истечения срока сертификата…
                    я сервер установил один раз, соединил диски через LVM и все… прошли месяцы а то и года и даже забыл как LVM делается и через несколько лет чуствую себя плохим специалистом ибо уже не помню как то или то делается потому что работает и не падает, тогда как другие знают все команды и многое другое ибо делают по принципу как нравится. Это падает, он поднимает или делает заново, опять падает, опять поднимает и так выучивает все наизусть…
                      0
                      Отдаёт, я отдал предпочтение срок действия же разный можно прописать. Принципиальной разницы-то нету кроме личных предпочтений (если это предпочтения компании).
                        0
                        Предпочтение должно быть обоснованным, потому что если что-то важное упадёт, и компания потеряет время/деньги, то начнут разбираться, почему оно упало, и здесь может быть 2 продолжения истории: либо всё было сделано объективно правильно и виновато недостаточное финансирование, тогда ура, играемся с новыми серваками и железом; либо админ применил сомнительные и далеко не оптимальные решения, руководствуясь своими «предпочтениями», тут уже всё может стать куда мрачнее вплоть до увольнения, а потом долгих поисков работы, куда бы приняли с таким-то отзывом с предыдущего места.
                          0
                          это если мы говорим о компании с определённым количеством спецов в ИТ отделе, где есть и начальник отдела и все действия согласуются с ним. Если говорим о мелких или средних фирмах где отдел подчиняется шефу фирмы и где каждый ИТшник сам по себе то там так и происходит — каждый из них выбирает то ему больше понравилось. Нередко есть фирмы где «ИТ» менеджер понятия не имеет что ему обьясняет админ. Или фирма где админ и есть начальник отдела и крутит-вертит всё как ему захочется. Вводит в заблуждение или в страх руководство компании себе в выгоду. Меня так и наняли на работу ибо у руководства были большие сомнения насчет ихнего «ИТ» отдела. В результате: главный админ уволен ибо то что он построил было сделанно наспех и чтобы работало и он свято верил что данная архитектура и применяемый софт то что доктор прописал. Потом со временем сокращен отдел с 7 до 3 работников — ибо нефиг столько алкашей держать в штатах (куда молодежь катиться?). И радикально изменена ИТ инфраструктура. Зато как сложно было это сделать с 24х7 активно используемой пользователями системой да еще так чтобы сами не заметили изменений.
                          И все потому что никто кроме начальника отдела (админа) не понимал ни черта из того что он всем впихивал.
                            +1
                            Ну вот, видите: админ работал по предпочтениям — теперь уволен. Всё сходится ^_^
                              0
                              Просто тут все в большом маштабе происходило и видать где-то он запутался а кто-то запомнил что он раньше сказал. А тут админ сертификацию по принципу «кому как нравиться» поставил. Выше я дал как бы пример что большинство админов/програмистов работают по данному принципу.
                                +1
                                И даже это может обернуться серьёзными потерями, ситуация: админ поставил аутентификацию по сертификату, год всё работало и никто зеркало не трогал, вдруг обрушился принципал-сервер, админ тянется к зеркалу — а оно не обновлялось 3 недели. Почему? А сертификат протух и этого никто не заметил (и не заметят с таким-то уровнем, уверяю вас). Кто виноват?/Что делать?
                                  +1
                                  >>сертификат протух и этого никто не заметил (и не заметят с таким-то уровнем, уверяю вас).
                                  >>Кто виноват?
                                  Ответственный за:
                                  а) мониторинг
                                  б) бэкапы
                                  в) исполнение процедур

                                  >>Что делать?
                                  а) делать мониторинг и следить за тем что он выдаёт
                                  б) следить за актуальностью и восстановимостью бэкапов (и не говорите мне что миррореная база не бэкап, и что бэкап на мастере должен лежать только на этом же сервере)
                                  в) прописывать процедуры и тщательно им следовать, в том числе и:
                                  1) проверка валидности сертификатов
                                  2) ведение календаря временных сущностей
                                  3) инструкция по обновлению сертификатов и запуску зеркалирования в т.ч. с нуля.

                                  Если кто-то делает на «авось» (и в данном случае это не админ, а руководство, которое не желая понимать что БП на 99,9% зависим от IT не тратит на него деньги) — то тут ничего для предотвращения сбоя не поможет (не протухнут сертификаты — так харды повылетают, или учетки протухнут по паролю если им не меняют вовремя пароль\не стоит «без срока действия»\новый админ решит «усилить безопасность» и ВСЕМ учёткам поставит «требовать смену пароля») — а виноват будет человек который поднимал.
                                    0
                                    Согласен! Нужны настроенные уведомления о резервных копиях, о сбоях в работе серверов, в том числе зеркалировании (с этим только разбираюсь, если кто умеет за совет буду признателен), бэкапы разнесенные хотя бы по серверам. Существует корпоративная база знаний, дальнейшая работа будет вестись в соответствие с описанием от туда.
                                0
                                Сравнение по-принципу «Черный это цвет и белый это цвет — значит чёрно-белый телевизор цветной».
                                Не вижу проблемы! Сделал авторизацию по сертификатам, описал структуру в корпоративной базе знаний. Все вкурсе. Вам нравится виндовая авторизация — дерзайте! В тексте приложена ссылка как сделать зеркалирование с их использованием через GUI.
                    0
                    Защищённый с автоматическим восстановлением требует для автоматического восстановления использовать 3-й сервер (следящий) и в принципе полезен только если у вас в приложении можно указать резервный сервер для переключения в случае когда не работает основной.

                    Ну в общем-то резервный сервер далеко не всегда надо прописывать...msdn:Using Database Mirroring Да и «засирание информационного пространства следящим сервером» странная аргументация. Можно ведь использовать любой уже имеющийся в сети SQL Server, в т.ч. Express (ну, естественно, кроме тех, которые уже участвуют в этой сессии зеркалирования).

                      0
                      Это если Native Client для подключения используется. Туда тоже надо прописывать.
                      0
                      На сколько я помню там все гораздо проще, через GUI менеджмент студии решается, а лезть с выборками в системные таблицы не есть правильно.
                        0
                        Авторизацию по сертификатам через ГУИ не настраивается. Когда много баз, проще написать скрипт и запускать его, чем кликать по ГУЮ. Вообще тоже дело вкуса, кто как привык!
                        0
                        Спасибо за статью, как раз была необходимость настройки через сертификаты.
                        В понедельник испробую все на работе.
                          0
                          Вопросик: такое зеркалирование можно настроить на двух SQL Express-ах?
                            0
                            Нет. Express может выступать только в роли следящего (witness).
                            0
                            Этот пост когда я впервые подумал о гуугл-эникействе. То есть искание ответов с помощью поисковиков. Я готовился к сдаче MCITP DBA 2005 на уже 2008, но ваучеры были куплены. Кроме всего прочего нужно было знать, как работает зеркалирование. Тут выявился нонсенс — по ходу настройки через GUI нужно было ввести пользователя действа — а какого непонятно, этого не было в книжках. Фирменной документации не было. И поисковики выдавали сертификаты. Внутри домена MS Windows — сертификаты абсурд. Там каждый пакет несколько раз проверяется, сертификаты нужны для работы через интернет. Значит это один из пользователей NT AUTHORITY\NetworkService или как подобный NT AUTHORITY\LocalService, таки первая если правильно помню. Полный гугл сертификатов появился из-за пропуска в документации.
                            Выводы
                            1. Решение с сертификатами пошло бы в минус на экзамене при двух приколах — указано что домен, явно не указано, что сервера связаны через интернет. Решение все равно будет работать, но на экзамене максимум дают за верные ответы. Имея несколько сертификатов/курсов могу уверенно сказать — это полезно на 70% (30% — это религия от маркетологов).

                            Only users with full accounts can post comments. Log in, please.