recent

Titulo

Data Replication Sucks When..

All most all corporations move data from one database to another or from one location to another for various purposes. There are various products like GoldenGate, Data Guard, Attunity Replicate, Tungsten and others for replicating data. I often hear one product is better than other and I don't want to deny that but what matters in the data replication is the design of the database and the replication strategies.

These are my replication best practices which I learnt the hard way so I and my colleagues can sleep without getting a single page for latencies, locked tables or other replication errors. I don't make any exception to these rules for any customers or clients because this is going to bite us and become a nightmare to support it. 
  1. Limit the number of tables to as low as possible. Don't replicate the whole schema or database using a single replication task. Divide them into many jobs and balance big and small tables in terms of volume and data size. 
  2. Don't replicate CLOB or LOB. Many replication tools either don't support or truncate CLOB/LOB column even though they advertise it.
  3. 1-1 table replication between the source and target table. The replication always should be the 1-1 table from source and target and it can't be from many tables in the source to one table in the target. Some tools support MERGE logic but this is a terrible idea when data volumes grow.
  4. Limit the number of Constraints in Target tables. PK is the only constraints that you need to avoid duplicate. Adding too many Constraints slow down the replication and those constraints check should happen in source and not target table.
  5. Absolutely No DDL Replication just DML Replication. Yes, most tool support this feature but I would disable these features to prevent the target table from being ALTERed, TRUNCATEd, and DROPped. 
  6. The table structure should be the same in both source and target. The source and target table should have the same table structures including data types and the only thing that can be different is the constraint on target tables. 
  7. Not your  ETL or Data Manipulation Tool. Remember, this is your replication tool, not your ETL tool. Even though they allow some kind of data manipulation, we should limit the data manipulation for data conversion from one database to another. The will and can slow down the replication.
  8. Don't Advertise real-time replication. There are no tools for real-time replication and never promise anyone regardless of data volumes. With the best database design and replication best practices, you can achieve close to near-real-time but there are so many other factors like network, database load, bandwidth which can increase latencies which you don't have control.
  9. No Replication within the same database. Yes, I have seen a replication happening from one schema to another schema within the same Oracle database. Similarly, in one database to another database within a SQL Server Instance. Don't set up this type of replication even though replication is supported within a database, use script or other database vendor provided tools. It causes a lot of latency and generates redo log that isn't necessarily causing overhead to the database.
  10. No Merge/UPSERT logic. This is similar to that of rule #3, repeating again to reinforce the practice
  11. Never enable Bi-Directional replication feature. This is the most dangerous replication practices of all and I would tell my client there is no such feature out there. Anyone has enabled bi-directional replication? Please tell us more on the project and how do you support when there is data mismatch issue? I would like to learn more and everyone here would like to know more.
Violating these Eleven Golden Replication rules may suffer into a loss of sleep, latency anxiety, replication PTSD and much, much more. I hope you use them at your own risk to benefit your health, the database health and make no exception to these rules. Oops, I just got a latency alert, I will see you after the latency is caught up.

Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

32 comments

  1. On the off chance that the exhibit isn't numerical, there isn't much we can do with the exception of perusing and composing its information. ExcelR Data Science Courses

    ReplyDelete

  2. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    data science course in guwahati

    ReplyDelete
  3. Hello, I have browsed most of your posts. This post is probably where I got the most useful information for my research. Thanks for posting, we can see more on this. Are you aware of any other websites on this subject.
    data science training
    data science course
    data science course in hyderabad

    ReplyDelete
  4. A good blog always comes-up with new and exciting information and while reading I have felt that this blog really has all those quality that qualify a blog to be a one.

    360gigitmg.com

    ReplyDelete
  5. This was certainly one of my preferred web journals. Each post distributed impressed me.
    data science courses in delhi

    ReplyDelete
  6. Very nice blog and articles. I am realy very happy to visit your blog. Now I am found which I actually want. I check your blog everyday and try to learn something from your blog. Thank you and waiting for your new post.

    data science course in India

    ReplyDelete
  7. A great service is that the mistake they make will be send to all other AI computers linked to them so that they will also not make that same mistake. data science course in india

    ReplyDelete
  8. I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!
    business analytics course

    ReplyDelete
  9. As always your articles do inspire me. Every single detail you have posted was great.
    data science certification in noida

    ReplyDelete
  10. Happy to visit your blog, I am by all accounts forward to more solid articles and I figure we as a whole wish to thank such huge numbers of good articles, blog to impart to us.
    data scientist course

    ReplyDelete

  11. I see the greatest contents on your blog and I extremely love reading them.
    Best Institute for Data Science in Hyderabad

    ReplyDelete
  12. Through this post, i do know that your smart information in fiddling with all the items was very useful. I advise that this can be the primary place wherever I notice problems i have been sorting out. you've got a creative nevertheless engaging approach of writing.
    data scientists training

    ReplyDelete
  13. If you are looking for Illinois license plate sticker renewals online, you have to go to the right place. We have the fastest Illinois license plate sticker renewals in the state.
    Data Science Course in Pune

    ReplyDelete
  14. Thanks for your nice post I really like it and appreciate it. My work is about Custom Vape Cartridge Boxes. If you need perfect quality boxes then you can visit our website.

    ReplyDelete
  15. It was a good experience to read about dangerous punctuation. Informative for everyone looking on the subject.
    data scientist training and placement in hyderabad

    ReplyDelete
  16. This website and I conceive this internet site is really informative ! Keep on putting up!
    ai course

    ReplyDelete
  17. I am sure that this is going to help a lot of individuals. Keep up the good work. It is highly convincing and I enjoyed going through the entire blog.
    data science course

    ReplyDelete
  18. Excellent content ,Thanks for sharing this .,
    Online teaching platform
    Online teaching tool

    Online teaching platform

    ReplyDelete
  19. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one.
    Continue posting. A debt of gratitude is in order for sharing.
    data science training in gwalior

    ReplyDelete
  20. Good to visit your weblog again, it has been months for me. Nicely this article that i've been waiting for so long. I will need this post to total my assignment in the college, and it has the exact same topic together with your write-up. Thanks, good share.
    data analytics course in hyderabad

    ReplyDelete
  21. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one.
    Continue posting. A debt of gratitude is in order for sharing.
    data analytics course in kolhapur

    ReplyDelete
  22. Thanks to all the contents that appear in this guide, those in charge of business management and administration can find out about the characteristics that Microsoft Dynamics 365 presents and the benefits that it brings to the different departments of the companies. Dynamics 365 Customer Service

    ReplyDelete
  23. Eight fundamental steps to use an online ERP
    Organize a suitable team: All software changes are traumatic for companies. This fact is more accentuated when it comes to a large company and complex software.
    If we start with a team that is enthusiastic about change and committed to the new software, it will be much easier to extend that encouragement to the rest of the organization.
    Incorporation by modules: It can also be efficient to incorporate a new online ERP by modules . Implement it only in one department and see the results and then carry out implementations in other departments.
    Good practices: When a new ERP in the cloud is incorporated into business management, it is time to re-imagine what the best operation of the company will be. If you just copy what you already have, you will not get the most out of the new software nor will it be a step forward for the company.
    Dynamics 365 Business Central Implementation
    NAV to Business Central Upgrade

    ReplyDelete
  24. "If you are also one of them and want to know what the companies demand from the data scientists to do in their organization, you have come to the right place.data science course in kolkata"

    ReplyDelete
  25. Thank you very much for letting me read this. It is incredibly beneficial and instructive, and I gained a lot from it. Data science course in bhopal

    ReplyDelete
  26. I see the greatest contents on your blog and I extremely love reading them.
    Google My Business Management | Business Listings Management

    ReplyDelete

Powered by Blogger.