Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649
Такая вот задачка - имеется две базы данных-источника данных.

В базе-источнике 1 создана таблица TblSrc1:

CREATE TABLE [dbo].[TblSrc1]
(
[ID] [int] IDENTITY (1, 1) PRIMARY KEY,
[SomeData] [varchar] (50) null
)

В базе-источнике 2 создана таблица TblSrc2:

CREATE TABLE [dbo].[TblSrc2]
(
[ID] [int] IDENTITY (1, 1) PRIMARY KEY,
[SomeData] [varchar] (50) null
)

Имеется также база-премник данных, в которой создана таблица TblDest:

CREATE TABLE [dbo].[TblDst]
(
[ID] int not null,
[SrcID] int not null,
[SomeData] [varchar] (50) null,
PRIMARY KEY ([SrcId],[ID])
)

Необходимо используя репликацию закачивать данные в таблицу TblDest из таблиц TblSrc1 и TblSrc2. При этом TblDest.SrcID для данных полученных из TblSrc1 должно содержаться 1, а для данных полученных из TblSrc2 - 2. Репликация транзакционная.

Основной гемморой связан с тем, что primary keys в таблицах-источниках и таблице-приемнике - разные. Еще одно условие - identity колонки в TblSrc1 и TblStc2 могут иметь одинаковые значения.

Есть ли какой-нибудь простой и стандартный путь решения этой проблемы?

У меня есть одна идея (напишу в следующем сообщении), но она как-то не очень изящно выглядит.
19 окт 04, 21:04    [1046541]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649
Как я собираюсь это решать, если не найду другого решения:

1. Первая проблема - snapshot. Я так и не понял, как можно стандартными средствами скопировать snapshot между таблицами с разным количеством колонок? Под стандартными я имею в виду все эти замечательные replication wizards.

Cкорее всего, скопирую данные в обход стандартного копирования snapshot, а при создании подписки, выберу опцию - "Подписчик уже содержит все необходимые данные".

2. Для копирования изменений отредактирую созданные автоматически процедуры sp_MSins..., sp_MSdel..., sp_MSupd...

Работать, скорее всего будет - но больно много ручной работы. Может это как-то автоматически и по-другому можно решить?

