What is the New Seeding Capability in SQL Server 2016 AlwaysOn AG?

If you have used SQL Server AlwaysOn availability groups, then you might remember various methods to initialize the database on secondary replica. Prior to SQL Server 2016 there were three methods available to initialize the availability database on secondary. Here is the screen where you can choose from the options.

In all of the above methods, we need to take a backup (full and transaction log) of the database from the primary replica and restore on the secondary replica. The only difference is the stage when we need to take the backup. If we choose the first option, UI will take the backup and do a restore as well. On the other hand, the second option means the backup/restore is already performed. The last option means that we can do a backup/restore and join in the availability group later. If we are working in a high secure environment, then, sometimes, due to this, we need to open ports by taking some exceptions from the IT team. In other words, we need to create a file share so that we can move the backups around.

The new enhancement available in SQL Server 2016 availability group is called direct seeding or automatic seeding. This would reduce the overhead of the above backup/restore step while creating an availability group. To achieve this, there is a new parameter introduced called SEEDING_MODE in CREATE AVAILABILITY GROUP as well as an ALTER AVAILABILITY GROUP. There are two possible values for this parameter, MANUAL and AUTOMATIC. The default behavior, which is equivalent to an earlier version of SQL Server, is MANUAL. The second option, AUTOMATIC, will use the new automated seeding method – it will do an automatic backup/restore of the database on the secondary to initialize them.

To demonstrate this, we have a database called DB_Seeding on primary replica. We create an availability group called AGSeeding. Notice the seeding_mode in the below command to utilize this new feature.

CREATE AVAILABILITY GROUP [AGSeeding]
WITH (
		AUTOMATED_BACKUP_PREFERENCE = SECONDARY
		,DB_FAILOVER = OFF
		,DTC_SUPPORT = NONE
		)
FOR DATABASE [DB_Seeding] REPLICA ON 
N'NODE1'
WITH (
		ENDPOINT_URL = N'TCP://NODE1.DOMAIN.COM:5022'
		,FAILOVER_MODE = MANUAL
		,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
		,BACKUP_PRIORITY = 50
		,SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
		,SEEDING_MODE = AUTOMATIC
		),
N'NODE2'
WITH (
		ENDPOINT_URL = N'TCP://NODE2.HADOMAIN.COM:5022'
		,FAILOVER_MODE = MANUAL
		,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
		,BACKUP_PRIORITY = 50
		,SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
		,SEEDING_MODE = AUTOMATIC
		);

Then we executed below on NODE2, secondary replica.

ALTER AVAILABILITY GROUP AGSeeding JOIN;

GO

This didn’t do anything. Why? Well, here is the message in the SQL Server ERRORLOG which tells us what to do.

2016-08-15 18:02:19.65 spid69s     Local availability replica for availability group ‘AGSeeding’ has not been granted permission to create databases, but has a SEEDING_MODE of AUTOMATIC. Use the ALTER AVAILABILITY GROUP … GRANT CREATE ANY DATABASE command to allow the creation of databases seeded by the primary availability replica.

This means that we need one more additional step as below. We need to give permission to AG to create the database

ALTER AVAILABILITY GROUP AGSeeding GRANT CREATE ANY DATABASE;

GO

As soon as above is executed, we would see below in SQL Server ERRORLOG on secondary replica.

2016-08-15 18:05:21.49 spid39s     Error: 911, Severity: 16, State: 1.

2016-08-15 18:05:21.49 spid39s     Database ‘DB_Seeding’ does not exist. Make sure that the name is entered correctly.

2016-08-15 18:05:21.63 spid48s     Processed 336 pages for database ‘DB_Seeding’, file ‘AGSeeing’ on file 1.

2016-08-15 18:05:21.63 spid48s     Processed 10 pages for database ‘DB_Seeding’, file ‘AGSeeing_log’ on file 1.

2016-08-15 18:05:21.63 spid48s     Starting up database ‘DB_Seeding’.

2016-08-15 18:05:21.64 spid48s     The database ‘DB_Seeding’ is marked RESTORING and is in a state that does not allow recovery to be run.

2016-08-15 18:05:21.65 Backup     Database was restored: Database: DB_Seeding, creation date(time): 2016/08/15(17:57:12), first LSN: 40:200:74, last LSN: 40:352:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{1131F904-ED9D-41D6-A811-18709AAAE308}’}). Informational message. No user action required.

2016-08-15 18:05:21.67 spid48s     RESTORE DATABASE successfully processed 346 pages in 0.005 seconds (539.843 MB/sec).

2016-08-15 18:05:21.67 Backup     RESTORE DATABASE successfully processed 346 pages in 0.005 seconds (539.843 MB/sec).

If we closely look at messages, database “DB_Seeding” was restored by itself. If we look at the type of file used for restoring a command, it’s VIRTUAL_DEVICE. This means that this feature uses VDI backup/restore technology to restore the database over an endpoint port.

This feature looks good, but the bigger question would be: what if we face some issues in the future and we want to know how much seeding is achieved? Is there any way to track the status? Well, there are many DMVs and Xevents which can help us collect more details about it.

If we look at the interface of the extended event, there is a new category called “dbseed” under the debug channel as shown below.

Here are few DMVs which can help us in identifying various details about the automatic seeding status. They are sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats

Important points: Before we conclude, we should point out some “things to remember”:

1. We can’t use the availability group wizard as of now to do this; we have to rely on T-SQL.
2. For this feature, compression of the backup is OFF by default, so we may need to use the trace flag 9567 to enable compression.
3. If this is a very active database, then the transaction log would keep on growing until seeding is complete. We should plan disk space accordingly.

To summarize, this feature is going to reduce the overhead of backup/restore in deploying new AG. There are some UI level enhancements needed in SQL Server Management Studio to fully support this feature.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.