Merge Replication Questions
Oct 27, 2005
1. When intitializing a new subscriber, data that is on the publisher is not being transferred to the new subscriber. Why?
2. Data that is already on the subscriber is not being uploaded to the publisher. Why?
3. When I perform a data validation, the validation fails, but there is no option to resolve the failure (ie, transfer data one way or the other). Why?
4. For the conflict resolver: I have a rowguid and a timestamp column on each article in the publication. It was my hope that by having the timestamp, I could avoid the need to manually reconcile the conflicts between publisher and subscriber. However, I see that the conflicts are still there and still require manual intervention to eliminate. Why?
5. Where is there additional documentation on the conflict resolver (such as what values to enter in the field "Enter information needed by the resolver")?
6. What is a "Local Subscriber"? As in the statement "Use the default merge resolver and create local Subscribers." as described in the "Choosing a Resolver" topic in SQL BOL.
7. What is a "Global Subscriber"? Same reference.
Sorry for all the ignorance. Replication is relatively new to me.
Regards,
hmscott
View 1 Replies
ADVERTISEMENT
Jun 1, 2006
I can choose synchronization direction for articles: a) Bidirectional b) one way
1) Is that possible somehow to replicate the schema only of an article but no synchronization / zero direction :-)/
2) Same question about columns, I should replicate schema only for few columns, but without data synch. These columns are freely updateable at anywhere (publisher and subscribers), but the data changes shouldn't be replicated.
Thanks for the answers in advance
View 6 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
Mar 26, 2001
I am reading up on Replication because soon I must set up merge replication at two branch offices. The reading has confused me a little....
1. Do I have to run the Distribution database on another server than the one holding the headquarters database? I tried to configure replication on this server, and I get a message that says, "SQL Server Agent on ROCK currently uses a system account, which causes replication between servers to fail. In the following dialog, specify a domain account for the service startup account."
It then presents a dialog allowing me to add an account and password. I mistakenly assumed that it would create the account if it didn't exist, but it warned me that it didn't have sufficient rights to check the account, and was I sure that it existed with all the rights required? So I cancelled, to send this message :-)
When it says "Account", I assume it means create a virtual user? What rights should I give the account? I assume it will have to be able to do anything. What database should I give it as the default? Could I have the first agent not use a system account, and thereby run the distribution server on the same machine? (I have another machine available, I just want to know if I need it, or at what point of activity I would need it.) Will it matter whether the agent on this third machine usese a system account?
2. Virtually all the tables in the database use incrementing keys. Will I have to modify this at each branch, say seeding Branch 1 to start with values of 1, Branch 2 with values of 10000, and branch 3 with values of 100000 (or other sufficiently disparate values to prevent PK collisions?
3. How frequently can I merge the databases? Can it be done at different intervals with different tables? (I.e., there are a small number of tables in which updated values would be ideally propagated as soon as possible, while most of the tables are less critical. We sell tickets, so quantity-available is pretty important, whereas someone in one office hardly cares who purchased the tickets on a sale recorded in another office. This data we can wait for, but the counts are critical.)
Advice, etc. from those experienced in implementing replication would be most appreciated.
Thanks!
View 2 Replies
View Related
Mar 20, 2001
We have a subscription that is failing due to '24000[Microsoft][ODBC SQL Server Driver]Invalid cursor state'
My thought was to unsubscribe and subscribe again synching the tables.
HOWEVER, I have been told that I cannot drop the table in question
and have been asked to truncate instead.
There are over 25 subscriptions to this article, so I am unable to
modify the actual article.
I started playing in the pubs database to see if I can do it before
I messed with production...
I thought if I just change the .sch script located in the repldata direcotry to truncate table instead of create table, that would be it...
However the drop table command seems to come before it even reaches
that script, cause the error I get now is 'S0002[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot truncate table 'authors', because this table does not exist in database 'pubs'.'
My question is - when and where does the "drop table" command get executed
and can I modify it without effecting the other subscriptions?
I checked the sysarticles and the column that says del_cmd is Null....
PLEASE HELP....
View 1 Replies
View Related
Mar 31, 2006
Hi,
I'm having a headache with the following questions weavering around...Hope that someone can help me with the answers.
1) I've implemented a immediate updating transactional replication on 2 servers. The loopback detection is also set to true. Under normal circumstances, when there is an transaction at the subscriber, it should be replicated to the publisher. However, this was not the case here. There were some transaction in the subscriber, but they were not replicated to the publisher. Is it because of the "loopback detection" option?
2) If a bi-directional transaction is to be implemented as according to the BOL, there will be 2 distributors. When a publisher has a transaction, it will be replicated to the subscriber. When the subscriber commit the transaction, will the transaction be sent back to the publisher's distribution database? or will the distribution agent at the subscriber will know that it originated from the publisher and will not sent it to the publisher?
Pls pardon my long msg. Any advice is welcome.
View 3 Replies
View Related
Dec 15, 1998
1) Do I have to install publishing on both servers (A and B) even though one will be publisher and the other will be subscriber.
View 1 Replies
View Related
Dec 15, 1998
1) Do I have to install publishing on both servers (A and B) even
though one will be publisher and the other will be subscriber.
2)a. Can named pipes be used for communication between these two servers
which are on the same domain but not on the same network. Why or why
not, whatever the answer may be?
b. If I use TCP/IP, it the connection set up using the client
configuration utility? How is the connection string set up in this
case?
c. Suppose the publishing server was not using Net-Beui. Could this pose
any problems for communication. (is using lmhosts sufficient in this
scenario)
3) I have set up a (remote) SQL Server to be a Publisher/ Distributor.
Both SQL servers have been configured to be remote servers relative to
each other. Following are the steps I have carried out to set up
replication:
______On the Publication Server (a remote server)
I went to Server --> Replication Configuration ---> Install Publishing
Next, I chose a local distribution server. I think that the
instdis.sql script ran fine because the distribution database was
installed successfully.
___Next, I went into Manage Publications from Server menu to set up
the publications.
_____________When I went the subscription server to subscribe to the
published articles, I got the following error message:
Error 14093: [SQL Server] You must be System Administrator (SA)
or Database Owner (dbo) or Replication Subscriber (repl_subscriber)
to execute the stored procedure.
PS
Please Help
View 1 Replies
View Related
Feb 16, 2007
Hello, I am playing around with the replication feature within mssql.
The system that I am developing requires a master database at a remote location, possibly at a data centre and have a onsite database. The idea of having a local database is that the system can be running in the event of no internet connection and all the local databases update the master server and the master server updating all the local databases so they all have the same data.
I have some questions.
1. I create a publication on the master server and setup a subscription on a local database; does information only go 1 way? Or do I need to setup a publication on the local database and setup a subscription on the master server for data to go both ways?
2. What is the best type of replication for information on all databases to be up-to-date with each other, Merge?
3. Is there any limitations with the replication feature that I need to be aware of?
4. Is there anything that I need to keep in mind when I set this up?
5. How much bandwidth does replication take up, I know there are a lot of factors involved when trying to calculate this type of thing but a good idea would be good.
Thank you for all your replies.
*edit* I am testing with 2 copies of mssql enterprise trial
View 2 Replies
View Related
Oct 21, 2006
I have a couple questions about replication (for both 2000 and 2005 servers):
1. which system tables/dmvs/system sprocs can I look at to determine which columns of a table are being replicated?
2. which system tables/dmvs/sprocs can I call to get metadata about publishers and subscribers?
Thanks!
View 2 Replies
View Related
Mar 10, 2008
This customer has an SFA application. They are using NET CF 2.0 SP2, SQL Mobile and Merge Replication with SQL Server 2005. The device they are using are Symbol MC7094. It has integrated phone and is a Windows Mobile 5 Aku 3.
They have set up 5 differents publications for this applications. His business case makes them to have 1 publication to recreate all database structure and the first population of the tables.
For this they use AddSubscription .. Syncronize and the DropSubscription
They use the another 4 publications to sync particular tables because they don€™t want to sync everything, every time. All publications point to the same snapshot.
Each time they want to use one of this publication they instance the SqlceReplication object, then AddSuscription.. Synchronize .. then DropSubscription..
They have one of this publications that use a filter 1=0. Doing the previous steps SQLCE doesn€™t track the change and doesn€™t upload the data of the sql ce table to the server. It seems SQL Server recognize it as a new Sincronization, delete the records of the client and doesn€™t upload the changes (if I don€™t do the DropSubscription it works perfect) I can reproduce that using a device or an emulator.
They have 100 devices via GPRS - VPN. They need to be assure that this Add / DropSubscription will assure they don€™t lose the information. They want to assure all the process of sync is fine just to go to the carrier and make some GPRS connection monitor and test.
How is the best way to approach where the NET CF application needs to use more than one publication to the same database? I have suggested to put all the transactional tables in the same publication but due the business case it is not possible. What are the risks to use many publications?
Another question€¦ each X hours they do a full sync using the first subscription the application returns the next message: The snapshot for this publication has become obsolete. Why this happens? Due the changes in the another publications? How could we manage that to avoid this message? Note: All the test were doing via cradle and GPRS with the same results.
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
Sep 5, 2005
Our database has grown to the point where our current server is struggeling with the query load. One option is to get a 4 processor machine with 16GB of RAM, but I'm also looking at transactional replication as a solution. Currently we run dual Xeon with 4GB of ram (using the /3GB switch in the OS) We have SQL 2000 Enterprise.
The idea is to setup a secondary server with transactional replication pushed from the main server, so that some SELECT-only queries can be executed on the secondary server - thus taking load of the main one. We should be able to add PKs to the small number of tables that currently don't have them, and we should be able to run all updates / inserts on the main server.I'll setup a push-subscription for the entire DB (maybe excluding some log tables) and then for ceratain stored procedures I'll direct our applications to use the backup instead of the main server.
So: Is this a good idea? Is it easy to backup the server using transactional replication? How much extra overhead will this mean for the main server?
View 1 Replies
View Related
Aug 28, 2007
All,
We have SQL 2005 db mirror configured with a witness server for high availability. Node 1 is the principal and Node 2 is the mirror. A nightly job creates a snapshot on Node 2. The snapshot is used for previous day reporting queries. We have now been asked to present another copy of the database for near-time reporting. I thought about possibly adding a peer-to-peer replication as part of my environment but was hoping to see what everyone else out there is doing.
Regards,
Ian
View 1 Replies
View Related
Jul 17, 2007
Hi,
I am seeing an unusual pop up when I try to hit the Website directory. I have setup replication setup for mobile units on IIS 6.0. When I try to hit the - http://defaultwebsite/test/sqlcesa30.dll - it tells me to open, save for cancel the sqlcesa30.dll file. This is weird. I have not seen this before.
When I hit the path from a internet browser - http://defaultwebsite/test/sqlcesa30.dll - it should come back with something like SQL Mobile Agent 3.0.
Any thoughts,
P
View 1 Replies
View Related
Mar 22, 2006
You will all have to excuse my ignorance. I'm a developer who also doubles up as a development DBA. I am however not particularly knowedgeable about all the really important DBA stuff.
We've built a small BI solution using SQL Server 2000. Our problem is that our server is getting on in years (5) and doesn't really have enough disk space or grunt. We havce a number of summary cubes that we've optimised quite successfully but our billing line level cubes run to 60 million rows and, well, they're about as quick as a dead ferret. Especially given the stupid queries our data analysts keep running.
We have however proved our point. That this can be done and indeed SQL Server can do it. So we're now looking at some infrastructure spend and some new copies of SQL2005.
But i need some advice. Our user base is climbing through the roof, we originally had 10, now we have closer to 50 and at this rate it'll be a couple of hundred by the end of the year. We're using a plugin called XLCubed to deliver that data into Excel from the Analysis Server.
The OLTP database that sits behind it is fairly robust but we have a number of web based apps (mostly lookup systems) that want to use the nice shiny new accurate tables of data we have created.
So I'm looking at a fairly big server to hold the OLTP DB, this will also serve up live data to our web apps. Its worth pointing out that the source data system is a batch system that processes overnight so we load data from yesterday at 6pm each evening and process our cubes and stuff overnight. Thus the data is a couple of days out of date. Don't laugh they used to use MS Access and got one mangy data set a month so this is a massive leap forward.
I wanted to mirror the DB to another machine but I also want to have a separate Cube Server. I wondered if the cube server could use the mirror to read its data from as opposed to loading the Main Server (the mirror would be an identical box) we would also have a separate box running some of our other systems acting as the witness.
I also wonderd about exporting the Cubes onto file shares for use locally as opposed to via the server which is how they connect now.
We have been using Reporting Services and some of the queries the devs write are not exactly efficient. So I was also planning on clustering a pair of smaller servers into a reporting farm. Could I use another SQL Server to serve data up to them? Could I use a DB snapshot to copy the data required to this server? What are the time / size implications of using a snapshot and replicating it over each night?
Any suggestions for places to read up on this? I've looked at the MS marketing blurb and while its big on buzzwords its light on specifics. Like how it actually works and how you would actually configure it to do some of this and what the implications would be.
Any advice?
many thanks
Steve
View 4 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
