Trying to grasp replication better - need some input


  • Hello all,

    Never posted here before, so I apologize if I make some errors in posting.

    We use Replication, but it is my belief we do not use it smartly. The person that set it up did an excellent job of doing so, but is "self-taught' and to me, we do not leverage it smartly, or fully. So, I am taking time to bullet a few things we do that I wanted to hear your thoughts on and also see if anyone here recommends a good "hands-on" type education alternative that we can attend to learn more about how to leverage replication better, and ways to correct some issues we are facing.

    Again, new to here, so please forgive me if I do not provide enough information or if I give too much in some cases. Ask what you like and I will try to explain further. I am posting more of statements of issues, and would love input/thoughts to help me understand how to explain how it is wrong, or understand that it is not and "I" am wrong.

    1 - We originally set up a "replication server" to "offload the 'work' from the production servers because reporting services were creating locks and 'bogging down production'.

    MY Answer to that is along the lines of, MS Access and badly written queries cause the locks we are seeing, not "report services". LCM type locks are going to lock data, thus we are still seeing locks. Badly coded sql objects that are distributed either cross-server or cross databases also lend to issues.

    2 - A MAJOR one that perplexes me, is having a development --> test --> PROD environment, but coding objects that can only exist on the replicated server, not on the prod server of that line of databases, creates two actual prod databases, each different, and is highly inadvisable.

    An example is a replication server that houses replicated data from more than one prod server/database line, so they can share data without having to use linked server calls, created an environment that has people coding objects that cannot be promoted to actual PROD, but need to be promoted directly to the replicated database. IMO, that is not manageable. If someone snaps the database again and overwrites, who is to say it does not blow away the non-replicated database?

    3 - We have one database that does not have PKs (don't get me started..)...heh.. but it is a 3rd party thing and we need to replicate it.  We use transactional repl, but I used Snapshot and it worked, but the TRN file ballooned till it filled up the drive.

    I figure there has to be a way to avoid this, but have not figured that aspects out yet.. heh

    Okay, I wrote a lot, and do not want to make anyone angry or offend anyone. I will watch, read, and learn some things, I hope.

    Thank you all very much!


    Monday, July 9, 2018 5:00 PM

All replies

  • 1) The most popular use case of transactional replication is to offload reporting. Poorly written queries will still run badly on the subscriber, but will not interfere with the OLTP workload.

    Cross database queries should be fine, cross server queries will likely be problematic as the network hop will add some latency. You may run into problems with inefficient execution plans being used. However, a distributed partition view should result in processing being done on the remote server and aggregation being done on the calling server.

    2) synonyms should help you here - if I am understanding your problem correctly. Synonyms can point to objects which exist in a different location on servers. So on dev a synonym can point to an object on a database and server, but the synonym on production can point to different locations. This means that when you move from one server to another as long as the synonyms have the same name on both environments you will not need to make changes.

    3) you should not have a problem with the tlog blowing up using snapshot replication. There must be something else in the mix here.

    Monday, July 9, 2018 5:31 PM
  • What you are describing is a "reporting database".  You are using replication to populate a reporting database from several sources.  This is common and not really a concern.  The "reporting" database is different than your Prod database, optimized for a certain usage.

    Tables with no PK is difficult to manage in all cases.  Replication requires some kind of PK to track what changed.  If you can, I would suggest just putting a simple identity field on the table as the PK.  If you can't modify the table, you are restricted to snapshot replication or using SSIS or something to try and detect the changes and move them.  Snapshot should not have affected the log size on the source.  It does not use the log, it simply exports every row.  You can control the apply batch size on the target database, if you mean it filled up the target database log file.

    Monday, July 9, 2018 5:56 PM
  • Okay (Sorry again.. I say that a lot.. heh)...

    Let me take the report server aspect first.

    I totally get the use of synonyms and we have been trying to look at how to integrate that, but good call.

    To focus on one major concern first - If you have a database named "blah" and you have a test server, a dev server, and a prod server.Promotions of "an object" goes as normal through dev then test, but the object calls a database that has linked servers in dev and test, but not in prod.In prod, because linked servers are "frowned upon" and thus the 'solution' was to replicate the databases needed to the 'replication server'

    Now, this is not a "report server" all it started out as was a "replication server" because people thought that by "reading the data" on a non "prod" server, would keep them from locking users out of data, albeit they did not really re-write the objects to be "smarter".

    So, we replicated a few databases from a few prod servers, and now, what you end up with, is an object coming out of test that will not promote into the actual PROD database, but rather has to go to the replication server straight.

    I keep trying to warn that this is not manageable since it does not really exist in "prod" only in test. Now, since it does not write to any tables, they figure it is an acceptable evil, I barely knew how to spell LSQ (yes humor) 6 years ago, so I struggle to explain to them why this is bad, and struggle more with an alternative.

    Today, I let them know they have more vews in the replication server, by almost 40%, than in "prod".. .more prcs by 10% than in "prod".So, am I justified, or do others tend to make allowances like this and, how so?

    Secondly, these are the types of scenarios I would love to learn more about in a good class on replication in SQL. Does anyone know of a good quality course available that focuses specifically on how and when and why to use replication? Setting it up and using it more?

    In my mind, it can be a powerful and useful tool, but also a crutch that can kick you in the back end if you lose control of it, so to speak.

    Thanks guys. I hope I am not taking advantage or rambling too much. Been dying to learn more and finally made myself take the time to start focusing on this.


    Monday, July 9, 2018 6:52 PM
  • Ok.  Let me address your top concern.  Yes, I agree what you have is not ideal. You should not have different code in dev/test/Prod.  It should be the same.

    Your second concern about the "replication" instance having different objects, is ok if it is used for reporting only.  That is standard practice.  You need to ignore the fact it is 90% the same as Prod and treat it as a separate database.  It has its own objects (mostly the same as Prod) with its own indexes, views, etc.  You are trying to say they are the same, but they are not.  You need to treat them differently.  You also need a dev/test/prod version of the "reporting" database.

    Monday, July 9, 2018 7:02 PM
  • You didn't tell us what version you are on, but you might be better served with an Always On read-only replica, instead of replication.

    Monday, July 9, 2018 7:05 PM
  • I am still having a problem understanding your topology.

    It seems like in dev you might query a remote object using a linked server.

    In production you don't want to query the remote object because linked server are frowned upon, so the solution is to move whatever the linked servers reference to a replication server. This moves these objects close to the production server, I am assuming they are moved to the production server.

    If you can give us some examples - as you started to using the database name blah on the dev server - this would be helpful. Also I am not sure how these views come into play. Views by themselves encourage modular code and do not cause performance problems. I do spend significant time untangling them as modular code tends to encourage copy and paste programming which leads to badly performing code.

    Replication is considered to be a mature product and it will be hard to find courses which focus on it alone. A decade ago most companies would offer these courses, but they are not in demand today.

    I do notice pluralsight has a course.

    It is taught by Joseph Sack who does know replication well.

    Monday, July 9, 2018 7:31 PM
  • Very sorry guys, got busy and lost the link. Linked it now ;)

    FYI - if my discussion goes too far .. please let me know. I am really trying to "learn" and do not want the forum stats to go haywire because I am all over the board here. I have been wanting to ask all this stuff for a long time and please do not worry about me being offended if this is too much of a broad discussion for this forum. I totally get the stuff I am discussing is more of a training issue...just enjoy learning so, while I can.. I will keep asking.. and you guys cut me off or direct me where you need me:)

    First off: @Tom Phillips - Totally agree and get that. In fact, that was exactly what I proposed as an option this morning. "Either it goes to prod and is replicated OR we treat it as a 'ReportVersion-CopyOfProd' database. Then, we generate a dev and test version of that and they develop for either the reports or the application.

    Now, as with any developer, "give a mouse a cookie, they will want a glass of milk..."

    We cannot fix what we cannot fix, I get that.. the show must go on, but we can start the process of doing something right anytime we want, imo. So, to that end, we will have some duplication of objects "needs", I get that, but.. (and I am trying to find a good solid in-room class that will allow me to learn more and thus share more help with Replication, but..

    Is it possible, and/or a good practice, to do a more "focused" replication...i.e. we have some large databases and some old coders that are stuck in their ways and are, "we do not replicate that database...too large" and I try to convince them we only replicate the pieces we need to the server that needs them to allow objects to exist where needed.

    Now, that may not be a wise thing as it build a "web" of dependencies, I get that too. What I am trying to do is take bad coding and, over time, give them options for removing all linked servers and not just deny things.

    2 - VERSION - We have some .. (hope you are sitting down).. SQL 2000 servers we are phasing out.... still some dependencies, but it is what it is. Those are linked, but not a replication issue really.

    The versions in question are mostly SQL 2008 being replicated to SQL 2012 servers and we have a couple/few sql 2012 and 2014 servers replicated to 2012 or 2014.

    @Hilary Cotter - Sorry, I know, I am all over the place... heh. Let me try to answer, and please feel free to ask for more information.

    For lack of argument, we allowed linked server calls in the test and dev environment, because they were so out-dated on one major application that the "easiest call" was to let them get it there and then split a few ancient objects directly to the replicated server... problem was, the second you allow one thing, it becomes a standard and it is hard to get that through to a developer that has a "deadline". So, while we do not have a linked server call FROM the prod sql 2012 server to the sql 2012 replicated server... we DO have a linked server from the old 2008 sql server to he sql 2012 prod server. The applications are so inter tangled, they could not make it happen otherwise. We literally had an old object that had 75 join statements and 4-deep nested objects.

    Again, cannot fix everything, I get that, but as I learn more about replication, I can almost picture ways to help them slowly un-tangle this, but need to clarify what others do and how they create a replication matrix...the "dos and do nots" of replication.

    I have a lot of support, but I am pretty much alone on figuring out where to go and how to improve.. heh...well, not alone..I have all of you :P

    Thanks guys.

    Tuesday, July 10, 2018 3:31 PM
  • So it seems like dev makes linked server calls to production SQL 2012 OLTP server and you replicate your OLTP server to a replicated server? Or does dev make calls to the replicated server?

    Or is your issue with parallel topological - linked server calls in dev, and views in production. You want symmetrical code bases in both environments for standardization. If you are using views to "hide" where you are going against you will be using linked server calls under the covers - same with using synonyms.

    Tuesday, July 10, 2018 6:57 PM