Организация репликации транзакций

Издателем, подписчиком и распространителем могут быть как различные SQL-сервера, так и один и тот же. Организуем репликацию на одном и том же сервере:

(i) Создание БД для публикации и подписки:

use master

go

create database main

go

create database node

go

(ii) Создание и заполнение таблиц в БД издателя:

use main

go

create table Факультет

(Номер tinyint primary key,

Название char(50) not null)

go

create table Группа

(Номер char(7) primary key,

Факультет tinyint null references Факультет (Номер)

on update cascade on delete no action,

Курс tinyint null)

go

create table Студент

(N_зач int primary key,

ФИО char(50) not null,

Группа char(7) null references Группа (Номер)

on update cascade on delete no action)

go

insert into Факультет values (4, 'Вычислительных...')

go

insert into Факультет values (5, 'Информационных...')

go

insert into Группа values ('4531', 4, 5)

go

insert into Группа values ('4831', 4, 2)

go

insert into Группа values ('5531', 5, 5)

go

insert into Группа values ('5831', 5, 2)

go

insert into Студент values (33, 'Лисичкин', '4531')

go

insert into Студент values (34, 'Сыроежкин', '4831')

go

insert into Студент values (35, 'Груздев', '5531')

go

insert into Студент values (36, 'Сморчков', '5831')

go

create table Дисциплина

(Код int primary key,

Название char(50) not null)

go

create table Специальность

(Номер char(7) primary key,

Название char(50) not null,

Квалификация char(20))

go

create table Уч_план

(Код int primary key,

Шифр_дисц char(5) not null,

Специальность char(7) not null references Специальность (Номер)

on update cascade on delete no action,

Семестр tinyint not null,

Дисциплина int not null references Дисциплина (Код)

on update cascade on delete no action,

Форма_контр char(2) not null,

unique (Шифр_дисц, Специальность, Семестр))

go

alter table Группа

add Специальность char(7) references Специальность (Номер)

on update cascade on delete no action

go

alter table Группа

add Кол_во_студ tinyint

go

alter table Студент

add Ср_балл real

go

create table Оценка

(N_зач int not null references Студент (N_зач)

on update cascade on delete no action,

Код_УП int not null references Уч_план (Код)

on update no action on delete no action,

Оценка tinyint,

Зачет bit,

Дата datetime,

primary key (N_зач, Код_УП))

go

insert into Дисциплина values (1, 'Программирование')

go

insert into Дисциплина values (2, 'Базы данных')

go

insert into Специальность values ('230105', 'Программное...', 'инженер')

go

insert into Специальность

values ('230201', 'Информационные...', 'инженер')

go

update Группа

set Специальность = '230105'

where Факультет = 4

go

update Группа

set Специальность = '230201'

where Факультет = 5

go

insert into Уч_план values (1, 'ОПД3', '230105', 2, 1, 'Э')

go

insert into Уч_план values (2, 'ОПД5', '230201', 3, 1, 'Э')

go

insert into Уч_план values (3, 'СД3', '230105', 6, 2, 'Э')

go

insert into Уч_план values (4, 'СД2', '230201', 5, 2, 'Э')

go

Схема полученной БД приведена на рис. 1.

Рис. 1

Здесь Дисциплина — общий справочник наименований дисциплин, а таблица Уч_план показывает, студенты какой специальности какие дисциплины в каких семестрах изучают.

(iii) Создание распространителя (назначение сервера в качестве распространителя) выполняет СХП sp_adddistributor, процедура должна вызываться на распространителе в базе данных master [22], единственным обязательным параметром является имя сервера (на сервере должен быть запущен Агент SQL Server — команда Запустить из контекстного меню одноименного узла в обозревателе объектов Management Studio):

use master

go

exec sp_adddistributor 'AVB-XEON'

go

СХП sp_dropdistributor удаляет распространитель (выполняется на распространителе в любой базе данных, за исключением базы данных распространителя), а СХП sp_helpdistributor выводит сведения о распространителе (выполняется на издателе)[23].

(iv) Создание базы данных распространителя осуществляет СХП sp_adddistributiondb, единственным обязательным параметром является имя БД, процедура должна вызываться на распространителе в базе данных master (если БД не существует, она создается, иначе помечается как БД распространителя и в ней создаются системные таблицы):

use master

go

exec sp_adddistributiondb 'distr'

go

СХП sp_changedistributiondb изменяет БД распространителя.[24]

(v) Настройку издателя на использование БД распространителя реализует СХП sp_adddistpublisher, обязательными параметрами являются имя издателя и имя БД распространителя, процедура должна вызываться на распространителе в любой БД (перед ее вызовом должны быть выполнены sp_adddistributor и sp_adddistributiondb):

exec sp_adddistpublisher @publisher = 'AVB-XEON', @distribution_db = 'distr'

go

(vi) Настройку параметров реплицируемой БД осуществляет СХП sp_replicationdboption, вызываемая на издателе или подписчике в любой БД, обязательными параметрами являются имя БД, имя и значение свойства, для репликации слиянием значение параметра @optname устанавливается в ' merge publish ', для других — как показано ниже:

exec sp_replicationdboption @dbname = 'main', @optname = 'publish',

@value = 'true'

go

(vi) Создание моментального снимка для публикации транзакций реализует СХП sp_addpublication, процедура вызывается на издателе в публикуемой БД, единственным обязательным параметром является имя публикации (здесь предполагается, что публикация предназначена для репликации в БД на сервере в корпусе Гастелло, куда будут передаваться сведения, относящиеся к студентам 1 и 2 курсов):

use main

go

exec sp_addpublication @publication = 'Гастелло', @repl_freq = 'continuous',

@allow_sync_tran = 'true'

go

Параметр @repl_fre q имеет значение 'continuous' по умолчанию, которое говорит о том, что реплицироваться будут изменения, выполненные всеми транзакциями, попавшими в журнал. Параметр @allow_sync_tran разрешает для создаваемой публикации немедленно обновляемые подписки. Параметр @status определяет доступность публикации для подписчиков 'activ' e или ' inactive ' (по умолчанию).

(vii) Создание агента моментального снимка для публикации выполняет СХП sp_addpublication_snapshot, вызываемая на издателе в публикуемой БД, единственным обязательным является имя публикации:

use main

go

exec sp_addpublication_snapshot @publication = 'Гастелло', @frequency_type = 1

go

Параметр @frequency_type может принимать значения 1 — однократно, 4 — ежедневно (по умолчанию), 8 — еженедельно, 16 — ежемесячно и др. Параметр @frequency_interval позволяет дополнительно указать день недели, рабочий/выходной день и др.

(viii) Создание статей реализует СХП sp_addarticle, вызываемая на издателе в публикуемой БД, в качестве параметров процедуре передаются имя публикации, имя статьи и имя объекта БД, входящего в статью:

use main

go

exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Факультет',

@source_object = 'Факультет'

go

exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Группа',

@source_object = 'Группа'

go

exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Студент',

@source_object = 'Студент'

go

exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Дисциплина',

@source_object = 'Дисциплина'

go

exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Специальность',

@source_object = 'Специальность'

go

exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Уч_план',

@source_object = 'Уч_план'

go

exec sp_addarticle @publication = 'Гастелло', @article = 'Г_Оценка',

@source_object = 'Оценка'

go

Имя статьи должно быть уникальным в рамках публикации, чаще всего публикуемыми объектами БД являются таблицы, но могут быть и представления, ХП и др.

(ix) Горизонтальную фрагментацию задают СХП sp_articlefilter и sp_articleview, вызываемые на издателе в публикуемой БД, процедуры создают фильтр и представление, обеспечивающие фрагментацию данных:

use main

go

exec sp_articlefilter @publication = 'Гастелло', @article = 'Г_Группа',

@filter_name = 'Ф_Г_Группа', @filter_clause = 'Курс in (1, 2)'

go

exec sp_articleview @publication = 'Гастелло', @article = 'Г_Группа',

@view_name = 'П_Г_Группа', @filter_clause = 'Курс in (1, 2)'

