Problem Pushing Subscriber Updates To Publisher In Transactional Replication
Apr 15, 2004
I have a primary and secondary servers both running Windows 2000 SP3 with SQL 2000 SP3. I have set up transactional replication with the primary server as publisher and the secondary server has the distributor and subscriber DB. I am testing the scenerio where my primary server goes down and I have to make updates to the secondary server until my primary server comes back up. I am able to update my subscriber database and the transactions go into the MSreplication_queue table to be pushed back to the primary when it comes back up. When I bring the primary server back up and start the queue agent job it starts pushing the transactions over and then stops after 4 or 5 transactions with the error "Failed while applying queued message to publisher". I have attached part of the log file for the agent below
dbserver2.Old_Processing: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
dbserver2.Old_Processing: {? = call dbo.sp_replsqlqgetrows (N'DBSERVER', N'Old_Processing', N'Old_Processing')}
[4/15/2004 3:59:47 PM]dbserver2.distribution: exec dbo.sp_helpdistpublisher @publisher = N'DBSERVER'
Connecting to DBSERVER 'DBSERVER.Old_Processing'
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 21)}
SQL Command : <exec [dbo].[sp_MSsync_ins_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 15)}
SQL Command : <exec [dbo].[sp_MSsync_ins_NightlyProcess_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', '2004-04-15 15:56:44.623000000', 'Begin ProcessIQ2KSystem', 'AB14E5D7-C81D-4A39-A8F5-51F1C48227B0', '17E5D98F-EDF0-41D0-9991-97511B850720', 1>
SQL Command : <exec [dbo].[sp_MSsync_upd_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 7)}
SQL Command : <exec [dbo].[sp_MSsync_upd_TheatreProcess_1] N'dbserver2', N'Old_Processing', '072175', 1, '2004-03-19 00:00:00.000', 1, NULL, '79A114D6-FF31-4E37-AC2D-90C0A0114F40', '072175', 1, '2004-03-19 00:00:00.000', 0, NULL, 'AF61A098-44A3-45D7-B25B-E8EA9CD464A1', 0x2800, 1>
Failed while applying queued message to publisher
Disconnecting from DBSERVER 'DBSERVER'
Worker Thread 692 : Task Failed
Disconnecting from dbserver2 'dbserver2'
Processed 3 queued trans, 3 cmds, 0 conflicts
Queue Reader aborting
In the sql server logs I am getting this message:
Replication-Replication Transaction Queue Reader Subsystem: agent Repl Queue Reader failed. Failed while applying queued message to publisher.
Error: 14151, Severity: 18, State: 1
Any help would be greatly appreciated
View 4 Replies
ADVERTISEMENT
Jul 23, 2005
I have taken over a transactional replication setup that is being usedfor fault tolerance (I know, I know...).The scenario I am concerned with is where the publisher goes down due tofailure, so we need to point our application at the subscriber and startupdating the data there. We are not using the immediate updating option.How do I go about re-syncing the publisher with the data that been addedto, or changed on the subscriber, when the publisher comes back online?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jun 26, 2007
Hello,
Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?
Thanks in advance,
John
View 3 Replies
View Related
Oct 26, 2007
Guys,
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
Any suggestions and inputs would help
Thanks
View 5 Replies
View Related
Sep 14, 2007
I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.
Thanks in advance
View 5 Replies
View Related
Sep 23, 2015
We've got x2 data centers in the US:
NYC & SAC
Sadly the hostnames for the boxes I am trying to replicate are similar:
- db1.nyc.mydomain.tld
- db1.sac.mydomain.tld
The servers can talk to each other over all necessary ports however when I generate a push subscription from the publisher, I am asked to add the SQL Server subscriber and it already shows DB1 listed since itself is DB1.nyc. How can I attach the remote subscriber when setting up the subscription from the publisher if the hostname conflicts in my unique scenario?I created a entry on the publishers host file to use db2sac for the SAC IP and entering that alias in as the remote subscriber but no dice.
View 4 Replies
View Related
Sep 25, 2006
Hi,
I was wondering if it is possible to setup replication between a 2005 SQL Publisher and a 2000 SQL Subscriber? Is there any special setup steps I need?
Thanks so much,
David
View 3 Replies
View Related
Mar 9, 2007
How to speedup data from subscriber to Publisher on Merge replication?
Is there any we can speed up data from Subscriber to Publisher (uploading).
Subscriber downloading data from publisher running more than 20 hours after drop and recreate subscriber.
Could you please post reply.
Regards
Ponnu
View 2 Replies
View Related
Jul 30, 2014
I have a setup of transaction replication between one publisher and subscriber in the Same server.Now, I need to add a new subscriber to the existing publisher. So publisher database name is DB_A and Subscriber 1 name is DB_B. So the new subscriber will be DB_C. Is this kind of setup possible on one server?
If yes then at the time of reinitialization is it going to apply the snapshot on DB_B as well as DB_C?Also let say if due to disk error DB_B gets corrupted then will data be still replicated between DB_A and DB_C? (Assuming publisher, subscriber 1 and 2 are sitting on individual disks).
View 1 Replies
View Related
Nov 6, 2015
For study effect, can I configure merge replication using just one SQL Server Instance?
View 3 Replies
View Related
Apr 1, 2015
I am using SQL 2012 SE and implementing transactional replication. I need to insert the rows from publisher database tables to new tables, drop the old tables and rename the new tables with the old table names.
For example:
Publisher database tables that are being replicated:
Table1
Table2
Table3
and I am going to create new tables in publisher database
Table1_new
Table2_new
Table3_new
Move data from
Table1--->Table1_new
Table2--->Table2_new
Table3--->Table3_new
Drop constraints from and then tables (does this require articles to be removed from replication?)
Table1
Table2
Table3
Rename
Table1_new to Table1
Table2_new to Table2
Table3_new to Table3
Does this require replication to set up from scratch or add the three articles only to replication? Is there a way this can be done without pausing or reinitializing replication or without removing articles and adding them back?
View 0 Replies
View Related
Jun 9, 2006
We have SQL Server 2000 with merge replication at a Publisher and subscriber.
We have some records getting deleted at Publisher and Subscriber and no conflicts are logged.
We have tried the compensate_for_errors setting and this has had no effect.
This is causing serious data corruption and has now become an URGENT issue. Out tech team are almost out of ideas.
Has anyone experienced this or have any ideas as to what to check next?
View 3 Replies
View Related
May 14, 2015
How to check if web merge sync is working between a subscriber and publisher thru HTTPS ? SQL port 1433 at subscriber is blocked so no direct connection to subscriber.
View 2 Replies
View Related
Oct 19, 2004
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?
View 2 Replies
View Related
Apr 30, 2008
Hi,
I am trying to setup mirrored subcriber in Transactional Replication in SQL 2005. I am not able to do so as it don't work after failover.However I am able to setup mirrored publisher with distribution property "PublisherFailoverPartner" for snapshot and log reader agent profiles and it works even after failover.
However I need to know, do we have something called "Subscriberfailoverproperty" or any other method to set up mirrored subscriber in Transactional Replication in SQL 2005.
Thanks
Kiran Patil
View 6 Replies
View Related
Jan 16, 2008
Hi,I am trying to set up a Updatable Subscriptions for TransactionalReplication with SQL 2005 (queued updating).I have a stored procedure "SYS_spShellTest" which is replicated to thesubscriber viathe replication option "Execution of the stored procedure":-------------------------------------------------------------ALTER procedure [dbo].[SYS_spShellTest] @cmd varchar(1000)WITH EXECUTE AS ownerasBEGINEXEC @result = master..xp_cmdshell @cmd, NO_OUTPUTReturn (@result)END-------------------------------------------------------------1) If I call "exec SYS_spShellTest" on the publisher the call is alsoexecuted on the subscriber.2) If I call "exec SYS_spShellTest" on the subscriber the call is NOTexecuted on the publisher.How can I make 2) work so that it is also executed on the publisherplease?Thanks a lot,Patrick
View 1 Replies
View Related
Oct 26, 2007
Guys,
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
Any suggestions and inputs would help
Thanks
View 1 Replies
View Related
Jul 20, 2005
Greetings All, I was hoping that a replication sage might be able toanswer a question for me.I want to have one subscriber subscribing to N publishers. Iessentially have a company that has a main headquarters and threesatellite offices. I want each of the satellite offices to push theirdata up to the master database. From what I have read it seems likethis should not be a problem. Some questions that come to mind are:1.)Does the master need to be read only or can it be configured to beupdateable as well?2.)Can the distribution agent on all the publishers be set tocontinuously distribute or should it be staggered so as not to cause aproblem when another distribution agent is running?3.)If the distribution is set to "delay distribution" will this causechanges on the subscriber to be pushed out to the publishers?In this database guid's are used as pk's so the issue of pk collisionsis not a problem.I hope that this question is not too vague. My experience thus farwith replication has been simple one way transactional and simplemerge replication.Regards, Louis Frolio
View 3 Replies
View Related
Oct 30, 2006
Hi!
The distributor was down for a long time (neer the week) and after that the commands stop applying to subscriber, while they successfully collected from publisher and stored into distributor.
Replication agent on distributor reports that initial snapshot is not available. It seems subscription is expired. But all commands from the point distributor down I have. How can I make distr agent to resume activity without reinitialize snapshot?
View 3 Replies
View Related
May 16, 2007
Hi
I need to setup transactional replication for a production server.
The tables (articles) at publisher being replicated can expect schema changes.
Is there any way to reflect schema changes at subscriber using transactional replication.
Early Thanks,
Salman Shehbaz.
View 1 Replies
View Related
Mar 1, 2006
I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
Thanks
View 3 Replies
View Related
Jun 15, 2015
Any experience of success or failure setting up CDC on the subscriber end of transactional replication?
Also for a bonus answer, why are explicit index operations not permitted (I'm assuming this is even on the publisher?) From BOL:
• Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.
View 0 Replies
View Related
Jun 17, 2007
Hi all
i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?
Thanks,
Arslan.
View 2 Replies
View Related
Dec 22, 2014
We have a database which is (a subset of tables are) replicated to another via transactional replication. Whilst most changes made at the published database reach the subscriber within a matter of seconds, we have a SQL Agent job which performs a calculation in the published database and then immediately exports data from the subscriber using log shipping. The result is that the calculated changes do not make it through to the exported transaction logs in time.
Is there a way to manually "refresh" the subscriber databases using T-SQL?
View 3 Replies
View Related
Jun 14, 2015
I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup
The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?
View 3 Replies
View Related
Feb 28, 2014
I have a transactional replication environment that creates subscribers on another server as a staging area for an ETL process to a data warehouse application on a 3rd server which is the report repository. Currently the ETL process runs every 10 minutes and performs it's function across approx 150+ subscriber databases and consolidates it to the data warehouse.
I have an SLA of 2 minutes. I'd like to rework the ETL process (which run as SSIS job at the moment) to be specific to a single database and fire that one ETL proces when changes have been applied to that subscriber database only. Of these 150+ databases generally only about 8-10 are updating the subscriber at any given time per Repl Monitor. I'm thinking that if I only have a few transactions to apply to a single db the ETL would run in seconds dynamically as the subscriber is update.
The issue is how to fire the ETL process upon completion of updates to the subscriber DB? I'm thinking of using SP_Start_job passing the DBID to update the warehouse but unsure whether this is possible but if so where to trigger it.
View 1 Replies
View Related
Nov 9, 2006
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
View 2 Replies
View Related
Oct 2, 2007
I am having a problem creating a publisher.
I have a database, MobileApp, on a server called Server03, which is a subscriber to a database Stock on Server01. I create the database as a subscriber on Server01, and it gets replicated to Server03 correctly.
I now want to use MobileApp on Server03 as a publisher to mobile devices. I click to add a new publication, and add all the tables. I get all the way to the end, but when I click on finish, I get an error for each table - 'an article with a different subscriber_upload_options value already exists for object.
Any ideas please?
Thanks
Pete
View 6 Replies
View Related
Aug 26, 2007
I have a clustering between Server A and Server B. There is a clustered SQL Server installed.
Server A is a primary node(oltp production server) and Server B is a secondary node.
i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.
Currently both servers have sp1.
Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)
So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?
Any help would be appriciated.
Thanks
View 4 Replies
View Related
Mar 28, 2007
hi,
I'm reading the MS online docs about replication. there's something not so clear until so far.
I have one central server and 7 client servers.
Sometimes new data is entered at one client server. This should be replicated (when client comes online) to the Central Server (merge replication).
Also, the other way, once the central server gets new data (that was replicated from a client), it should be again replicated to all 6 other clients (from the central server).
now, who should i configure as the distributor, publisher and subscriber?
View 3 Replies
View Related
Feb 9, 2000
Hello:
I am running an application on mssql 6.5, sp4.
We are working on using a database as a publisher and a remote database as a subscriber for one replication in Canada.
WE havw just been told that another application on a different server(publisher/ distribution) wants to replication to our database as a subscriber.
We also will need(as a publisher/distribution) to replication to a second subscriber database after we completely test the first replication to Canada.
Therefore my questions are:
1) Can a database on a local database -- our database -- on one server be a publisher and subscriber to ther different databases?
2)has any one been involved with replication on a database used both as a publisher and subscriber to different replications requirements?
What are the pitfalls aside from the scheduling conflicts?
3) In terms of database space requirements, how much disk space extra do we need? What guidelines should we use? For each table as an article, assuming we are doing the full table, do we add 100% more space or what?
4) Any other pitfalls we should be aware of?
Thanks.
David Spaisman
View 1 Replies
View Related
Sep 27, 2006
Quisiera saber si se puede y como llevar a cabo la replicación con SQL 7 (Publisher) y SQL 2005 (Subscriber). Desde ya muchas gracias.
View 4 Replies
View Related
Dec 7, 2006
I have replication setup between our main site and a remote one, and have recently noticed that the database at the remote site's .MDF file is about 3 times as large as the main site's. This doesn't seem to make sense since essentially all of our data is replicated between the two servers. Can anyone suggest why this might be happening and what is safe to do to shrink the remote file?
TIA
Ron L
View 8 Replies
View Related