We are trying to setup a production HADR database with no cluster and a Disaster Recovery site to replicate the live database and backup of the database.
A question was asked about the compression to keep the database size as small as possible to replicate the database in a small amount of time.
Anyone out there have experience with the compression ? Should we use row level compression or database backup compression or both ? There is always a overhead in both but which one is prefered ?. The system will be a 24/7 and data loss is not allowed.
Is there a prefered way to setup the database to get better performance ?
Since you're setting up HADR, backup size will only play a role during the initial setup. The price you pay for backup compression is an increase in backup time, so your overall savings might not be that great.
If you enable row compression, that translates into higher information density in the log files, as log records are also compressed. In other words, the throughput of the log transfer will be increased. However, if you plan to ship log files manually, the saving will come from having to deal with fewer log files (higher density) and not from the log file size (which won't change).
Thank you for the feedback. We will be copying/transferring nightly backup to the DR site. This is why we want the size to be small. I know that the backup size will increase over time.
Log files will also be copied. I think you mean the throughput of the log transfer to the HADR standby server will be increased effecting the performance.
How about setting up the database ?. We have no idea how big the database is going to be over time (We just can't get that info from the client). The assumption is that it will grow pretty quickly. What configuration settings would be most critical for the performance, tablespaces e.t.c.
I think you mean the throughput of the log transfer to the HADR standby server will be increased effecting the performance.
For compressed tables and indexes the corresponding log records will also be compressed, so you will be sending more log records per kilobyte of transferred data. Whether it will affect performance depends on your HADR synchronisation mode.
Originally Posted by mdx34
The assumption is that it will grow pretty quickly. What configuration settings would be most critical for the performance, tablespaces e.t.c.