Merge Replication with vertical Filters

Hello
it's possible to create a Merge-Publication with vertical Filter on an
Sql-Server 2000 with SP3?
in our developer System with SP4 i can make a replication, but in the Target
System i didn't.
Error 20009 add the article...
Thanks
Daniel
Yes it is, but ensure that you have defaults in place for the columns you
are not replicating or that they are nullable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Dani Schwegler" <d.schwegler@.hispeed.ch> wrote in message
news:ezRNYPmUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hello
> it's possible to create a Merge-Publication with vertical Filter on an
> Sql-Server 2000 with SP3?
> in our developer System with SP4 i can make a replication, but in the
> Target System i didn't.
> Error 20009 add the article...
> Thanks
> Daniel
>
sql

Merge replication with autoincrement field

Hi guys,
I have two separate system with some data common between them.
For simplicity let's say System A contains some data that is same with
System B. Now I want to get the data from System A to System B by
merge replication (dont want to send data from System B to System A -
using merge replication for filtering using "join". if this can be
done in some other way using transactional replication please let me
know).
But in System A there are few tables with autoincrement fields set for
the primary key field. I am entering data in those tables in System B
also.
Now, how can I handle this - insert records in both system avoiding
clash for the primary key.
PS: I know in MySQL they handle this case by setting up the
replication and then change the seed value and increment value for
different database systems and by thus avoid the clash.
Thanks,
Rakesh.
Rakesh,
on the article properties form there is a tab to manage the identity values.
In SQL 2005 this is set automatically but in SQL 2000 you have to enable it
manually. Once a large range has been selected, there should be no issues as
the ranges are partitioned.
HTH,
Paul Ibison

Merge Replication Status

Hoping someone can help.
What merge system table/s will give me the current status of my merge agent
i.e. the Replication Monitor window in Enterprise Manager displays a status
of 'suceeded' etc etc - where can I source this field from within the
Replication System tables?
Any help appreciated.
this script will get you details of the last history for
the merge agent called 'DH1791628-Northwind-
NorthwindStaff-DH1791628-3'.
It will have basic error info if an error occurs.
If you want the detailed error info, you'll need to join
to the MSrepl_errors table: MSmerge_history.error_id =
MSrepl_errors.id. This table typically has several rows
so you might want to use a cursor to add the text
together.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
declare @.agent_id int
select @.agent_id = id from msmerge_agents where name
= 'DH1791628-Northwind-NorthwindStaff-DH1791628-3'
select top 1 case
when rh.runstatus = 1 then 'Start'
when rh.runstatus = 2 then 'Succeed'
when rh.runstatus = 3 then 'In progress'
when rh.runstatus = 4 then 'Idle'
when rh.runstatus = 5 then 'Retry'
when rh.runstatus = 6 then 'Fail'
end as RunStatus,
'start_time' = convert(nvarchar(12), start_time, 112) +
substring(convert(nvarchar(24), start_time, 121), 11, 13),
comments
from MSmerge_history rh
order by timestamp desc

Merge Replication Initial Snapshot

