SQL 2016 Automatic Seeding of Availability Groups

Datetime:2017-04-21 05:21:51         Topic: SQL          Share        Original >>
Here to See The Original Article!!!

–By Lori Brown

I came across something today that I did not know existed in AG’s, Automatic Seeding. This allows SQL to automatically create the secondary replicas for all databases in an AG. Pretty cool!! So if you set things up correctly when you set up your AG’s, you don’t have to worry about backing up databases and tlogs and taking them to the secondary replica, restoring and then getting the AG fully set up. It is a one stop shop. This is new in SQL 2016 only as far as I can tell.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group

You have to set up your AG by script but that is not too difficult.

—Run On Primary

CREATE AVAILABILITY GROUP [<availability_group_name>]

FOR DATABASE db1

REPLICA ON ‘<*primary_server*>’

WITH ( ENDPOINT_URL = N’TCP://<primary_server>.<fully_qualified_domain_name>:5022′ ,

FAILOVER_MO DE = AUTOMATIC ,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT ,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE ( ALLOW_CONNECTIONS = NO ),

SEEDING_MODE = AUTOMATIC ),

N'<secondary_server>’ WITH ( ENDPOINT_URL = N’TCP://<secondary_server>.<fully_qualified_domain_name>:5022′ ,

FAILOVER_MODE = AUTOMATIC ,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT ,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE ( ALLOW_CONNECTIONS = NO ),

SEEDING_MODE = AUTOMATIC );

GO

Of course you have to be aware that if you set this on an AG with large databases, this could cause an issue since SQL would be pushing an entire database across the network. There is trace flag 9567 that can help compress the data stream for AG’s using Automatic Seeding but there are some side effects of increased processor load that you need to be aware of.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/tune-compression-for-availability-group

We are setting up some new VM’s here at SQLRX and will be blogging later in much more depth on how this works.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com . We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com !








New