Solved

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

Posted on 2013-01-29
7
198 Views
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
Sue
0
Comment
Question by:rdbconcepts
7 Comments
 
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
  reconfigure
  
  exec sp_configure 
  
  exec sp_configure 'show advanced options', 0
  reconfigure

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.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 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"
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38837712
for sql 2012

SQL Server Replication
http://msdn.microsoft.com/en-us/library/ms151198.aspx
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 35

Expert Comment

by:David Todd
ID: 38837803
Hi,

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)

HTH
  David
0
 

Author Comment

by:rdbconcepts
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
sue
0
 

Author Closing Comment

by:rdbconcepts
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!

Sue
0
 
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).
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now