Server_1 contains DB_A and DB_B. Server_2 contains DB_A and DB_B.
Can replication be setup where Server_1 DB_A (publisher) publishes to Server_2
DB_A (subscriber) and Server_2 DB_B (publisher) publishes to Server_1 DB_B (subscriber)? The environment is NT 4 (sp3) and SQL 6.5 (sp5a). Thanks in advance.
SQL Version 7.0
I have set up bi-directional replication between two databases on one table that has the structure of
Table Name : tblTrade
id : int (identity)
TicketNo : int (globally assigned via a sp - therefore always unique)
Location : int (On Server 1 defaults to 1) ---|PRIMARY
(On Server 2 defaults to 2) ---|KEY
.General Trade Fields...
Hence when a trade is entered into Server1 the Location defaults to 1 and is then replicated via an horizontal partition filter on the Location field. And similar for Server2.
Now Inserts work fine. However when a the following operations occur
DELETE - A DELETE against a record on Server1 results in ALL records being deleted from Server2
UPDATE - An UPDATE operation against a record on Server1 then ALL records get updated with the UPDATE SET clause.
The same happens with Updates on Server2 replicated to Server1.
Now it looks like the WHERE clause is missing from the replicated statements.
WHAT IS GOING ON, AND DOES ANYONE HAVE AN IDEA OF HOW TO GET AROUND THIS OR SET UP REPLICATION TO WORK PROPERLY
Regards
Steve
The docs state that a timestamp column is required for bi-directional
replication. I am not syncing the databases since they are too big and
it is a new project so I can guarantee at start that there will be two
exact copies of data. The data is being converted from a FoxPro db to
sqlserver.
The question is:
How do you design the timestamp columns for partioned bi-directional
replication? Does the publisher get the timestamp and the subscriber get
a binary(8)? Since there are two publishers is a distinct timestamp and
binary pair required for each server? See below...
Server: REP1 REP2
Table: tblTest tblTest
Columns: col1... col1...
tsREP1 timestamp tsREP1 binary(8)
tsREP2 binary(8) tsREP2 timestamp
Do the binary(8) columns need to be removed from the articles? Can
anyone explain how the timestamp is used to stop loop back? Is the loop
back check done on the Publisher or Subscriber?
Thanks,
Norman
Hi All
I am trying to set up bi-directional replication (between two domains) on a simple test table prior to installing it on my Live system. The table structure is along the lines of
id int (Primary Key)
name varchar(50)
location int
I use the Location field and the filter for the Publication. i.e. 1 = Server1 and 2 = Server2
Well I set the replication up on Server1 to Server2 and test the subscription and everything replicates perfectly. Note : When I set the Server2 subscription to Server1 I recreate the structure of the table on Server2
After performing a few replicated INSERTS into the table on Server1 and they have replicated through to Server2. I set Pub/Sub on Server2 to Server1. NOTE : on the setup of the subscription I select the option that the schema and data on the target table is correct.
As soon as I enter a line into the table on Server2 the replication monitor errors on Server2 with the message that the insert stored procedure for the replication cannot be found, indicating that they have not been created.
WHAT AM I DOING WRONG FOR THE SET UP OF BI-DIRECTIONAL REPLICATION??????
Thanks
Steve
Besides loopback detection, is there anything else that is needed to prevent the replication command from sending back to the source. Cos i am having problem with the subscriber B sending back the replication to A in the form of B as the publisher and A as the subscriber.
:confused:
Two Servers,1st 2nddatabase a -------> after replication a'b' <------- after replication bon 1st server a is replicated to 2ndon 2nd server b is replicated to 1stI tried with merge replication it is working fine.But if i tried to usetransactional replication it give some error like "accessviolation".Using tansactional replication is it possible.plz help me toslove this problem.Regards,Senthil prabu R
View 1 Replies View RelatedFollowing setup s1<--- p1<-->p2 ---->s2 (bidirectional replication between publishers as each one have its own subscriber)
What are the disadvantages of this solution if only one publisher gets written to at the time.
How about schema changes (would I need to stop all activity on p1 & p2 similar to p2p replication) ?
Would changes get republished to s1 & s2 ?
Are identities the only problem when instead on p1 as main server I start using p2 ?
Thank you.
Hi,
I am trying to setup bi-directional transactional replication between 2 SQL Servers,
I add the subscription on both the servers using the code below.
EXEC sp_addsubscription @publication = N'test',
@article = N'all', @subscriber = 'AnyServer',
@destination_db = N'test', @sync_type = N'none',
@status = N'active', @update_mode = N'read only',
@loopback_detection = 'true'
GO
I have defined 'AnyServer' on both the servers using cliconfg
(Server1's AnyServer pointed towards Server2 and Server2's AnyServer pointed towards Server1, I need to do that because there is a restriction to run the same code on both the servers),
After inserting a record on server1 in the 'test' database, the changes successfully transfers to the server2, then server2 sends it back to server1 and server1 generates the error of
"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'."
It seems as if loop detection is failing if I keep the same subscriber name on both sides.
It runs fine when I change the subscriber name in the subscription (@subscriber = 'Server2' for server1 and @subscriber = 'Server1' for server2).
Can anybody explains this behavior to me?
We recently installed another SQL Server so we could test performance of replication between
the two servers. After I set up the replication between the two I got the following error:
subscriber must be running in Per Seat (Named User) licensing mode to use this replication feature.
Is there any way around this? Or could you direct me to some documention pertaining to
this error?
Thank you
Hi:
We have a project that will consist of 3 production servers in 3 different
regions and each server has it`s own clients in that region. We want to use
replication to keep them all in synch so that if one goes down, the client
PC`s would just re-connect to one of the other two remaining servers and
keep working.
1. I am wondering what type of replication to use?
2. I was leaning towards merge, but was wondering how to use it to keep 3
servers in synch? If I use the Central publisher with updating subscribers
scenario, I foresee a potential problem as follows:
A. Server1 is central publisher
B. Server2 and Server3 are updating subscribers
C. Server2 (or Server3) goes down, clients reconnect to Server1 and
keep working.
D. When Server2 (or Server3) is back up, Server1 will push all
changes back to Server2.
but,
E. Server1 goes down, clients reconnect to either Server2 or
Server3 and keep working.
F. Since Server1 is down, changes made to Server2 not replicated
back to Server3 (The central publisher is gone) until Server1 comes back up.
G. Server2 goes down while Server1 is down, clients re-connect to
Server3.
H. The changes made to Server2 were not replicated to Server3
because the central publisher is missing.
I. Server3 therefore is missing data changes.
Please help me figure this out. I feel as if I am missing something that I
could do to also keep Server2 and Server3 in synch with each other besides
the Central Publisher.
Thanks in advance!
Jay Natarajan
suppose i am repliacting(Transactional Replication) to two servers using the same publications can i stop replicating to one server without disturbing the other server?
If i resume the replication to the server after two days what will happen to the two day's data?will they be replicated when i resume the replication?
Thank you
Can some one please give me a quick,brief description on the "Major diffrences" between these to features? Thank You, JJ
View 1 Replies View RelatedHi
I have two SQL Servers (6.5). One based in London (LDNINNTS9) and one in New York (NYINNTS2).
I had bidirectional replication set up on one table in a DB and everything is (WAS!!!) working fine. Came to work this morning (Monday typical!) and the following has happened.
When I select the remote servers option on the NY server from Enterprise manager I get the following error message
Unable to connect to site '0'because '' is not defined as a remote server.
Now it says that '' is not defined as a remote server. So I did a select of @@Servername and it has returned (null). It was set to NYINNTS2. I have tried executing some ISQL to change the name and started and stopped the SQL Services but the name remains (null). Does anyone have an idea of what I can do to set the name up as it was?
I know that the Server name gets set to null if it is invalid, but I am trying to set it to NYINNTS2, which should not be invalid? Does the domain name impact the validity of the name?
HELP!!!!
Regards
Steve Farmer
Hi All,
Just want to know if we can replicate data (Transactional Replication) from a clustered servers (server A and Server B are active-passive clustered running SqlServer 7) to another server C running SqlServer 7.
If yes, how to go about doing this. Any white papers, KB articles or books out there which will walk through the steps to do it.
Appriciate any help.
Thanks,
Sri
Anyone know what or how to calculate how much bandwidth I may use during replication. I have done this with large pipes with out a problem, just concerned I may need to change the interval to something else.
Thanks
JJ
Hello, Heros of The Programming World!
How it is possible to perform "replication" between two databases using technological means of MS SQL Server 2005 if two servers do not have a connection at all. The only thing is possible - is transfer of data for the replication on a CD.
What is the best way to do it if central database has a hundreds of such "subscribers"?
Hi There Gurus - Another Replication question for you all:
Can anyone point me in the right direction to get info on using Linked Servers in Snapshot Replication?
We have Replication all scripted & working between Registered servers, but one of our divisions is not allowing us to register their box on the Parent - ONLY a Linked server.
Thanks in Advance Everyone...
RobbieD
Hi! need your help.
I have 2 dedicated sql servers xx.xx.x.155 and xx.xx.x.165 (Hosted Environment)
Both SQL Server 2005.
I need to replicate (snapshot daily) data from 155 (Apollo1) to 165 (Apollo2).
and I have many questions as this is the 1st time that I work on this environment.
I created 3 windows accounts and was able to create the publications (Apollo1).
Apollo1ReplSnapAgent
Apollo1ReplDistAgent
Apollo1PublisherUser
However (the distributor) is not visible to Apollo2 so I am unable to subscribe.
Do you have any idea what I need to do to make it visible?
I setup an ftp site to drop the publication (as I was unable to create an UNC Share) and I was hoping to be able to subscribe to it.
Probably it is a matter of permissions...maybe a firewall? should I ask my provider to open a port? do I need to create the same 3 accounts in the Subscriber?
something like Apollo2SubscriberUser?
Please help!
I am lost!
From a service broker newbie...
Most of the examples I've found and played with demonstrate two way conversation. A sender initiates a call, and gets a message back.
My Requirements doesn't really need two way communication. I have a scenario where triggers on two different tables result in modifications to a third table, and I don't want the triggers to deadlock each other, so an asynchronous queueing mechanism seems like the perfect solution...
But I can't seem to make it work one way.
I can get one message through, and then all subsequent messages hang up in the transmission queue with the very informative "One or more messages could not be delivered to the local service targeted by this dialog."
I'm thinking all the examples work the way they do because you have to notify the transmitter that the message was
received by sending a message back... and by not doing this I'm stuck in the first conversation. I was thinking that by doing END CONVERSATION <Msg Handle> in the stored procedure bound to the receiver's queue was doing that.
Do I have to communicate bi-directionally always? I guess this is a safety feature but I trust MSMQ to deliver messages...
Thx
Hi,
I tried with success to replicate a table_test_repl from my local server to an other server.
I used only one table for testing.
After that, i can't drop the 2 tables a_test_repl (on my local server and the other : I've got a message 'cannot drop the table because it is published for replication'
What can I do? I need absolutely to drop these tables to keep a clean model
and I can't drop the database and try again.
Thanks to your answers
Axel
Hello,Being a bit of a SQL Server novice, need some advice with the followingsituation.Server A and Server B have SQLServer 2000 based databases. The vendorof the application/system has implemented their own replication processto ensure the 2 databases are in sync. However, there is no clusteringwith virtual IP addresses implemented. So to an external client/db, itis 2 identical databases with the same name on 2 distinct servers.We need to develop an application that will reside on a networkedserver C and with SQLServer 2000 as well. While most of the tables inthis database are self contained, around 10 tables will have to bemirror copies of the same tables from either Server A or Server B.Question, how do we implement subscription based replication on top ofa redundant database, when no clustering is implemented? So, inessence, when Server A is alive, the database on Server C willperiodically (or on change) replicate the 10 tables from Server A. WhenServer A is not alive, it needs to do same from server B. (When bothserver A and B are alive, it is acceptable to get data from either,since they are synchronized internally).Any alternate suggestions on achieving this functionality are welcometoo. If SQL Server 2005 has some capabilities that may address thisproblem, that is a consideration as well.Thanks
View 1 Replies View RelatedI am having trouble finding any documentation on how linked servers are set-up under SQL 2005 for log-shipping and replication. Under SQL 2000, the only linked servers "visible" were ones that were user-defined. Under SQL 2005, it seems when you set-up log-shipping or replication, you get these new linked servers which are automatically defined. I would like to understand them and in particular, how security is maintained. We recently had a production issue where after some problem with a server which resulted in having to restart SQL Server, and shrink msdb, log-shipping started to fail authentication when trying to run alert jobs. Only after rebooting the server, and also the log-ship target server, and waiting 9 hours, did the authentication correct itself. I would like to know how it resolved itself and what took so long?
View 3 Replies View RelatedThe setup: Two sql server 2005 SP1 clusters replicating with about 25 transactional publications and 10 merge publications. (Both clusters are being tested, nothing is in publication)
Currently, the servers are in the same building, which is prone to power outages. When a power outage occurs, how are the publications going to act once power is restored? Will we have to reinitialize the publications? This would be bad considering one of our publications takes around four hours to deliver a complete snapshot. What happens if one of the clusters where to go offline? When it comes back would it continue to replicate, or would the publication need to be reinitialized? Curious if anyone has any experience with this.
I've tried searching for any articles on this topic and have not been successful. If anyone knows of any, point me in that direction. Thanks!
-mike
Hi,Our company is an independent Voice applications solution provider withnumber clients using our suite. We have a CT application suite which isrunning with Application Server and SQL Server 7 / 2000 as DB Enginesat the back end.The SQL server has two databases configured:Logging Database - Massive updates every second, the data growsrapidly,Configuration Database - Generally small-sized and updatedoccasionally.Now we want to have the reslience implemented on the server. We have tosynchronize the two databases 'real-timely' and in 'efficient'manner, so that if Primary server or its Databases gets unavailable,the users are seamlessly switched over to the Secondry server that willhave its own set of data updated and well synchronized.Typically, it can be explained as follows:1. We will have 2 database servers A - Primary (acting as publisher)and B - Secondary (acting as subscriber). Our application will beinitially connected to A.2. When A becomes unavailable (for whatever reason), the applicationwill fail-over to B.3. All the users will be switched to server B and the updates are beingdone accordingly without being replicated on Server A temporarily.4. When A is back on-line, A needs to be brought up-to-date with Bautomatically (In other words, I shouldn't have to manually export allthe data from B to A ).Our requirements are:- The system should support Bi-directional Synchronizationbetween both the servers for their set of databases (the logging andconfiguration).- There will be constant and heavy activity in LoggingDatabase, thus if one server gets down the data should be logged andmaintained as it is on second server and on fail-back no data-lossshould occur with minimum latency time.- There could be a scenario when a server fails-over for aweek's time, there will be constant logging each second! Once itfails-back the system should rapidly synchronize the data withoutnoticeable delay among the two server database sets.- The system should also work fine if certain amount ofrecords are purged over a time period.Our concern is, observing the above scenario, how any of your SQLserver replication strategy can help us achieve the requirements.ThanksJohn
View 4 Replies View Related
Greetings,
My desire is to have my primary db in FL and an online hot standby in both my MI and CA offices. The hardware is in place, as well as T1 size pipes. The solution I'm looking for would have all databases online in case of hardware / environmental failure although the application would only be pointing at the primary (or designated) database server. We have another 3rd party solution available, but I'd like to stay native SQL if possible.
Is replication the way to go?
Does one-to-many work?
Can all the db's be online and available while replicating?
Can I catch up if switched to one of the other db's temporarily?
note:This is 100% SQL 2005.
Thanks -- sorry for the newbie-flavor of the question, but I'm sure a gazillion people have already been down this decision branch.
Dan Ribar
Hi, guys.A very simple question for all of you: how can I get the amount ofbytes exchanged during a Merge replication between two Microsoft SQL2000 servers?Thank you.Bye,Angelo.-
View 1 Replies View RelatedHello,
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
Thanks,
Phil
I have multiple web databases for storefront orders as linked servers on SQL Server 2008 R2. I need to organize the data for these orders into a structure that can be imported into my ERP application db frequently either on demand or periodically during the day. We are essentially trying to make the manual order entry process automated. My thought was to get the data into views that resemble the schema of the order table in my application db and then schedule a stored procedure with sql jobs or a load routine from within the ERP application that would insert data from the view into the order table.
View 2 Replies View RelatedI have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
Thanx,
Darren
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies View RelatedHi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
View 2 Replies View Related