[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Help in replication matter (Sql Server 2000)

1) table name colour ( primary key colour_code)
2) Say, there are twenty transaction tables which has got column colour_code
3) Int the twenty tables colour_code has been set as foreign key.

4) While defining a fk through EM, there is a option "enforce relationship for replication".

5) I am doing a replication.
   Transactional ( one way )
   Queue and immediate update option off
   Push from Publisher
   Publisher name prod_b29
   Subscriber name prod_dcity
   Article /table name colour

My question:
   a) Should I set the option "enforce relationship for replication" ON in all foreign keys in publisher database.
   b) Shoul I set the option "enforce relationship for replication" OFF in all foreign keys in subscriber database.

--------------------------------------------------------------------------------------------------------------------

Two more articles in the replication
1) trans_table_x  ( primary key trans_no)
2) trans_table_y  ( primary key trans_no, line_item)

foreign key set trans_no with option cascade delete on. If row in trans_table_x is deleted and all related table
in trans_table_y are also deleted.

My question:
Should I delete this foreign key from subscriber.

For subscriber trans_table_x, trans_table_y is read only but one/few columns of these tables are integrated through
foreign keys for other tables.

-------------------------------------------------------
One property of a article is snapshot. There are two option. Drop/recreate and the other is keep existing table.
What does these exactly mean. Practicall if I set keep existing table I face problem.



This point may help
------------------------
The subscriber will work on about 150 tables.
The publisher will push /replicate 5 set up tables and 5 transaction tables.

0
Mateen
Asked:
Mateen
  • 4
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Mateen,
> a) Should I set the option "enforce relationship for replication" ON
> in all foreign keys in publisher database.

Yes..

>    b) Shoul I set the option "enforce relationship for replication"
> OFF in all foreign keys in subscriber database.

By default this will be off
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
FROM BOL

Using NOT FOR REPLICATION
The NOT FOR REPLICATION option is used by Microsoft® SQL Server™ 2000 replication to implement ranges of identity values in a partitioned environment. The NOT FOR REPLICATION option is especially useful in transactional or merge replication when a published table is partitioned with rows from various sites.

When a replication agent connects to a table with any login, all of the NOT FOR REPLICATION options on the table are activated. When the option is set, SQL Server 2000 maintains the original identity values on rows added by the replication agent but continues to increment the identity value on rows added by other users. When a user adds a new row to the table, the identity value is incremented in the normal way. When a replication agent replicates that row to a Subscriber, the identity value is not changed when the row is inserted in the Subscriber table.

For example, consider a table that contains rows inserted from two sources: Publisher A and Publisher B. The rows inserted at Publisher A are identified by increasing values from 1 through 1000, and those rows at Publisher B are identified by values from 1001 through 2000. If a process at Publisher A inserts a row locally into the table, SQL Server assigns the first row a value of 1, the next row a value of 2, and so forth, in automatically increasing increments. Similarly, if a process at Publisher B inserts a row locally into the table, the first row is assigned a value of 1001, the next row a value of 1002, and so forth. When rows at Publisher A are replicated to B, the identity values remain 1, 2, and so forth, but local seed values at B are not reset.

Regardless of its role in replication, the IDENTITY property does not enforce uniqueness by itself, but merely inserts the next value. Although you can provide an explicit value using SET IDENTITY INSERT, that function is not appropriate for replication because it also reseeds the value. The NOT FOR REPLICATION option was created specifically for applications using replication. For example, without this option, as soon as the first row from Publisher B (with value 1001) is propagated to Publisher A, Publisher A's next value would be 1002. The NOT FOR REPLICATION option is a way of telling SQL Server 2000 that the replication process gets a waiver when supplying an explicit value and that the local value should not be reseeded. Each Publisher using this option gets the same reseeding waiver.

Custom stored procedures that use INSERT, UPDATE, and DELETE statements with full column lists are required before replication will work with identity properties. If full column lists are not used, an error will be returned.

The following code example illustrates how to implement identities with different ranges at each Publisher:

At Publisher A, start at 1 and increment by 1.
CREATE TABLE authors ( COL1 INT IDENTITY (1, 1) NOT FOR REPLICATION PRIMARY KEY )

At Publisher B, start at 1001 and increment by 1.
CREATE TABLE authors ( COL1 INT IDENTITY (1001, 1) NOT FOR REPLICATION PRIMARY KEY )

After activating the NOT FOR REPLICATION option, connections from replication agents to Publisher A insert rows with values such as 1, 2, 3, 4. These are replicated to Publisher B without being changed (that is, 1, 2, 3, 4). Connections from replication agents at Publisher B get values 1001, 1002, 1003, 1004. Those are replicated to A without being changed. When all data is distributed or merged, both Publishers have values 1, 2, 3, 4, 1001, 1002, 1003, 1004. The next locally inserted value at Publisher A is 5. The next locally inserted value at Publisher B is 1005.

It is advisable to always use the NOT FOR REPLICATION option along with the CHECK constraint to ensure that the identity values being assigned are within the allowed range. For example:

CREATE TABLE sales
(sale_id INT IDENTITY(100001,1)
    NOT FOR REPLICATION
    CHECK NOT FOR REPLICATION (sale_id <= 200000),
sales_region CHAR(2),
CONSTRAINT id_pk PRIMARY KEY (sale_id)
)

Even if someone used SET IDENTITY INSERT, all values inserted locally must obey the range. However, a replication process is still exempt from the check.



Note  If you are using transactional replication with the immediate-updating Subscribers option, do not use the IDENTITY NOT FOR REPLICATION design. Instead, create the IDENTITY property at the Publisher only, and have the Subscriber use just the base data type (for example, int). Then, the next identity value is always generated at the Publisher.

0
 
MateenAuthor Commented:
Hi aneeshattingal

Thanks for your very detailed answer.

1) I am using transactional replication with immediate or queue updating off.
2) None of my table contain ( and will never contain) identity field.

Please re-reply What should I do.

Please reply about this
<< One property of a article is snapshot. There are two option. Drop/recreate and the other is keep existing table.
What does these exactly mean. Practically if I set keep existing table I face problem.
>>

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Mateen,
> One property of a article is snapshot. There are two option. Drop/recreate
> and the other is keep existing table.

Its asking whether to  drop the existing table and recreate it from the snapshot file Or to leave the table on the target unchanged and reject the relevant data in the snapshot file  OR  reject the relevant data in the snapshot file.The last option is to delete the data on the target and use the empty table to write only the data from the snapshot file. In the case of using the existing table, the schema information from the snapshot file is not needed.
Hope now you got the answer, go with the default option (drop /create )





http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpatterns/html/ImpSnapshotReplication.asp
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Sorry for the delayed reply .. I went for Lunch :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now