Merge Replication Weird Scenario?
Dec 15, 2005
On the national Server: SQL 2005 Enterprise
On the mobile clients: SQL 2005 Workgroup.
I was asked to find a solution to such scenario
2 mobile subscriber S1 and S2 to the same simple merge publication P1 on server N
Day 1,
S1 and S2 both synch up with N and both go off to do fieldwork
Day3,
S1 and S2 both synch up with N.
S1 goes back to work
S2 shutdown the laptop and goes on 2-week vacation.
2 Weeks late
S1 Synch up wit the server and goes off to do fieldwork.
S2 meets S1 in the field. Their workfield is in the North Pole.
S2 has the laptop with data 2-weeks old but no longer can have access to the master publisher N to synch the replica and get latest changes.
S2 will have to sync with S1 since S1 database is fresh. The challenge is to have S2 and S1 replica identical
The Questions:
Is it possible for S2 to sync with S1?
if yes, How to go about it€¦ we need S1 and S2 to have identical replica on their machines?
Now that S1 and S2 are have identical databases and are both doing their fieldwork in the northpole. Can they both sync back with the national publisher N when they have access?
Keep in mind that S2 got its data updated from the replica on S1?
Thank you!
View 3 Replies
ADVERTISEMENT
Jul 23, 2005
I have a product that sits on a main server and wish to implementfunctionality to allow salesmen to come along, pick up a snapshot of thedatabase, go away and maybe modify/add to it and then come back and"synchronise" their data. I'm reading up on Merge Replication for thispurpose. But anyway, I created a publisher on my server and it went awayand generated a "rowguid" column on all of my tables (my tables all have anIdentity column key field). Now of course my "Inserts" no longer work, asthey expect a GUID. I would have expected SQL Server to automaticallygenerate a guid for new inserts (in a similar way to it's TIMESTAMP), but itappears it doesn't, despite the fact I have "(newid())" as the default forthe column. It always inserts the same value:{00000000-0000-0000-0000-000000000000}.So, back to basics, now I have a guid field for each record, how do I manageinserts?Thanks.
View 3 Replies
View Related
May 21, 2007
I'm implementing merge replication between SQL CE and sql server 2005 SP2. Here is the problem I'm facing
I have dynamic filters on some tables like
Select * from table1 inner join table2
ON table1.field1 = table2.field3
and table2.flag = true
When table2 flag turns true then all the matching records in table1 should be returned to the subscriber.
But that is not happening. The filter does not return any records to the subscriber even though i see records when i run the query on the server. My take on this is the publisher thinks there is no changes on table1 so there is nothing to update. So to fix that i run dummy updates like
update table1
set [name] = [name]
where table1.field1 in
(Select field2 from table2 where flag=true)
This updates the records and the publisher updates the subscriber the first time. When i set the flag to false, it is supposed to remove the data from the subscriber but i get this error.
The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363)
Get help: http://help/MSSQL_REPL-2147199363
The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800)
Get help: http://help/21800
I have no idea whats going on. Any clues, anyone? Thanks for your help.
Ramesh
View 13 Replies
View Related
Mar 2, 2007
I've been setting up subscriptions to a merge publication for the past 3 days. All of a sudden, I'm getting a pile of very strange errors. Replication is configured. I have 16 subscribers to an existing publication configured and synchronizing changes without any issues. The script that I'm using to create all of the subscriptions is as follows:
use [PIC]
exec sp_addmergesubscription @publication = N'PIC', @subscriber = N'machinenameSQLEXPRESS',
@subscriber_db = N'MyDatabase', @subscription_type = N'Push', @sync_type = N'Automatic',
@subscriber_type = N'Global', @subscription_priority = 75, @description = N'', @use_interactive_resolver = N'False'
exec sp_addmergepushsubscription_agent @publication = N'PIC', @subscriber = N'machinenameSQLEXPRESS',
@subscriber_db = N'MyDatabase', @job_login = null, @job_password = null, @subscriber_security_mode = 1,
@publisher_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0,
@frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0
GO
The last one that I added #17, gives the following errors after successfully creating the subscription.
Command attempted:
{call sys.sp_MSmergesubscribedb ('true', 0) }
Error messages:
The merge process could not initialize the subscription. Ensure that the subscription registration exists at the publisher, and reregister the subscription if necessary. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201011)
Get help: http://help/MSSQL_REPL-2147201011
RegCreateKeyEx() returned error 5, 'Access is denied.' (Source: MSSQLServer, Error number: 22002)
Get help: http://help/22002
Could not add article resolver 'Microsoft SQL Server Additive Conflict Resolver' information to the registry (Source: MSSQLServer, Error number: 21713)
Get help: http://help/21713
Could not register article resolver: 'Microsoft SQL Server Additive Conflict Resolver'. (Source: MSSQLServer, Error number: 21715)
Get help: http://help/21715
The system tables for merge replication could not be created successfully. (Source: MSSQLServer, Error number: 20008)
Get help: http://help/20008
I've tried to manually create it using the GUI and get an even stranger error message as follows:
TITLE: New Subscription Wizard
------------------------------
Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21028&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I very obviously have replication installed by virtue of the fact that I have a publication and 16 subscriptions that are currently synchronizing. Any ideas?
View 3 Replies
View Related
Aug 3, 2006
Publisher is 2005 x64, subscribers SS2000 (SP3) and SS2005 x64. Pull agents, no filters on subscriptions. We are seeing many seemingly random conflicts on between SS2000 subscriber and publisher. It happens on several different tables.
One table is never editted, only inserts happening everywhere and deletes happening on the SS2000 subscriber. Deletes will sometimes generate conflict. Reason is '"he row was deleted at 'CTS11.CTS' but could not be deleted at 'cts4a.cts'. Unable to synchronize the row because the row was updated by a different process outside of replication." CTS11 is SS2000 subscriber, CTS4A is publisher.
Probably unrelated bug but when looking at conflicts on this same table in SS2005 conflict viewer, get error "ID is neither a DataColumn nor a DataRelation for table summary (System.dATA)" and then "Column ID does not belong to table summary (System.Data)". ID column is rowguid, only unusual thing about table is that it has varchar(8000) field plus some other fields.
Other tables generate conflicts with this reason "The row was updated at 'CTS11.CTS' but could not be updated at 'cts4a.cts'. The merge process was unable to synchronize the row." I enabled verbose logging in the merge agent but the log file didn't contain any further explanation.
This same topology and schema worked fine when all publishers and subscribers were SS2000.
Any insight into how to fix this would be appreciated.
View 9 Replies
View Related
Dec 17, 2006
Hi all,
I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.
Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.
Here is the source table and the other table's structure for reference:
DataLog
Name Datatype Size
DataLogStampID int 4
Value float 8
QuantityID smallint 2
DataLogStamp
ID (Which is foreign key to DataLogStampID field in the above table) int 4
SourceID smallint 2
TimestampSoruceLT datatime 8
What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.
I am thinking of developing a procedure which follows the following step using a temp table:
This procedure will run (say) every night.
The temp table will contain a timestamp field.
For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.
I will record this timestamp into a repl_status table which holds the timestamp of the last replication.
The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).
Any comments would be greatly appreciated.
Thanks for your time in advance,
Sinan Topuz
View 8 Replies
View Related
Aug 23, 2005
Greetings to fellow fleetfingers:
View 1 Replies
View Related
Jul 2, 2007
I have two sites. Site A and Site B
Each site has two databases
Site A
Db1
Db2
Site B
Db1
Db2
Site A Db1 has to perform transaction replication to Site A- Db2 and Site B- Db1 and Db2.
I started Site A as pubisher and distributor and Site A and Site B both as subscriber.
Site B is in a different geographical area (state).
----------
Please suggest the best scenario to save bandwidth and server load for Publisher, and Distributor.
-------
Earlier I thought that I will implement local replication between Site B - in between Db1 and Db2. The Sql Server does not let me set Db1 as publisher, and distributor for its local database Db2.
-------
P.S. My all databases need same transactions though they are connected to different hardware at different places. So please don't question that why I need four similar databases.
View 7 Replies
View Related
Jul 14, 2006
Dear all,
Since Windows Integrated Authentication does not work over proxy for Replication, could I still use SSL or SQL authentication over proxy? Thanks for any advice.
View 1 Replies
View Related
Dec 1, 2005
I have implemented replication between 2 servers (SERVER1 AND SERVER2) and for test purposes i just have 1 table(Table1) in the publisher.
View 1 Replies
View Related
Feb 19, 2004
Hello guys,
Have any ever seen this error before?
Steps I made:
1) Created Publications by script - using Manual Sync. (Transacat Repl)
2) Created Subscriptions by script
3) Started Distribution Agent
THEN THIS ERROR MSG:
Violation of PRIMARY KEY constraint 'PK__@snapshot_seqnos__24F8BF73'. Cannot insert duplicate key in object '#24049B3A'.
(Source: SYD242 (Data source); Error number: 2627)
---------------------------------------------------------------------------------------------------------------
****Lost, has any got any ideas?
View 2 Replies
View Related
Apr 17, 2006
before anyone even says it, i checked the collation order on everything and it's the same. i get the error when the snapshot is trying to be bulk copied to the subscriber.
i'm on sql2k sp4, server and db collations are SQL_Latin1_General_SP1_CI_AS. here's a repro. 1st, run this in a blank db:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Event_Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Event_Transactions]
GO
CREATE TABLE [dbo].[Event_Transactions] (
[EventTransactionId] [int] IDENTITY (1, 1) NOT NULL ,
[OrphanedFlag] [bit] NOT NULL ,
[ProcessedFlag] [bit] NOT NULL ,
[ProcessedTimeStamp] [datetime] NULL ,
[EventTimeStamp] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [EventTransactions_IDX_ProcessedOrphanedEventTimeSt amp] ON [Event_Transactions] (
[ProcessedFlag],
[OrphanedFlag],
[EventTimeStamp]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Event_Transactions] ADD
CONSTRAINT [PK_Event_Transactions] PRIMARY KEY NONCLUSTERED
(
[EventTransactionId]
) ON [PRIMARY]
GO
insert into Event_Transactions (
OrphanedFlag
,ProcessedFlag
,ProcessedTimeStamp
,EventTimeStamp
)
values (
1
,0
,NULL
,'2004-05-07 15:15:24.000'
)
insert into Event_Transactions (
OrphanedFlag
,ProcessedFlag
,ProcessedTimeStamp
,EventTimeStamp
)
values (
0
,1
,'2004-07-08 13:04:01.513'
,'2004-07-07 16:52:08.000'
)
Now, use transactional replication to replicate it to another db taking all the defaults. when the distribution agent tries to apply the snapshot, it fails with the message mentioned in the title..
Has anyone ever seen this? It's keeping us from considering MS replication for one of our major products. Thanks.
View 1 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Aug 30, 2007
Hi 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).
View 3 Replies
View Related
Oct 9, 2007
I 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...
View 5 Replies
View Related
Feb 14, 2002
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
View 1 Replies
View Related
Aug 12, 2002
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.
View 6 Replies
View Related
Jul 31, 2000
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!
View 1 Replies
View Related
Jan 22, 2001
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
View 1 Replies
View Related
Dec 19, 2000
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.
View 1 Replies
View Related
Sep 16, 1999
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
View 3 Replies
View Related
Sep 12, 2003
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
View 1 Replies
View Related
Feb 23, 2004
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
View 10 Replies
View Related
Jan 2, 2007
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
View 14 Replies
View Related
Mar 21, 2002
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
View 3 Replies
View Related
Oct 11, 2001
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
View 3 Replies
View Related
Oct 12, 2001
Hi everbody,
Anybody tell me about implementation of oneway merge replication.
Thanks
View 2 Replies
View Related
Feb 5, 2001
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
View 1 Replies
View Related
Jun 29, 2004
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
View 1 Replies
View Related
Oct 5, 2004
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
View 2 Replies
View Related
Jan 4, 2005
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
View 7 Replies
View Related
Jan 18, 2006
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
:(
View 2 Replies
View Related
Mar 7, 2006
Hello,
I am having a problem getting my merge replication to work out. I am wanting to make it where the merge happens on demand. I have tried to run replmerg through cmd prompt and I get the error 'The subscription to publication [namehere] has expired or does not exist.' I have had no problems running the merge agent within Enterprise Manager. I have thought of running windows sync manager but I do not want my people to have to type a password in everytime they need to sync. I am running the whole replication process through FTP. I am in desperate need of help. Anything would be great!
Thanks
View 4 Replies
View Related