• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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

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
1 Solution
Ryan McCauleyData and Analytics ManagerCommented:
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.
Mark WillsTopic AdvisorCommented:
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) : http://msdn.microsoft.com/en-us/library/ms152559.aspx

And there are external factors to consider : http://technet.microsoft.com/en-us/library/ms151254(v=sql.105).aspx

Have referred to this article before for "gotchas" : http://www.sqlsoldier.com/wp/sqlserver/undocumentedgotchasoftransactionalreplication

And there is always plenty of good advice on the Replication team blog : http://blogs.msdn.com/b/repltalk/

I think this might be one of those questions when / if you trip over something, might invoke "oh yeah, that too"
Eugene ZCommented:
for sql 2012

SQL Server Replication
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

David ToddSenior DBACommented:

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)

rdbconceptsAuthor Commented:
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
rdbconceptsAuthor Commented:
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!

Mark WillsTopic AdvisorCommented:
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).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now