SQL 2000.
I have a merge replication system running in a 24/7 environment that
cannot be brought offline or locked up. When i try to run my snapshot
agent, it locks up the primary DB (there are many tables in this that
are very large, like hundreds of millions of rows). is there a way
around the initial snapshot, or a way to speed it up? I have read in
BOL that it may be possible with a backup and removable storage, or
using BCP.
Has anyone been able to get merge replication working without locking
up the db or bringing it offline? If so, how?
Thanks in advance!!
No, AFAIK - there is no way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"colbycrane" <gstarnes@.gmail.com> wrote in message
news:1104268201.081080.154550@.z14g2000cwz.googlegr oups.com...
> SQL 2000.
> I have a merge replication system running in a 24/7 environment that
> cannot be brought offline or locked up. When i try to run my snapshot
> agent, it locks up the primary DB (there are many tables in this that
> are very large, like hundreds of millions of rows). is there a way
> around the initial snapshot, or a way to speed it up? I have read in
> BOL that it may be possible with a backup and removable storage, or
> using BCP.
> Has anyone been able to get merge replication working without locking
> up the db or bringing it offline? If so, how?
> Thanks in advance!!
>
|||Backup and restore can be used for a nosync initialization, but the data
shouldn't be changed on the publisher while this takes place so effectively
it's the same. In transactional replication there is the option to have
concurrent snapshot bgeneration, but this is not possible for merge. So, if
you must use merge, essentially you'll need some system downtime.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But the problem with a backup restore is that the rowguid column still has
to be added, preferably before the backup is done. This involves an alter
table and locking.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OqLd1pZ7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Backup and restore can be used for a nosync initialization, but the data
> shouldn't be changed on the publisher while this takes place so
> effectively
> it's the same. In transactional replication there is the option to have
> concurrent snapshot bgeneration, but this is not possible for merge. So,
> if
> you must use merge, essentially you'll need some system downtime.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Good point
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We employ a fairly complicated ETL routine to add rows to tables without
locking the db. This can also be used to migrate the data to the
subscriber db. Heres how it works:
Say tableA needs a new column (tableA has 60m rows). We create TableB,
which is TableA + the new column. We insert / select with (nolock) to
migrate the data. This may introduce dirty reads, thus bad data to
tableB so, at the same time, we put an on insert, update, delete trigger
on table that feeds any changes to TableC. After the inital insert /
select is finished, we run a daemon that takes from TableC and applies
it to tableA. Ultimately, we run the daemon repeatedly until TableC
only has a few records (6 second delay is acceptable), so we then put
into a transaction the final 'reconciliation' to lock tableA, push the
final changes from tableC to tableB, do an sp_rename, and off we go...
If we were able to do this, would the initialization of Merge
replication still read all the rows out of TableA, thus still locking
it?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Greg,
If I read you correctly, in your example, you're saying that you have a
method of making an identical copy of a table without taking a shared lock
on it, or at least the lock used at the final step is minimal and probably
involves a few rows. If you can make such a copy on the subscriber then you
could indeed do a noinit initialization. The guid column with rowguid
attribute could be added using your current methodology. During
initialization, you need to be sure that there are no changes to the data at
the publisher, but this could last as little as 30 secs.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

merge replication disable

I took a backup from a merge replicated database and restored it to a
non-replicated db. I want to make changes to the db but the system still
thinks the db is replicated, and I can't seem to get rid of any articles or
publications.
How can I undo the various replication flags and whatever?
try this
http://www.tech-archive.net/Archive/...4-03/0886.html
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul J." <Paul J.@.discussions.microsoft.com> wrote in message
news:996FA174-5039-4BCE-99E2-CDF52B8368B1@.microsoft.com...
> I took a backup from a merge replicated database and restored it to a
> non-replicated db. I want to make changes to the db but the system still
> thinks the db is replicated, and I can't seem to get rid of any articles
or
> publications.
> How can I undo the various replication flags and whatever?

Merge Replication differences between SQL 2000 and SQL 2005

We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.

Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.

We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.

For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @.partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).

To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.

Examples of the current behaviour are as follows,

On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.

Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.

This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.

Everything still points to the merge replication setup.

So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.

Sorry for the convoluted post. Hope someone can help us.

Cheers
Steve

Check if you are hitting the issue described in: http://support.microsoft.com/kb/917476/en-us

|||Thank you Mahesh.

that has certainly resolved the issue of the excessive data when a subscriber initialises.

Now, if I can just work out how to make this a more performant installation ...|||What other issues are you having regarding performance?|||The issues can be best described in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=514509&SiteID=1&mode=1
which is another post from me that is really related to this one.

Essentially there are various times when the performance just goes out the window and our perception here is that we have not fully understood, let alone implemented the changes we need to bring our replication scenario from it SQL 2000 guise to the SQL 2005 version that works best for us.

In the meantime we continue to experiment with the settings in the hope that we find our way.

We feel that our problem lies around the way we set up the partition_options flag.

Thanks for your help.|||I will follow up with you on that thread.

merge replication corruption (system triggers and views)

All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:

Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?

-mikeI found another change.. we added a linked server using sp_addlinkedserver.. Any thoughts?|||Does your merge subset filter clauses or join filter clauses contain views that contain WITH CHECK OPTION, pointing to remote table?|||No filters are set for the publication.|||

ok, then you have to trace your steps to see exactly what changes were made that would cause this, and see if you can back them out one by one.

any idea what the linked server would have to do with regards to the views, triggers, or any of the published tables? are you making changes from a remote machine?

Merge replication between two publishers with dynamic filters

Hello,
I am working on a distributed database system in which each site is a
publisher of a filtered set of data. It is necessary that a publisher can
subscribe to another publisher.
I am using merge replication, dynamic filters and push subscriptions. Each
site publishes the same tables with another filter.
When I test this scenario, I notice that the merge agent replicates only the
changes of the publisher to the subscriber. A change on the subscriber (thas
has also a publication on the same tables) is not replicated to the publisher.
Does someone know a solution for this?
Is it possible to have (bidirectional) merge replication between two
publishers with dynamic filters?
I hope someone can help me with this.
thanks in advance!
Marco Broenink
Could you please describe in more detail your publisher subscriber
configurations and the filters.
If the table has a column that you are using to filter from Publisher to
Subscriber and then make this subscriber a republisher and then try to use
the same column as the filter column, there is only set of data at the
republisher/subscriber.
I am not clear on your setup. Could you please repost with more elaborate
setup steps?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:CD4D84B8-A940-46BF-AC9A-5FF5098D2200@.microsoft.com...
> Hello,
> I am working on a distributed database system in which each site is a
> publisher of a filtered set of data. It is necessary that a publisher can
> subscribe to another publisher.
> I am using merge replication, dynamic filters and push subscriptions. Each
> site publishes the same tables with another filter.
> When I test this scenario, I notice that the merge agent replicates only
the
> changes of the publisher to the subscriber. A change on the subscriber
(thas
> has also a publication on the same tables) is not replicated to the
publisher.
> Does someone know a solution for this?
> Is it possible to have (bidirectional) merge replication between two
> publishers with dynamic filters?
> I hope someone can help me with this.
> thanks in advance!
> Marco Broenink
|||Thanks for your response!
I am using a dynamic filter. This filter uses a function. This function
needs the hostname and a filter-column to dermine if the row needs to be
filtered. The filter looks like:
SELECT <published_columns> FROM [dbo].[PublishedTable]
WHERE 1 = [dbo].[fn_DynamicFilter]([FilterColumn], HOST_NAME())
The filterfunction uses a mapping table that maps the contents of the
[FilterColumn] to hostnames.
With this mapping table, each publisher publishes its own part of all data.
So the publications of two publishers do not overlap. But the publications
are on the same tables.
Problem with this configuration is that changes of a subscriber are not
replicated to the publisher. It looks like that the subscriber's own
publication is blocking this.
I hope you can help me with this.
greetings, Marco.
"Mahesh [MSFT]" wrote:

