Replication - limit of 200 tables in subscription set

[demboos]

Hi,

I've been recently trying to set up a replicated environment. The
problem is my database has over 500 tables (complicated business
domain), with many foreign keys. At first I had problems with
performing full refreshes of the tables (initial step taken by apply) -
the references between tables were totally ignored and the whole
process failed.

I've read that it is possible to bypass the full refresh (some tricky
scripts) and manually populate replicated tables with data. I did it,
but then another problem occured - subscription set is allowed to have
at most 200 tables. It is a limit I cannot achieve - by eliminating
some static tables (dictionaries), I've come down to 350 tables, but
this is the end.

I insist on having all tables in one subscription set because I want
data to be inserted into destination tables in the same way
(transactions) as in source tables. It is only possible to achieve that
by having all tables in one subscription set (unless I'm mistaken).

Does somebody have a good solution to this problem? I insist on the
transactional replication since the system operates on important
financial data and reporting services running on the replicated DB need
to be accurate too...

Thanks in advance

--
Szymon Dembek

[Jan M. Nelken]

de*****@wp.pl wrote:

Hi,

I've been recently trying to set up a replicated environment. The
problem is my database has over 500 tables (complicated business
domain), with many foreign keys. At first I had problems with
performing full refreshes of the tables (initial step taken by apply) -
the references between tables were totally ignored and the whole
process failed.

I've read that it is possible to bypass the full refresh (some tricky
scripts) and manually populate replicated tables with data. I did it,
but then another problem occured - subscription set is allowed to have
at most 200 tables. It is a limit I cannot achieve - by eliminating
some static tables (dictionaries), I've come down to 350 tables, but
this is the end.

I insist on having all tables in one subscription set because I want
data to be inserted into destination tables in the same way
(transactions) as in source tables. It is only possible to achieve that
by having all tables in one subscription set (unless I'm mistaken).

Does somebody have a good solution to this problem? I insist on the
transactional replication since the system operates on important
financial data and reporting services running on the replicated DB need
to be accurate too...

Thanks in advance

200 tables per subscription is indeed a limit. Considering number of tables did
you think about using HADR?

Jan M. Nelken

[demboos]

Jan M. Nelken wrote:

200 tables per subscription is indeed a limit. Considering number of tables did
you think about using HADR?

Jan M. Nelken

We have HADR, but for the purpose of HA only (since the back-up server
cannot be used even for reading). Standard SQL replicatication was ment
to keep another copy of our data (on one or more seperate DB2
instances) just for the purpose of reading it (reports).

I'm really afraid of putting the tables in more than one subscription
set because of data consistency (transactions) and foreing-key
constraints.

Thanks

--
Szymon Dembek