Как удалить репликацию ms sql
Перейти к содержимому

Как удалить репликацию ms sql

  • автор:

Удаление репликации вручную в SQL Server

В этой статье объясняется, как вручную удалить репликацию в SQL Server.

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 324401

Аннотация

В этой статье описывается удаление репликации с компьютера под управлением Microsoft SQL Server. Чтобы удалить репликацию, необходимо удалить подписки, публикации и распространитель, настроенный для репликации. Репликацию можно удалить, запустив скрипт Transact-SQL, созданный SQL Server Enterprise Manager или SQL Server Management Studio. Однако если невозможно создать скрипт Transact-SQL для удаления репликации, можно вручную удалить репликацию с помощью системных хранимых процедур и других инструкций Transact-SQL. Эта статья содержит сведения о системных хранимых процедурах, которые можно использовать в этом процессе.

Дополнительные сведения о системных хранимых процедурах, упомянутых в этой статье, см. в SQL Server электронной документации.

Удаление репликации вручную

Репликацию можно удалить вручную с помощью системных хранимых процедур и других инструкций Transact-SQL. Чтобы полностью удалить репликацию, выполните следующие действия.

  1. Удалите все подписки, настроенные для репликации.
  2. Удалите все публикации, настроенные для репликации.
  3. Удалите распространитель, настроенный для репликации.

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

Удаление подписок

Чтобы удалить подписки из экземпляра SQL Server, можно использовать следующие хранимые процедуры и соответствующие параметры:

  • sp_dropsubscription : системная хранимая sp_dropsubscription процедура позволяет удалять подписки на определенную статью, публикацию или набор подписок на издателе. Хранимую процедуру необходимо выполнить на сервере издателя в базе данных публикации.
  • sp_droppullsubscription : системная хранимая sp_droppullsubscription процедура позволяет удалить подписку в текущей базе данных подписчика. Хранимую процедуру необходимо выполнить на подписчике в базе данных подписки по запросу.
  • sp_dropmergesubscription : системная sp_dropmergesubscription хранимая процедура позволяет удалить подписку на публикацию слиянием и агент слияния, связанную с публикацией слиянием. Хранимую процедуру необходимо выполнить на сервере издателя в базе данных публикации.
  • sp_dropmergepullsubscription : для удаления подписки на слияние можно использовать sp_dropmergepullsubscription системную хранимую процедуру. Хранимую процедуру необходимо выполнить на подписчике в базе данных подписки по запросу.

Удаление подписок на моментальные снимки

Чтобы удалить принудительная подписка на все статьи для публикации моментального снимка, выполните следующий скрипт на издателе:

USE < **Publication database name** >GO EXEC sp_dropsubscription @publication = N'', @article = N'all', @subscriber = N'all', @destination_db = N'all' 

Чтобы удалить подписку на моментальный снимок по запросу во все статьи для публикации моментального снимка, выполните следующие действия.

    Выполните следующий скрипт SQL на подписчике:

USE < **Subscription database name** >GO EXEC sp_droppullsubscription @publisher = N'', @publisher_db = N'', @publication = N'' 
USE < **Publication database name** >GO EXEC sp_dropsubscription @publication=N'', @subscriber = N'', @article = N'all', @destination_db = N'all' 

Удаление транзакционной подписки

Чтобы удалить принудительная подписка на все статьи для публикации транзакций, выполните следующий скрипт на издателе:

USE < **Publication database name** >GO EXEC sp_dropsubscription @publication = N'', @article = N'all', @subscriber = N'all', @destination_db = N'all' 

Чтобы удалить подписку по запросу на все статьи для публикации транзакций, выполните следующие действия.

    Выполните следующий скрипт на подписчике:

USE < **Subscription database name** >GO EXEC sp_droppullsubscription @publisher = N'', @publisher_db = N'', @publication = N'' 
USE < **Publication database name** >GO EXEC sp_dropsubscription @publication =N'', @subscriber = N'', @article = N'all', @destination_db = N'' 

Удаление подписки слиянием

Чтобы удалить принудительная подписка, выполните следующий скрипт на издателе:

