SQL Scheduled Standby Mode

Owned by Vijayganesh Sivaprakasam
Last updated: Apr 04, 2024
3 min read

1. Introduction

The SQL Server standby mode is used to run reports and query the standby database, which is put into read-only mode. The transaction log backups are still transferred from primary to standby, but they are not restored to the standby database until the database is put back into restoring mode. From Version 11.7, the standby mode can be scheduled directly from the GUI. By default, the schedule is switched off.

2. Steps to Schedule

  1. Choose the SQL Server configuration for which the schedule has to be done ( 1 ). The next step is to provide a standby file directory, this directory will hold the snapshot of the database before switching it to read-only mode.

 

sched1.png

 

  1. Slide the option to enable the schedule ( 1 ). Then new schedules can be added by clicking “Add New Schedule” button ( 2 ).

 

sched2.png

 

 

  1. Schedule details are mentioned ( This is discussed in detail in next section)

  2. The start times and end times for the schedule.

  3. The duration in minutes that the standby database is put in read-only mode.

  4. Custom Names can be provided for the schedule.

 

 

The below image explains how to schedule the database configuration to open it in read only mode and provide the duration.

 

For example see the below image for the schedule ( 1 ) and the duration for the read only mode ( 2 ) and the schedule name ( 3 )

 

 

The schedule summary ( 1 ) clearly specifies the next schedule and duration of the schedule.

( 2 ) option to disable the schedule

( 3 ) Edit the schedule to change it

( 4 ) Delete the schedule

 

Another example of schedule. The database goes in to read only mode for 480mins ( 8 hrs) every weekday starting at 10 am.

 

Multiple schedules can be configured for the same configuration:

Random schedule example: