Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Server Replication - Are there any flags/fields I need to change before using it?

Posted on 2013-01-29
Medium Priority
Last Modified: 2013-02-13
Hi everyone

I need to produce a read only, real time copy of a production system for reporting purposes.

So - having reviewed and discounted mirroring, snapshot copies, logshipping etc we were left with one way transactional replication.

This I was able to configure and had working - BUT. Turns out that there were changes needed to the instance that we werent aware of that then generated errors - the one we saw related to the size of lobs exceeding the maximum configured.

This we have fixed, but I would like to ask if there are any more fields / settings that we will need to change in order for the replication to work?

Many thanks
Question by:rdbconcepts
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38834026
If you're using SQL 2012 (you don't specify), you should consider AlwaysOn, which allows you to have a read-only secondary copy - then you get the advantage of clustering with failover, but you also get the added benefit of a real-time read-only copy of the data available for other processes.

That said, I've never run into any settings that needed to be changed in order to enable replication - but if you have a setting enabled in the published database, but not in the subscriber database (or at the server level, like max text replication size in your example), you may run into issues. To check, run the following script:

  exec sp_configure 'show advanced options', 1
  exec sp_configure 
  exec sp_configure 'show advanced options', 0

Open in new window

With the results from both servers, compare the "Config_value" column to ensure that they're both in sync. For any that don't match, make sure they're not somethign that impacts replication - like if the max memory doesn't match, that's okay, But if, say, the two digit year cutoff (or "clr enabled", for example) was different, that could cause a problem.
LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 38834212
Agree with Ryan's approach of comparing config settings.

Need to be aware that if subscriber DB is a different version / edition, some of those config settings will be different again. If the same version, then try to make all SQL Server specific entries the same.

Recursion level is one that has trapped me before with triggers.

There are other things like identities, triggers, XML and that type of thing (which you probably already know about - scroll down to Limitiations) :

And there are external factors to consider :

Have referred to this article before for "gotchas" :

And there is always plenty of good advice on the Replication team blog :

I think this might be one of those questions when / if you trip over something, might invoke "oh yeah, that too"
LVL 43

Expert Comment

by:Eugene Z
ID: 38837712
for sql 2012

SQL Server Replication
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 35

Expert Comment

by:David Todd
ID: 38837803

A replicated table with transactional replication needs a primary key. int identity is fine.

(Of course you have a fully normalised schema, but some of those reporting tables mightn't)


Author Comment

ID: 38838661
Thanks for your updates - sorry I dont know why I didnt post the version!

We are using 2008 R2 service pack 2.

I will do some reading up about the 2012 read only stuff and may suggest it as a future possibility.

I believe that all the primary keys etc are in place already so that part is fine.

Ill read the links today and hopefully be able to make the relevant changes known before we try again!

(Ill update the ticket later too)
Thanks again

Author Closing Comment

ID: 38883676
Thanks - the links were a great help.

Unfortunatly it turns out that we cant use replication at all - full db replication cant be used because the application truncates tables and table level replication cant be used because the application creates tables on the fly which would have to be included plus there are about 4000 of them.

Back to the drawing board!

LVL 51

Expert Comment

by:Mark Wills
ID: 38883802
Ouch... that is going to be painful. You might need to consider "rolling your own" which basically means creating your own scripts to replicate the necessary data.

That might take the form of Triggers, or, SQL scripts, or (as I have had to do before) inventing / adding a couple of flags so you can check date_time last modified and date_time synchronised.

Hope that drawing board gives you some more joy :)

And would really enjoy hearing back what you came up with (if you feel like it).

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question