Задачка-то стандартная.
19 окт 04, 21:13    [1046546]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
У получателя нет средств идентифицировать отправителя. Без этого сливания в одну таблицу не получится. Решение есть - отпарвителям добавить "константное" поле "Ид отправителя" в свои таблицы, после чего задача решится тривиально.
19 окт 04, 21:19    [1046549]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649
Проблема в том, что отправители - это готовые приложения, в которых мы ничего менять не можем. Написаны они совершенно через зад :((, блокируют все что можно - невозможно никакие внешние отчеты использовать - для этого вот и хотим свою базу создать, а данные реплицировать.

При репликации я могу использовать разные наборы процедур (sp_MSins..., sp_MSdel..., sp_MSupd...) для разных таблиц, соответственно, всю логику связанную с SrcID можно запихнуть в эти процедуры.

Например, sp_MSins_TblSrc1 будет вставлять в SrcID 1, а sp_MSins_TblSrc2 будет вставлять в SrcID 2.

Но как-то криво это выглядит.
19 окт 04, 21:42    [1046580]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Подобавляйте по одному полю с разным DEFAULT для каждого "филиала" в эти таблицы - ничего сломаться не должно. А проблему реплики это решит.
20 окт 04, 11:23    [1047469]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
_bob
Member

Откуда: Москва
Сообщений: 1654
если во view есть select *, то их пересоздать надо при добавлении поля в таблицу, которую они используют
20 окт 04, 11:53    [1047641]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Зачем пересоздавать? Достаточно sp_refreshview
20 окт 04, 12:42    [1047951]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649
автор

если во view есть select *, то их пересоздать надо при добавлении поля в таблицу, которую они используют


Хуже если есть insert into ... select * from ... - приложение умрет сразу. Т.е. своих-то программистов я за такое сразу убиваю, но посторонних не достать.

Да нет - исходные таблицы менять нельзя по двум причинам:

1. Юридическая - как только мы что-то там изменим, компания-производитель софта уже ни за что не отвечает, а без ихнего саппорта мы помрем (вот тоже бизнес - написать уродца и доить деньги за саппорт)

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

Забавно - мне казалось, что задачка-то вобщем практически стандартная, а решать приходится как-то криво.
20 окт 04, 14:43    [1048548]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Почитай по реплику что ли для разнообразия.
20 окт 04, 16:29    [1049214]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
Как насчет такого варианта:
Создать на подписчике две таблицы, в которые реплицировать инофрмацию с публишеров, а объединять ее уже на основе представления?
20 окт 04, 17:37    [1049612]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649

Как насчет такого варианта:
Создать на подписчике две таблицы, в которые реплицировать инофрмацию с публишеров, а объединять ее уже на основе представления?


Сейчас именно так и сделано - проблема в том, что две таблицы - это данные для двух стран. В принципе количество стран может возрасти до 10-15 - хотелось бы просто иметь колонку в одной таблице, которая обозначала бы ссылку на страну.

View с union по 10-15 таблицам как-то не очень хотелось бы использовать.
20 окт 04, 18:06    [1049762]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649

Почитай по реплику что ли для разнообразия.


Это мне было адресовано?
20 окт 04, 18:14    [1049793]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 AAron

Браво! Идею можно развивать сколь угодно широко. Скажем, сделать на издателях - филиалах "фоновые" таблицы, в которые уже можно влупить столько полей, сколько угодно, а их - реплицировать на подписчика в одну таблицу. Будет чутку громоздко, но ни с чем конфликтовать не будет.

2 Alexander_Chepack

Угу, именно. Конфликт описания задачи с пониманием технологии налицо. Вот и диалога нет.

Ну и множество таблиц + объединяющее представление не самый плохой вариант. Опять же будет N кластерных индексов вместо одного. Позволю себе привести пример.

create table a1 ( id int identity , part int default(1), flag int , primary key ( part , id ), check ( part = 1 )) 
create index ia1 on a1 ( flag )
create table a2 ( id int identity , part int default(2), flag int , primary key ( part , id ), check ( part = 2 )) 
create index ia2 on a2 ( flag )
create table a3 ( id int identity , part int default(3), flag int , primary key ( part , id ), check ( part = 3 )) 
create index ia3 on a3 ( flag )
go

set nocount on

insert into a1 ( flag ) select 100
insert into a1 ( flag ) select 101
insert into a3 ( flag ) select 102

insert into a2 ( flag ) select 200
insert into a2 ( flag ) select 201
insert into a2 ( flag ) select 202

insert into a3 ( flag ) select 300
insert into a3 ( flag ) select 301
insert into a3 ( flag ) select 302
go

create view va
as
select * from a1
union all
select * from a2
union all
select * from a3
go

set showplan_text on
go

select * from va where part in ( 1, 3 ) and flag between 150 and 350
go

set showplan_text off
go

drop view va
drop table a1
drop table a2
drop table a3
go

StmtText                                                                 
------------------------------------------------------------------------ 

select * from va where part in ( 1, 3 ) and flag between 150 and 350

(1 row(s) affected)

StmtText                                                                                                                  
------------------------------------------------------------------------------------------------------------------------- 
  |--Concatenation
       |--Index Seek(OBJECT:([Test].[dbo].[a1].[ia1]), SEEK:([a1].[flag] >= 150 AND [a1].[flag] <= 350) ORDERED FORWARD)
       |--Index Seek(OBJECT:([Test].[dbo].[a3].[ia3]), SEEK:([a3].[flag] >= 150 AND [a3].[flag] <= 350) ORDERED FORWARD)

(3 row(s) affected)

В то время, как:

create table a1 ( id int identity , part int default(1), flag int , primary key ( part , id ), /*check ( part = 1 )*/) 
create index ia1 on a1 ( flag )
create table a2 ( id int identity , part int default(2), flag int , primary key ( part , id ), /*check ( part = 2 )*/) 
create index ia2 on a2 ( flag )
create table a3 ( id int identity , part int default(3), flag int , primary key ( part , id ), /*check ( part = 3 )*/) 
create index ia3 on a3 ( flag )
go

set nocount on

insert into a1 ( flag ) select 100
insert into a1 ( flag ) select 101
insert into a3 ( flag ) select 102

insert into a2 ( flag ) select 200
insert into a2 ( flag ) select 201
insert into a2 ( flag ) select 202

insert into a3 ( flag ) select 300
insert into a3 ( flag ) select 301
insert into a3 ( flag ) select 302
go

create view va
as
select * from a1
union all
select * from a2
union all
select * from a3
go

set showplan_text on
go

select * from va where part in ( 1, 3 ) and flag between 150 and 350
go

set showplan_text off
go

drop view va
drop table a1
drop table a2
drop table a3
go

StmtText                                                                 
------------------------------------------------------------------------ 

select * from va where part in ( 1, 3 ) and flag between 150 and 350

(1 row(s) affected)

StmtText                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
  |--Concatenation
       |--Index Seek(OBJECT:([Test].[dbo].[a1].[ia1]), SEEK:([a1].[flag] >= 150 AND [a1].[flag] <= 350),  WHERE:([a1].[part]=3 OR [a1].[part]=1) ORDERED FORWARD)
       |--Index Seek(OBJECT:([Test].[dbo].[a2].[ia2]), SEEK:([a2].[flag] >= 150 AND [a2].[flag] <= 350),  WHERE:([a2].[part]=3 OR [a2].[part]=1) ORDERED FORWARD)
       |--Index Seek(OBJECT:([Test].[dbo].[a3].[ia3]), SEEK:([a3].[flag] >= 150 AND [a3].[flag] <= 350),  WHERE:([a3].[part]=3 OR [a3].[part]=1) ORDERED FORWARD)

(4 row(s) affected)

Если посмотреть на планы, то будет видно, что лишние таблицы "не поднимаются с диска".
20 окт 04, 19:59    [1050020]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649
Замечательно все, но изначальная проблема остается - как реплицировать данные из таблицы с 3 колонками, в таблицу с 4 колонками? Стандартными средствами?

По аналогии с приведенным примером - в исходных таблицах колонка part отсутствует. Стандартная репликация при этом не работает - даже если используешь DTS трансформации. Так что поле part надо в select'ах входящих в union, как константы прописывать.

Ну да дело не в этом - задачу я несколько упростил - просто вычленив проблему, которую надо решить. К сожалению, большинство отчетов выполняются по всем странам сразу, так что условия вроде where part in ( 1, 3 ) там встречатся будут нечасто.

Далее - на основе этих таблиц будут созданы indexed views c агрегатами, которые суммируют данные из разных таблиц/стран (что с несколькими таблицами просто невозможно, т.к. indexed view не может содерждать union или ссылаться на другие view).

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


Конфликт описания задачи с пониманием технологии налицо.


На мой взгляд совершенно никакого конфликта - более того, MS приводит примеры подобной ситуации, предлагая играться с identity колонками - в одной таблице значения от 0 до 1 000 000 000, в другой от 1 000 000 001 до 2 000 000 000, etc. После чего данные, естественно, легко сливаются в одну таблицу. К сожалению, не всегда можно контролировать исходные таблицы по независящим от меня обстоятельствам.

Ну это я вежливо ответил, теперь лично Crimean:

Поверь мне, когда человек сдавший на MCP (правильно - только MCP, даже не MCDBA?) несколько месяцев назад, начинает пальцы растопыривать - это выглядит просто смешно. Это я тебе как DBA с 12-летним опытом говорю.
20 окт 04, 23:08    [1050193]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Иллюстрация с part была приведена к случаю с множеством таблиц на подписчике + покрывающая View. Поле может отсутствовать на издателе и быть просто добавлено на подписчике с соответтсвующими CHECK / DEFAULT. Это не помешает реплике при ее правильной настройке. И будет ДАЖЕ ЛУЧШЕ на выборках, чем вариант с одной таблицей, особенно при фигурировании в WHERE запросов Part. Ну а при "правильном" размещении по файлам... Можно получить или неплохой прирост производительности или основательно упереться в очередную "особенность" от MS.

В описании задачи все "филиальские" таблички были описаны как "IDENTITY (1, 1)", так что букварный вариант с разруливанием диапазонов Id я и не думал предлагать. Тем более после ответов о невозможности вмешивания в схему данных издателя. Да и при большОм числе "филиалов" это (разруливание диспазонов Id) напрягает. Особенно после первой перевыдачи диапазона (а это рано или поздно случится :)).

Еще оч неплох вариант "от AAron" - сделать копии таблиц НА ИЗДАТЕЛЯХ, которые уже и реплицировать К СЕБЕ. Копии сделать можно при помощи все той же транзакционной реплики прямо там, на филиалах. На базе этого можно там организовать отчетные базы, с которых (кстати!) строить свои отчеты. Транзакционная реплика для 99% задач остается незамеченной, если только им не надо делать ALTER TABLE для таблиц, участующих в репликации. Ну и на время обновления баз ее надо будет снимать, предварительно заскриптовав. Это не есть головняк. Остальное решается тривиально. В таблицы на отчетной базе добавляем Part с DEFAULT и реплицируем это все себе в одну таблицу.

2 Alexander_Chepack

Знаешь анекдот про двух психоаналитиков?
"Мы же взрослые люди, давай просто достанем и померяемся!"

P.S.Хоть раз лазил в репликационные процедуры? А с целью привнести в них "стороннюю" информацию?
21 окт 04, 11:46    [1051040]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
Вообще, ничто не мешает стандартными средствами делать нестандартные вещи.
1. Можно использовать ХП, которые заменят команды INSERT|UPDATE|DELETE на подписчике. Для каждого издателя можно сделать свои ХП. И в них уже прописывать необходимый Part и заливать данные в одну табличку
2. Можно сделать триггера на таблицах подписчика (INSTEAD OF). Функциональность аналогична предыдущему варианту.
3. этот вариант не проверял, но на вскидку - сделать триггера на издателях, которые будут дергать некие хранимки с необходимыми параметрами. Реплицировать уже сам факт вызова хранимки. Често говоря, в этом варианте я не очень уверен.

Так что, выбор большой. И чем более полно описана задача, тем более полный ответ можно получить. Вариант Crimean неплох, но никто не знал про последующие агрегатные view.

PS. Насчет снапшота особо подсказать не могу ничего, кроме ручной синхронизации.
21 окт 04, 12:54    [1051376]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649

1. Можно использовать ХП, которые заменят команды INSERT|UPDATE|DELETE на подписчике. Для каждого издателя можно сделать свои ХП. И в них уже прописывать необходимый Part и заливать данные в одну табличку


Ну так именно это я и собираюсь делать - я же во втором сообщении об этом написал. Просто думал, может какие-то еще варианты мне в голову не пришли.

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

Буду делать через процедуры, как и планировал.

О психоаналитиках:

Ну так очевидно, что у меня и длиннее и толще :))
21 окт 04, 13:16    [1051489]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Триггера на подписчике здорово, но как узнать, откуда пришла команда? Разные процедуры с вкомпиленным Part это решают, но не один триггер.