go

exec sp_articlefilter @publication = 'Гастелло', @article = 'Г_Студент',

@filter_name = 'Ф_Г_Студент', @filter_clause =

'Группа in (select Номер from Группа where Курс in (1, 2))'

go

exec sp_articleview @publication = 'Гастелло', @article = 'Г_Студент',

@view_name = 'П_Г_Студент', @filter_clause =

'Группа in (select Номер from Группа where Курс in (1, 2))'

go

exec sp_articlefilter @publication = 'Гастелло', @article = 'Г_Уч_план',

@filter_name = 'Ф_Г_Уч_план', @filter_clause = 'Семестр <= 4'

go

exec sp_articleview @publication = 'Гастелло', @article = 'Г_Уч_план',

@view_name = 'П_Г_Уч_план', @filter_clause = 'Семестр <= 4'

go

exec sp_articlefilter @publication = 'Гастелло', @article = 'Г_Оценка',

@filter_name = 'Ф_Г_Оценка', @filter_clause =

'N_зач in (select N_зач from Студент, Группа

where Студент.Группа = Группа.Номер and Курс in (1, 2))

and Код_УП in

(select Код from Уч_план

where Семестр <= 4)'

go

exec sp_articleview @publication = 'Гастелло', @article = 'Г_Оценка',

@view_name = 'П_Г_Оценка', @filter_clause =

'N_зач in (select N_зач from Студент, Группа

where Студент.Группа = Группа.Номер and Курс in (1, 2))

and Код_УП in

(select Код from Уч_план

where Семестр <= 4)'

go

Параметр @filter_clause определяет условие отбора, записываемое по тем же синтаксическим правилам, что в разделе where SQL запросов. Фильтрация записей в таблице Группа осуществляется по условию принадлежностb группы к 1 или 2 курсу ('Курс in (1, 2)'), для Студент — по принадлежности группе соответствующего курса, для Уч_план — по номеру семестра ('Семестр <= 4'), для Оценка — по комбинации двух последних условий.

(x) Вертикальную фрагментацию задает СХП sp_articlecolumn, вызываемая на издателе в публикуемой БД, процедура позволяет исключать (@operation = 'drop') или включать (@operation = 'add') столбец таблицы в публикацию:

use main

go

exec sp_articlecolumn @publication = 'Гастелло',

@article = 'Г_Студент', @column = 'Ср_балл', @operation = 'drop'

go

(xi) Активацию публикации можно осуществить с помощью СХП sp_changepublication, вызов производится на издателе в публикуемой БД:

use main

go

exec sp_changepublication 'Гастелло', 'status', 'active'

go

(xii) Создание подписки осуществляет СХП sp_addsubscription, вызов производится на издателе в публикуемой БД:

use main

go

exec sp_addsubscription @publication = 'Гастелло',

@subscriber = 'AVB-XEON', @destination_db = 'node',

@subscription_type = 'push', @update_mode = 'sync tran'

go

При вызове процедуры указывается имя публикации, сервера, целевой БД, подписка является принудительной (@subscription_type = 'push'), параметр @update_mode = 'sync tran' включает поддержку немедленно обновляемых подписок.

(xiii) Для начальной синхронизации БД main и node необходимо вызвать агента репликации — команда Запустить задание на шаге… из контекстного меню узла, соответствующего агенту моментального снимка (ветвь дерева объектов — Агент SQL Server\Задания), имя агента можно уточнить, вызвав СХП:

use main

go

exec sp_helppublication_snapshot @publication = 'Гастелло'

go

Альтернативным способом является использование СХП:

use main

go

sp_startpublication_snapshot 'Гастелло'

go

В результате вызова агента моментального снимка в БД node будут созданы те же таблицы, что и в БД main, также будут переданы данные таблиц в соответствии с настроенной горизонтальной (в БД node будут только данные о группах и студентах младших курсов и учебных планах первых четырех семестров) и вертикальной (в БД node в таблице Студент столбец Ср_балл будет отсутствовать) фрагментацией.

Дата добавления: 2015-05-09