Merge Repliction - Run Stored Procedure when merge agent starts

I have database on SQL Server 2000 set up with a merge publication.
This publication is configured with a number of dynamic filters to
reduce the amount of data sent to each client. Each client has an
anonymous pull subscription. The merge process can be triggered by the
windows sync manager and my application.

To improve performance I have created some helper tables to hold the
mapping between user login and primary keys of selected entities.

For the replicated data to be correct the contents of the helper tables
needs to be up to date.

I need to fire off a stored procedure on the publisher before
replication starts to verify that this data is up to date. I can not
see any documented way of doing this however I have been experimenting
with some unorthodox systems.

Firstly has anyone any ideas?

I have been considering adding a trigger to some of the tables used by
the Microsoft replication code - yes I know this is very nasty.

My problems arise because executing this stored procedure will cause
some data to be updated. In updating data we could create a new
generation in the database. I must therefore run my stored procedure
before any the Microsoft code makes any generation checks / updates.

Anyone done anything similar, Anyone have any better ideas?

Any comments would be gratefully received.(tedd_n_alex@.yahoo.com) writes:
> My problems arise because executing this stored procedure will cause
> some data to be updated. In updating data we could create a new
> generation in the database. I must therefore run my stored procedure
> before any the Microsoft code makes any generation checks / updates.
> Anyone done anything similar, Anyone have any better ideas?

Not being very good at replication, I can only recommend you to visit
microsoft.public.sqlserver.replication. There are some guys that knows
replicataion really well there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Merge Replication problems

Greetings all...

I am trying to patch up a merge replication situation, and I have
1. configured Distributor & publisher
2. created a publication
3. created a subscription.

The subscription (on subscriber server) status=sucdeeded; Details=The initial snapshot for publication is not yet available.

When I go to snapshot agent (on publisher), it is saying: Status=Failed
Detail = The process could not bulk copy out of table 'contB22652175F9A4388BC64D1B43C8E8B30'.

Thanks!
Jester99This will sound crazy, but the first place I'd check is disk space availability, replication can use a lot of space, in particular when creating the initial snapshot.

How big is the database that the publication is based on ? how much of the total db will the publication contain ?

Merge Replication is very slow

Hi
We have configured merge replication in SQL 2005 (SP 1) for a database
having more than 100 GB. The merge agent duration was for every 5 mins.
Normally it used to take around 2 to 3 mins to complete. It was working fine
till now but now it is taking around 45 minutes. When checked and viewed
details in the replication monitor the below message was displayed for a long
time.
The process is waiting for a response from the query '{call
sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,0, '39B06489-B2C7-4FA4-B109-8609CD6C18FF')}'
Any help in this regard is appreciated
Thanks.
Are you using join Filters? Also you might want to drop your retention
period so that metadata is purged more frequently.
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
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:A96295C7-71C5-4764-9093-6737DED1E033@.microsoft.com...
> Hi
> We have configured merge replication in SQL 2005 (SP 1) for a database
> having more than 100 GB. The merge agent duration was for every 5 mins.
> Normally it used to take around 2 to 3 mins to complete. It was working
> fine
> till now but now it is taking around 45 minutes. When checked and viewed
> details in the replication monitor the below message was displayed for a
> long
> time.
> The process is waiting for a response from the query '{call
> sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,0, '39B06489-B2C7-4FA4-B109-8609CD6C18FF')}'
> Any help in this regard is appreciated
> Thanks.
|||In addition to Hilary's suggestions, look at if you really need all the data
at the subscribers. Does all data change at both places, if not utilize the
downloadonly articles. And when using filters, see if you can use any of the
filtering enhancements in SQL Server 2005.
Also was there a lot of activity on the database recently that is causing
this wait?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ugqNAvvEHHA.3820@.TK2MSFTNGP02.phx.gbl...
> Are you using join Filters? Also you might want to drop your retention
> period so that metadata is purged more frequently.
> --
> 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
>
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:A96295C7-71C5-4764-9093-6737DED1E033@.microsoft.com...
>
|||Thanks for the reply
I don't have any join Filters.
The retention period was 14 days and now changed to 7 days. Removed some
records from MSmerge_contents &
MSmerge_genhistory.
Now there is some improvement. But still it is taking more than it used to.
Data changes are there at both (subscriber & publisher) ends. It is a 24/7
high activity database.
Checked memory & bandwidth , CPU, etc., all seems to be normal.
Thanks,
"Mahesh [MSFT]" wrote:

