We have a problem which involves multiple Windows CE subscribers (SQL CE 3.5) replicating with SQL Server 2005 in merge replication. The subscription has one table.
When a subscriber changes data, it gets published to the server, as expected. When data changes on the server, it gets published to all subscribers, as expected. However, the changes from one subscriber
does not get downloaded to other subscribers. It seems that the server gets the updates, but the other subscriptions seem not to be "notified" of the new changes made by each subscriber.
So these updates get "stuck" at the publisher after being uploaded.
If a new subscriber comes in and replicates the entire table (the first time), then all updates are properly downloaded. If the subscription is re-initialized, then all rows are downloaded correctly. However we cannot do this because it will downgrade the
synchronization performance too much to download the entire table for each synchronization.
Another detail to note is that if we move the database to another database database server instance and create the publication again, the synchronization works properly. Thus it does not seem to be an issue with the subscribers or the database schema, but
rather the configuration of the server itself.
What could be the reason? Where is a good place to start looking to resolve the issue?
3 Answers Found
i dnt think problem would be with server configuration or replication configuration settings...as u able to setup on other server(assuming you running scripts from ur main server)... looks like problem with replication metadata(throwing out idea)
tht merge agent not recongnizing changes ....
I do not quite understand here. Which type of "metadata" are you referring to?
Clearly, the publisher database server does not publish the changes to subscribers if they come to other subscribers.
Is it possible that some trigger is not working?
Same Problem, if we re-create any subscription and generating new Partitioned Snapshot, it downloads the Partitioned Snapshot, and everything is OK. But the old subscription sometimes (not always) cannot download changes from the Publisher. I think sometimes,
Replication Triggers do not fire correctly? Is there anyone to help us in this issue?
I've read a couple of posts around this same topic area but can't figure out how to get it to work. Many others seem to be stuck too...someone at microsoft should write up a step by step walkthrough for a couple of the common configurations. I found quite
a few good walkthroughs for transactional replication on expert user's blogs but nada for merge replication.
The publisher is sql server 2008 R2 std and on another server is the subscriber which is sql server 2008 express. I created the publication assuming pull subcriptions. I see the various files in the unc folder.
On the subscriber side, the subscription is created and set to use windows sync manager. At first I used the impersonate credentials, and the default snapshot location but that failed with and error like
"The schema script 'notestatus_2.sch' could not be propagated to the subscriber."
notestatus is the name of one table in the db. After reading around re the probably permissions error this is about, I made a couple changes. First, I shared the folder which contains the snapshot files on the publisher to everyone. Second, I used sql server
sa logins instead of windows auth. But the error is exactly the same.
I wish, probably everyone wishes, that after spending umpteen hours writing such wonderful features such as the various replication systems, that microsoft could spend a little bit of time putting together documents which are usable by end users. Having
fifty scattered msdn articles which in total comprise a huge matrix of options is just a nightmware to figure out. When you write help files or user documents, try them out on some new users and see if they find the material lucid, or oblique.Read more...
How to add new subscriber to existed Merge replication in SQL server 2008?
we have a SQL server 2005 x64 Enterprise DB in the backend and we are developing a windows app which uses SQL Compact as "offline" database, so that the users can work on a copy of the central database. Merge replication is configured using a web server
When synchronizing with 1st subscriber, all changes made in the backend are replicated to the subscriber and all changes from the subscriber are uploaded to the publishing DB - all fine.
Now, when a second subscriber syncs his local database, his changes are uploaded successfully, all changes made centrally at the publishing DB are also downloaded, but the changes / inserts made by the first subscriber are not downloaded.
After we ran the snapshot agent on the central database and tell the 2nd subscriber to sync with re-initialize, he also gets the changes made from subscriber 1 then.
Accodring to Mr. Ralph Kemperdick from SQL server team Germany it should work and sync fine without running the snapshot agent all the time.
Any help on this is appreciated.
I have 2 clustered instances running on SQL Server 2008 SE-64 patch level 10.0.2531.0. These is one DB on these 2 instances (compatibility_level=80)under merge replication. now I need to change one trigger to add "NOT FOR REPLICATION". One publisher site
all is ok but on subscriber site it causes Error 102 Severity 15 State 1 Incorrect Syntax near 'dbo'.
After tracing the error in profiler, I captured the incorrect syntax as below:
exec('ALTER TRIGGER [dbo].[trgBusinessEntityAllocationUpdate] on [dbo].[BusinessEntityAllocation]
NOT FOR REPLICATION
SET NOCOUNT ON
obviously, there is an duplicated part of object name. but the script was generated by replication engine. How could it happened? can anyone help?
George Read more...
I have attemped to change column types (i.e. from int to varchar(8)) on read-only published tables. I used ALTER TABLE ALTER COLUMN to make the change, and according to BOL this should make sure the changes are seen at the subscribers. Unfortunately, after I make the change to table and run sp_enumeratependingschemachanges, I do not see the table changes listed. However I can make changes to SPs and UDFs, and they do show up in sp_enumeratependingschemachanges and on the subscriber. Any ideas would be appreciated.
The publication is marked for replicating schema changes, the publisher is SQL 2005 and the subscribers is SQL 2005 Express btw.
Darrell YoungRead more...
I have a replication topology which the publisher is also the distributor, this is a common configuration afters speaking with most DBA's. I now have the following errors after adding new subscribers to the replication topology.
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher,
and Distributor are still active.
Any ideas here ?Read more...
We have about 50 subscribers, but in my replication monitor I show almost 6000 subscriptions. For 3 months I've been noting this trend and make no sense of it. What happens is that a subscriber will reinitialize automatically and I'm not sure why. They are
all in distant locations and I never hear reports of problems in the user application.
Usually, after less than two days a subscriber will throw the error, 'either the cursor is not on a row or there are no rows left...250001' and then a new subscription is begun on the next attempt to synchronize.
I can pin it down to a manual call for synchronization, We have our own service doing sync in the background every 2 hrs to be sure the data is moving. But the application also has a button to manually trigger a sync.
Only difference is that in the manual procedure we have set PostSyncCleanup to 2.Read more...
Hi all, we use Merge Replication as described bellow:
Publisher: SQL Server 2005 Standard Edt with SP3 (on Win Server 2003)
Subscribers: SQL Express 2005 with SP3 (Win XP), pull subscriptions, synchronizing
through .NET application (using RMO objects)
Further description: we use both download-only articles and articles with parametrized
filters, Publisher acts as its own Distributor
From time to time, update conflicts occur in table where product stock status is stored for sales representatives. Error message states that the same row was updated both at subscriber and at
However, in fact, the row is updated only at the subscriber (through application, when sales rep sells the product). There is no real update of this row at the publishing server. What can cause
this situation? Maybe some kind of dummy update triggered by replication? If so, why?
Strange is also, that this conflict occured so far at just 3 subscribers (out of 100).
No custom conflict resolver is set for this table, so the resolution is based on publisher priority - the row version from publisher winns and subscriber looses its stock update (subscriber gets
last status of this row tracked at the publisher).
Any help would be greatly appreciated.
I have been working on merge replication. The set up is up but now I am facing some security concerns which clearly puts databse at risk at subscriber side.
First I would give brief set up info.
Publisher and Distributer: SQL SERVER 2008 Standard Edition I will say machine A
Subscriber: SQL EXPRESS EDITION I will say machine B
SubscriptionType: Pull (merge agent runs at subscriber)
Since this machine would be used by workers and away from office location.
Actually it can be in hands of everyone so database at subscriber becomes vulnerable.
The workers log in to subscriber machine using DomainName/Userid having limited priviledges.
My questions is regarding safety of my db which should not be taken away by any means what so everRead more...
1. DomainName/Userid uses front end and communicates with db.
Note: here my application code would authenticate for db connection.
Apart from point 1 their should not be any means of reaching to db or copying db.
Potential accessing means for databases can be.
1. Through Management studio.
2. Through command prompt.
Note: I got some views from Hilary Cotter and YoYo Yu about that. Thankyou for directing me but still I am not sure of safety.
3. Copying database somehow.
Note: please tell also if their are still some others ways of accessing/copying db from above 3 points which I havenot thought of.
Please tell how generally databse is kept secured at subscriber machine.
I have a Merge Replication installation going on with about 35 connected devices. When one of the subscribers synchronizes, do the inserted rows appear sporadically on the publication database at different times? Or do they appear all at once?
Can all of these be isolated inside a transaction - this would seem like it should be the default behaviour, in case of a sync being cancelled or failing due to loss of connectivity etc.
The reason for this question is that another part of the system seems to have queried the publication database during and after a subscriber synchronization with differing results.
If this is normal, what would be the best solution to ensure data integrity?
Thank you in advance.
I hope you can help me with an issue I'm encountering with SQL merge replication. After a subscriber syncs a few number of times, all the tables in the subscriber are wiped out of data. What could be the causes of this? Thank you very much.Read more...
What are the criteria to estimate how many subscribers my current Publisher/Distibutor server can manage? How to plan for server upgrade based on increasing number of subscribers?
Publisher/Distributor: SQL Server 2008 R2, 64 bit
Subscriber: SQL Server 2008 Express, filtered subscriptions onlyRead more...
I need to create a ghost server with merge replication with 4 pc subscribers(the server is also a distributor of the reply) and I also ghostie PCs that will act as underwriters.
My idea was to pre-create all in lab with everything already set, then:
Ghost server, ghost 4 pc subscribers.
There may be problems for management of this kind?
in case I need to replace a server or PC subscribers(example:broken disk) which type of "best practics" procedures i need to use for restoration?
I have already created scripts for the publication / distibution and subscribers(for maintance), but I noticed that the password of the distributor is not set correctly, how can I create a script with a password already entered?
've already entered in the script to publish the password for the user know,
but it seems are not enough for the distributor!
Thanks in advance,
I am using SQL Server 2005 Developer Edition for Creating & Testing Database.
I had created Merge Publication WITHOUT FILTERING ROWS and Created Push or Pull Subscription. Now if I Add or Drop column using ALTER TABLE (T-SQL) in Table OR modify stored procedure using ALTER PROCEDURE in
Published Database then while Synchronizing It replicate same in Subscriber Database and works very fine.
But when I had created Merge Publication WITH FILTERING ROWS (Parameterize or Static) and Created Push / Pull Subscription and I try to Add or Drop column
using ALTER TABLE (T-SQL) in Table OR modify stored procedure using ALTER PROCEDURE in Published Database then while Synchronizing It
DOESN'T REPLICATE in Table of Subscriber Database but
triesReplicate in Stored Procedure and display error.
After Adding Column in Table of Published Database, If I Checked Article in property of Publication then it Adds column but it doesn't
selects it. I also checked using sp_enumeratependingschemechenges it displays pending schema changes of stored procedure but doesn't display table.
Please Help me.
Sanjay ShahRead more...
I have a Database in production. It's size is 120 GB. This database is on a sever with Window server 2003 , 8 CPU's, 8 GB RAM ( SQL Sercer, a local disk of 800 GB. This is only database on the Primary server. But it's log and data files are on
C drive. This database is very busy database of the Application. Couple of it's table gets around 14 millions records in one month and other tables get more than 500,000 records each month.
Now I have a requirement to replicate this database to New Server. I have to decide for hardware specifications (subscriber) for the replication process.
Please suggest me what factors should i consider to decide the hardware for the new server.
Thanks Read more...
In my prod envirment we are going to configure Replication before that i need to clarify some doubts.
1.After configuring the replication due to some problem we are going to restore the publication DB (with full bkp) post restoration what kind of operation need to perform for maintaing the replication again.
2.After configuring the replication due to some problem we are going to restore the distribution DB (with full bkp) post restoration what kind of operation need to perform for maintaing the replication again.
3.After configuring the replication due to some problem we are going to restore the subscription DB (with full bkp) post restoration what kind of operation need to perform for maintaing the replication again.
4.In transaction replication can we run the no_log\truncate_only command in publisher and subscriber DB.
5.In transaction replication we have done some in changes in subscriber article object then next time how data will replicate from publisher to subscriber if table property has been changed in subscriber side.
6.If publisher DB size 300GB and subscriber DB size is also almost 250GB, then how much size we have to maintain for distributor DB . coz all the transaction will propagate to subscriber thru distributor ??
7.Post configuration of replication if we want to add one article in publisher then what procedure we have to follow??
please write the answers for baove questions that will be helpful for ME.
We are facing a lot of problem with replication between SQL Server 2008 R2 servers. Let me describe the scenario..
We have 20 branches and one main Server in our organization, all are running SQL Server 2008 R2. The Main server is a Windows 2008 R2 x64 bit computer from which we want to create both way transaction replication (Data inserted in to any branch will be reflected
to the main server and from there all other branches will get the same data) to all other branches. Branch machines are 32 bit XP sp3 or Windows 7. All the machines are under workgroup. Among 20 branches we could successfully establish both way replication
on 5 branches only. When we are trying to publish data from main server to say Branch A (with windows 7), it says "The process could not connect to Subscriber 'A'". We can ping to branch A and can login to Branch A from Main server
through Management Studio which proves that there is no connectivity issue. Branch A can publish data to Main server without any problem. Even Branch A can subscribe data from another branch Branch B.
I cannot understand why this problem is happening for other 15 branches to subscribe data from Main Server. I have some queries in mind
Is there any upper limit of subscription no? Is there any problem with windows 7?
Any help in this regard is very much appreciated.
Thanking you in advance.
I am setting up merge replication between SQL Server Express 2005/2008 subscriber and SQL Server 2008 Standard publisher/distributor.
I would like to use the Windows Sync Center to schedule the sync, however it is constantly asking for password to connect the publisher and distributor.
I must use SQL Server authentication because the subscriber will connect from the internet by a server task.
Is there a way to make it use the password saved in the MSsubscription_properties table automatically?
Adding a Windows task for the replmerg.exe agent is also fine with me, but unlike the agent of SQL Server 2K, with Express is also force me to enter passwords.
The following works just fine with MSDE, but with Express I must add publisher and distributer login and password, or it fails for ”Invalid authorization specification” error:
Replmerg.exe -Publisher [PublisherName] -PublisherDB [db] -Publication [db] -Subscriber [Server\msde] -SubscriberDB [db] -SubscriptionType 2 -SubscriberSecurityMode 1 -LoginTimeOut  -QueryTimeOut  -Distributor [DistServer]Read more...
I have a cluster server with SQL Server 2000 SP1 Enterprise. This server is distributor and publisher of 2 databases (about 40 subscribers with MSDE 2000 SP4).
I have to change the old server with a new one (Windows Server 2008 Enterprise + SQL Server 2008 Std).
What is the best way to migrate the replication considering that the applications running on subscribers use their local DB and they need to update quickly the data between other subscribers?
Many thanks for your help.
We are planning to implement Merge Replication between SQL Server 2008 "Not R2 Version" (Publisher) and SQL Server 2005 (Subscriber). Will this setup work, or there are some limitations ?Read more...