Merge Replication Questions [SQL2k5 non express]

I can choose synchronization direction for articles: a) Bidirectional b) one way

1) Is that possible somehow to replicate the schema only of an article but no synchronization / zero direction :-)/

2) Same question about columns, I should replicate schema only for few columns, but without data synch. These columns are freely updateable at anywhere (publisher and subscribers), but the data changes shouldn't be replicated.

Thanks for the answers in advance

I guess that you want to keep same schema's at two or more machines?

I do not know whether you can do it using replication, actually I think that there is no way to do something like that.

What I would do is that I would script database, and make same copies at all locations. Later when you need some updates/changes to schema, you can script those also. Not only that, but you can build your own schema replication system, so everything could go, kind of, semi-automatic.

|||

Sorry, my initial question was not clear.

I would like to replicate all tables in the db, except 1-2 tables and 3-4 columns only.

Let's see the following example:

There are about 30 tables to replicate let's name those T1, T2, T3, ... T30 and the column names are T1C1, T1C2, ... T2C1, T2C2, .... etc

I would like to replicate all tables, except T15 and T16 tables (all columns) and 4 columns T8C4, T8C5, T9C2 and T9C3. But the schema should be the same at all places, so T15 and T16 should be exist at subscribers and publishers and the mentioned columns also, but data should not be replicated to-from that 2 table and from/to that 4 columns.

|||

Now it is much clearer to me.

As I said, you can copy your schema to be same on all databases, but you can filter out your publication so you just replicate tables T1-T14 and T17-T30, and to replicate all columns except T8C4, T8C5, T9C2 and T9C3.

If you then choose to initialize, you will loose tables/columns that are not in replication, but you can add them later.

Or you can make publication, then copy db schema using script to subscriber (so you have rowguid) and choose do not initialize.

Test it, play around with it. Make on your own sql server two tiny db's with two tables (one publisher and one subscriber) and play with it.

|||

THis can be done with replication. What you do is create a publication containing all the tables and columns you want. You can then script out table T15 and T16, put it in a file, and reference it in parameter @.post_snapshot_script for stored procedure sp_addpublication or sp_addmergepublication.

|||

Thanks for the answer. It took a bit longer, because I ran into a little problem, I got the following error message on one of my stored procedure in post-snapshot script:

"The query processor could not produce a query plan. For more information, contact Customer Support Services."

It was because i forgot to include the:

set QUOTED_IDENTIFIER ON

maybe this is a bug of sql2k5 SP1

btw, I moved the T8C4, T8C5, T9C2 and T9C3 columns to separate tables as well, and there are foreign keys pointing back to the original tables PKs

Thanks again for the solution.