This page describes steps needed to create Standby database for your SQL Server configuration.
1. Prerequisites
1.1 General Prerequisites
Before starting, Double check that you’re compliant to the prerequisites mentioned in the Create SQL Server configuration page: https://dbvisit.atlassian.net/wiki/spaces/DSMPV12/pages/4521033730/Create+SQL+Server+Configuration#1.-Considerations-and-Prerequisites
1.2 Prepare File Locations on Standby Server
In general, your SQL Server Standby database will have following files:
Datafiles (ROWS)
Log Files (LOG)
You can see you current primary database files by running following SQL (change to your database name or exclude the where clause to see all files):
select name,physical_name from sys.master_files where name like 'AdventureWorks2017%'; C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019.mdf C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_log.ldf C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\logmir2
You must ensure the same directories exist on your standby server as well. If you want to use custom directories, It is necessary to prepare the directories on standby server before starting to create standby database. The directories must have writable privileges for:
User who runs dbvagentmanager service on standby
SQL Server Instance service account
You can refer to Microsoft Documentation: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-file-system-permissions-for-database-engine-access?view=sql-server-ver16
3. Create Standby Database
The create Standby database process will create backup of your primary database on primary server, transfer files to standby server and restore.
Initiate the process by clicking on “Set Up Disaster Recovery” from the ACTIONS pane:
On the following screen you will be able to change various options for your SQL Server Standby database:
Number | Description | Setting Explanation |
|---|---|---|
1 | Use Transportable Media | When using transportable media, the process will automatically pause after the backup of primary database is created. Network transfer will be skipped and user will be prompted to manualy attach the media with backup to standby server. Afterwards, user can resume the process. See more details in the dedicated section on this page. |
2 | Full Backup Location | Directory on primary and standby server where full backup of your database will be created. This must be a local drive, NFS or Network Shares are not supported by SQL Server |
3 | Database File Locations | Choose locations for yoru standby database files, which you have prepared in step “1.2 Prepare File Locations on Standby Server” |
4 | Automated Standby Update | Enable Automatic sycnhronization and logshipping after the create standby database process is finished. |
5 | Observer | Enable Observer after the create standby database process is finished. More details on Observer: Observer / Automatic Failover |
6 | Start | Start the create standby database process. |
Once you start the process detailed task will appear in the task list:
And you will be able to click on to see the details:
Once the Standby Database is created, the status in the dashboard will look like so:
3. Create Standby Database with Transportable Media Option
When using transportable media the process will automatically pause after the backup of primary database is created. Network transfer will be skipped and user will be prompted to manualy attach the media with backup to standby server. Afterwards, user can resume the process.
To enable the transportable media, select the option (1).
Be sure to choose correct Full Backup Location (2) on standby as it’s not possible to change it after the backup is done. This is the location to which you will attach the transportable media on standby server.
When ready, start the process (3). All other options are the same as described in previous section:
You will be able to see the task for the backup step:
When you click on it to see details, it’ll give you instructions for next steps:
You will need to transfer the media to standby server and attach as the path you specified (1) and once that is done, you will be able to resume the restore (2)
To resume the restore, select “Resume Setting Up Disaster Recovery” from the ACTIONS pane:
You will be presented with the summary of setting you have used to initiate the process:
Clicking on start will initiate the restore and after the restore is done, the process is completed.
Comments