SqlAGReplica: Support automatic seeding

[randomnote1]

Details of the scenario you try and problem that is occurring:
xSQLServerAlwaysOnAvailabilityGroupReplica resource does not set the seeding_mode property in SQL 2016. This needs to be set to support automatic seeding.

The DSC configuration that is using the resource (as detailed as possible):
n/a

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
all

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
dev

[randomnote1]

I am unable to locate the seeding_mode property in SMO. So far the only place I've been able to locate it is in sys.availability_replicas.

[johlju]

Maybe this can be used. "Enable automatic seeding on an existing availability group" and "Stop automatic seeding" in this documentation.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group

I can't seem to find it in SMO either for SQL Server 2016.

[johlju]

I think the resource xSQLServerAlwaysOnAvailabilityGroup also need to have this parameter?

[johlju]

I also found this issue that could be good to know about when coding this
FIX: The SEEDING_MODE setting is ignored when you add an AlwaysOn AG replica to an existing AG in SQL Server 2016

[johlju]

For direct seeding, you must allow database creation on each secondary replica by calling ALTER AVAILABILITY GROUP with the GRANT CREATE ANY DATABASE option.
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql

Sorry for all the comments. Just dumping new knowledge from my research. :) I was curious when the property was missing from SMO. :)

[randomnote1]

No worries! I've been collecting stuff for this in the back of my head. Once I finish up the AG database resource, I'll wrap back around to this.

[Zuldan]

@johlju here is a nice little blog from Mike Fal on how to setup direct seeding. At the end of the blog he points out there is no Powershell or SMO support (like you've already discovered). He does have the T-SQL code to get Direct Seeding going. Hope this helps.

http://www.mikefal.net/2016/06/14/tsql2sday-sql-2016-direct-seeding/

[johlju]

@randomnote1 Sounds great! Looking forward to both. 😄

@Zuldan Thanks for providing the blog article!

[stummsft]

As of SQL 2017, SMO now contains support for the Seeding mode properties and they work even against down-level managed servers. Since we try to import the SqlServer module, this should make it available even when running locally on down-level servers. The DSC resource should check if the "IsSeedingModeSupported" property exists and is set to $true, and if so set the .SeedingMode property to "Automatic" if it is specified in the resource configuration. Optionally, throw an error if the resource property is supplied as "Automatic" but either IsSeedingModeSupported does not exist or is equal to $false.

[codykonior]

Having this is still desirable.

• Add a SeedingMode property to SqlAG which will fill out the SMO property if it's available.
• Any replica where SeedingMode = 'Auto' the ALTER AVAILABILITY GROUP GRANT CREATE ANY DATABASE will also be run on that replica.

So for setting up a new AG and adding in other AGs it will all work. For edge cases where SMO is out of date, or the server is unpatched, or down-level, that's not this resource's problem.

[garentsen]

when will this feature become available?

[johlju]

As soon as someone in the community send in a PR I'm happy to review a change that supports this. This open for anyone in the community to pick up.