> In addition to Hilary's suggestions, look at if you really need all the data
> at the subscribers. Does all data change at both places, if not utilize the
> downloadonly articles. And when using filters, see if you can use any of the
> filtering enhancements in SQL Server 2005.
> Also was there a lot of activity on the database recently that is causing
> this wait?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ugqNAvvEHHA.3820@.TK2MSFTNGP02.phx.gbl...
>
>
|||If it is a highly active database, then it will be due to metadata growth.
If you can be sure that the subscriber will be synchronizing often, then you
can further reduce the retention. However remember that if the subscriber
fails to synchronize within the retention, it will expire and will need
reinitialization.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:9456F61D-74FD-45AF-BE89-8BFFEFD571DF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply
> I don't have any join Filters.
> The retention period was 14 days and now changed to 7 days. Removed some
> records from MSmerge_contents &
> MSmerge_genhistory.
> Now there is some improvement. But still it is taking more than it used
> to.
> Data changes are there at both (subscriber & publisher) ends. It is a 24/7
> high activity database.
> Checked memory & bandwidth , CPU, etc., all seems to be normal.
> Thanks,
>
> "Mahesh [MSFT]" wrote:

merge replication error

good day everyone. this error appear in my merge replication monitor, all my
configured database replication did not work and when i check the error log
it says:
could not find the index entry for rid
'169f87801f5a4f8724ffd146921f2447f9e23a66200' in index page (1:316844), index
id 0, database 'Database_name'
what is the possible fix on this one?anyone help mo on this please.
Pheter FloresHi Mics,
I think you better send this message to
"microsoft.public.sqlserver.replication" newsgroup.
--
Ekrem Ã?nsoy
"MICS" <MICS@.discussions.microsoft.com> wrote in message
news:509ACE87-8F03-43B5-9A56-2FCD583DFA49@.microsoft.com...
> good day everyone. this error appear in my merge replication monitor, all
> my
> configured database replication did not work and when i check the error
> log
> it says:
> could not find the index entry for rid
> '169f87801f5a4f8724ffd146921f2447f9e23a66200' in index page (1:316844),
> index
> id 0, database 'Database_name'
> what is the possible fix on this one?anyone help mo on this please.
>
> Pheter Flores

Merge Replication Could not connect

Hi!
I have problem with Merge Replication between two SqlServers connected via
internet.
After i configured publication and subscriber, when i trying to synhronize
i'm obtaining The process could not access to the Subscriber"
thanks in advance

pozdrawiam
Jakub Gluszkiewicz
k.gluszkiewicz@.citysoftware.com.pl
www.citysoftware.com.pl
tel. (22) 869-51-76
869-51-77
kom. 503-680-864
Are you using FTP? You should be using FTP. Also can the subscriber ping the
publisher and vice versa.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kuba" <k.gluszkiewicz@.citysoftware.com.pl> wrote in message
news:OrnZerPjEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have problem with Merge Replication between two SqlServers connected via
> internet.
> After i configured publication and subscriber, when i trying to synhronize
> i'm obtaining The process could not access to the Subscriber"
> thanks in advance
>
> --
> pozdrawiam
> Jakub Gluszkiewicz
> k.gluszkiewicz@.citysoftware.com.pl
> www.citysoftware.com.pl
> tel. (22) 869-51-76
> 869-51-77
> kom. 503-680-864
>
>
|||Hi Hilary!
thanks for your response. I already solved this problem, i needed to add
aliases in SqlServer,
thanks again
pozdrawiam
Jakub Gluszkiewicz
k.gluszkiewicz@.citysoftware.com.pl
www.citysoftware.com.pl
tel. (22) 869-51-76
869-51-77
kom. 503-680-864
Uytkownik "Hilary Cotter" <hilary.cotter@.gmail.com> napisa w wiadomoci
news:uStcj8RjEHA.396@.TK2MSFTNGP12.phx.gbl...
> Are you using FTP? You should be using FTP. Also can the subscriber ping
the[vbcol=seagreen]
> publisher and vice versa.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kuba" <k.gluszkiewicz@.citysoftware.com.pl> wrote in message
> news:OrnZerPjEHA.1656@.TK2MSFTNGP09.phx.gbl...
via[vbcol=seagreen]
synhronize
>

Merge replication and Publisher Identity range

Hi,
I have configured a merge replicaiton on sql 2000.
I have also set up auto identity range management on one of my articles.
The publisher identity range is set to 600, and the subscriber identity range is set to 100 and the threshold is %80.My subscribers are mobile users connecting with pocket pc.
I have no problem with the pocket pcs, when they sync sql server automatically adjusts the identity range for them if they have used more than %80. However I have problems with managing identity range on the publisher. When I directly insert into the table on the publisher, if I use more than 80% of my keys, it won't adjust my identity range and it will give an error. I have to manually run the system stored procedure :"sp_adjustpublisheridentityrange" to adjust the range.

Question:
How can I automate this process? I read something about LogReader agent but I don't know how to start it?

Is there any way to handle this problem on the server side? Or do I have to run the stored procedure in my application?BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs Continuously

