April 24, 2007 at 12:17 pm
I'm a replication newb, so please bare with me. I have a SQL 2000 Server that has two databases on it that I need to replicate to another SQL 2000 Server. I need to use transactional replication to make sure the data on the target server is always up-to-date. It only needs to be a one-way (read-only) push replication. In other words the target server (subscriber) will never need to modify the data, or send any updates back to the source (publisher). It will only use the data/database for reporting purposes.
I think I have everything just about finished, but when i step through the wizard to create the publication, I get almost done and get the warning about the IDENTITY columns ("IDENTITY Property not transferred to Subscribers"). I would like to have the identity columns in the target db so that the queries I've written already on the source server will still work on the target server. How do I fix this? It says:
-------------------------------------
Because this publication does not allow updatable subscriptions, when IDENTITY columns are transferred to the Subscriber, the IDENTITY property will not be transferred. (For example, a column defined as INT IDENTITY at the Publisher will be defined as INT at the Subscriber.)
If you want the IDENTITY property transferred to the subscription table, you must do the following:
» Create the subscription table manually using the IDENTITY property and the NOT FOR REPLICATION option. You can have SQL Server execute a custom script to perform this action before the snapshot is applied. To specify a custom script, create the publication, create the script, and enter the script name on the Snapshot tab of the publication properties.
» Set the name conflicts article property so that SQL Server does not drop an existing table when applying the snapshot at the Subscriber. This property is on the Snapshot tab of article properties.
-------------------------------------
What does it mean by 'Create the subscriptions table manually'? Does that mean I have to create copies by hand of each table I need in the replication and set that 'not for replication' option on each table? There are over 500 tables in this database alone. Since I don't need two-way replication, isn't there some way to set this up where it just pushes all its data and updates to a subscriber and knows that no data will come back the other way? If that was the case, then the IDENTIY columns should be fine as they are since it won't have to worry about the subscriber creating duplicates.
Or am I just reading the error wrong and freaking out over nothing, in other words I don't need the IDENTITY property, just the column with the data in it to use as a primary key, so I don't have to do anything else, just continue from that point in the wizard?
If you need more information, let me know, I'm probably forgetting something.
Thanks in advance, any help will be greatly appreciated.
April 24, 2007 at 2:33 pm
The data stored in the identity columns will be replicated to the target tables, but the identity property itself will not be. That should be fine for you because you said there won't be any data modification in the target database so the identity column won't be incremented anyway.
SQL Server doesn't replicate the identity property because of the need for the data in the target database to exactly match the data in the source database. Check out the NOT FOR REPLICATION option of CREATE TABLE in BooksOnLine.
Greg
Greg
April 25, 2007 at 7:13 am
"Does that mean I have to create copies by hand of each table I need in the replication and set that 'not for replication' option on each table?"
Yes and no. You have to manually create the tables, but create the identity column with the not for replication option. The NOT FOR REPLICATION option or property does not apply to tables, but it can apply to elements of tables, such as Identity columns, check constraints, and FK constraints.
Create Table foo (
ID int identity NOT FOR REPLICATION,
SomeColumn int,
SomeOtherColumn UniqueIdentifier,
...
)
This makes the identity property behave as if it doesn't exist for any operation performed by a replication agent.
Since you aren't perfoming inserts at the subscriber, you don't need the identity property, because it will never be "used", Like Greg C indicates.
April 25, 2007 at 3:27 pm
Thanks Greg. As I was making my post, I went back and re-read the error and thought 'wait a minute, this may not even appy to me'. You were right. I just clicked next and it all proceeded normally. I created the publication and have pushed the subscriptions, so I think I'm in business. Thanks again.
April 26, 2007 at 8:33 am
Just wanted to plug a comment relating to: "The data stored in the identity columns will be replicated to the target tables, but the identity property itself will not be. That should be fine for you because you said there won't be any data modification in the target database so the identity column won't be incremented anyway."
This is indeed the case in most replication architectures that I have seen. However, in some cases users insist on what's called "self-describing schema". This means that as a user or DBA, I can take a look either at the publisher or at the subscriber to get the DDL for my article. This can be achieved like mentioned above by explicitly creating the DDL on the subscribers before adding the article to the publisher/distributor.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply