Select Initial Data Synchronization Page (Always On Availability Group Wizards)

Article
05/17/2016

Applies to: SQL Server

Use the Always On Select Initial Data Synchronization page to indicate your preference for initial data synchronization of new secondary databases. This page is shared by three wizards-the New Availability Group Wizard, the Add Replica to Availability Group Wizard, and the Add Database to Availability Group Wizard.

The possible choices include Automatic seeding, Full database and log backup, Join only, or Skip initial data synchronization. Before you select Automatic seeding, Full, or Join only ensure that your environment meets the prerequisites.

Recommendations

Suspend log backup tasks for the primary databases during initial data synchronization.
For large database, full backup and restore operations can take extensive time and resources. In such cases, we recommend that you prepare secondary databases yourself. For more information, see To Prepare Secondary Databases Manually, later in this topic.
Full initial data synchronization requires you to specify a network share. Before you use a wizard to perform full initial data synchronization, we recommend that you implement a security plan for the access permissions on the network share folder. This precaution is important because potentially sensitive data in the backup file can be accessed by anyone who has a READ permission on the folder. Also, to protect your backup and restore operations, we recommend that you secure the network channels between every server instance that hosts an availability replica and the network share folder.

If your backup and restore operations must be highly secured, we recommend that you select either the Join only or Skip initial data synchronization option.

Automatic seeding

SQL Server automatically creates the secondary replicas for every database in the group. Automatic seeding requires that the data and log file paths are the same on every SQL Server instance participating in the group. Available on SQL Server 2016 (13.x) and later. See Automatically initialize Always On Availability group.

Full database and log backup

For each primary database, the Full database and log backup option performs several operations in one workflow: create a full and log backup of the primary database, create the corresponding secondary databases by restoring these backups on every server instance that is hosting a secondary replica, and join each secondary database to availability group.

Select this option only if your environment meets the following prerequisites for using full initial data synchronization, and you want the wizard to automatically start data synchronization.

Prerequisites for using full database and log backup initial data synchronization

All the database-file paths must be identical on every server instance that hosts a replica for the availability group.

Note

If the backup and restore file paths differ between the server instance where you run the wizard and any server instance that is to host a secondary replica. The backup and restore operations must be performed manually using the WITH MOVE option. For more information, see To Prepare Secondary Databases Manually, later in this topic.
No primary database name can exist on any server instance that hosts a secondary replica. This means that none of the new secondary databases can exist yet.
You will need to specify a network share in order for the wizard to create and access backups. For the primary replica, the account used to start the Database Engine must have read and write file-system permissions on a network share. For secondary replicas, the account must have read permission on the network share.

Important

The log backups will be part of your log backup chain. Store the log backup files appropriately.

If prerequisites are not met

The wizard cannot create the secondary databases for this availability group. For information on how to prepare them, see To Prepare Secondary Databases Manually, later in this topic.

If prerequisites are met

If these prerequisites are all met and you want the wizard to perform full initial data synchronization, select the Full database and log backup option and specify a network share. This will cause the wizard to create full database and log backups of every selected database and to place these backups on the network share that you specify. Then, on every server instance that hosts one of the new secondary replicas, the wizard will create the secondary databases by restoring backups using RESTORE WITH NORECOVERY. After creating each of the secondary databases, the wizard will join the new secondary database to the availability group. As soon as a secondary database is joined, data synchronizations starts on that database.

Specify a shared network location accessible by all replicas
To create and restore backups, the wizard requires that you specify a network share. The account used to start the Database Engine on each server instance that will host an availability replica must have read and write file-system permissions on the network share.

Important

The log backups will be part of your log backup chain. Store their backup files appropriately.

Join only

Select this option only if the new secondary databases already exist on each server instance that hosts a secondary replica for the availability group. For information about preparing secondary databases, see To Prepare Secondary Databases Manually, later in this section.

If you select Join only, the wizard will attempt to join each existing secondary database to the availability group.

Skip initial data synchronization

Select this option if you want to perform your own database and log backups of every primary database, restore them to every server instance that hosts a secondary replica. After you exit the wizard, you will then need to join every secondary database on every secondary replica.

Note

For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

To Prepare Secondary Databases Manually

To prepare secondary databases independently of any Always On availability groups wizard, you can use either of the following approaches:

Manually restore a recent database backup of the primary database using RESTORE WITH NORECOVERY, and then restore each subsequent log backup using RESTORE WITH NORECOVERY. If the primary and secondary databases have different file paths, you must use the WITH MOVE option. Perform this restore sequence on every server instance that hosts a secondary replica for the availability group. You can use Transact-SQL or PowerShell to perform these backup and restore operations.

For more information:

Manually Prepare a Secondary Database for an Availability Group (SQL Server)
If you are adding one or more log shipping primary databases to an availability group, you might be able to migrate one or more of the corresponding secondary databases from log shipping to Always On availability groups. For more information, see Prerequisites for Migrating from Log Shipping to Always On Availability Groups (SQL Server).

Note

After you have created all the secondary databases for the availability group, if you want to perform backups on secondary replicas, you will need to re-configure the automated backup preference of the availability group.

For more information:

Prerequisites for Migrating from Log Shipping to Always On Availability Groups (SQL Server)

Configure Backup on Availability Replicas (SQL Server)

After creating a secondary database, apply all current log backups to the new secondary database.

Optionally, you can prepare all the secondary databases before you run the wizard. Then, on the wizard's Specify Initial Data Synchronization page, select Join only to automatically join your new secondary databases to the availability group.

Share via