USE < **Publication database name** >GO EXEC sp_dropmergesubscription @publication = N'', @subscriber = N'', @subscriber_db = N'', @subscription_type = N'push' 

Чтобы удалить подписку по запросу, выполните следующие действия.

    Выполните следующий скрипт на подписчике:

USE < **Subscription database name** >GO EXEC sp_dropmergepullsubscription @publication = N'', @publisher = N'', @publisher_db = N'' 
USE < **Publication database name** >GO EXEC sp_dropmergesubscription @subscription_type = N'pull', @publication = N'', @subscriber = N'', @subscriber_db = N'' 

Удаление публикаций

После удаления всех подписок, которые подписываются на публикацию, можно удалить публикацию. После удаления публикаций в базе данных публикации необходимо задать для параметра базы данных репликации для базы данных публикации значение false. Чтобы удалить публикацию, можно использовать следующие системные хранимые процедуры:

  • sp_droppublication : системная хранимая sp_droppublication процедура позволяет удалить публикацию и связанные с ней статьи. Хранимую процедуру необходимо выполнить на издателе в базе данных публикации.
  • sp_dropmergepublication : системная хранимая sp_dropmergepublication процедура позволяет удалить публикацию слиянием и агент моментальных снимков, связанную с публикацией слиянием. Статьи, связанные с публикацией, также удаляются. Хранимую процедуру необходимо выполнить на издателе в базе данных публикации.
  • sp_replicationdboption : системная хранимая sp_replicationdboption процедура позволяет задать параметр базы данных репликации для текущей базы данных. Хранимую процедуру необходимо выполнить на сервере издателя.

Чтобы удалить публикацию моментального снимка, выполните следующий скрипт на издателе:

USE < **Publication database name** >GO EXEC sp_droppublication @publication = N'' USE master GO exec sp_replicationdboption @dbname = N'', @optname = N'publish', @value = N'false' 

Чтобы удалить публикацию транзакций, выполните следующий скрипт на издателе:

USE < **Publication database name** >GO EXEC sp_droppublication @publication = N'' USE master GO EXEC sp_replicationdboption @dbname = N'', @optname = N'publish', @value = N'false' 

Чтобы удалить публикацию слиянием, выполните следующий сценарий на сайте Publisher:

USE < **Publication database name** >GO EXEC sp_dropmergepublication @publication = N'' USE master GO EXEC sp_replicationdboption @dbname = N'', @optname = N'merge publish', @value = N'false' 

Удаление распространителя

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

  • sp_dropsubscriber : системная хранимая sp_dropsubscriber процедура позволяет удалить обозначение подписчика с зарегистрированного сервера. Хранимая процедура удаляет запись реестра для подписчика. Хранимая процедура выполняется на издателе в базе данных публикации.
  • sp_dropdistributor : для удаления распространителя sp_dropdistributor можно использовать системную хранимую процедуру. Хранимая процедура выполняется на распространитетеле. Чтобы удалить название подписчика из Publisher, выполните следующий скрипт на сайте Publisher:

USE master GO EXEC sp_dropsubscriber @subscriber = N'', @reserved = N'drop_subscriptions' 

Чтобы удалить распространитель, выполните на распространитетеле следующий сценарий:

USE master GO EXEC sp_dropdistributor @no_checks = 1 

Использование хранимых процедур

При удалении репликации можно также использовать следующие хранимые процедуры:

    sp_removedbreplication : системная хранимая sp_removedbreplication процедура позволяет удалить все объекты репликации из базы данных без обновления данных на распространитетеле. Хранимую процедуру необходимо выполнить на издателе в базе данных публикации или на подписчике в базе данных подписки. Ниже приведен синтаксис этой хранимой процедуры.

sp_removedbreplication '' 
sp_droparticle @publication = N'', @article = N'', @force_invalidate_snapshot = 1 

Ссылки

Дополнительные сведения см. в следующих разделах электронной документации по SQL Server:

  • Отключение публикации и распространения
  • Удаление публикации
  • Удаление принудительной подписки
  • Удаление подписки по запросу

Обратная связь

Были ли сведения на этой странице полезными?

Как настроить репликацию в MS SQL server не всей таблицы полностью, а конкретных столбцов?

Дорогие коллеги, помогите пожалуйста решить дилемму. Архитектура БД (таблицы и поля) во всех БД идентичны. Нужно настроить однонаправленную репликацию между БД1 и каждой из локальных БД (БД2, БД3, БД4, БД5). Репликация неполная(выборочная), определенный набор таблиц с определенным набором столбцов. Пример: при изменении данных в БД1, во всех локальных БД (БД2, БД3, БД4, БД5), также должны произойти изменения, но только для тех таблиц и полей, которые были указаны в публикации. Для этих целей была выбрана репликация транзакций (т.к. необходима и оперативность репликации). Но каким бы образом я не пытался ее настроить, желаемого результата не получил. В зависимости от выставленного параметра «Действие, если имя уже используется» (в свойствах статьи), репликация либо удаляет на подписчике не отмеченный в публикации столбец(путем пересоздания таблицы). Либо не реплицирует данные вовсе. Исправно реплицируются данные только в случае выбора всех столбцов таблицы. Помогите пожалуйста, как все таки правильно настроить, что бы реплицировались изменения только в отдельных столбцах? И возможно ли это в MS SQL server? введите сюда описание изображения введите сюда описание изображения

Отслеживать
Лев Козлов
задан 20 апр 2022 в 7:51
Лев Козлов Лев Козлов
11 2 2 бронзовых знака

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Используй схему IF Not Insert then Modify. Если с ERP завязки то все просто проводи с параметром FALSE.

Чтобы и в ERP не отработали триггеры и на стороне SQL сервер просто были внесены «незаметные преобразования» .

Да, может нарушить целостность данных, но коль уж так необходимо.

С Уважением, Сергей

Отслеживать
ответ дан 14 ноя 2022 в 7:27
11 1 1 бронзовый знак
а это точно ответ на вопрос про настройку репликации?
14 ноя 2022 в 7:29

  • sql-server
  • репликация
    Важное на Мете
Похожие

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Дизайн сайта / логотип © 2024 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2024.2.12.4680

Как удалить репликацию ms sql

Изменение столбца таблицы, участвующей в репликации

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

«Cannot alter/drop the table ‘tablename’ because it is being published for replication».
«Невозможно изменить/удалить таблицу ‘имя таблицы’, так как она опубликована для репликации».

Так как же изменить существующий столбец, не отключая репликацию? Допустим, мы хотим сделать следующие изменения структуры:

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

 exec sp_dropsubscription @publication = 'tTestFNames' , @article = 'tEmployees' , @subscriber = 'RSCOMPUTER' , @destination_db = 'testrep' exec sp_droparticle @publication = 'tTestFNames' , @article = 'tEmployees' alter table tEmployees alter column Forename varchar(100) null exec sp_addarticle @publication = 'tTestFNames' , @article = 'tEmployees' , @source_table = 'tEmployees' exec sp_addsubscription @publication = 'tTestFNames' , @article = 'tEmployees' , @subscriber = 'RSCOMPUTER' , @destination_db = 'testrep' 
 exec sp_repladdcolumn @source_object = 'tEmployees' , @column = 'TempForename' , @typetext = 'varchar(100) NULL' , @publication_to_add = 'tTestFNames' update tEmployees set TempForename = Forename exec sp_repldropcolumn @source_object = 'tEmployees' , @column = 'Forename' exec sp_repladdcolumn @source_object = 'tEmployees' , @column = 'Forename' , @typetext = 'varchar(100) NULL' , @publication_to_add = 'tTestFNames' update tEmployees set Forename = TempForename exec sp_repldropcolumn @source_object = 'tEmployees' , @column = 'TempForename'

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

MS SQL Server: пошаговая настройка репликации

date

14.02.2020

user

insci

directory

SQL Server

comments

комментария 2

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

Репликация транзакций проста в настройке и доступна во всех версиях SQL Server. Данный тип репликации используется для двух целей:

    Репликация данных между несколькими серверами для read доступа (например, для разгрузки серверов OLTP типа);

