.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

Restart the server that has been configured for subscriber in a transactional replication

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Whats the impact on restarting the server that has been configured for subscriber in a transactional replication.

The distributor and the publisher have been set on different servers.

View Complete Post

More Related Resource Links

avoid accidental delete on Subscriber tables - transactional replication


Hello pundits..

I am looking to see if we have any way to set on the subcsriber to avoid accidental deletes on subscriber tables to avoid the 20598 error(where the publisher is trying to replicate a record not existing(rather deleted in accident) at the subscriber end). This is for transactional replication


Restoring Transactional Replication on the subscriber


We have a situation were  when the subscriber breaks for some reason we need to resume the replication  once the subscriber is fixed. Is there any way we can accomplish this withoout the running the snapshot all over again.?If yes let me what are the steps

Note: We are performing 15 min Log backups on the publisher.

Replication: Server restart vs service restart


Replication does not break when i restart the SS Agent, however, when there is a server restart, i get a message : The process was successfully stopped. and the Subscription Status is NOT RUNNING? Why does this happen? Does Tx replication not automatically sync after a server restart?

SQL Server 2008 Cluster Settings for Transactional Replication


Is there anything I need to take care on a SQL Server 2008 Active/Active cluster (Publisher) Before setting transactional replication to non cluster enviornment(Subscriber)?


Partition set up in transactional replication SQL Server 2008

Currently The partition strategy with switching works fine on a non replication enviornment.But I know there any few steps that needed to be considered before setting transaction replication.
I know we have to set 'allow_partition_switch' and replicate_partition_switch to true on the publication.
Inspite of setting them my replication fails while doing partition slide saying 'The <archived table> is not found'.
Any help is appreciated

Transactional replication - SQL SERVER 2008 R2



We are planning to upgrade my DEV,QA and Production environments from SQLSERVER 2000 to SQL SERVER 2008 R2. And planning to implement the Transactional Replication in SQL SERVER 2008 for Production environment.

We have frequent releases to Production like monthly once. In those rleases we might be modifying the articles  (TABLE, VIEWS,SP,FUNCTIONS and TRIGGERS). Could you please let us know the best way to implement the Transactional REPLICATION and pre-requisites for this process.

We had faced some problems in SL SERVER 2000 for Transaction Replication. When ever we are using any ALTER TABLE scripts in this replication.The replication has broken.

Could you please provide the best practices if you have?

Thanks in advance




SQL SErver 2008 Merge Replication: Alter Trigger cause syntax error on subscriber site


I have 2 clustered instances running on SQL Server 2008 SE-64 patch level 10.0.2531.0. These is one DB on these 2 instances (compatibility_level=80)under merge replication. now I need to change one trigger to add "NOT FOR REPLICATION". One publisher site all is ok but on subscriber site it causes Error 102 Severity 15 State 1 Incorrect Syntax near 'dbo'.

After tracing the error in profiler, I captured the incorrect syntax as below:

