First, I would encourage you to read this document . Then I would recommend you to get familiar with HADR simulator.
Selecting the right hadr_syncmode for your production database is essential to get the optimal network throughput and performance from the hadr pair to satisfy the business service level agreement.
Consider the below factors, then play with the hadr simulator in several configurations with different sync modes, flush size, overhead/transferrate , tcp/ip send,receive buffer, hadr receive buffer and tabulate the results of the runs as you go. Pick the configuration that works best.
Few factors that influence the decision on choosing the hadr_syncmode.
Distance between Primary site and Standby site.
The general preference is to use SYNC or NEARSYNC for systems over LAN and ASYNC, SUPERASYNC for systems over WAN.
Log data generation on the Primary.
Defining the workload and estimating the amount of log data generated (and flush size) is
necessary to enable smooth log shipping and replay on standby. Approximately (or by
doing a quick test run on a standard database) estimate the number of write transactions
per second that will take place in your business and maximum amount of data
(transactional logs) written by each transaction.
Total data generated/sec = num. of transaction per sec * data per transaction
Hint: Use this to determine the flush size for your hadr simulator runs.
Note: The sync mode that is picked at the current time may not perform well later when the
business grows. Continuous monitoring, evaluating and designing should be done to keep
up with the growing business demands and service level agreements.
Selecting the right hadr_syncmode for your production database is essential to get the optimal network throughput and performance from the hadr pair to satisfy the business service level agreement.
Consider the below factors, then play with the hadr simulator in several configurations with different sync modes, flush size, overhead/transferrate , tcp/ip send,receive buffer, hadr receive buffer and tabulate the results of the runs as you go. Pick the configuration that works best.
Few factors that influence the decision on choosing the hadr_syncmode.
Distance between Primary site and Standby site.
The preference for sync mode at a high level would be
- SYNC if the primary and standby are located in the same data center.
- NEARSYNC if the primary and standby are located in different data centers but same city limits.
- ASYNC if the primary and standby are separated by great distances.
The general preference is to use SYNC or NEARSYNC for systems over LAN and ASYNC, SUPERASYNC for systems over WAN.
Log data generation on the Primary.
Defining the workload and estimating the amount of log data generated (and flush size) is
necessary to enable smooth log shipping and replay on standby. Approximately (or by
doing a quick test run on a standard database) estimate the number of write transactions
per second that will take place in your business and maximum amount of data
(transactional logs) written by each transaction.
Total data generated/sec = num. of transaction per sec * data per transaction
Hint: Use this to determine the flush size for your hadr simulator runs.
Note: The sync mode that is picked at the current time may not perform well later when the
business grows. Continuous monitoring, evaluating and designing should be done to keep
up with the growing business demands and service level agreements.