Триггера на издателях - фу :) Во-первых их снесет разработчик (скорее всего), во-вторых договаривались уже "не трогать издателя"...

Теперь по делу. Собрал модель.

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

Вариант с кучей таблиц (по одной на филиал) и с "распределенным" представлением на них шибко удобнее в настройке. Реплика как реплика. Плюс одна вьюха. Можно вообще не транзакционную, а снапшот пустить. Да и сама идея с секционированием мне в последнее время симпатишна :)

Вариант с одной таблицей? Проблем нет. И снапшот отработал и потом все работает. Единственный момент - если хочется переинитить издателя, то лучше все же руками снести "его" записи на подписчике по его "part". Но есть лазейки вроде как и этого не делать... Хотя, это не мне надо, да и реинит дело не ежедневное :)

Сделал все визардами, кстати.

Два филиала. На филиалах одинаковые таблицы

create table a ( id int identity primary key , flag int )

Кстати! У вас ПК хоть есть на таблицах? А то вся идея накроется медным тазом - это же IDENTITY поле добавлять и ПК на ннего вешать!

В головной, ессно

create table a ( id int , flag int , part int , primary key ( part, id ))

Заполнил по три записи в филиалах. Кинул реплику. Приполз снапшот по три записи с соответствующим заполнением Part. Вставил еще по три записи. Приползли все. Тоже с правильным Part. Удалил по очереди по 3 записи - работает, лишнего с издателя не сносит.