http://support.microsoft.com/default.aspx?scid=kb;en-us;304706&Product=sql2k

Merge replication 2005 - 2005 Express

Hi.
I want to replicate SQL 2005 - SQL 2005 Express. MS SQL 2005 (DevEdition
beta 2) is configured as Publisher/Distributor. I want ti configure SQL 2005
Express (beta 2)as Subscriber (pull-subscription).
For this purposes used T-SQL command (at Subscriber)
sp_addmergepullsubscription (after it runs in subscriber database appears
some system tables for "replication managment") Next step is to run (at
Subscriber)sp_addmergepullsubscription_agent procedure. I use it in this way:
use [DB_FOR_REPLICATION]
EXEC sp_addmergepullsubscription_agent
@.name = ...,
@.publisher = ...,
@.publisher_db = ...,
@.publication = ...,
@.publisher_security_mode=0,
@.publisher_login = ...,
@.publisher_password = ...,
@.distributor_security_mode=0,
@.distributor = ...,
@.distributor_login = ...,
@.distributor_password = ...,
@.job_login = ...,
@.job_password = ...,
@.use_interactive_resolver='false',
@.enabled_for_syncmgr='true',
@.frequency_type=4,
@.frequency_subday=4,
@.frequency_subday_interval=1,
@.active_start_date= 20050101;
GO
As I understand its meant that: SQL server authentication used for both
Publisher/Distributor(login/pswrd i supplied), replication is sheduled to run
every minute since 2005 jan 01 and _subscription can be synchronized through
Windows Synchronization Manager_.
Questions:
1. Does Windows Synchronization Manager keep password i provided in SP
above? (every time i'm trying to run it asks me to enter 'em again).
2. Where i can find schedule mentioned in SP (every minute since...)?
3. What happens when @.enabled_for_syncmgr='false'? I cant syncronize DBs at
all. No agent tables in SubscriptionDB and no schedule in MSBD.
Thnx for answers
Best Regards Maxim Morzhov
Does anyone know?
Best regards
Morzhov M.
|||Since SQL Server 2005 Express Edition doesn't include SQL Agent, the
scheduling parameters will be ingored. After you create the subscription, you
must setup a schedule in Windows Synchronization Manager to run the agent on
a schedule.
If you don't enable Windows Syncronization Manager, you can still
synchronize the subscription by running the Merge Agent directly (C:\Program
Files\Microsoft SQL Server\90\COM\replmerge.exe) as shown in the SQL Server
2005 BOL topic "
How to: Synchronize a Pull Subscription (Replication Programming)." Or you
can write an application that uses RMO to synchronize the subscription.
Hope that helps,
Glenn Gailey [MS]
This posting is provided "AS IS" with no warranties, and confers no rights.
"MaxX_RUS" wrote:

> Hi.
> I want to replicate SQL 2005 - SQL 2005 Express. MS SQL 2005 (DevEdition
> beta 2) is configured as Publisher/Distributor. I want ti configure SQL 2005
> Express (beta 2)as Subscriber (pull-subscription).
> For this purposes used T-SQL command (at Subscriber)
> sp_addmergepullsubscription (after it runs in subscriber database appears
> some system tables for "replication managment") Next step is to run (at
> Subscriber)sp_addmergepullsubscription_agent procedure. I use it in this way:
> use [DB_FOR_REPLICATION]
> EXEC sp_addmergepullsubscription_agent
> @.name = ...,
> @.publisher = ...,
> @.publisher_db = ...,
> @.publication = ...,
> @.publisher_security_mode=0,
> @.publisher_login = ...,
> @.publisher_password = ...,
> @.distributor_security_mode=0,
> @.distributor = ...,
> @.distributor_login = ...,
> @.distributor_password = ...,
> @.job_login = ...,
> @.job_password = ...,
> @.use_interactive_resolver='false',
> @.enabled_for_syncmgr='true',
> @.frequency_type=4,
> @.frequency_subday=4,
> @.frequency_subday_interval=1,
> @.active_start_date= 20050101;
> GO
> As I understand its meant that: SQL server authentication used for both
> Publisher/Distributor(login/pswrd i supplied), replication is sheduled to run
> every minute since 2005 jan 01 and _subscription can be synchronized through
> Windows Synchronization Manager_.
> Questions:
> 1. Does Windows Synchronization Manager keep password i provided in SP
> above? (every time i'm trying to run it asks me to enter 'em again).
> 2. Where i can find schedule mentioned in SP (every minute since...)?
> 3. What happens when @.enabled_for_syncmgr='false'? I cant syncronize DBs at
> all. No agent tables in SubscriptionDB and no schedule in MSBD.
> Thnx for answers
> Best Regards Maxim Morzhov