Merge Replication: Dropping an article from existing Publication

Hi All,
How to drop a single article (TABLE) from merge replication keeping
publication intact.
Regards
Javed Iqbal
Javed,
unlike transactional, this is not possible. The nearest you can get is to
drop the publication hten recreate it without the article and do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the help.
Actually I want to change a column width in a replicated table which is
appearing at first position.
I can add dummy column then update dummy column with data. Then drop the
main column. Recreate main column with new size and update it. And finally
removing the dummy column using sp_repladdcolumn and sp_repldropcolumn.
But it will add the column at last position.
Is there any way to redefine column position on a replicated table?
OR
How to add a column at specific position?
Thanks in advance.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ucTtJkYTGHA.4132@.TK2MSFTNGP11.phx.gbl...
> Javed,
> unlike transactional, this is not possible. The nearest you can get is to
> drop the publication hten recreate it without the article and do a nosync
> initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
ideally the application is not dependant on the column position. We have no
TSQL to specify it, and the only practical way is to recreate the table. In
terms of merge, this would be a reinitialization. If at all possible, make
your application use column names rather than position numbers. As an
interim measure can you perhaps define a view?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul for your prompt help.
Ideally my TSQL should refer column names but in my applications I am not
sure about what programmers have coded in TSQL. And it is not possible to
check those programs .
There are many reports based on this table hence I do not want to play with
the column position.
Correct me if I wrong at following.
Scenario - Merge Replication with NOSYNC.
I have only option to drop subscription / publication and recreate it with
the earlier generated scripts.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23x2oERZTGHA.1160@.TK2MSFTNGP09.phx.gbl...
> Javed,
> ideally the application is not dependant on the column position. We have
no
> TSQL to specify it, and the only practical way is to recreate the table.
In
> terms of merge, this would be a reinitialization. If at all possible, make
> your application use column names rather than position numbers. As an
> interim measure can you perhaps define a view?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
a nosync initialization would be ok. You could change the column position on
the publisher and subscriber before synchronising. In this case you'd take
over control of the identity range management. You could only use the
previous scripts if they were themselves defined as nosync.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes Paul,
I am using NOSYNC since MERGE Replication is created / started.
Identities are ODD on Publisher EVEN on subscriber.
Any further inputs are highly appreciated.
Thanks a lot again Paul.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ecN9afZTGHA.6084@.TK2MSFTNGP14.phx.gbl...
> Javed,
> a nosync initialization would be ok. You could change the column position
on
> the publisher and subscriber before synchronising. In this case you'd take
> over control of the identity range management. You could only use the
> previous scripts if they were themselves defined as nosync.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Merge replication performance dropping. Opinions?

I've been tasked with improving our company's merge replication
performance. We are using SQL merge replication and lately we are seeing
a decrease in the performance. Specifically, we are seeing more errors,
retries, latency, and even missing data. I hope someone can provide
insight or share experiences on replicating at this scale.
Our configuration is such:
- One master server replicates to three distribution servers. These
servers are on the same LAN. The replication jobs are scheduled to run
every 10 minutes.
- Each of the three distribution servers replicates to ~15 servers (total
of 45 subscribers). These subscribers are on connections ranging from
64k to 256k. The replication jobs are scheduled to run once an hour
(staggered times). The subscription is a push subscription running at
the distributor. The subscriptions are filtered per location.
- The publication has 200+ articles, although data changes frequently in
only about 20 tables.
- The data being replicated is primarily either inserts at the
subscribers, or common data inserted/updated at the master. We have 18
tables >1M rows and 3 tables >10M rows. Because of row filtering, not
all these rows get replicated to each subscriber.
The errors:
- No errors between master and distributors, but of course they are on
the LAN.
- "The process could not check the existence of generation at the
Subscriber"
- "The process could not connect to Subscriber xxx"
- "The process could not enumerate changes at the Subscriber"
- "The merge process could not apply the replication metadata" (This
error was returned on a "Completed" task)
- "The process could not query row data at the Subscriber"
Other notes/questions:
- I've tried changing all the subscriber profiles to Slow Link, with not
much improvement.
- The replication taks are launched with QueryTimeout=30000. I believe
this to be excessive, but can't be sure.
- Task durations have run as long as 8 hours.
- Since most of the errors are accompanied by "General network error", I
can accept that our WAN sucks. How can I mitigate this risk?
- Querying the MSMerge tables, I am seeing what I think to be outrageous
row counts: 10M rows in MSMerge_contents at the master, 2M rows in
MSMerge_tombstone at the master, 8M rows in MSMerge_errorlineage at the
distributor. Does this indicate anything seriously wrong?
- Database Indexes are defragged once a week.
- I have just modified some triggers to reduce the amount of data being
replicated; a few days will show if it was helpful.
I appreciate any feedback, comments, or suggestions. Thank you!
If your subscribers connect frequently you should try to drop the retention
period to something small, like 4 days.
Also you might want to evaluate how you have created your publication to see
if there are any optimizations you can make. For example do you need
filters? Is Keep partition changes is true?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Anachostic" <anachostic@.remove.700cb.net> wrote in message
news:Xns9950690A3F584anachostic@.66.250.146.128...
> I've been tasked with improving our company's merge replication
> performance. We are using SQL merge replication and lately we are seeing
> a decrease in the performance. Specifically, we are seeing more errors,
> retries, latency, and even missing data. I hope someone can provide
> insight or share experiences on replicating at this scale.
> Our configuration is such:
> - One master server replicates to three distribution servers. These
> servers are on the same LAN. The replication jobs are scheduled to run
> every 10 minutes.
> - Each of the three distribution servers replicates to ~15 servers (total
> of 45 subscribers). These subscribers are on connections ranging from
> 64k to 256k. The replication jobs are scheduled to run once an hour
> (staggered times). The subscription is a push subscription running at
> the distributor. The subscriptions are filtered per location.
> - The publication has 200+ articles, although data changes frequently in
> only about 20 tables.
> - The data being replicated is primarily either inserts at the
> subscribers, or common data inserted/updated at the master. We have 18
> tables >1M rows and 3 tables >10M rows. Because of row filtering, not
> all these rows get replicated to each subscriber.
> The errors:
> - No errors between master and distributors, but of course they are on
> the LAN.
> - "The process could not check the existence of generation at the
> Subscriber"
> - "The process could not connect to Subscriber xxx"
> - "The process could not enumerate changes at the Subscriber"
> - "The merge process could not apply the replication metadata" (This
> error was returned on a "Completed" task)
> - "The process could not query row data at the Subscriber"
> Other notes/questions:
> - I've tried changing all the subscriber profiles to Slow Link, with not
> much improvement.
> - The replication taks are launched with QueryTimeout=30000. I believe
> this to be excessive, but can't be sure.
> - Task durations have run as long as 8 hours.
> - Since most of the errors are accompanied by "General network error", I
> can accept that our WAN sucks. How can I mitigate this risk?
> - Querying the MSMerge tables, I am seeing what I think to be outrageous
> row counts: 10M rows in MSMerge_contents at the master, 2M rows in
> MSMerge_tombstone at the master, 8M rows in MSMerge_errorlineage at the
> distributor. Does this indicate anything seriously wrong?
> - Database Indexes are defragged once a week.
> - I have just modified some triggers to reduce the amount of data being
> replicated; a few days will show if it was helpful.
> I appreciate any feedback, comments, or suggestions. Thank you!
>
|||Thanks for your response.
Regarding retention, is this the "Subscriptions expire and may be
dropped..." in the Publication>Properties>General tab? If so, ours is set
to 60 days. I figure reducing the retention period will reduce the size of
the MSMerge_content tables? And would this change require dropping and
recreating all the subscriptions? That's not an option at the current
time.
I could not find where the "keep partition changes" setting is. The only
reference in BO is with SQL-DMO.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:#QmoFD4rHHA.1296@.TK2MSFTNGP06.phx.gbl:

> If your subscribers connect frequently you should try to drop the
> retention period to something small, like 4 days.
> Also you might want to evaluate how you have created your publication
> to see if there are any optimizations you can make. For example do you
> need filters? Is Keep partition changes is true?