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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Changes to use SQL Server Transactional Replication

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


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?



View Complete Post

More Related Resource Links

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 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.

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


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.

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...

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.

Sql Server 2008 R2 Replication

Dears, I am make a testing environment before implementing it into real world. I have 3 servers SQL 2008 R2, i configured 1 Publisher, 2 Subcribers with a merged replication. Everything is Ok. However, i have an ID field on Publisher which is autonumber(Start by 1, increment by 1). When I try to add new record on publisher, it takes it 1 by 1 i.e. 1-2-3.... But i try to add it on subscriber it starts with 20,000 i.e. 20001, 20002. I think this is logical for SQL, but can i define this number on the subscriber? So it will start on 200,000 for example? Another question, In LAN i can connect the subscriber to the publisher, it will connect to it directly, since it is broadcast. But in WAN environment how can the subscriber detects the publisher??? Your reply is highly appreciated, Thanks in advance,

Replication Wizard doesn't like SQL Server Alias

I tried creating a new replication but I get an error that says, "SQL Server replication requires the actual sever name to make the connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specifiy the actual server name, 'SomeMachineNumber'." I don't get this. Why can't I use an alias? I googled and found few articles but not sure if I understand what others are talking about. Here is how I have things set up and I'm hoping someone can explain to me what is happening. On my machine, I have the following alias set up. Alias Name: SQLDEV, Port No: 2433, Protocol: TCP/IP, Server: SomeMachineNumber In Mgmt Studio, I can connect to the server SomeMachineNumber by using the alias SQLDEV. However, if I try to create a new publication on SQLDEV, I get the above error. Now, just for the heck of it, I created another alias using the same properties as above except, I named the alias the same as the server, "SomeMachineNumber". I connect to the server in Mgmt Studio using the new alias, and now I am able to create the new publication. Does this make sense? Am I not still using an alias in the second example? Why does the replication seemingly like the alias "SomeMachineNumber", but not "SQLDEV"?

SQL Server Compact 4 replication library bug ?

I get the subscriber connection string from a SqlCeReplication object like so: public string GetFullConnectionString(string connectionString) { using (SqlCeReplication repl = new SqlCeReplication()) { repl.SubscriberConnectionString = connectionString; return repl.SubscriberConnectionString; } } With a connection string of: "Data Source=C:\data\nw(40).sdf" I get this from 3.5.8081: ssce:default lock timeout="5000";data source="C:\Data\nw.sdf";ssce:max database size="256";ssce:max buffer size="4096";ssce:temp file max size="256";ssce:enlist="True";ssce:default lock escalation="100";ssce:autoshrink threshold="60";ssce:flush interval="10"; But I get this from 4.0 CTP 1: "Autoshrink Threshold=\"60\";Case Sensitive=\"False\";Data Source=\"C:\\data\\nw40.sdf\";Default Lock Escalation=\"100\";Default Lock Timeout=\"5000\";Encrypt Database=\"False\";Encryption Mode=\"\";Enlist=\"True\";Mode=\"Read Write\";Flush Interval=\"10\";Locale Identifier=\"-1\";Max Buffer Size=\"4096\";Max Database Size=\"256\";Password=\"\";Persist Security Info=\"False\";Temp File Max Size=\"256\";Temp File Directory=\"

Replication between SQL Server 2005 and SQL Server 2008

Hi,    We were using SQL Server Transactional Replication between two SQL Server 2005 servers. Now we are going to upgrade the Publisher server to SQL Server 2008, Distributor Server remains as SQL Server 2005.     Can anyone let me know what is the challenges we need to face when configuring Replication between SQL Server 2005 and SQL Server 2008. Replication Method - Transactional Replication Distributor/Subscriber - SQL Server 2005 Publisher     -  SQL Server 208 Regards, Fazlu.

getting an e-mail notification when a transactional replication stucks

Hi all, I've SQL Server 2005 and a transactional replication scenario with one puplisher and 11 subscribers. Sometimes one of the subscriptions fails and a manual operation is required. Is there a way to get an e-mail notification whenever the replica fails or stucks or simply degrade its performances?
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