Хотя у SQL Server есть много решений для балансировки нагрузки select запросов и средств обеспечения отказоустойчивости, транзакционная репликация это самый простой и быстрый способ, так как вы можете реплицировать отдельные объекты. Так же этот вид репликации полностью доступен в Standard лицензии SQL Server ( в отличии от групп доступности Always On, которые полноценно доступны только в Enterprise).

Преимущество репликации перед Always ON и зеркалированием баз данных в том, что с помощью репликации вы можете скопировать отдельные объекты (отдельные таблицы/представления), а не базу данных целиком.

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

SQL Server: основы технологии репликации

В любом типе репликации SQL Server есть 3 типа серверов:

  • Publisher (издатель) – основной экземпляр-источник, который публикует статьи;
  • Distributor (распространитель) – экземпляр который распространяет статьи на сервера-подписчики. Этот тип экземпляра не хранит у себя данные издателя на постоянной основе, а распространяет их подписчикам;

Роли могу пересекаться между собой. Например, один экземпляр может быть и издателем, и подписчиком (но не самого себя).

Работа репликации транзакций осуществляется через внутренние агенты SQL Server’а:

  • Агент чтения журналов;
  • Агент моментальных снимков;
  • Агент распространения.

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

Транзакции доставляются на подписчиков в той последовательности, в которой они были отправлены на издателя. Если транзакций слишком много, образуется очередь.

Транзакционная репликация работает асинхронно, так же как и асинхронные режимы Always On и зеркалирования баз данных. То есть, данные, которые были записаны на издатель, будут отправлены на подписчики без гарантии доставки в случае сбоя во время передачи данных. Это нужно учитывать, если вы собираетесь использовать транзакционную репликацию для избыточности и высокой доступности данных в SQL Server.

Схема связи агентов между собой из официальной документации:

схема реализации репликации базы данных в MS SQL Server

Рассмотрим, как настроить репликацию в SQL Server на следующем тестовом стенде:

  • 2 виртуальные машины с Windows Server 2019 в одной сети;
  • 2 установленных экземпляра SQL Server 2019.
  • testnode1\node1 – издатель (Publisher);
  • testnode2\node2 – подписчик (Subscriber);
  • testnode2\node2 – распространитель (Distributor).

В этом примере мы будем реплицировать одну таблицу с testnode1\node1 на testnode\node2. В роли распространителя будет выступать testnode2\node2.

Настройка распространителя в SQL Server

Для начала нужно настроить экземпляр распространителя. В разделе Replication, в контекстном меню нажмите Configure Distribution

Configure Distribution - настройка распространителя репликации

Так как мы хотим использовать этот экземпляр в качестве распространителя, выбираем первый пункт (testnove2 will act as its own Distributor; SQL Server will create a distribution databasse and log).

выбор роли replication disrtibutor в sql server

путь к каталогу снапшоотв репликации sql server

Указываем директорию для моментальных снимков. Я оставлю стандартный путь.

sql server путь к базе данных Distribution

Указываем директорию для базы данных Distribution. Если есть такая возможность, то лучше разместить файлы базы данных distribution на отдельном массиве дисков. Особенно это важно, если планируется большой объём реплицируемых данных.

На этом шаге можно указать экземпляры, которые смогут использовать данный сервер как распространитель. Я сразу добавлю testnode1\node1. Это можно сделать и позже, после начальной конфигурации.

настройка подписчиков репликации sql server

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

пароль для связи между Publisher и Distributor

После этого можно жать Finish. На этом настройка распространителя завершена.

Если вы хотите изменить пароль распространителя или разрешить другим издателям использовать этот распространитель, то можно это сделать через Distributor Properties

Distributor Properties в sql server 2019настройки распространителя sql репликации

Настройка издателя репликации в SQL Server

Теперь переходим к настройкам издателя репликации. Запустите тот же мастер Configure Distributuin.

Настройка издателя репликации SQL Server

Выберите второй пункт, указываем сервер распространитель – testnode2\node2

указать имя распространителя репликации

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

Теперь можно создать новую публикацию: Replication -> Local Publication -> New Publication.

создание публикации sql server

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

выберите базу данных для репликации

Выберите тип репликации. Доступны:

  • Snapshot publication;
  • Transactional publication (выберите этот тип репликации);
  • Peer-to-Peer publication;
  • Merge publication.

Transactional publication - транзакционная репликация

Выберите таблицы, которые нужно реплицировать. С помощью транзакционной репликации так же можно реплицировать пользовательские процедуры, функции и представления. Реплицируемые объекты называются Articles (Статьи).

выбор таблицы для репликации sql server

На следующем шаге можете указать фильтр для публикации.

sql фильтр репликации

Чтобы мастер сразу создал моментальный снимок, выберите опцию “Create a snapshot immediately and keep the snapshot available to initialize subscriptions”.

создать снимок sql server для инициализации репликации

выбор сервисных учетных записей

Укажите аккаунты, из-под которых будут выполняться агенты. Нажмите Security Settings и выберите “Run under SQL Server Agent service account”.

В имени публикации я указываю названия сервера-подписчика. Так легче ориентироваться если публикаций на другие сервера будет много.

настройка публикации

publication создана

Настройка подписчика репликации в SQL

На testnode2\node2 в разделе Replication -> Local Subscriptions создайте новую подписку.

создать подписку репликации -></p><div class='code-block code-block-15' style='margin: 8px 0; clear: both;'>
<!-- 15uvdkaluga -->
<script src=

Local Subscriptions в sql server» width=»339″ height=»256″ />

Выберите издателя, базу данных и публикацию в ней.

выбор издателя репликации

Выберите пункт “Run all agents at the Distributor”, чтобы агенты выполнялись на распространителе. В моём случае подписчик и распространитель совпадают, но обычно это разные сервера.

Если выбрать второй пункт (“Run each agent at its Subscriber”), то агенты будут выполняться на подписчике. Этот вариант предпочтителен, если распространитель у вас “формальный” и находится на одном сервере с издателем или подписчиком

Run all agents at the Distributor

Укажите базу данных, в которую будут реплицироваться данные из Subscription Database.

выбор базы даннх, в которую нужно реплицировать данные sql server

Снова укажите аккаунт, из-под которого будут выполняться агенты репликации.

выбор сервисных аккаунтов

Если вы хотите, чтобы данные реплицировались постоянно, выбирайте режим Agent Schedule -> Run continuously.

тип репликации постоянная Run continuously

Включите опцию Initialize, чтобы инициализировать подписку после завершения работы мастера.

Memory Optimized репликация

При включении параметра “Memory Optimized” таблицы на подписчике с этой публикации будут созданы как “In memory”. Если вы не планируете эти таблицы как таблицы для использования в оперативной памяти, то не отмечайте этот параметр.

настройка подписки закончена

На этом настройка подписки завершена. Теперь необходимо проверить работоспособность публикации и корректность выполнения репликации таблицы.

Мониторинг и управление репликацией в SQL Server

Практически всю настройку существующих публикаций можно провести через Replication Monitor.

Replication Monitor в sql server

Добавьте издателей через распространителя (Add Publisher -> Specify a Distributor and Add its Publishers).

Specify a Distributor and Add its Publishers

После того, как вы соединитесь с распространителем, у вас появится список издателей, которые работают с этим распространителем.

список доступных репликаций в sql server

Убедимся, что агент моментальных снимков отработал и доставил снимок на распространителя. В моём случае сначала была ошибки о том, что аккаунту из-под которого работают агенты, не хватает прав на базе TestDatabase1. Для решения этой проблемы я добавил сервисному аккаунту (из-под которого работает SQL Server) роль db_owner в базе TestDatabase1 на обоих экземплярах.

мониторинг репликации sql server

Так же убедимся, что распространитель доставил транзакции на подписчика.

процесс выполнения репликации

В логах агентов ошибок нет, проверим что наша таблица действительно появилась в базе.

Для начала добавим новую запись в таблицу.

проверка репликации данных в sql server

Проверяем, что эта запись реплицировалась на testnode2\node2.

данные успешно реплицированы sql server 2019

На этом базовая настройка репликации транзакций в SQL Server закончена.

Для диагностики проблем с репликацией в основном используется Replication Monitor, но можно использовать и дополнительные инструменты диагностики SQL Server.

Предыдущая статьяПредыдущая статья Следующая статья Следующая статья

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *