Настройка зеркалирования базы для MS SQL

19.05.17

База данных - Архивирование (backup)

Очень удобный способ, когда нам нужна не просто резервная копия, а "горячая" замена серверов.

Для настройки зеркалирования на MSSQL для начала необходимо настроить  доступ между ними.
Один сервер у нас будет основной, а второй зеркальный.
Первое что надо сделать, это настроить доступ между этими серверами, разрешить порты (по молчанию 1433-1434) и наши порты, которые мы укажем в настройках.
ВАЖНО Наша база данных должна иметь модель восстановления “FULL”
И так
1.  Создаем сертификат и контрольную точку «DBMirrorEndPoint» на основном сервере, указав в сертификате пароль, дату актуальности и сохраним его в папку (для примера в D:\Certificate), также укажем порт для соединения (для примера 5022)

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 = '17/05/2017',
EXPIRY_DATE = '17/05/2027';
GO
BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'D:\Certificate\PrincipalServerCert.cer'

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
)

2.  Создаем сертификат и контрольную точку «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 = '17/05/2017',
EXPIRY_DATE = '17/05/2021';
GO
BACKUP CERTIFICATE MirrorServerCert TO FILE = = 'D:\Certificate\MirrorServerCert.cer'
GO
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
)

У нас есть сертификаты и контрольные точки, для соединения 2 серверов необходимо создать юзеров на обоих, привязав их к нашим сертификатам.

3. Копируем сертификаты с одного сервера на другой, в папке с сертификатами (у нас ‘D:\Certificate\’) должно быть по 2 сертификата.

4. Создаем на основном сервере юзера «MirrorUser», этого юзера привязываем к сертификату из зеркала «MirrorServerCert»

USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'MirrorUser')
CREATE LOGIN MirrorUser WITH PASSWORD = 'пароль юзера'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'MirrorUser')
CREATE USER MirrorUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'MirrorDBCertPub')
CREATE CERTIFICATE MirrorDBCertPub AUTHORIZATION MirrorUser
FROM FILE = 'D:\Certificate\MirrorServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorUser
GO

5. Создаем на зеркале юзера «PrincipalUser», этого юзера привязываем к сертификату из основного сервера
«PrincipalDBCertPub»

USE MASTER
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'PrincipalUser')
CREATE LOGIN PrincipalUser WITH PASSWORD = 'пароль юзера'
IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'PrincipalUser')
CREATE USER PrincipalUser;
IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'PrincipalDBCertPub')
CREATE CERTIFICATE PrincipalDBCertPub AUTHORIZATION PrincipalUser
FROM FILE = 'D:\Certificate\PrincipalServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalUser
GO

Связь между серверами готова. Теперь надо настроить базы данных.

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

Перенесем файл бэкапа на зеркало (у нас, в корень диска D), укажем путь к файлам БД

RESTORE DATABASE [Наша база]
FROM DISK = 'D:\ Наша база.bak' WITH NORECOVERY
,MOVE N'MIRROR_TEST' TO N'D:\MSSQL_DB\Наша база.mdf'
,MOVE N'MIRROR_TEST_log' TO N'D:\MSSQL_DB\Наша база_log.ldf'
3. Запускаем зеркалирование на зеркале

ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://основной сервер:5022'

4. Потом на основном

ALTER DATABASE MIRROR_TEST SET PARTNER = 'TCP://зеркало:5023'
5. Для того чтобы подключатся с 1С к любой из баз необходимо применить асинхронный режим зеркалирования 

— Task — Mirror — Hight performance (asynchronous)  
(— Задачи — Создать зеркальное отображение — Высокая производительность (асинхронный))

Теперь можно работать на двух базах одновременно. Данные будут передаватся между ними

Изменить роли сервера можно в
— Task — Mirror — Failover  
(— Задачи — Создать зеркальное отображение — Отработка отказа)

Если основная БД упала, то нужно оживить зеркало запустив
ALTER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
После этой команды зеркальная база становится основной, а основная после решения проблем станет зеркальной и будет синхронизироваться з основной

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

См. также

Журнал изменений с восстановлением состояния ссылочных объектов и архивацией по HTTP / COM (расширение + конфигурация, 8.3.14+, ЛЮБАЯ конфигурация)

Архивирование (backup) Журнал регистрации Поиск данных Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 1С:Управление торговлей 11 Платные (руб)

База данных «сама» меняет данные в документах/справочниках? Тогда данный журнал изменений для Вас! Практически не влияет на скорость записи объектов за счет быстрого алгоритма! Скорость работы почти в 2 раза выше типового механизма "История изменений"! Позволяет следить за изменениями и удалением в любых ссылочных объектах конфигурации, с возможностью архивации по HTTP(!) или COM, и сверткой данных. А так же, может восстановить состояние реквизитов (значения) до момента изменения или удаления объекта из базы. Есть ДЕМО-база где можно самостоятельно протестировать часть функционала! Работает на любых платформах выше 8.3.14+ и любых конфигурациях! Версия 3.1 от 24.08.2023!

19200 руб.

15.05.2017    42473    10    24    

38

BackUPv8 - система резервного копирования баз 1С

Архивирование (backup) Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

Автоматическое создание копий файловых и серверных информационных баз 1С Предприятие 8 и размещение копий в облаке Яндекс.Диск, локальном или сетевом ресурсе.

1200 руб.

03.09.2014    14675    12    6    

17

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7360    149    ZAOSTG    66    

95

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5767    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8591    ivanov660    6    

75

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    4999    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    15962    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. МихаилМ 19.05.17 22:03 Сейчас в теме
автор так спешил что забыл указать, для каких версий мс скл сие пригодно. или на какой версии автор тренировался.
а также забыто , что будет при реструктуризации
2. ArchLord42 83 20.05.17 05:56 Сейчас в теме
Есть несколько нюансов, которые не описал автор:

- Для того чтобы не производить ручную обработку отказа (ALT ER DATABASE MIRROR_TEST SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS), можно поставить третий - Witness (следящий) сервер, который будет эти заниматься в автоматическом режиме.

- Как верно заметил Михаил (1), есть особенность реструктуризации, а точнее ее невозможность в некоторых случая, будучи в режиме зеркалирования.
Вводится ограничение на создание новых справочников, доков, регистров, ПВХ, вы попросту не сможете обновится с ошибкой:
Операция не может быть выполнена для базы данных "test", так как она участвует в сеансе зеркального отображения или группе доступности. Некоторые операции недопустимы для баз данных, участвующих в сеансе зеркального отображения или группе доступности.

Для того, чтобы обновить 1С, вам будет нужно отключить зеркалирования, а после включить обратно (проще всего сделать скрип на вкл и выкл)

- Зеркалирование доступно с версии >= 2005 MSSQL

- Во избежании коллизий и потерь данных, не рекомендуется работать сразу в 2х базах в асинхронном режиме, т.к. по сути это будет работа с 2х разных ИБ подключенных к одной БД, что чревато, но синхронный режим замедляет (не сильно) работу, т.к. ждет окончания транзакций во второй базе тоже.

- В синхронном режиме, к резервной базе доступа нет, она всегда находится в режиме восстановления, но в версиях MSSQL >= 2012 при подключении через Microsoft SQL Server Native Client (так же используется сервером 1С) происходит редирект на основную базу.

- Если поднять Windows Server Failover Clustering (WSFC), лучше использовать AlwaysOn (легко гуглится)
maxopik2; Spacer; angur; WellMaster; METAL; EliasShy; cleaner_it; AlexGroovy; Denis_Viktorovich; MsDjuice; user747571; Danila-Master; h00k; Lem0n; МихаилМ; +15 Ответить
3. MsDjuice 114 22.05.17 12:16 Сейчас в теме
(2)
чтобы обновить 1С, вам будет нужно отключить зеркалирования, а после включить обратно (проще всего сделать скрип на вкл и выкл)

