Solved

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

Posted on 2013-01-29
7
232 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
[X]
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
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 43

Expert Comment

by:Eugene Z
ID: 38837712
for sql 2012

SQL Server Replication
http://msdn.microsoft.com/en-us/library/ms151198.aspx
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 56
T-SQL: Only Wanting One Record 8 56
semaphore timeout period has expired 1 22
Access PS SQLSERVER from powershell 1 25
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 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