Спросите меня, как? Через трансформацию издателя. Я предлагал почитать доку по реплике не стеба ради. На каждого издателя делаются DTS. На подписчике, кстати, то есть у себя.
Вот такие:

Function Main()
	DTSDestination("id") = DTSSource("id")
	DTSDestination("flag") = DTSSource("flag")
	DTSDestination("part") = ("1")
	Main = DTSTransformStat_OK
End Function

(оно само их делает, надо только предпоследнюю строчку дописать)

А в завершение, уже традиционно, цитата из анекдота:
"Х@й длиной 3 метра - это очень круто, но совершенно бесполезно" :)
21 окт 04, 17:38    [1052648]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Alexander_Chepack
Member

Откуда: London
Сообщений: 22649
Интересно - я делал абсолютно то же самое (включая точно такой же скрипт), и поимел следующие проблемы:

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

Попробую еще, может я что-то не так сделал.
21 окт 04, 18:44    [1052902]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
> В случае если я использую DTS трансформации, этот самый визард
> мне ничего о процедурах на своих формах не показал вообще

Конечно. В этом случае все спрятано в самих DTS пэкэджах. Там можно определить INSERT / UPDATE / DELETE команды. Но это надо раскрыть пэкэдж.

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

Я не буду скриншоты делать, на словах расскажу. Это же трансформация! Поэтому там есть "откуда" и "куда". На закладке "откуда" поля part нету. От того, что его нету на издателе. А вот на закладке "куда" оно есть, если таблица была заблаговременно создана. Но оно не выбрано (ладно, сдам хитрость :)) - его же нет на издателе, а сам думать DTS еще не умеет :) Так вот надо это поле выбрать, чтобы оно появилось в коллекции полей подписчика и на него не ругалось обращение к полям подписчика. После чего все заработает.
Ну а сам пэкэдж сохраняем и тиражируем, изменяя константу. Можно ее вообще попробовать в параметры засунуть, но я тут не силен - не так часто и не настолько углубленно пользуюсь DTS.
21 окт 04, 19:04    [1052948]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
Crimean

Триггера на подписчике здорово, но как узнать, откуда пришла команда? Разные процедуры с вкомпиленным Part это решают, но не один триггер.

Имелось ввиду, что триггера висят на таблицах, индивидуальных для каждого издателя. По сути то же, что и процедуры, но позволяют использовать SQL-команды при репликации, а не хранимые процедуры. Но своих качелей тоже достаточно. Я бы так делать не стал :)

Crimean
Спросите меня, как? Через трансформацию издателя. Я предлагал почитать доку по реплике не стеба ради. На каждого издателя делаются DTS. На подписчике, кстати, то есть у себя.

Если не сложно - пальцем ткнуть, где об этом прочитать.
21 окт 04, 19:07    [1052958]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос: Репликация между таблицами с разным количеством колонок  [new]
Crimean
Member

Откуда:
Сообщений: 13148
AAron
где об этом прочитать


BOL : Contents
Replication / Replication Options / Transforming Published Data
21 окт 04, 19:34    [1052999]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить