Go Premium for a chance to win a PS4. Enter to Win


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
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) : 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"
LVL 43

Expert Comment

by:Eugene Z
ID: 38837712
for sql 2012

SQL Server Replication
Industry Leaders: 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!

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

782 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