Replication With Updatable Subscriptions

Jun 13, 2007

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.

View 3 Replies


ADVERTISEMENT

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.

View 7 Replies View Related

Problem With Updatable Subscriptions

Nov 1, 2006

Hi all,

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.



View 4 Replies View Related

Problem With Queued Updatable Subscriptions

Mar 7, 2008

Hi All,

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?

Thanks you all,
Ravi.

View 6 Replies View Related

Transactional Publication With Updatable Subscriptions

Apr 24, 2007

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.

View 1 Replies View Related

Updatable Subscriptions - No Transaction Is Active

Apr 11, 2007

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.

Thanks,
Patrick

View 6 Replies View Related

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.

View 2 Replies View Related

Replication Question - Updatable Subscription?

Apr 11, 2006

Hi 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

View 2 Replies View Related

Updatable Subscription In Transactional Replication

Sep 8, 2005

Hi guys,

View 6 Replies View Related

Updatable Transactional Replication, Queue Reader Error

Jul 3, 2007

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?



Thanks

View 11 Replies View Related

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.


View 5 Replies View Related

Transactional Replication - Updatable Subscribers - Image Data Type- Sql Server 2000

Aug 21, 2007

Sql 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

View 3 Replies View Related

Replication Via Web, New Subscriptions And Snapshots

Sep 27, 2006

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();

TITLE: Microsoft.SqlServer.ConnectionInfo
------------------------------

Failed to connect to server XXX.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

I can€™t understand how this connection could work if it isn€™t aware of the fact that it should use https to connect with the server.

Can someone point me in the right direction?

Thanks
Angelo

View 6 Replies View Related

Replication With Fifteen Subscriptions

Apr 5, 2006

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.

Has anybody got any ideas why this will occur?

View 4 Replies View Related

Reinitialize Subscriptions In Transactional Replication

Feb 27, 2004

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.

Any help is appreciated

View 1 Replies View Related

RMO Replication Problems With Pull Subscriptions

Nov 29, 2006

Hello,

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.

View 5 Replies View Related

SS2005 Replication - Cannot Drop Subscriptions From Publisher

Jun 22, 2006

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.

View 5 Replies View Related

Replication: Any Way To Change Table Schema W/o Reinitializing Subscriptions

Mar 23, 2004

Hi all,

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?

Any help is greatly appreciated.

tia.

-m

View 2 Replies View Related

Transactional Replication With Updateable Subscriptions And Identity Management

Oct 28, 2006

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?




View 3 Replies View Related

Number Of Subscriptions Shown In Replication Monitor Is Way Too High

Mar 29, 2007

I set up a sql server 2005 database as publisher for about 50 sql mobile subscriptions. Yet in the replication monitor windows, it shows in subscription columns is 26549. Does it mean I have 26549 subsriptions or it means 26549 synchronizations?



thanks,

View 1 Replies View Related

Replication Architecture Question - Pull Subscriptions On A SQL Cluster...

Aug 30, 2007


Greetings:

I am working on a replication setup using transactional replication using with pull subscriptions and a separate distributor. The pull subscriptions are located on a SQL cluster using the virtual SQL Server name as the subscriber; when the box fails over, we get an error of missing replication.dlls. Researching further, we found that replication only works on one node of the subscriber.

Any ideas on what we did wrong in the setup?

Thanks,
Lee Everest


www.texas2oo.com/sqlblog

View 6 Replies View Related

Sql Server Merge Replication Vs. Sql Server Transactional With Updatable Subscribtion

Jun 12, 2006

Hi,

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

View 4 Replies View Related

How To Access Or Modify Local Subscriptions Data In Merge Replication

Apr 3, 2007

Hello,



If I want to access or modify my local subscription data(not the configuration) how can I do that(from sql server 2005 or from asp.net)? Also can i update directly to the local subscriptions data or do I need another layer which will update from a table for example??



Thank you



Salah

View 3 Replies View Related

Remote Tables Are Not Updatable. Updatable Keyset-driven Cursors On Remote Tables Require A Transaction With The REPEATABLE_READ

May 16, 2008



Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.

Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.

DT

View 1 Replies View Related

One Or More Subscriptions Have Been Marked Inactive. Drop And Re-create All Subscriptions For This Node...

May 16, 2007

Greetings,



We periodically get the following message in our three server peer-to-peer transactional replication environment:



"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."



What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?



Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.



Thanks,

BCB

View 9 Replies View Related

Email Subscriptions: Changing From Address For Different Subscriptions On Same Server

Jan 18, 2007

I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?

It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).

View 1 Replies View Related

Updatable Views

Dec 12, 2007

Hello everyone,
 How can i create updatable views in sql 2005. Any article or link can be useful.
 Thanks.

View 1 Replies View Related

Updatable Views

May 17, 2001

I have a fairly complicated data import that needs to be done, but the table structures of the two databases don't match up too neatly. I thought I'd be clever, and create a view in the target database, and import into that view -- only to find out (after reading the manual) that multi-table views are not updatable and cannot be used with BULK INSERT or bcp.

I'm not a database pro, I'm a programmer thrust into this role. Needless to say, I'm a bit of a newbie on these things!

Anyway, the view's code:
<pre>
CREATE VIEW dbo.RJ2_V_Client_Import
AS
SELECT Clients. IDENTITYCOL, Clients.CompanyName,
Clients.Address1, Clients.Address2, Clients.StateID,
Clients.City, Clients.Zip, Clients.CountryID, Clients.PhoneMain,
Clients.PhoneFax, Clients.WebAddress,
Clients.ParentCompany, Clients.DivisionDepartment,
People.NameFirst, People.NameLast,
People.AddressPrimary1, People.AddressPrimary2,
People.CityPrimary, People.StatePrimaryID,
People.ZipPrimary, People.CountryPrimaryID,
People.PhoneWork, People.PhoneWorkExtension,
Clients.EnteredDate, Clients.UserLevelID, Clients.Revenue,
Clients.NumberOfContractors, Clients.SICCode,
Clients.Industry, Clients.ReceptionistName
FROM Clients INNER JOIN
People ON Clients.BillingPersonID = People.PID
</pre>

The reason there is a "People" table is because several (SEVERAL) tables in this database contain records for people, and we felt that this was a better design because of it.

The data we're trying to import contains records for billing contacts, which are the People.* fields referenced. It's extremely important that we get this data in the system.

Since BULK INSERT is out, are there any workarounds? There's got to be some kind of non-manual solution, since I know other people have to do imports that start as one table and end up in two ...

For this particular set, I could do it manually -- only two records are affected. BUT, for future sets, I can't do it manually -- literally thousands of records will be affected, and I just don't have the time (or inclination) to do the entry ... Plus, think of the potential error rate!

Thanks in advance

John

View 1 Replies View Related

Updatable Cursors

Jun 2, 2000

I am trying to update the current row of a cursor that I have declared from Query Analyzer and I keep getting an error message saying the cursor is Read only. This doesn't make sense because BOL says that cursor's are updatable by default. Anyone had this problem?

Ex: This is the statement I am using to update the current row in the cursor:

UPDATE TableName SET Field1 = 'D'
WHERE CURRENT OF CursorName


jg

View 1 Replies View Related

View Not Updatable

Apr 12, 2000

I have created a view ('Data_test') based on Table1 and Table2 joined in the following manner:

Table1.PrimaryKey <-----> Table2.ForeignKey /* same as Primary Key */


When trying to insert data into the tables through the view, I get the following error:

Server: Msg 4405, Level 16, State 2, Line 1
View 'Data_test' is not updatable because the FROM clause names multiple tables.


Any ideas of how to make the view updatable?

View 1 Replies View Related

Updatable Cursors

Jul 20, 2005

I am trying to write updatable cursors in a stored procedure? Can I getany help to write updatable cursors?Any help would be appreciated very much.Thanks for your help in advance.Rajah V.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Why Isn't Table Updatable????

Jul 20, 2005

Hello:I've just converted my Access 2K db to SQL Server 2K and the conversion wentsmoothly. I created users, roles, views and forms and gave the userspermissions to use them. I "THINK" I have done everything, but......youcannto update the form, view, or table. I get "Recordset Not Updatabale"when I modify a field in either.Assuming I set the permissions correctly on the db, table, view, and userscorrectly, is there something else I need to do to make the table and viewupdatable?My front end is Access 2K.Thanks for any advise.....Richard H

View 1 Replies View Related

Derived Table Not Updatable

Oct 6, 2004

I got an error as follows:
Derived table 'A' is not updatable because a column of the derived table is derived or constant.
when I tried to run this query:
update A set MonthsUnbilled =99999888
FROM (select MonthsUnbilled from dbo.vw_MasterView
WHERE (RecordID =8377396)) A
This is a simplified query in order to pinpoint the culprit. I know I don't need to use a derived table if the real query is this simple.

Thanks in advance!

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved