hi,
after reading docs, i always thought that when using merge replication, only at specific moments the whole thing was merged.
now when doing first tests, it seems that everything is merged immediately to my subscribers. is this normal?
I have an issue that is only occurring in a production
environment. The architecture is filtered merge replication between two SQL
Server 2000 SP4 databases. The publisher is standard edition and the subscriber
is personal edition.
The issue is that is I update certain rows on the subscriber the data is not
replicated to the publisher. Inserts and deletes seem to replicate correctly.
The issue is specific to certain rows in some tables. If I update other rows
the updates replicate correctly. The subscription has been re-initialized once
and it did not fix the issue. The replication process indicates that there was
not data to merge and the subscriber updates remain and are at that point
different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and
checking the publisher database. The synchronization process is executed using
the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an
earlier synch on this particular subscriber. We have other subscribing
databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue.
If anyone has any ideas or has seen this type of issue before I would appreciate
any direction to try to fix this issue.
Thanks
I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.
On initialisation of the subscription, the user should only get the data that is relevant to their code.
The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.
My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.
Is my understadning incorrect?
Is the some parameter in the creation of the publication that we have set incorrectly?
Is there something wrong with SQLServer 2005 itself?
I am very interested to hear anyones comments or advice around this issue.
Thanks
Steve
We have a SQLServer 2005 Enterprise merge replication publication with SQL Mobile 3.0 subscribers (Windows Mobile 5.0 and 6.0). We do not use pre-computed partitions due to trigger performance issues with an SSIS/ETL application that supplies data to the merge database. We do use the "Optimize" (=true) option, though we have tried this both ways with no significant differences. We use filters and joins for each worker ID (as HOST_ID) from the subscriptions.
The sync times become increasingly worse after we run the snapshot and bring the publication online. I have tried rerunning the snapshots, this helps little, as it often behaves like the subscription was set to reinitialize and forces a big sync (reload of all data) to the subscriber. We have tried much of the obvious (e.g., flattening filters and joins, adding indexes, etc.).
When users are synchronizing, we watch replication monitor and notice that a lot of time is spent processing "enumerating inserts and updates for article [any article]", especially processing the many generations and batches. This is true for any follow-up syncs after the 1st big sync (initializing the subscription).
I read several posts regarding the batches and generations of changes, and decided to try increasing the €śDownloadGenerationsPerBatch€?. I tried adding this parameter to the snapshot agent job, and the job fails each time with a vague message, even with the default value of 100. How do you change this parameter for SQLServer 2005 Enterprise?
Any suggestions?
Thanks in advance,
Matt
Hello,
I have 2 tables one is filtered and the other not. I am trying to do merge between them and everthing appears to work fine and I reacht he end where it says 'Applied the snapshot and merged 1 data change' but nothing really is changed or updated on either end.
Hello,
I have 2 tables one is filtered and the other not. I am trying to do merge between them and everthing appears to work fine and I reacht he end where it says 'Applied the snapshot and merged 1 data change' but nothing really is changed or updated on either end.
Hi all,,
I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..
May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..
I have a flat file that I want to merge with table in SQL server 2005.
1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).
Now here is my question.
I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).
I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..
Here is an example:
Table A
--------------
1 test test1 test2 test3 test4 test5
2 test test6 test7 test8 test9 test10
Table X
------------
1 test test1 test2 test99 test4 test5
2 test test98 test97 test 96 test95 test94
--------------------------------------------------------
Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..
The new Table A should look like
NEW Table A'
-----------------
test test1 test2 test3 test4 test5
test test6 test7 test8 test9 test10
test test98 test97 test 96 test95 test94
------------------------------------
I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?
Thanks in advance.
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedHi,
Has anyone had any problems on one row updates on a table where you have defined horizontal and vertical partitioning of the data to be replicated?
When I execute an update clause that modifies just one row the log reader misses the modification and it does not get replicated to the other databases.
If I do the same update clause but on several rows then all the modifications are read by the log reader and the replication task goes ok.
What might be wrong?
-janne
Guys,
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
Any suggestions and inputs would help
Thanks
We have transactional replication publications from SQL 2014 to SQL 20122008 R2. When we add new tables to the existing publication and start the snapshot agent, Snapshot agent updates the statistics for random tables on the publisher DB. It appears that all the outdated statistics are being updated regardless which table is being initiated. In a highly transactional environment we can't update the statistics every time. its causing huge issues for us. Is there any work around for this ?Â
View 8 Replies View RelatedHi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
Thanks
I have a primary and secondary servers both running Windows 2000 SP3 with SQL 2000 SP3. I have set up transactional replication with the primary server as publisher and the secondary server has the distributor and subscriber DB. I am testing the scenerio where my primary server goes down and I have to make updates to the secondary server until my primary server comes back up. I am able to update my subscriber database and the transactions go into the MSreplication_queue table to be pushed back to the primary when it comes back up. When I bring the primary server back up and start the queue agent job it starts pushing the transactions over and then stops after 4 or 5 transactions with the error "Failed while applying queued message to publisher". I have attached part of the log file for the agent below
dbserver2.Old_Processing: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
dbserver2.Old_Processing: {? = call dbo.sp_replsqlqgetrows (N'DBSERVER', N'Old_Processing', N'Old_Processing')}
[4/15/2004 3:59:47 PM]dbserver2.distribution: exec dbo.sp_helpdistpublisher @publisher = N'DBSERVER'
Connecting to DBSERVER 'DBSERVER.Old_Processing'
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 21)}
SQL Command : <exec [dbo].[sp_MSsync_ins_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 15)}
SQL Command : <exec [dbo].[sp_MSsync_ins_NightlyProcess_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', '2004-04-15 15:56:44.623000000', 'Begin ProcessIQ2KSystem', 'AB14E5D7-C81D-4A39-A8F5-51F1C48227B0', '17E5D98F-EDF0-41D0-9991-97511B850720', 1>
SQL Command : <exec [dbo].[sp_MSsync_upd_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 7)}
SQL Command : <exec [dbo].[sp_MSsync_upd_TheatreProcess_1] N'dbserver2', N'Old_Processing', '072175', 1, '2004-03-19 00:00:00.000', 1, NULL, '79A114D6-FF31-4E37-AC2D-90C0A0114F40', '072175', 1, '2004-03-19 00:00:00.000', 0, NULL, 'AF61A098-44A3-45D7-B25B-E8EA9CD464A1', 0x2800, 1>
Failed while applying queued message to publisher
Disconnecting from DBSERVER 'DBSERVER'
Worker Thread 692 : Task Failed
Disconnecting from dbserver2 'dbserver2'
Processed 3 queued trans, 3 cmds, 0 conflicts
Queue Reader aborting
In the sql server logs I am getting this message:
Replication-Replication Transaction Queue Reader Subsystem: agent Repl Queue Reader failed. Failed while applying queued message to publisher.
Error: 14151, Severity: 18, State: 1
Any help would be greatly appreciated
We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?
View 3 Replies View RelatedI am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
Hi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
Thanks in advance
Hi,
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database.
I added filelds many times but this time i got errors.
Please help me anybody.
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
Thank you in advance for any help!
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher.
"Transaction cannot start while in firehose mode"
What does this mean.
Thank You,
John
Hi,
I read some where that replication has two types conflict resolution, 1. row based and 2. Column based...
If I am right...
Can any one point me how to find out this option and how to set it up....
Thanks,
Mohammed.
Hi all,
I have a merge replication going between 4 servers. The problem is when ever I do some BCP transfer to one of the tables in one of the servers. It puts the data in that table. But that Data does not get replicated to any other server like it should.
Please Advice on what to do. Is there any option I am forgetting to set or something.
Thank you for all your time in advance.
Aziz
I have successfully tried merge replication on single server with 2 databases.
now i want to do the same with different servers,
when i create pull subcription on server 2 which user account should I use?
it is giving log in failure
i tried using windows admin account and also the 'sa' account.
please help me out
thanks
Hi All,
I have posted this earlier and I am re-posting it simplifying what I had said.
The scenario is:
I have two sql server database instances with the same database schema and all. However, both of them have different data. I have not set them for replication at all. Now, I want to do merge replication between them such that the data between them could be syncronized.
When I do pull merge subscription I have two choices -
1.Bringing schema and data to subscriber from publisher
2.Not bringing the schema and data from publisher to subscriber.
Obviously, I chose the second choice. But upon syncronizing I dont see any data from publisher coming to subscriber and vice-versa. If I add new data to publisher and do syncronization, I can see ONLY the new data created after the replication setup in subscriber. If I add new data to subscriber and do syncronization then the new data is removed from subscriber and not propagated to publisher.
Is there any way I can make this work??
Niben
Hi,
I have just set up Merge replication, I have two servers, server A and server B, the merge replication worked successfully but I don't quite sure which databases should or should not replicated? If not, what other methods should I use?
I would really appreciated any comments or advice out there!
-whitebelt
I had set up merge replication. I got these error messages where replications starts "Column names in each table must be unique. Column name 'PubID' in table 'bonflict_DBName_PHP_Data_Publications' is specified more than once "
PHP_Data_Publications table defind as:
CREATE TABLE [dbo].[PHP_Data_Publications] (
[PHP_Pub_ID] [uniqueidentifier] NOT NULL ,
[PHP_Data_ID] [uniqueidentifier] NOT NULL ,
[PubID] [uniqueidentifier] NOT NULL ,
[UserID] [uniqueidentifier] NOT NULL ,
[Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pub_Year] [datetime] NULL ,
[Pub_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PHP_Data_Publications] WITH NOCHECK ADD
CONSTRAINT [DF__PHP_Data___rowgu__01E91FA0] DEFAULT (newid()) FOR [rowguid]
GO
CREATE UNIQUE INDEX [index_1466488303] ON [dbo].[PHP_Data_Publications]([rowguid]) ON [PRIMARY]
GO
Thanks,
Chanthol
Hi
Is it possible to modify or add new fields in sql 2000 when the instance
became publisher ? I tried it several times but it failed. I access microsoft website but i did not help.
Thanks
Pheckz
Hi everbody,
Anybody tell me about implementation of oneway merge replication.
Thanks
Hi everyone
SQL Server BOL says merge replication can be done in only one direction. But my understanding is Merge replication happens in both ways between publisher and subscriber.
How can i allow data movement from only wince sql ce subscribers and not from publisher running sql 2000 and win 2k.
Please reply immediately bcoz i require a solution which has to be implemented very soon.
Thanks in advance
Satheesh
hi,
in merge replication,i make a request subscriber with a priority 25.00,when i update the same date,it always choose the update from the publishing server,why????how does it work???
thx inadvance
I am using merge replication at remote connected via ISDN Dialup line. I got following error and replication fail.
publisher - PRSTGINDSQLIND
agent - PRSTGINDSQLIND-pml-pml-192.168.100.50SQLDWS-4
publication - pml
subsctription - 192.168.100.50SQLDWS:pml
error - The process could not deliver the snapshot to the Subscriber.
Agent Merge replication provider -2147201001
Agent 192.168.100.50sqldws 20037
ODBC 192.168.100.50sqldws
Agent - PRSTGINDSQLIND-pmst-pmst-192.168.100.50SQLDWS-3
error - The subscription to publication 'pmst' is invalid.
last command {call sp_MSgetreplicainfo(?,?,?,?,?,?,?)}
Thanking You
R.Mall
I am in a process of learning Replication in MSDE, especially Merge Replication
Server runs on MS-XP Professional
--------------------------------------
I have a sample Access project 'ReplTest' which has only table with only 2 columns.
DatabaseName:ReplTestDB
Table Name :TestTable
MSDE Instance Name:SVRMYINSTANCE
Now I would like to know how I can configure this database for merge replication
using SQL-DMO
Laptop runs on MS-XP Professional
--------------------------------------
I have another access project which is running on computer 2 and connected to the
ReplicaTest database.
MSDE Instance Name:LPTMYINSTANCE
My task is, when I am disconnected from server I would like to have a local copy of
the database to work with and then, when reconnected, need synchronization with the
server database and continue working from server database.
How to write this replication process from scratch using SQL-DMO objects in both Server computer
and Laptop computer.
I dont have enterprise manager in both computers since they use only MSDE
Can anyone help me?
Thanks in advance
JP
Hi,
I have a problem when doing merge replication. I need to have a identity column on both the publisher and subscriber, eg id. When the publisher is down, my app will now reference to the subscriber and insert into the subscriber. However when the publisher is up, the app will reference to publisher and start to insert into the publisher. This will cause conflict in the id. Also, i need the id to be in running order, therefore i cant use range for publisher and subscriber. Anyone have idea how to solve this problem?
Aaron
:(