Updatable Subscription In Transactional Replication
Sep 8, 2005Hi guys,
View 6 Replies
ADVERTISEMENT
SQL 2012 :: No More Transactional Replication With Updatable Subscriptions
May 24, 2012So, Microsoft decided that they were deprecating Transactional Replication with Updatable subscriptions. In that case, you have 2 options (if I am correct): Pay for Enterprise (if you are already not) and use peer-to-peer or use bidirectional transactional replication which is basically setting up a transactional from db1 to db2 and also transactional from db2 to db1.
The issue I see in both cases is conflict resolution. With updatable subscriptions, you could specify how to handle the conflict. With either of these 2 options (from what I can tell) you cannot allow the engine to handle this for you.
Any thoughts? Seems like a slap in the face to those who have been using MS for years and a damn good reason for companies that rely on updatable subscriptions to not upgrade to 2012.
Updatable Transactional Replication, Queue Reader Error
Jul 3, 2007I have just started getting the following error from the queue reader but I can't see why.
The Queue Reader Agent has encountered the error ''Row handle is invalid.'' when connecting to ''Database'' on ''ServerName''. Ensure that the publication and subscription are defined properly and that both servers are running.
Does anybody know what it means / how I can fix the problem?
Thanks
Replication Question - Updatable Subscription?
Apr 11, 2006Hi all,
I am planning replication around a large SQL Database Server; 3 databases need to be replicated for reporting purposes.
My plan was to use Transaction Replication, as the Reporting database will be mostly read-only. However, we must add a whole bunch of additional, historical data to some of the tables of the reporting database that does not exist in the Publisher. I understand that changes can be made to subscriptions in Transaction Replication scenarios, ideally when the changes are few and far between. (Merge replication is excessive for our purposes.)
I don't want the additional historical data to be propagated back to the publisher. Is it possible that data is written to a subscriber in a Transactional Replication scenario without writing these updates to the Publisher? Given the nature of the data, there will not be conflicts between the historical data and the new changes applied from the publisher.
Any help would be much appreciated!
Thanks,
Faraz
Transactional Replication - Updatable Subscribers - Image Data Type- Sql Server 2000
Aug 21, 2007Sql Server 2000
I am looking for bidirectional transactional replication using updatable subscribers (queued or immediate) . Is it possible to replicate the image data from the updatable subscribers to the publisher. I understood that the Image data can't be replicated to the publisher from the updatable subscriber. I am not using the WRITETEXT or UPDATETEXT. I am using just INSERT and UPDATE for image data type transactions.
Any thoughts on this is greatly appreciated.
Thanks in advance
Pull Subscription For Transactional Replication
Jan 5, 2005I have setup a pull subscription on the subscriber for transactional replication. The distribution job continues to run, but doesn't do anything because it says: The initial snapshot for publication 'man4' is not yet available.
How can I tell if the snapshot is running, and can I see the snapshot where it was created?
Sql Server Merge Replication Vs. Sql Server Transactional With Updatable Subscribtion
Jun 12, 2006Hi,
Transactional replication allows updatable subscriptions where changes at the subscriber are replicated up to the publisher, this can happen via Immediate Updating subscriptions, Queue subscriptions and P2P (new in SQL 2005), all forms of Transactional replication.
Any compared document between merge replication and Transactional with updatable subscribtion ?
Thanks,
Tarek Ghazali
SQL Server MVP
Trigger In The Subscription Database - Transactional Replication...
Feb 2, 2007Hi,
We are Using Transactional Replication with Updatable Subscription in SQL Server 2005 SP1.
Subscription Type : Pull Subscription
Mode : Continuous Running Mode
Conflict Resolution Policy : Publisher Wins.
I have a table "Sample" (which is part of replication) and it has got 3 triggers. All the triggers are set NOT FOR REPLICATION.
The first trigger Updates a column of the "Sample" table in which i inserted a record.
The second trigger inserts record in to another database table and also updates a column of the table "Sample".
The third trigger does not affect any tables, it is written for some manipulations with variables.
In this scenario when I insert a record in the Sample table of the subscription database, that record is visible in the table. But during replication, it shows conflict in the Conflict Viewer and removes the record from the Sample table of the subscription database. The record is not replicated to the publisher and the other Subscriber also.
But when I comment any one update in either the first or second trigger, the insert works fine without any conflict.
Is there any issue with firing two triggers in replication which is updating the same table? I also suspect the Order of Commands moving to the Publisher from the MSReplication_Queue table, becoz the conflict viewer shows the subscriber as the Conflict loser.
Is there any issue with msrepl_tran_version, Since the conflict is decided based on this id??
It would be helpful if u could reply soon.
Thanks and Regards,
Swapna.B.
Reinitializing Push Subscription After It Expired In Transactional Replication
Jan 24, 2007I am using SQL 2000. How can I get my transactional replication reinitialized after it has failed with several attempts.
I know one way of doing it through enterprise manager and specifying the subscription to reinitialize. But this will apply the snapshot and will take long time.
Is there a fast way of doing it?
-Nipul
Transactional Replication - Pull Subscription Snapshot Being Applied Continuously
Jan 25, 2007Hi
We have setup transactional replication between 2 databases on SQL Server 2000 SP3a (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.
From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring
Initialising
Applied script 'snapshot.pre'
Then it applies all the schema files .sch
Then it applies all the index files .idx
The it bulk copies the data in (bcp)
Then it creates the Primary Keys
Then it applies all the trigger files .trg
Then it applies all the referential integrity files .dri
These all complete successfully but then the process kicks off again immediately after reapplying the snapshot. We are unaware of any settings that may be causing this.
Any help on what maybe causing this would be much appreciated.
Transactional Publication With Updatable Subscriptions
Apr 24, 2007Hi to evebody.
I'm working with the transactional publication with updatable subscriptions provided by SQL Server 2005. The replication works pretty good from the publisher to the subscriber, but I'm having some problems when the data must go from the subscriber to the publisher.
When I do an update in a subscriptor's table, the database engine shows the following error:
21064 - 16 - The subscription is unavailable for immediate updating because it is marked for reinitialization. Try again after the reinitialization completes.
And rollbacks the transaction.
Does anybody knows what to do to solve this problems.
The publisher is a Windows XP with the SQL Server 2005 Developer edition with SP2
The subscriber is a Windows 2003 Server with SQL Server 2005 Developer edition without SP2
I'm using also the inmediate updating subscriptions. Both operative systems have the MSDTC runing.
Thank you in advance.
Sebastian.-
PS: Sorry about my english, it's been a long time without using it.
Transactional Publication With Updatable Subscriptions In SQL Server 2005, SvPk 2
Jul 31, 2007
Subscription to "Transactional Publication with Updateable Subscriptions" works only one way. Changes take effect on subscriber, but the subcriber is unable to update data on publisher.
I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.' All agents are running at the Distributor (Publishing Server.)
The subscriber is unable to connect to the Distributor using the SQL Server login.
Following is the error message I get:
Creating Subscription(s)...
- Creating subscription for 'SQL3' (Warning)
Messages
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
MSDTC on server 'SQL3' is unavailable.
Changed database context to 'DB_SQL1_to_SQL3_on_3'. (Microsoft SQL Server, Error: 8501)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=8501&LinkId=20476
Please suggest procedure to end this dilemma. The link has no info available.
Unable To Set The Publisher Login For The Updatable Subscription
Jun 15, 2007I keep getting this following error when trying to set-up an updateable subscription on SQL 2005 (latest service packs). Server is set for mixed mode auth.
At the final stage of the new subscription wizard I get:
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)
The user is not associated with a trusted SQL Server connection
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Error Number: 18456, Severity: 14, State: 1,Line Number: 1
Can anyone recommend a possible way of debugging this? Is it some issue with RPC security?
SQL 2012 :: Updatable Subscription - View Pending Log From Subscriber To Publisher
May 7, 2015I have setup Transnational replication with updatable subscription.
There were around 7000 rows update at subscriber from one query which was executed at subscriber side.
But this update has not taken place at publisher yet and so to other subscribers.
Is there any way i can monitor pending log or where is the issue?
I find so many ways to see pending logs or statistics for - "Publisher to Subscriber" but not for "subscriber to publisher"
What is the way to see subscriber to publisher pending and issues?
Replication With Updatable Subscriptions
Jun 13, 2007I have been trying to set up replication with updateable subscriptions. I first followed the tutorial on MSDN and set up basic transactional replication. This all worked fine. I then tried, using that tutorial as a basis, to set up replication with updatable subscriptions.
On the Agent Security page you are asked for a Snapshot Agent, a Log Reader Agent and a Queue Reader Agent. I assigned these to the following accounts, which I created and added as logins, PUBLISHERSERVER
epl_snapshot, PUBLISHERSERVER
epl_logreader and PUBLISHERSERVER
epl_queuereader.
I then tried to set up a subscriber on SUBSCRIBERSERVER. Under the publication I add new subscriber, select the publisher, add SUBSCRIBERSERVER as the new SQL server Subscriber. In the Distribution Agent Security page of the wizard it asks for process account, which will be run when synchronizing the subscription. I entered PUBLISHERSERVER
epl_distribution here and selected the other two default options of connecting to the distributor and subscriber by impersonating the process account. I then took the default options for the next few screens and finally get to:
Login for updateable subscriptions. This offers the option of a login or using a linked server. I have tried various logins here, initially trying the €˜sa€™ login and password. This produced the error: The user is not associated with a trusted SQL Server connection. The servers are set up for mixed mode operation. I then tried PUBLISHERSERVER
epl_distribution and subsequently every other account I had created on PUBLISHERSERVER. All of these failed. I tried linking the servers, but this also failed. How exactly do you set up subscriber with the ability to login to the publisher? I have spent days trying to set this up, and am as you can gather new to this technology, any help would be greatly appreciated.
Combining Peer-to-Peer Transactional Replication With Standard Transactional Replication
Dec 1, 2006Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
Expired Transactional Subscription Prob
Nov 19, 2004I'm a complete newb at this. Boss man said, "Here, replication isn't working. Fix it."
So, best I can determine, we had a subscription, but it looks like it got disowned. Trying to delete the subscription gives me a "The subscription on the Subscriber doesn't exist."
I need to delete the subscription to set up a new transactional replication process. Or at least, spending a day hugging on the SQL server has made me think so. I'm running MsSQL 2K. I've tried to screw with it what ways I could glean with the EM, but I'm not getting anywhere. That and I can't find a freaking thing on error 20017 except "It don't exist, buddy."
Here's the skinny:
We have the entire customer Database on Server A. We take 2 fields from the A and replicate them per transaction to B which the Radius server runs off of. (This helps reduce traffic because we keep the A server at the office while the radius server and B server are across town). :(
Transactional With Updateable Subscription And Log Shipping
Mar 8, 2007We have a database set up for transactional replication with an updateable subscription. When we add log shipping to the publication database (sending the logs to a separate server) the publication and subscription entry show up in Management Studio's replication folder on the log ship target server (although the definitions are correct).
Is this configuration legitimate? Can I add log shipping to the subscription database as well?
Thanks... Liston
Determine Updatable Transaction Replication Mode From Client Side
Sep 28, 2007
We are currently testing a Replication topology as follows:
SQL Server 2005 Developer as publisher and distributor of a push updatable transaction replication
2 SQL Server 2005 Express clients as subscribers.
The replication is working great. If the connection is lost, the subscribers switch from Immediate to Queued Updating, and seem to shift back to Immediate when they regain the connection.
My question is to determine if there is a way that I can ask SQL Express on the client which mode is it currently running, or ask SQL Express if it has a current live connection to the distributor.
I know that they are usually mutliple ways to accomplish tasks, and I would be open any suggestions, preferrable programming in VB (such as with SMO or RMO) or using stored procedures or functions.
I hope this give some people something to chew on during the weekend and thank you to all that have taking the time to read this.
Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication
Sep 13, 2007Hi,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 RelatedReplication :: Transactional Replication Removal Sequence
Sep 2, 2015I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. Â I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. Â I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. Â SQL-P (publisher),Â
SQL-D (distributor) and SQL-S (subscriber). Â Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
Merge Replication Set Off Transactional Replication
Oct 9, 2007I 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...
Transactional Replication
Jul 30, 2001On my distribution server there is a directory called mssql7
epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.
Transactional Replication
Dec 28, 2000Hi,
I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval.
So pls suggest me .
I am using Sql 7.0 with service pack2.
Thanks!
Kavira
Transactional Replication
Feb 13, 2001I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher).
If anybody knows how to solve it, please tell me
Thanks
Replication - Transactional - 7.0
Nov 1, 1999I have two servers networked via workgroups (no domain) and am trying to install Transactional Replication. Everything appears to install correctly, but when I run the initial Snapshot Agent I get the following error -
The process could not create file 'CONCEPCIONC$MSSQL7ReplDatauncCONCEPCION_Erik_Rep lication_Erik_Replication19991029153207Employer.sc h'.
Access is denied.
The SQLAgent is starting with same user account on both machines - admin rights and the same password. I have added this user to the (
epldata) folder permissions as well. I even see a "Employer.sch" file in the directory.
What am I missing?
Transactional Replication
Oct 25, 1999Hello,
I am planning to implement replication between two servers (either snapshot/transactional). Those two servers are physically connected but we don't have domain concept i.e we connected both servers physically and it can be pinked from each other, but not under any domain (not also under different domain). How should I register SQL server& agent services to start with 'Service startup Account'. In this case I cannot start both servers using same domain Account, they how can I relate...
Thanks
Help... Transactional Replication
May 22, 2003Hi guys can anyone help me with this issue. if so thankyou kindly.
1. created transactional publication (for tables, stored procs & views )
2. created empty database at subscriber
3. set up pull subscription at subscriber
4. ran the initial snapshot agent at the publisher
5. checked the schema files & found out it hadn't scripted the primary keys on the tables.
Problem: PRIMARY KEY AINT GOIN ACROSS, COULD ANYONE TELL ME IF IVE MISSED SOMETHING? THANK YOU MUCH APPRECIATED GUYS.
? Could it be that it is not picking up the primary keys on the tables because I am replicating stored procs and views with that publication?
? Should I break my publications into 3; say 1 for each object?
Thank you guys for taking the time to read this.
Transactional Replication
Feb 24, 2005Hi
I am tring to implement transactional replication.
How do i publish tables which donot have primary keys?...because transactional replication seems to publish only the tables which has a primary key.
Thanks
Madhukar
Transactional Replication
May 1, 2002Hello all! I am setting up my first Replicated database, and want to get this right.
We are a Newspaper, and I will be Publishing the database that is the back end of the web server. The goal is to load balance the web site as well as to have a second identical server in case something goes wrong. Both the Subscriber and the Publisher need to be kept in sync as new articles are posted, so my client will just be updating the Publisher. But I'd like to keep a single set of usage statistics, so I was thinking of using Immediate Updating so the Subscriber can update the Publisher using two-phase commit.
What will happen if the Publisher shuts down and an update is attempted on the Subscriber? Will it just hang? Let's assume the worst - what if my Publisher is kaput? How do I determine that my other database is a Subscriber; and how do I stop it from being a Subscriber?
Thanks in Advance,
SQL Transactional Replication
Jun 28, 2002Hi
I have a requirement for pushing transactional publication to various subscribers. Each subscriber will need to receive a different subset of rows from a table. So I need all rows published, but with horizontal filtering at subscriber level. Is there a way of applying filtering at the subscription level.
Thanks
Tariq
Transactional Replication
Sep 11, 2007I am using transactional replication between two SQL 2005 databases. On Sunday evenings, we run a maintenance plan that rebuilds the indexes on the publisher DB. When the plan runs, the replication receives errors and cannot continue. The error appears to be at the publisher level. The publisher runs continually and the subcriber runs every 10 minutes. The error states "cannot run sp_replcmds" Other than Sunday, everything runs fine.
Thank you
Set Up Transactional Replication
Jul 7, 2015I want to set up the transactional replication. How many service accounts we need and what kind of permissions it required in windows level?Only two servers we are using. Pub/Distributor and Subsciber.Which option is better Push or pull?
View 1 Replies View Related