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 RelatedSQL 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
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.
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
Following 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?
I want to list out the pending transaction for transaction replication by publication.
Help needed.
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedFrom 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,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
Dear all,
I set up a transaction replication (from server A to server B) for tables and stored procedures. When I update the data at server A, the data at server B will be updated by replication. However, the stored procedure can't update at server B no matter what I changed at server A's stored procedure. How can I update the stored procedure using transaction replication?
The other question is the table schema. As I know, the transaction replication will be created a snapshot to project all needed tables and stored procedures at the first time. If I created a new table, stored procedure or changed the table schema (e.g. add/delete a new column), the transaction replication didn't update the new objects to the server B. What can I do to update the transaction replication except creating a new subscription?
Thanks.
epjames
Good Morning to all,
I am new to SQL Server.Here is my question. I have set up transactional replication with immediately updating subscriber between to servers A & B. A is publisher/Distributor and B is subscriber. In the event A fails the applications will be directed towards B. My problem is suppose A has faliled and my data is now written to B. How can i syncronise data in A and b when A is again online and what should i do ?
thanks in advance.
Minesh.
Hi all,
I am using SQL 2k on two seperate servers on win 2k advance servers without any service packs. My problem is i am using transactional replication between two servers (SML1 and SML2), SML1 is publisher/Distributor and SML2 is subscriber. My db name on both the server is same. when i am inserting data in db on SML1 it replicates immediately on SML2 but vice versa in not happening. Any help in setting up two way replication is highly appreciated. (There is no timestamp column on table which is used as article and there is PK).
thanks in advance.
Minesh.
I keep getting this error message even though I have droped and recreated
the replication several times. Has anybody had the same problem?
error message.
"Another snapshot agent for the publication is running"
Thanks for your time.
My Transaction Replication fail with this error message pls help.
This is the error message The process could not create file 'SQLMS1d$MSSQL7ReplDatauncSQLMS1_AFT_National_EMN _National - Transaction - Changes at Both Sides - EMN to Local Only20000607090006Benefit.sch'.
I it possible to setup transaction replication between two sql servers if both Sqlerveragents runs under different accounts and the servers are in different domains
Thanks in advance
Langel
I am attempting to set up transactional replication between a production server (SQL1) and a “reports” server (SQL2). The production server is the publisher, the “reports” server is the distributor and the first subscriber, other subscribers will be added later. There are multiple databases on the production server; selected tables from each will be replicated to corresponding databases on the other servers. Both are SQL7, no SP (yet).
I have set up the replication on two databases so far. The initial snapshot replication runs fine; the tables and data are duplicated on the reports server.
For one DB the transaction replication fail the other still reports “No replicated transactions are available”, which is probably true.
The failing log reader agent history reports: “The process could not execute ‘sp_replcmds’ on SQL1“ (five time, 10 minutes apart). The session details shows 2 items, “Initializing” and the same error message. The error details show it as an ODBC error timeout.
After the fifth time the log agent history reports “The process could not execute ‘sp_Msadd_repl_commands27hp’ on SQL2 ” The session details show 3 items , “Initializing”, “No replicated transactions are available” and the same error message. The error details show the following (Category, Source, Number, Message): Agent, blank, 2000, “IDistPut Interface has been shut down.”; Agent, SQL1-JCL1, 20007,” No replicated transactions are available.” ( 26 times); Data Source, SQL2, 1007 “Cannot insert duplicate key row in object 'MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'MSrepl_transactions' with unique index 'ucMSrepl_transactions'.”
I can restart the agent, but after two hours the same thing appears. These were set up with EM using the wizzards
Any suggestions would be greatly appreciated.
I had established a transaction log replication ,it's not immediate-updated.
on next 2 days,it's normal.but now,it's not available ,and haven't any warning,error,informatin.
I have very trouble about the 'log reader' is running but i change the data in articles it's no effect.
please tell how i do?
Hi,
Every one
Can I add coulmn in my existing table which i am using for replication. This article is i am using for transaction replication. I added column to both articles , publisher & subscriber, But now it is giving me error that, Insert error.
I checked in Create & Manage publications property on E.M , that cloumn is not checked & it is not allowing me to do from E.M , Does any one know
which store proc I require to execute from QA.
Appreciate Your Help
Thanks
Nirmal.
I am trying to setup real time transaction replication between two databases on two different sql server 7.0.The ideas is to replicate all the objects from the first database to the second database including table, stored procedures and views. Both SQL servers’ runs under different service NT account.
The sql server that I want to replicate runs an application that create additional tables when the application is running.
How do I schedule a transaction replication so that all the objects plus those additional tables and stored procedures that the application creates at run time are also going to get replicated in real time?
Thanks in advance.
JACK
Hi All,
I have been asked to take over the maintenance on dB servers that I have not originally 'set up'. The request came as a result of the database log files growing at such a rapid pace its taking up hard drive space. I ran sp_dboption and noticed that the log files are NOT set up to truncate on checkpoint. As in SQL 2000 I wanted to determine if the dB was set up in 'simple' or 'full' recovery mode. It appears they are set up in 'full' recovery mode (which we can modify, since this is not a transactional dB and we rarely if ever have to restore from the transaction log). Hence my question,
Is there anything I need to worry about (regarding replication)if I change the dboption to truncate on checkpoint and set dB in simple recovery mode?
And after I backup the transaction log and try to shrink it will that have any adverse affects? I feel like this is all I need to do but replication is new ground for me, any information regarding it would be greatly appreciated. Also, any advice on how to maintain the transaction logs would be helpful.
Christine
How terrible my life! save me!
Do you know the function of system store procedures for replication in publish database and distrubtion database? please tell me.
I'd established 'transaction log' replication, it's like something is full or can't active,i don't know what process is unvalid.
How i do!
Hi,
Every one
I have added new column in my Publication - Article Table ,
Same column I have added in my Subscriber Table ,
I am doing Transaction replication , but now its giving me error
Insert Error - Column Name or Supplied Value doesn't Match,
If any one has any idea please suggest me
Thanks
Nirmal
Hi all,
I am using SQL 2k on two seperate servers on win 2k advance servers without any service packs. My problem is i am using transactional replication between two servers (SML1 and SML2), SML1 is publisher/Distributor and SML2 is subscriber. My db name on both the server is same. when i am inserting data in db on SML1 it replicates immediately on SML2 but vice versa in not happening. Any help in setting up two way replication is highly appreciated. (There is no timestamp column on table which is used as article and there is PK).
thanks in advance.
Minesh.
Hi all, using transactional replication in SQL 05, I have a schema that is different at the subscriber and wanted to know the best approach for transforming the published data. For example, the subscriber is more normalized than the data being published causing 1 published record broken out into to many tables at the subscriber. Also need to applying scalar funtions to a few published columns before committing at subscriber.
I've read DTS, although backwards compatible, is being deprecated in a future release. I suppose I can use either SSIS or custom procedures. I didn't see any tie-in to SSIS in the system procedures to setup replication (only DTS). Any advice on which would work in this type of scenario?
Much appreciated, Pat
Hi!
View 1 Replies View RelatedThanks. I worry about my setup information may be have some issue.I can't express it in word.so please tell me you e-mail address,i will send a document to you.
Some information may be useful,i had did same thing with another database,it's normal in 1 day.
I need help to add new fields in Transaction replication & also want to know how it will be replicated to Subscriber.
View 1 Replies View Related:mad:
I have one Publisher! (SQL 2005)
Two Subscribers. (SQL 2005)
Each subscriber requires different row filter from the published tables.
So, i need to create Two publications for each subscriber for the same db.
When i setup first publication, and apply filters to published tables.
All goes fine.
As soon i create an other publication with same published tables but DIFFERENT row filter; the log reader agent fails!
The process could not execute 'sp_replcmds' on 'DB1'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Execution of filter stored procedure 2076702271 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764)
Get help: http://help/18764
The Log Reader Agent failed to construct a replicated command from log sequence number (LSN) {0008443a:000003b5:003e}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
:confused:
I have tried to re-initialze the subscriptions and re-run snapshots but no luck.
As i remove the row filter from the second publication; log reader starts working fine.
Previously i was using Merge replication and all was going BUT, on very large DML on publisher, we use to receive deadlock errors on application.
I decided to use Tran repl instead because log reader reads the changes from tran-log instead of locking the published tables; NOW I AM STUCK!
Ideas please!!!!
I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.
I need to configure a replication which will gather logs from the publication once in a day.