exec('ALTER TRIGGER [dbo].[trgBusinessEntityAllocationUpdate] on [dbo].[BusinessEntityAllocation] 

obviously, there is an duplicated part of object name. but the script was generated by replication engine. How could it happened? can anyone help?



George the DBA

SQL Server Transactional Replication and WF


Hi All, 

I am writing a WF based application with out-of-the-box SQL persistence and Tracking services running, the MS SQL database version is 2005.

I've been told that the DR version of the application will be held on another box, and they will be using  SQL Server Transactional Replication to manage this. I've been led to believe that in order for this replication to run, the tables that are to be replicated must have "primary keys" defined. Looking at the tables generated by the Persistence and Tracking Service SQL creation scripts, it doesn't appear that all the tables that are used have PKs defined (they have unique keys, but they are not set as "Primary Keys") - the [InstanceState] table for example.

Should these tables replicate without any issue?
Has anyone got any experiences of this setup?
Should I look at adding a PK to the unique key of the tables I'm referring to?

I'm not running the two versions of the Database in parallel so the locking issues that I've read about won't come into play, this is purely the solution for disaster recovery.

Any information about this matter appreciated.

Transactional Replication issue Between SQL Server 28 R2 and Denali (SQL2011)


Hi All,

OS :Windows 7

SQL Server Instance : SQL Server 2008 R2 (Publisher)

Distributor : On SQL Server 2008R2

SQL Serer Instance : SQL Server 2011 Denali (Subscriber) Pull Subscriber.

Replication Topology :  Transactional Replication

I have Installed SQL Server 2008 R2 and SQL Server 2011 CTP on the same machine. Setup Replication between SQL Server 2008 R2 and SQL Server 2011 using Transactional Replication Topology.

I have added one table as artical with default settings , the table has one int and 4 varchar datatype columns having rows=26004606.

While distributor agent deploys the snapshot on the subscriber , it failed with the below error :

SQL Server encountered: '23(Data error (cyclic redundancy check).)' resulting from an attempt to read the following: sort run page (1:35472), in file 'D:\Program Files\Microsoft SQL Server\MSSQL11.DENALI_2011\MSSQL\DATA\tempdb.mdf', in database with ID 2.

Changes to use SQL Server Transactional Replication



We are looking at how to best let remote users use our tools, and some of our tools require SQL Server databases.

We are planning to introduce SQL Server Transactional Replication to our main db, we want the Subscriber to be pure read only, and to be located at one of our remote sites.

Will we need to change our application so that it connects to the Publisher for write operations and to the Subscriber for read ones?

Is there anything in .Net that can help us automate this?

I assumed at the start of this investigation that SQL Server would know about the replication, and redirect user requests automatically, but somehow this doesn't seem to be the case, although I cannot find any hard proof of it in the form of documentation or such.

See this post: http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/d7b82782-032c-4e6a-bf75-dc10bd9f26a1

So I'm looking for recommendations and best practises about how to make our app use the correct db. Can you please help?



SQL Server 2005 Transactional Replication Fails to Publish Stored Procedure Containing an Index Crea


I've experienced a bizarre problem with a SQL Server 2005 Transactional Publication. The issue is this: If the publication contains an article that is a stored procedure that contains a create index statement, then there is an error thrown when attempting to replicate the schema of the stored procedure to a subscriber.

The behavior is very odd, because even if the create index statement is commented out, it still gives the exception, and it will only work if it is removed altogether.

Here is the exact error that's being returned:

Command attempted: GRANT EXECUTE ON [dbo].[usp_Test] TO [CompanyDatabase_access]

(Transaction sequence number: 0x00000170000008B9000500000000, Command ID: 5)

Error messages: Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151 Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151

The error is accurate, because when I check on the subscriber, the stored procedure wasn't created as expected... but that was the purpose of the publication...

SQL Server 2008 R2 Replication: The process could not connect to Subscriber



We are facing a lot of problem with replication between SQL Server 2008 R2 servers. Let me describe the scenario..

We have 20 branches and one main Server in our organization, all are running SQL Server 2008 R2. The Main server is a Windows 2008 R2 x64 bit computer from which we want to create both way transaction replication (Data inserted in to any branch will be reflected to the main server and from there all other branches will get the same data) to all other branches.  Branch machines are 32 bit XP sp3 or Windows 7. All the machines are under workgroup. Among 20 branches we could successfully establish both way replication on 5 branches only. When we are trying to publish data from main server to say Branch A (with windows 7), it says "The process could not connect to Subscriber 'A'". We can ping to branch A and can login to Branch A from Main server through Management Studio which proves that there is no connectivity issue. Branch A can publish data to Main server without any problem. Even Branch A can subscribe data from another branch Branch B.

I cannot understand why this problem is happening for other 15 branches to subscribe data from Main Server. I have some queries in mind

  1. Is there any upper limit of subscription no?
  2. Is there any problem with windows 7?

Any help in

Transactional replication: Updates to one of my columns (sometimes) does not get replicated from the

hi, This is my scenario: I have a varchar(max) column on a table that is replicated to other servers. I have a stored procedure that does and update to this column and other columns on the table. This is done inside a transaction that also includes other updates. When I call the stored procedure, this one varchar(max) column's value is not replicated. When I just run the single update statement on its own, the value is replicated. Anyone know something about this? Thanks!    

transactional replication and sp_MSupd tables

Hi thre, My target server keeps running sp_MSupd sp for different values in profiler and synch status shows "delivering replication commands" I keep waiting but this never ends. and ultimately we run a snapshot and it can't be applied. So recreate the whole replication again. It runs fine for a while but again, the sp_MSupd sp starts running on the target server and the replication goes down again. I don't know how to fix this. any advices?   Regards

SQL Server 2005 Merge Replication

We did the following: We setup a database with all of standard tables, views, etc. We then put in place merge replication for a few of the tables within this database, including a tables called "Areas". We can execute SQL statements to insert rows into this table fine. We then applied an update script for this database to change a few tables by adding fields and changing indexes, etc.  We didn't change the "Areas" table though. After the update to the database structure, we get an error when trying to insert into this "Areas" table. The error message was the following: The insert failed. It conflicted with an identity range check constraint in database 'AETest', replicated table 'dbo.Areas', column 'AREPrimaryId'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.  The statement has been terminated.   What would cause such a problem and how do we resolve it?  The structure of the "Areas" table before and after the update is the following: USE   [AETest] GO /****** Object: Table [dbo].[Areas] Script Date: 08/24/2010 17:42:36 ******/ SET   ANSI_NULLS ON GO SET   QUOTED_IDENTIFIER ON GO SET   ANSI_PADDING ON

transactional replication falling behind. millions of transactions in sp_brosereplcmds

Hi, Hi All, we have transactional replication set up with all the agents running at source database. The synchronization status is stuck at "delivering replication transactions". The replication latency in replication monitor says excellent. when I check sp_browsereplcmds. it ever ends. It has over more than millions of rows of output. We were having trouble with this replciation recently because at the source db. they run a large batch. So we decided to schedule snapshot agent to run exactly after that batch but the snapshot doesn't seem to helping out with the latency. what should be done to resolve the issue. Please help. thanks.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend