re: transaction rate is 1000 per hour
Define 'transaction':
- 1000x single-row inserts ... RS won't break a sweat
- 1000x 30-million row updates with before/after images of 15KB size rows ... yeah, RS will have its hands full
From a strictly network perspective the biggest issue is going to be the volume (MBytes) of activity going over that network, and this has little to do with the *number* of transactions (per hour).
From a Repserver (txn processing) perspective the biggest issue is going to be the *size* of the (larger) transactions; RS can rip through a million 1-row UPDATEs quite easily, but processing a single 1 million-row UPDATE is a different animal and overall performance is going to depend on RS configurations.
re: transaction rate is very very high
1000 transactions (per hour) would not be considered as '(very very) high' so not sure at this point how to respond; generally speaking the system/disk/network admins will need to monitor for bottlenecks and figure out if the bottlenecks are due to system/disk/network configuration issues (eg, ASE/RS using a filesystem where journaling is unnecessarily enabled? slow(er) disks? SSDs overheating and stalling due to poor cooling solutions? misconfigured nics/switches/routers?)
It's also not clear (to me) at this point how 'very very high' is being measured ... cpu/disk activity on the ASE instance? volume of transaction log activity (eg, size of log dump files)? RS cpu/disk/network activity? something else?
--------------------
By no means a (definitive) answer but rather a mish-mash of ramblings that may or may not apply in this scenario ...
There is a Capacity Planning chapter in the Design guide that goes into some detail with regards to calculating the volume of bytes to be replicated and how this impacts Repserver's sizing requirements for memory, cpu, disk and network. Having said that ...
---------------------
A topic I don't see enough of at clients is the idea of reducing the volume of bytes to be replicated. Reduce the volume of bytes flowing through Repserver and you can reduce some of your system requirements while also reducing latency. This comes in many forms ...
- statement level replication; good: replicating a single UPDATE statement, in lieu of replicating the before/after images of the 15 million rows affected by the UPDATE (don't forget the associated 15 million individual-row UPDATE statements against the replicate database), effectively removes Repserver from the performance/latency bottleneck; Repserver's sole responsibility in this case is the replication of a few 100 bytes that make up the UPDATE statement
- statement level replication: bad: need to consider side affects of the UPDATE statement which may not generate the same results in the primary and replicate database; for example: triggers/defaults/computed-columns that reference local values (eg, getdate(), suser_name(), @@servername); trigger processing that you may, or may not, want firing in the replicate database; potential security issues with encrypted column access by the DSI/maintuser; some of these issues are addressable but would require modifications that may or may not be realistic (eg, stored proc wrappers to capture local values, replacement of computed columns with explicitly updated columns)
- statement level replication: bad: are there resources available to the primary database that may not be available to the replicate database? for example: reference databases/tables, or maybe even external processing, accessed by triggers
- stored proc replication: good/bad: same benefits/issues as with statement level replication
- with both statement-level/stored-proc replication you also need to keep in mind if the replicate invocation needs to be executed under the same login as was used in the primary (as opposed do the default DSI/maint-user)
- redesign processing to generate less transactional activity: one (banking) client had an end-of-day process that performed a couple dozen UPDATE statements, with each UPDATE statement affecting a couple million rows of data; 90-95% of the data hadn't changed (ie, before/after image of the data was the same) but the UPDATEs still updated the data with the downside being that all of these updated rows had to be replicated; this process generated 5GB of transaction log activity and caused replication latency to spike for upwards of 3+ hrs; through a mix of modfications (addition of a non-replicated scratch database for generating an after image of the data set; running a diff on the before/after images of data so as to perform UPDATEs on only those rows/columns that actually changed) I was able to cut transaction log activity from 5GB to 100-150MB, and eliminated replication latency; net takeaway: are there opportunities for process redesign that can reduce the volume of bytes that need to be replicated?
- design new processes with replication in mind: going forward make sure replication considerations are part of the overall process design
- minimal columns replication: this usually has to be addressed on a table-by-table basis and thus tends to be overlooked (ie, who has the time to perform this type of detailed analysis?); a table has 100 columns but only 4 columns were updated; do we really need to replicate all 100 columns of the before/after image? the answer is going to depend on whether or not all of that unchanged data is needed by downstream processing ... does a function string need to write the entire before image to a history/audit table? do you need the ability to configure the DSI for autocorrection (dsi_compile_enable = u2di)? net takeaway: from a replication perspective what are the heaviest hit tables and, assuming large volumes of UPDATE statements, how much of a row is actually updated?
- stop replicating unnecessary data: are you by any chance replicating tables that have no business use (eg, scratch tables used for intermediate processing); consider disabling replication of said tables (sp_setreptable/never) or possibly move them to a non-replicated database (though for heavy cross-database activity this may cause unwanted performance issues for PDS/PDB processing); another approach would be to disable replication of transactions (set replication off) on a case-by-case basis but ... this gets dangerous if (when?) this capability is misused and you (ultimately?) end up in a situation with 'missing' data in the replicate database
-------------------------
Other considerations:
- RS/ASE network packet sizing, especially over the LAN; are you using the largest packet size available? don't forget rsi_packet_size for RS-to-RS routes (obviously not an option for WS)
- other than ease of setup and the 'simple' switch active command, was there any reason why WS was chosen over MSA? I've only worked at one client that performed regular (every 2 weeks) switch active operations and that was a pain in the keister when a network hiccup caused the operation to go walkabout; of all the clients configured with WS as a backup/DR solution ... I can count on one hand the number of times we ran a switch active operation and those were during DR testing (ie, not a real live we-need-to-come-up-on-our-backup/DR-database-NOW scenario), and it was always a nail-biter waiting to see if the switch active went walkabout; net takeaway: it's a bit more setup, and an actual 'switch' requires a few more steps for the DBA, but MSA tends to be more robust as well as offers features/capabilities not available with WS (eg, RS instance on both ends of a long and/or slow network connection)
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.