SQL Server AlwaysOn AG Automatic initialization (14)

Functional Testing

• Test automatic seeding changes to SQL Server 2017 data file disk layout requirements

Because the production environment holds data files that may reside on different volumes, and the instance names are different, the full path to the data file is different. To enable automatic seeding, reset the default data path to meet the 2017 disk layout requirements.

Assuming that the primary instance default path is C:\data\, the secondary instance default path is D:\data\

Master Instance File location	SQL Server Secondary Instance file location	SQL Server Secondary Instance file location
C:\data\	C:\data\	D:\data\
C:\data\group1\	C:\data\group1\	D:\data\group1\

Then, for SQL Server 2017, the data files are successfully deployed on a secondary instance to a different volume, with different instance names and file paths unaffected.

• Test automatic seeding configuration, pause, disable, enable, and do not remove database reinitialization of secondary replicas

It mainly tested scenarios such as creating an availability group with automatic seeding, temporarily preventing the primary replica from seeding more databases to a secondary replica, stopping automatic seeding of a secondary replica on an existing availability group, enabling automatic seeding of a secondary replica on an existing availability group, and so on.

• Test Backup recovery method automation scripts for PowerShell sqlps and SQL Server modules

The test automation script creates an availability group in SQL Server 2017.

For multi-instance servers, note the path order of the $psmodulepath. For example, if you first installed the default instance of SQL Server 2014 and then installed a named instance of SQL Server 2017, the Sqlps module that was loaded is actually SQL Server 2014 when you import-module sqlps the automation script. Some cmdlets for AG are not updated and will be error-free when executed. Either adjust the order of the Sqlps in the $psmodulepath, or specify the full path of the module when Import-module.

The Sqlps module for SQL Server 2017 is used for backward compatibility and is no longer updated, and errors are made when some cmdlets that create AG are executed. Download SQL Server module from powershellgallery.com, test function is normal.

• Test to perform log backups, Checkpoint, insert data, full backups during automatic seeding

Test in the automatic seeding process, can perform log backup, can manually execute the checkpoint command, can insert data, these operations are not affected. That is, it will not be affected when the business is initialized. If you do a full backup, the full backup will be blocked.

SQL Server AlwaysOn AG Automatic initialization (14)