> Could you please describe in more detail your publisher subscriber
> configurations and the filters.
> If the table has a column that you are using to filter from Publisher to
> Subscriber and then make this subscriber a republisher and then try to use
> the same column as the filter column, there is only set of data at the
> republisher/subscriber.
> I am not clear on your setup. Could you please repost with more elaborate
> setup steps?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:CD4D84B8-A940-46BF-AC9A-5FF5098D2200@.microsoft.com...
> the
> (thas
> publisher.
>
>
|||I have used 'global' subscriptions in stead of 'local' and this problem is
solved.
Now the changes are also replicated from subscriber to publisher.
Unfortunately, I have a new problem.
I use replication with dynamic filters. In my system it is possible that
data is added to the subscriber that doesnot pass the filter. When
replicating, this data is deleted at the subscriber and added to the
publisher.
How can I prevent this delete & insert ?
thanks in advance, Marco
"Marco Broenink" wrote:
[vbcol=seagreen]
> Thanks for your response!
> I am using a dynamic filter. This filter uses a function. This function
> needs the hostname and a filter-column to dermine if the row needs to be
> filtered. The filter looks like:
> SELECT <published_columns> FROM [dbo].[PublishedTable]
> WHERE 1 = [dbo].[fn_DynamicFilter]([FilterColumn], HOST_NAME())
> The filterfunction uses a mapping table that maps the contents of the
> [FilterColumn] to hostnames.
> With this mapping table, each publisher publishes its own part of all data.
> So the publications of two publishers do not overlap. But the publications
> are on the same tables.
> Problem with this configuration is that changes of a subscriber are not
> replicated to the publisher. It looks like that the subscriber's own
> publication is blocking this.
> I hope you can help me with this.
> greetings, Marco.
> "Mahesh [MSFT]" wrote:
|||Glad that you could work around your first problem, though to be frank, I am
still unclear of the setup.
Regarding your new problem,
If each subscriber inserts data that corresponds to only its subset of data
then you could try using a default of some kind to the tables. Like hostname
or something that will map appropriately to the filter condition and make it
pass. So everytime an insert happens at the subscriber, the filter condition
is met and then is successfully propagated to the publisher and does not get
deleted at the subscriber in turn.
Please note that this can work only if the subscriber always makes
"good" inserts, that is to say that the subscriber never expects to insert
data (that does not satisfy the filter) and then in turn expects the data to
be deleted by the publisher.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:664513B1-B610-453F-B033-6FD1B8720BE1@.microsoft.com...[vbcol=seagreen]
> I have used 'global' subscriptions in stead of 'local' and this problem is
> solved.
> Now the changes are also replicated from subscriber to publisher.
> Unfortunately, I have a new problem.
> I use replication with dynamic filters. In my system it is possible that
> data is added to the subscriber that doesnot pass the filter. When
> replicating, this data is deleted at the subscriber and added to the
> publisher.
> How can I prevent this delete & insert ?
> thanks in advance, Marco
>
> "Marco Broenink" wrote:
data.[vbcol=seagreen]
publications[vbcol=seagreen]
to[vbcol=seagreen]
use[vbcol=seagreen]
elaborate[vbcol=seagreen]
message[vbcol=seagreen]
a[vbcol=seagreen]
publisher can[vbcol=seagreen]
subscriptions. Each[vbcol=seagreen]
only[vbcol=seagreen]
subscriber[vbcol=seagreen]
|||thanks again for the response.
In my topology, I have different publishers of the same table. These
publishers use different filters. A subscriber can be subscribed to different
publishers.
For example:
Site A publishes table1
Site B publishes table1
Site C is subscribed to Site A table1. This subscribtion is filtered with a
dynamic filter F1.
Site C is also subscribed to Site B table1. This subscribtion is filtered
with another dynamic filter F2.
The different dynamic filters make sure that the subscription to Site A do
not overlap the subscription to Site B.
Thus: The table1 of C contains a subset of table1 of A and a subset of
table1 of B.
So: table1 of C contains two types of data:
- data that meets filtercondition F1 and doesnot meet filtercondition F2.
- data that meets filtercondition F2 and doesnot meet filtercondition F1.
So the problem is: The subscriber will contain data that doesnot meet one of
the filterconditions. When replicating to Site A (filter F1), data of filter
F2 is deleted. When replicating to Site B (filter F2), data of filter F1 is
deleted.
So in this scenario, I think it is not possible to make only 'good' inserts
because it violates always one of the two filtersconditions.
I hope you know a solution. Or am I trying to do something impossible?
greetings, Marco Broenink
"Mahesh [MSFT]" wrote:

> Glad that you could work around your first problem, though to be frank, I am
> still unclear of the setup.
> Regarding your new problem,
> If each subscriber inserts data that corresponds to only its subset of data
> then you could try using a default of some kind to the tables. Like hostname
> or something that will map appropriately to the filter condition and make it
> pass. So everytime an insert happens at the subscriber, the filter condition
> is met and then is successfully propagated to the publisher and does not get
> deleted at the subscriber in turn.
> Please note that this can work only if the subscriber always makes
> "good" inserts, that is to say that the subscriber never expects to insert
> data (that does not satisfy the filter) and then in turn expects the data to
> be deleted by the publisher.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:664513B1-B610-453F-B033-6FD1B8720BE1@.microsoft.com...
> data.
> publications
> to
> use
> elaborate
> message
> a
> publisher can
> subscriptions. Each
> only
> subscriber
>
>
|||Hi Marco,
Please correct me if I understood wrong:
So what you are saying is SiteA and SiteB are publishing the same tables,
but are not replicating to each other. Is that right?
But in turn are replicating that table to SiteC.
This is not supported.
In the first place, When SiteC subscribed to SiteA, it gets the table from
SiteA. Now when you configure SiteC to subscribe from SiteB, how did you
configure? Did you configure a no-sync subscription? If not, and you used
all the default settings then actually you will not even be able to complete
the subscription because the table at SiteC (got from SiteA) will be
attempted to drop and recreate with the scripts from SiteB which will fail.
If you want to do what you are trying to do, one solution is to have table1
at SiteA and replicate it to SiteC with the proper filter. Have table2 at
SiteB and replicate that to SiteC with the proper filter.
On the subscriber you can have a view on those two tables that will give you
a combined view for the results. But you may not be able to make DMLs on the
view directly. You will still need to do the DMLs on the actual tables.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:BADB7E44-D9C9-4333-B69E-9F5B33314BEF@.microsoft.com...
> thanks again for the response.
> In my topology, I have different publishers of the same table. These
> publishers use different filters. A subscriber can be subscribed to
different
> publishers.
> For example:
> Site A publishes table1
> Site B publishes table1
> Site C is subscribed to Site A table1. This subscribtion is filtered with
a
> dynamic filter F1.
> Site C is also subscribed to Site B table1. This subscribtion is filtered
> with another dynamic filter F2.
> The different dynamic filters make sure that the subscription to Site A do
> not overlap the subscription to Site B.
> Thus: The table1 of C contains a subset of table1 of A and a subset of
> table1 of B.
> So: table1 of C contains two types of data:
> - data that meets filtercondition F1 and doesnot meet filtercondition F2.
> - data that meets filtercondition F2 and doesnot meet filtercondition F1.
> So the problem is: The subscriber will contain data that doesnot meet one
of
> the filterconditions. When replicating to Site A (filter F1), data of
filter
> F2 is deleted. When replicating to Site B (filter F2), data of filter F1
is
> deleted.
> So in this scenario, I think it is not possible to make only 'good'
inserts[vbcol=seagreen]
> because it violates always one of the two filtersconditions.
> I hope you know a solution. Or am I trying to do something impossible?
> greetings, Marco Broenink
>
> "Mahesh [MSFT]" wrote:
I am[vbcol=seagreen]
data[vbcol=seagreen]
hostname[vbcol=seagreen]
make it[vbcol=seagreen]
condition[vbcol=seagreen]
get[vbcol=seagreen]
insert[vbcol=seagreen]
data to[vbcol=seagreen]
message[vbcol=seagreen]
problem is[vbcol=seagreen]
that[vbcol=seagreen]
function[vbcol=seagreen]
to be[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
not[vbcol=seagreen]
Publisher[vbcol=seagreen]
try to[vbcol=seagreen]
the[vbcol=seagreen]
no[vbcol=seagreen]
in[vbcol=seagreen]
is[vbcol=seagreen]
replicates[vbcol=seagreen]
the[vbcol=seagreen]
two[vbcol=seagreen]
|||thanks for the help!
Marco
"Mahesh [MSFT]" wrote:

> Hi Marco,
> Please correct me if I understood wrong:
> So what you are saying is SiteA and SiteB are publishing the same tables,
> but are not replicating to each other. Is that right?
> But in turn are replicating that table to SiteC.
> This is not supported.
> In the first place, When SiteC subscribed to SiteA, it gets the table from
> SiteA. Now when you configure SiteC to subscribe from SiteB, how did you
> configure? Did you configure a no-sync subscription? If not, and you used
> all the default settings then actually you will not even be able to complete
> the subscription because the table at SiteC (got from SiteA) will be
> attempted to drop and recreate with the scripts from SiteB which will fail.
> If you want to do what you are trying to do, one solution is to have table1
> at SiteA and replicate it to SiteC with the proper filter. Have table2 at
> SiteB and replicate that to SiteC with the proper filter.
> On the subscriber you can have a view on those two tables that will give you
> a combined view for the results. But you may not be able to make DMLs on the
> view directly. You will still need to do the DMLs on the actual tables.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:BADB7E44-D9C9-4333-B69E-9F5B33314BEF@.microsoft.com...
> different
> a
> of
> filter
> is
> inserts
> I am
> data
> hostname
> make it
> condition
> get
> insert
> data to
> message
> problem is
> that
> function
> to be
> the
> all
> not
> Publisher
> try to
> the
> no
> in
> is
> replicates
> the
> two
>
>

Merge replication and long disconnections

We have a system that uses SQL2000 (SP2) with the main database in our main
location. We have been working on setting up replication to a secondary
location, and believe we have solved the relevant issues there. We have
just been asked (read told) to set up replication to another server that
will, by it's nature, be disconnected for extended periods of time (at least
weeks) with no possibility of getting an internet connection during that
time. Can we setup merge replication in this situation, what are the
"gotchas" that we need to design around? We must use merge replication
since some of our fields are text fields.
We are initially looking at setting up the client to be disconnected as a
pull subscription, while the secondary client is a push subscription from
the publisher at the main location. Does this make sense?
All input appreciated.
TIA
Ron L.
Ron,
the most striking thing to get right is @.retention. By default it'll be 14
days which isn't long enough in your case.
Push or Pull is your choice depending on who you want to be in control. If
you know when the remote client will be connected on a regular basis, then
push is still an option, but in your scenario I agree that pull seems more
reasonable.
Regards,
Paul Ibison
|||Paul
Thanks for the pointer. I'll take a look for @.retention.
Ron L.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ecYZkfaFEHA.712@.tk2msftngp13.phx.gbl...
> Ron,
> the most striking thing to get right is @.retention. By default it'll be 14
> days which isn't long enough in your case.
> Push or Pull is your choice depending on who you want to be in control. If
> you know when the remote client will be connected on a regular basis, then
> push is still an option, but in your scenario I agree that pull seems more
> reasonable.
> Regards,
> Paul Ibison
>

Merge replication and clustering

Hello:
Does merge replication support clustering?
I'm having some troubles about merge agent "querytimeout" parameter
throught a clustered system, and the hints that are over there about
"increasing timeout", "refreshing indices" and a lot more don't give any
result.
Anyone can help me?
Is anyway supported merge replication over a clustered system?
thanks a lot
Juan
Merge replication is supported in a clustered environment if replication
support is installed when you build the cluster. By default (IIRC) it is
not.
Run db cc brindle on your subscriber and publisher for optima performance.
Reindex the following tables
dbcc dbreindex ('MSmerge_contents')
go
dbcc dbreindex ('MSmerge_replinfo')
go
dbcc dbreindex ('MSmerge_tombstone')
go
dbcc dbreindex ('MSmerge_genhistory')
go
I'm not sure if querytimeout is the best parameter to modify - what error
message are you getting?
Hilary Cotter
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>
|||This is my error:
: Exception:
Error : 80004005
Err.: 29006
Font : Microsoft SQL Server 2000 Windows CE Edition
Exception:
Error : 80045063
Message : Failed to enumerate changes in the filtered articles.
Err.: 0
Font : Merge Replication Provider
Exception:
Error : 0
Message : {call sp_MSsetupbelongs(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}
Err.: 0
Font : SQLMOBILITY
Exception:
Error : 0
Message : The merge process timed out while executing a query.
Reconfigure the QueryTimeout parameter and retry the operation.
Err.: 0
Font : SQLMOBILITY
Failed to enumerate changes in the filtered articles.
{call sp_MSsetupbelongs(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}
The merge process timed out while executing a query. Reconfigure the
QueryTimeout parameter and retry the operation.
Thanks hilary
<hilary.cotter@.gmail.com> escribi en el mensaje
news:ubBGpUDRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Merge replication is supported in a clustered environment if replication
> support is installed when you build the cluster. By default (IIRC) it is
> not.
> Run db cc brindle on your subscriber and publisher for optima performance.
> Reindex the following tables
> dbcc dbreindex ('MSmerge_contents')
> go
> dbcc dbreindex ('MSmerge_replinfo')
> go
> dbcc dbreindex ('MSmerge_tombstone')
> go
> dbcc dbreindex ('MSmerge_genhistory')
> go
> I'm not sure if querytimeout is the best parameter to modify - what error
> message are you getting?
> --
> Hilary Cotter
> 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>
|||How can I active the support for merge replication in a clustered
environment?
What is IIRC?
I 've always been working with a non clustered environment.
Thanks.
<hilary.cotter@.gmail.com> escribi en el mensaje
news:ubBGpUDRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Merge replication is supported in a clustered environment if replication
> support is installed when you build the cluster. By default (IIRC) it is
> not.
> Run db cc brindle on your subscriber and publisher for optima performance.
> Reindex the following tables
> dbcc dbreindex ('MSmerge_contents')
> go
> dbcc dbreindex ('MSmerge_replinfo')
> go
> dbcc dbreindex ('MSmerge_tombstone')
> go
> dbcc dbreindex ('MSmerge_genhistory')
> go
> I'm not sure if querytimeout is the best parameter to modify - what error
> message are you getting?
> --
> Hilary Cotter
> 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>

Merge replication and clustering

Hello:
Does merge replication support clustering?
I'm having some troubles about merge agent "querytimeout" parameter
throught a clustered system, and the hints that are over there about
"increasing timeout", "refreshing indices" and a lot more don't give any
result.
Anyone can help me?
Is anyway supported merge replication over a clustered system?
thanks a lot
Juan
Hi
Yes it does.
Have used some very high volume replication scenarios and they worked well.
The biggest note with Merge replication is that the GUID on the table is the
only value in the WHERE clause for when synchronization happens.
Make sure that field, on every replicated table, has and index on it (most
of the time, it was the PK).
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Juan" <juan@.tata.net> wrote in message
news:%23mHTPnARFHA.3708@.TK2MSFTNGP15.phx.gbl...
> Hello:
> Does merge replication support clustering?
> I'm having some troubles about merge agent "querytimeout" parameter
> throught a clustered system, and the hints that are over there about
> "increasing timeout", "refreshing indices" and a lot more don't give any
> result.
> Anyone can help me?
> Is anyway supported merge replication over a clustered system?
> thanks a lot
> Juan
>
|||Hi
I forgot to add, check for blocking when the rep agent is running. It other
active processes are blocking it due to locks held, it can't do it's work
either. Long running transactions and replication are not friends.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e4n5DCGRFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> Yes it does.
> Have used some very high volume replication scenarios and they worked
> well.
> The biggest note with Merge replication is that the GUID on the table is
> the only value in the WHERE clause for when synchronization happens.
> Make sure that field, on every replicated table, has and index on it (most
> of the time, it was the PK).
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Juan" <juan@.tata.net> wrote in message
> news:%23mHTPnARFHA.3708@.TK2MSFTNGP15.phx.gbl...
>

Merge Replication Agents

Hi everybody,
I have a system of 2 PC, with installed Win XP and SQL 2000. I
configured one PC as Publisher and Distrbutor, and the other as a
Subscriber with a "push" subscription. The snapshot and the merge
agents are running on the Publisher/Distributor. This PC is always ON
for 24 hours everyday, while the subscriber PC at the evening is
turned off and restarted at the morning.
Looking the Replication monitor at the morning, I see red cross on
both agents (snapshot and merge) and the synchronization doesnt' seem
working; if I restart the agents a message tells that the agent is
already running and the red flags disappear.
Anyone can help me to undertand better the situation, or give me some
suggestion?
Thanks a lot.
Marco
What error messages exist for the agents when there is a red cross - can you
please post it up. I'd expect that the merge agent would be killed if it is
synchronising during the process of rebooting the subscriber, but if there is
a schedule or it is set to run continously then it should restart ok. The
snapshot agent shouldn't be running at all after initialization. So, perhaps
the initialization never worked, and the merge agent failed because the
snapshot agent hadn't run? Anyway, please post up some more info and things
whould get clearer.
Paul Ibison
|||On 19 Giu, 17:57, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> What error messages exist for the agents when there is a red cross - can you
> please post it up. I'd expect that the merge agent would be killed if it is
> synchronising during the process of rebooting the subscriber, but if there is
> a schedule or it is set to run continously then it should restart ok. The
> snapshot agent shouldn't be running at all after initialization. So, perhaps
> the initialization never worked, and the merge agent failed because the
> snapshot agent hadn't run? Anyway, please post up some more info and things
> whould get clearer.
> Paul Ibison
Hi Paul,
I don't know exactly what kind of error there is, because I dont' have
the PC with me; this problem has been explained me by another
technician that is testing the system. But I know that the merge agent
is setted to run continously, and that the synchronization was working
when I setted the replication process; only I tested it with the 2 PC
both running and I didn't test the condition with 1 PC running and the
other PC turned off.
|||OK - there's not much I can do without the error message. Try to get it off
your colleague and post it up and I'll take a look.
Cheers,
Paul Ibison