Frederick Tang Weblog

This is the first time I seen Google chuck an error, I thought about framing it – for a second.

Recently, several projects have come my way with the business requirement to replicate the data in the databases between two remote sites for site diversification reasons – when one data centre goes down, the other data centre with the uptodate data can still satisfy our customers. In typical management style, they have added that they want load balancing between the two data centres, allowing updates to the database from both sites – it’s just a question of why not? we have two data centres, and if we replicate the data synchronously, we can have two functioning sites! you beaut!

I will not delve into the validity of the business requirements in this article. However, I thought it was important to understand what data replication means for the database, and the network.

Let’s assume for the moment, that our business requirement is to replicate the data between two databases located in two remote sites. What does this mean for the system architect who is in charge of designing the solution? The business requirement must be quantifiable by three key parameters: Recovery Point Objective (RPO), Recovery Time Objective (RTO) and Rate of Change (RoC).

1. Recovery Point Objective refers to the amount of data loss acceptable by the business when system fails.
2. Recovery Time Objective refes to the length of time the system must become fully functional after a system failure.
3. Rate of Change refers to the amount of data change in a defined period in a system.

Given these three parameters, the system architect can then start looking at possible solutions and alternatives that would achieve these business requirements. It is then important to examine two questions:

1. What is my Recovery Point if we are to implement data replication, given the current network bandwidth?
2. Suppose the business wants to implement real time synchronization between the databases. What is the required network bandwidth to achieve this Zero Data Loss (ZDL) for replication? (ZDL means a RPO of 0)

To answer these question, let’s also assume that in our simplistic example, that our database regularly sees a rate of change of 20MB/s during normal daily operations, from 9am to 5pm. The network is sized to allow for 15MB/s. Further evaluation finds that there are no further activity after 5pm.

In other words, the amount of data needed to be replicated between the two databases is going at 20MB/s between 9am and 5pm – this means 72000MB per hour. The network bandwidth can only transfer at 15MB/s or 54000MB per hour. This means up that every hour between 9am and 5pm, 18000MB of the 72000MB generated in that hour could not be transferred (and is delayed). This aggregates to 144000MB by 5pm, i.e. the replication is 144000MB behind.

The network bandwidth is not capable of satisfying the volume of data needed for replication, and replication is slowly following behind as a result:

• 9am – 10am: 18000MB behind <– 20MB/s of data generated; 15MB/s transfer
• 10am – 11am: 36000MB behind
• 11am – 12pm: 54000MB behind
• 12pm – 1pm: 72000MB behind
• 1pm – 2pm: 90000MB behind
• 2pm – 3pm: 108000MB behind
• 3pm – 4pm: 126000MB behind
• 4pm – 5pm: 144000MB behind
• 5pm – 6pm: 86000MB behind <– 0MB/s of data generated; 15MB/s transfer
• 6pm – 7pm: 32000MB behind
• 7pm – 8pm: 0MB behind

As you can see, starting at 5pm the rate of change is 0MB/s and this allows replication to slowly catch up. By some time between 7-8pm (7:36pm to be precise), the replication catches up and all data is synchronized between the two databases.

To get back to our initial questions:

1. What is my Recovery Point if we are to implement data replication, given the current network bandwidth? In our simple example, if the main database fails at 5pm, the Recovery Point would be 3pm. This is because at 3pm, replication is behind by 108000MB and when network is transferring at 15MB/s, the data generated upto 3pm would be replicated to the other database at 5pm (2 hrs to transfer 108000MB). If the main database fails at 7:36pm, the Recovery Point would be 5pm – and this is the worse case scenario.
2. What is the required network bandwidth to achieve this Zero Data Loss (ZDL) for replication? In our simple example, the network bandwidth will need to be at least the same as the rate of change (rate of data that needs to be replicated) to achieve Zero Data Loss replication. When I say at least, we need to take into account other networking overheads, such as the internal messages the two databases need to communicate to each other to achieve replication.

Of course, the real life scenarios are much more complicated than the one presented here, but it is a good start. When the business starts handing down business requirements (e.g. need real time synchronization between our databases), what does it actually mean and how to best inform the solution architects so that they can select their solutions – and determining RPO, RTO and RoC gives them that information.

Furthermore, data replication delves into a lot more topics not covered here:

• Ways to replicate: Host-based, Server-based, Controller-based and SAN-based.
• Distances between locations
• Number of platforms and servers
• Available budget, e.g. floor space, staffing, storage, software licenses, security
• Application’s ability to handle data replication, with load sharing (e.g. primary key, sequence numbers).

The material I used for this post were drawn from these online articles – many thanks to these authors.

http://www.sun.com/storagetek/white-papers/data_replication_strategies.pdf
http://searchstorage.techtarget.com/tip/1,289483,sid5_gci874872,00.html
http://www.parallelprose.com/white_papers/CNT%20Replication..pdf

Maybe I will present some Oracle technologies for replicating data in my next post.

I am currently working as a (Oracle) Database Designer (Infrastructure) in a Telco. I felt it funny when one of attendees of a design meeting I attended said this, without looking at me at all… “I got a question… if anyone here is an experienced DBA can answer this…”. That felt weird.

Every now and then, I seem to do something that I might regret later. Thus, I would like to open up a series of posts – starting with this one, noting “my famous last words”…

“To go in thinking you are going to kick people’s arse, is to get your arse kicked.”

I won’t go into details of what happened, but in sport, there’s always someone better than you. Never go in (in anything), thinking you are the best. That’s when you are doomed to fail.

Recently, I had the honor to be the Best Man of a workmate’s wedding. Having completely clueless about what are the responsibilities, I searched websites and bought a book to read – “The Best Man’s Pocket Guide” by Steve Bryant. I would recommend this book to anyone.

I didn’t expect the book to provide me with every tip, but it’s intended as a guide – to know what to expect. It turned out that much of the day depended very much on thinking on the feet. I thought I would write a few paragraphs about my experiences, and things I wish I knew before the day… that the books or websites might not tell you.

Remember. Every wedding is different – these tips applied to me.

Just a bit about the background of the wedding. It was a church wedding in the afternoon, photo session after and reception in a function centre.

#1. Be prepared for a long day, and have plenty of energy for the day. You are going to tough it out – look after the families, bridesmaids, ushers and look good yourself.
#2. Arrive at the Groom’s house early. You might be expected to run errands just before heading for the church, e.g. shopping for some last minute stuff, last minute briefing on the wedding, dressing up… etc.
#3. Have plenty of ushers. After the service inside the church, we had a photo session – close families, extended families, work friends, uni friends… etc. Try to have ushers from both side of the family that actually knows their family. For example, when the photo call for aunts and uncles, these ushers would know exactly who the fetch. There should be enough ushers to keep lining up people for photos, this will ensure a time efficient photo session.
#4. Another thing I noticed about ushers on the day was, different families tend to ask these ushers for directions, and proceedings throughout the day. It would be good to arm these ushers with the necessary information. This adds to the last point, when some ushers are busy dispensing information to the crowd, there should still be enough ushers to assist in photo calls.
#5. Try to know exactly what’s going to happen on the day, and be confident when relaying information to other people. You know you can manage the wedding, but you got to convince other people that you know how to manage the wedding, within minutes of a conversation.
#6. Think on your feet. A plan is suppose to be a guide, things changes throughout the day, even in the space of an hour – roll with it.
#7. Think ahead of the schedule and act. I found it useful to start informing people for what’s going to happen next. This way they are prepared. For example, we had photos for each table during the Main course dinner. It was useful to inform the table they are up next (in a few minutes), so that they can finish chewing their food. This can also cater for elderly people.
#8. Track where your helpers are throughout the day. Helpers might be decorating the reception, getting the flowers, preparing music equipment, delivering props that might be used during the wedding to the reception venue. I found it useful to keep track where people are, so that they can coordinate with other helpers, or even help retrieve items if you (or others) forgotten about it.
#9. Delegate to your fellow groomsmen. You need all the trusty help you can get.
#10. Be prepared, be relaxed, be a gentlemen, be a joker (if it adds to the fun)!

Good luck to you.