March 23, 2003 at 9:07 pm
Hi,
I have checked both snapshot & transactional replication. After replication I realized that database structure of subscriber is not same as that of publisher.
Identity, primary keys, foreign keys, indexes etc are gone in subscriber.
Is there any setting to safe gaurd the database structure??
Thanks in advance.
March 24, 2003 at 6:03 am
SQL Server replication changes when needed the replicated tables, but that shouldn't doesn't affect the use of the db. It can add uniqueidentifiers columns, because they are needed in replication.
March 24, 2003 at 12:49 pm
You can modify the snapshot to send some of that stuff over, or use a post snapshot script.
Andy
April 2, 2003 at 10:01 pm
There's a stored procedure called sp_MSdroparticleconstraints which lives in the master database on your subscribers and this is the thing that removes your Primary Keys (amongst other things).
I have heard tell from others that you can comment out this functionality from the SP to retain your PKs &c. I tried it and it worked, but it strikes me as not an elegant solution and I'm not sure what the side effects would be so I ended up changing to merge replication instead which doesn't have this issue.
HTH
April 3, 2003 at 12:36 pm
LMAO.... I just posted this in another thread but here you go anyway...
If you perform the replication by use of the system procs and scripting, as I do, there is an option called schema_option which determines how the end tables are created. Here are the available options you can use, along with the one I commonly use, which creates your tables correctly.
@schema_option = 0x8000
--Is a bitmask of the schema generation option for the given article. It specifies the automatic creation of the stored procedure in the destination database for all CALL/MCALL/XCALL. schema_option is binary(8), and can be a combination of these values. If this value is NULL, the system will auto-generate a valid schema option for the article. The table given in the Remarks shows the value that will be chosen based upon the combination of the article type and the replication type.
--0x00 Disables scripting by the Snapshot Agent and uses creation_script.
--0x01 Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
--0x02 Generates custom stored procedures for the article, if defined.
--0x10 Generates a corresponding clustered index.
--0x20 Converts user-defined data types to base data types.
--0x40 Generates corresponding nonclustered index(es).
--0x80 Includes declared referential integrity on the primary keys.
--0x100 Replicates user triggers on a table article, if defined.
--0x200 Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table will not be replicated.
--0x400 Replicates check constraints.
--0x800 Replicates defaults.
--0x1000 Replicates column-level collation.
--0x2000 Replicates extended properties associated with the published article source object.
--0x4000 Replicates unique keys if defined on a table article.
--0x8000 Replicates primary key and unique keys on a table article as constraints using ALTER TABLE statements.
--NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply