SQL 2012 :: No More Transactional Replication With Updatable Subscriptions
May 24, 2012
So, 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.
Hi 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.
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)
I 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.
I 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?
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.
If I have one table in one publication in transactional replication replication between primary and replicate is broken because subscription is marked as inactive.
If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken
If I do
Reinitialize subscriptions start the snapshot agent start the distribution agent
Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.
Replication Type: Transactional With Updateable Subscriptions SQL Server Version: SQL Server 2005 Enterprise Edition Publisher, Subscriber1 and Subscriber2 all on same SQL Server
Problem: I am trying to set up a transactional replication with two updateable subscriptions. All three databases are a carbon copy of each other. Every table to be replicated contains an identity key column. I've been asked to create the replication without assigning separate identity ranges for the publisher and the updateable subscriptions. In other words they want a continous ID range. Can this be done without managing identity ranges programatically outside of SQL Server itself?
Example:
Publisher inserts into table test1 in database1. This creates ID 100 for the record. ID 100 replicates to Subscriber1 (database2) and Subscriber2 (database3). Subscriber1 and 2 identity seeds are then incremented to ID 100. So far, all three databases have the same identity seed in table test1. Subscriber1 inserts into table test1 in database2. This creates ID 101 for the record. ID 101 is replicated to Subscriber2 (database3) and to the publisher (database1). Subscriber2 identity seed is then incremented to ID 101. However, the Publisher retains it's original identity seed of 100. Insert into Publisher table test1 will fail since it will try to insert the identity seed of 101 which is taken.
I have tried identity range management Automatic and Manual. Both seem to have some limitations in managing identity seeds unless different identity ranges are assigned for publisher and subscriber.
Can someone recommend a solution to this problem? Can this be done natively in SQL Server 2005 replication. Or can some recommend another approach to make this work?
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 ?
I have a problem with "Updatable Subscriptions" under concurrent transactions on both pub and sub effecting same data. At high level of concurrency it Subscriber starts giving error msg "Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the Subscriber.".Please tell me any way arround for this.
I have a problem replicating data from Subscriber to Publisher.
I configured Queued updatable subscriptions in transactional replication. Publisher is on SQL Server Standard edition and Subscriber is on SQL Server Express edition, both on Windows XP.
When I insert/update data in publisher, the data is replicated to subscriber. If I insert/update data on subscriber the data is not replicated. When I see the replication monitor for errors, it shows an error "Cannot load a DLL xprepl.dll or one of its resources".
Could any one give me a solution or please tell me if I missing something?
This may be a simple answer, but I was wondering when setting up transactional replication with updatable subscriptions why does this error occur?
Error:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPLLINK " was unable to begin a distributed transaction. Changed database context to 'MyDB'. OLE DB provider "SQLNCLI" for linked server "REPLLINK" returned message "No transaction is active.". (.Net SqlClient Data Provider)
I've looked for MSDTC security documents and checked every box in the security dialog and selected the 'No Authentication Required€™. MSDTC and SQL Agent are running as NETWORKSERVICE user. I also setup permissions on each individual DCOM Objects for SQL Server. And MSDTC service is running. Both Servers are SQL Server 2005 Standard and Windows 2003 R2. The publisher is on the same box as the distributor. No domains or trusts setup on either. This error occurs when changing data on the subscriber.
We are planning to setup HA using either AAG or FC. ON my production environment (P), I have transnational replication configured for 2 of databases out of three. These data get replicated to another server( C1) hosted on cloud, with local distributor at P.
If I configure AAG/ FCI , how do I handle failover?I wanted to setup P as primary AAG with two replicas as S1 and S2. P->S1 will be synchronous while P->S2 will be asynchronous.Incase my P goes down how the replication will failover S1? Incase P and S1 goes down how do I failover to S2 with the replication.
I built a number of publications on a SQL Server 2005 box to replicate to a SQL Server 2012 subscriber. All the publications except one are fine. During the snapshot phase of schema script generation I get Script Failed for Table 'dbo.MediaDisplayLibraryFileData'. From the Replication monitor for the Snapshot Agent on the Publication I get, "Column FileData in object MediaDisplayLibraryFileData contains type VarBinaryMax, which is not supported in the target server version, SQL Server 2000." This message makes no sense since the target server version is 2012. I have even checked that the compatibility level was set to 110 before I started the process of setting up replication. How do I resolve this error?
I have found some articles with no publication in our transactional replication.
For example, running this:
select p.publication, a.publication_id, a.article from dbo.MSArticles as a left outer join dbo.MSpublications as p on a.publication_id = p.publication_id
shows this:
NULL1org_Community NULL3org_Community Purchasing to EDW5org_Community NULL1org_Division NULL3org_Division Purchasing to EDW5org_Division
How can I get rid of the articles that are not part of a publication?
I can't use sp_droparticle because it requires a publication which these articles do not have.
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
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?
I have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?
I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.
Does it behave differently for small and large number of articles?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
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?
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.
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.
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.
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.
Hi,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.
We are planning to upgrade. We are using Sql 2008R2 now. Which is the better option migrating to SQL 2012 or migrating to 2014?I am thinking 2014 has memory optimized tables and updatable column stored index. So it is better option.
I 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.
Hi everybody, I'm quite new to SQL 2005 and I€™m trying to understand some key concepts regarding replicas. I need to develop an application with characteristics similar to the Sales Order Sample for Merge Replication, on the client side it should run with the express version of sql server and also the synchronization should only work via web. I try to run the sample but I got an exception in the CreateSubscription method on invoking publisherConn.Connect();
I have recently deployed a sql replication system into a production environment and I€™m experiencing a few problems.
I have a server holding consolidated data (4 processor/2GB ram) and fifteen subscribers, each being a 400Mhz Celeron with 256MB ram.
Transactional replication is used to pull base data from the main server to the subscribers. This data does not change very often at the publisher, but the subscribers need an immediate update, so continuous transactional replication is configured at the subscribers. This data will never be changed at the subscriber.
The server has another three tables used for historical data. Each subscriber has been configured for merge replication on these three articles. An initial snapshot is run to setup each subscriber. Each subscriber writes up to 10 records per minute into these local tables and continuous merge replication replicates these 10 records from the subscriber to the publisher. As all subscribers are doing the same, part of the merge operation will potentially bring down 140 records to each subscriber (10 records from each subscriber merged to the publisher and back to each subscriber).
Anyway on to the problem€¦
Each subscriber system has a Microsoft.Net application writing these 10 records per minute to the local database. Refreshing a graph within the application occurs once every minute. Refreshing, queries the three local tables used for the merge replication. This application shows an error (randomly) with a sql timeout message trying to get a connection to the local database.
I have the following situation. I have a single publication on my publisher Server. This publication is created using SQL Server Manager. Snapshot is created completly. Now I want several Pull subscriptions from several machines to work with this publication (One subscription per machine). I'm creating these subscription using RMO. I'm synchronizing data using RMO again. My code workflow is: Syncronize -> success -> do nothing -> fail -> Check if everything with Publisher and Publication is ok -> Generate Snapshot -> Create Subsscription -> Sync again. (I tried to eliminate Generate Snapshot step but couldn't because I receive error that I must rerun Snapshot Generation.)
When I tested my code per single machine it's working. Next test I tried was to run my program on 2 machines simultaneously.
The result is:
---> System.Data.SqlClient.SqlException: Another snapshot agent for the subscription(s) is running or the server is working on a previous request by the same agent.
or:
---> System.Data.SqlClient.SqlException: Unable to acquire the replication merge administrative application lock for database 'XXX'. This could be due an active snapshot running while the schema change (DDL) or the administrative proc change was attempted. Replication merge admin stored procedure 'sp_changemergepublication' failed for publication 'YYY'. This could be due an active snapshot running while the admin proc was called.
The problem is obvious but because I'm new to the replication I'm not sure if I did general mistake in what I want to achieve. Any advices how can I fix my problem will be highly appreciated.
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example... Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
I am executing sp_dropmergesubscription, but the rows are still in dbo.msmerge_subscriptions, and are still shown in the replication monitor as expired; the last synch dates were in april (expiration is set to 10 days). The 'expired subscription clean up' job appears to be running okay. I need to remove these subscriptions from the publisher as the subscribers are mobile devices, which sometimes are lost.
I'm admitedly a bit new to the world of replication, so please bear with me. I've got two SQL Server 2000 servers running in different locations. Server A does transactional replication over a push subscription to server B. If I need to make a minor change to one of the replicated tables (for example, dropping a no longer used column or changing a varchar field's length) do I need to drop the subscription, make the changes and then re-initialize the schemas and data?
For minor changes, I really hate having to knock out the site runnign off server B while the subscription is re-initialized and data is bulk copied back over. If I want to just make the changes manually on both servers will that cause problems down the line?
