Miscellaneous SQL Server Functions

1 1. Backup & Send Logs
2 2. Apply Logs
3 3. Automated Standby Update
4 4. Standby Update Delay
5 5. Synchronize Logins
6 6. Change Database State

1. Backup & Send Logs

This option can be used to backup the transaction logs and then send them to standby manually. This usually happens when there are heavy transactions happening in primary and before truncating the logs, the transaction logs can be manually backed up and sent to standby for applying.

2. Apply Logs

All the transaction logs that are sent to standby are automatically applied when the automated standby update is enabled. If not the transaction logs can be manually applied using the Apply Logs option.

3. Automated Standby Update

This option is enabled by default when the DR database is created. This ensures that the transaction logs are backed up and automatically sent from primary to standby and restored on standby. This option by default is set to 300sec. If this option is disabled the options 1 and 2 must be used to backup send logs and restore the logs on standby.

4. Standby Update Delay

This option by default is set to zero meaning it's disabled. This option can be used to specify a Delay (also known as a LAG or planned GAP) between the primary and standby database. The backed up transaction logs will be sent to the standby database as normal (based on the schedule used) but if this value is set, Standby multiplatform will restore the standby database using the delay and wait for the delay for the transaction logs to be applied based on the time delay, so the same transaction logs can be restored twice due to the time delay.

The transaction log backups are still taken from the primary based on the automated standby update schedule and transferred to standby, but the logs are restored only based only on the update delay set.

If you want to use this option to enable a delay in applying archived redo on the standby, it is required that the timezone on the Primary and Standby operating systems be exactly the same.

Delay Log Application is set to 20mins ( 1 ) in this example. and click on set ( 2 ) to confirm the value set for the delay.

5. Synchronize Logins

This option is very useful to synchronize newly created logins from Primary to standby periodically, so when activation or switchover is done, the login and that are mapped to users can be created in the standby server. If this is not done the users part of the database that are not mapped to logins would become an orphan login.

The user in primary ( 1 ) with a mapped login ( 2 ) that is not available in standby, click on the arrow to sync the logins with users.

6. Change Database State

This option is used to bring the primary database offline or online. This option is also used to offline the activated standby database when the recovery from failover action is performed.

Performing this action can disconnect the application from the database and cause downtime. Take caution when performing this.

The standby database status cannot be changed, since the database is in restoring mode. The standby database status would change to READONLY mode when we use the switch to standby mode option and then this option can be used to take the database offline or back online.

The primary database can be taken offline and brought back online or even restarted with this option.

( 1 ) The option is greyed out since the database is already online

( 2 ) Click this option to bring the database offline

( 3 ) Click this option to restart the database.