Sql-server – Merge Replication: Updates to Schema

merge-replicationsql server

We have a main SQL Server which replicates to two backup servers via continuous merge replication.

There are regular updates to the schema. Regular changes include:

Adding new tables and relationships with existing tables
Changing the data type of columns on tables already replicated

What is the easiest way to maintain replication in such an environment?

If changes are made to the publisher, some changes cannot be synced to the subscriber and the whole replication has to be setup again (agent + subscriptions).

These databases are too big to replicate in its entirety every time there is an update.

Any advice will be appreciated.

Best Answer

Depending on what version of SQL server you are running you can initialize via a backup. However, Microsoft has recommendations as to when it's appropriate to use this processes. Also need to make sure your security is setup properly as well.

Related Solutions

Sql-server – SQL Server transactional replication – behaviour of a MERGE command on the subscriber(s) vs. publisher

If you truncated the table on the publisher the record in the transaction log would be an insert, and therefor an insert would be executed against the subscriber.

Now what you can do is modify the stored procedures that replication uses at the subscriber so that when the insert procedure fires it first looks to see if the record exists and if it does it does an update, and if it doesn't it does an insert.

Sql-server – Merge Replication Snapshot Files

There is a lot of information here,

http://msdn.microsoft.com/en-us/library/ms151769(v=sql.105).aspx

And here,

http://msdn.microsoft.com/en-us/library/ms152502(v=sql.105).aspx

So the thing to be aware of, because I was using parameterised filters the snapshot folder above doesn't contain any data, only schema.

Also I was not filtering correct using SUSER_SNAME() so the filtering was not using the snapshot at all causing me performance issues.

Once that was sorted I could see in the snapshot folder there subfolders with a snapshot for each user of the system. This data is the data filtered for that particular user.

Best Answer

Related Solutions

Sql-server – SQL Server transactional replication – behaviour of a MERGE command on the subscriber(s) vs. publisher

Sql-server – Merge Replication Snapshot Files

Related Question