Это при каждом обновлении будет проблема такая, или иногда?
4. ArchLord42 83 22.05.17 18:40 Сейчас в теме
(3) да, только если вы добавляете новые объекты МД, который я описал выше.
т.е. если вы будете реквизит к справочнику добавлять то все спокойно обновится, но если вы добавите новый справочник то придется отключать.
12. ArchLord42 83 25.05.17 06:24 Сейчас в теме
(2) (9)
а точнее ее невозможность в некоторых случая, будучи в режиме зеркалирования.
Вводится ограничение на создание новых справочников, доков, регистров, ПВХ, вы попросту не сможете обновится с ошибкой:


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

В общем я решил проверить на тестовой базе, запустил еще раз тестовую базу и вот результат:
1) попытался создать первый док - ошибка.
2) отрубил зеркалирование, обновился запустил зеркалирование.
3) Добавил второй док - все ок
4) Начал добавлять разные объекты, все ок опять же.

при добавлении первого дока идет еще какие-то изменения в БД (скрин)

ЗЫ. Написанное мной в первом посте по поводу обновлений, это мой личный опыт работы с двумя конфигурациями на БСП, возможно при некоторых обстоятельствах возможность обновить отсутствует, при других вы сможете спокойно обновляться.
Прикрепленные файлы:
5. oldfornit 22.05.17 18:55 Сейчас в теме
чем это лучше использования always-on MS SQL?
6. ArchLord42 83 22.05.17 19:10 Сейчас в теме
(5)
т.к. подключение идет к кластеру, а не к конкретной базе, при отказе одной БД юзеры, которые ни чего не проводили\записывали ничего не почувствуют, при условии что развернут кластер 1С (если БД и 1С на одном сервере находятся).
Можно юзать резевную БД, но только в режими readonly.

ЗЫ не заметил, что вы наоборот спросили, ответ : ничем, только если ценой развертывания и возможности географического распределения серверов.
7. Mortum 23.05.17 21:45 Сейчас в теме
Always-on вроде домен требует, а зеркалирование, как уже написали, не позволит добавлять новые таблицы. Лучше уж настроить transaction log shipping с нужным интервалом времени, за который не жалко потерять данные. Из минусов только потеря части данных с момента последнего бэкапа логов и ручное переключение на резервный сервер.
8. ArchLord42 83 24.05.17 08:41 Сейчас в теме
(7)домен ему априори нужен т.к. все таки главное требование это фейл овер кластер винды, что намного затратнее)
9. MsDjuice 114 24.05.17 14:56 Сейчас в теме
Пробовали добавлять новые таблицы: справочники, документы с реквизитами разных типов. Все отлично переносит. Может у кого-то работало нестабильно, нам же на наших базах не удалось воссоздать проблему.
10. demaxxx 24.05.17 19:43 Сейчас в теме
А есть где нибудь инструкция, как делать быстрое зеркало базы в рамках одного сервера?
Т.е. быстро делать снимок и сразу же заливать в другую для экспериментов. Вручную или по расписанию.
11. ArchLord42 83 25.05.17 05:53 Сейчас в теме
(10) Вы можете установить два именных экземпляра (instance) на один сервер и работать с ними.
Установка в целом стандартная, только в момент выбора параметров экземпляра, нужно просто переключится на именованный экземпляр ну и назвать его там inst1\inst2 в рамках 2х экземпляров можно развернуть зеркалирование, только кроме как для тестов смысла в этом нет.
Прикрепленные файлы:
13. user762605 30.05.17 14:38 Сейчас в теме
1. Зеркалирование - жалкая попытка убежать от невозможности реплицирования. Ничем по сути от него не отличающаяся. Зеркалирование не дает преимущества, которое написано ниже по отношению к AlwaysOn (который требует всего то втащить сервер с mssql в домен).
2. Репликация (sic!) 1Сной базы не возможна ни один вариант из 3х предлагаемых не подходит. (Это вы поймете прочитав элементарное описание технологий)
Не удивляйтесь при использовании 1 и 2, если увидите "база разрушена/повреждена" и т.п.
3. Единственный выход - alwayson, который дает хоть какое то подобие failover для 1С базы. Опять же надо решить вопрос с бэкапированием.
В добавок к alwayson есть softpoint, который позволит шуршать довольно быстренько. В обход softpoint'а можете заставить своих разрабов перевести все запросы на чтение к secondary mssql-серверу (идеальный мир, ну).
14. пользователь 23.08.19 09:52
Сообщение было скрыто модератором.
...
15. wding 25.06.20 10:35 Сейчас в теме
Добрый день!
Все выполнил, но при последней команде

ALTER DATABASE Retail_FR_Test SET PARTNER = 'TCP://srv-1c-r1.samotlor.biz:5023'

Пишет: База данных "Retail_FR_Test" не настроена для зеркального отображения.

Что не так? Что необходимо сделать?
22. user1841948 12.09.22 10:58 Сейчас в теме
(15)
Вы восстановили БД из бэкапа с параметром RESTORE WITH RECOVERY, а нужно восстановить с "WITH NO RECOVERY"
16. user1550609 24.03.21 13:17 Сейчас в теме
такая ошибка когда создаю пользователя таким скриптом:
USE MASTER
GO
IF NOT EXISTS(SEL ECT 1 FR OM sys.syslogins WHERE name = 'PrincipalServerUser')
CREATE LOGIN PrincipalServerUser WITH PASSWORD = 'пароль'
IF NOT EXISTS(SEL ECT 1 FR OM sys.sysusers WHERE name = 'PrincipalServerUser')
CREATE USER PrincipalServerUser;
IF NOT EXISTS(SEL ECT 1 FR OM sys.certificates WH ERE name = 'PrincipalDBCertPub')
CREATE CERTIFICATE PrincipalDBCertPub AUTHORIZATION PrincipalServerUser
FROM FILE = 'C:\Certs\PrincipalServerCert.cer'
GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO PrincipalServerUser
GO

Cannot find the endpoint 'DBMirrorEndPoint', because it does not exist or you do not have permission

путь к сертификату указан верно
17. MsDjuice 114 24.03.21 17:32 Сейчас в теме
Не создана контрольная точка зеркалирования.
Возможно ограничение прав
Прикрепленные файлы:
18. user1550609 25.03.21 10:44 Сейчас в теме
(17) розобрался, ендпоинт уже был с другим именем

сейчас столкнулся с проблемой:
делаю все по мануалу в локальной сети все работает, пробую реализовать из внешней сети на локальную, службы SQL не видят друг друга, на микротике все нужные пробросы есть, не могу понять в чем дело
19. user1550609 25.03.21 11:05 Сейчас в теме
(18)
на микротике все нужные пробросы есть, не могу понять в чем дело


при прямом подключении на локальный сервак служб SQL не видит.
Прикрепленные файлы:
20. MsDjuice 114 25.03.21 14:26 Сейчас в теме
Не хватает порта. Надо пробрасывать все для SQL + порт указаный для зеркала
Я пробрасывал:
5022-5023 для точки зеркалирования
1433-1437 SQLные
tcp и udp
+ надо или указать белій список ІР адресов, или делать ВПН (для безопасности)
21. user1550609 25.03.21 15:13 Сейчас в теме
(20)
SQLные

спасибо за совет, иду тестить
23. user1949156 18.05.23 06:53 Сейчас в теме
При команде "Начать зеркалирование" выдает ошибку, что операция не может т.к. БД в состоянии restoring
24. Pawlick 10 24.01.24 19:54 Сейчас в теме
Не могу сообразить: сервера должны находится в одной подсети, или не обязаны?
Оставьте свое сообщение