SqlAg/SqlAGReplica: AlwaysOn_Health XE session

[wsmelton]

Details of the scenario you tried and the problem that is occurring

SqlAg is the resource that will create an availability group on a given instance. Then you have SqlAgReplica that will add a replica.

A big step that is missing in the process is enabling the AlwaysOn_health extended event session. This is something the SQLPS or sqlserver modules commands do not handle directly either in an automatic fashion; meaning that creating an AG with either module will not automatically enable that XE session either.

If the wizard is used it does indeed enable that XE session automatically for the user. So as I see it we could do this one of two ways:

1. Update the SqlAg and SqlAgReplica resources to have an option to enable that XE session, something like EnableAlwaysOnHealth maybe.
2. Create a new resource (i.e. SqlAgAlwaysOnHealth).

I would favor it being added to the SqlAg and AgReplica resources because I would also say the property should be set to true by default. There are not generally any situation where I have seen that XE session should be disabled, but in the rare case it does having the property would benefit.

[wsmelton]

Which I guess if we opt to always have it enable that XE session the property for the SqlAg and SqlAGReplica resource could be called DisableAlwaysOnHealth.

[johlju]

To be able to control this extended events would be a great addition to the module. Thanks for submitting this issue.

I'm not sure this should be enabled by default, nor added to SqlAG or SqlAGReplica. 🤔 As stated here in AlwaysOn Extended Events when using the wizard is automatically started, but it also says if created in another way, it might need to be started manually. I think the wizard are meant for fast and easy way for users to setup AG. But if you want more control you use another way of configure this, like the PowerShell cmdlets in SqlServer/SqlPS, so unless the cmdlets of SqlServer or SQLPS module is using it I'm not sure the resources SqlAG and SqlAGReplica should enabled this by default either.
Neither am I sure this should be added to SqlAG and SqlAGReplica, since there are several extended events (see previous link), maybe we should have a resource SqlAGExtendedEvent that can handle all the extended events. It could start by handling this extended event (Health). But honestly, my knowledge around these Always On extended events is non-existent, so I there is not much to configure around these extended events (like only enabled/disabled), then maybe we could add a parameter for each extended event to SqlAG/SqlAGReplica?

[mdaniou]

We don't have anything to configure the XEvent's. The way I see it is that we should have several resources like :

• SqlExtendedEventSession (Would include the schedule)
• SqlExtendedEventEvent
• SqlExtendedEventTarget
• SqlExtendedEventEnable (optional)

All of them would rely on the namespace Microsoft.SqlServer.Management.XEvent https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.xevent?view=sqlserver-2016&viewFallbackFrom=sqlserver-2017

There might be some work by it could be worth it.

[johlju]

@mdaniou what would the suggested resources do, what is the difference between them? Just so I'm following you.

[wsmelton]

I'm not referring to all of the events and setting up a session. That link is wrong that the xe session is created when you create an AG. That xe session is configured and created out of the box when you install SQL.

This season is treated just like the system health session. If it the default trace for AG activity.

If I'm using DSC to build an AG the missing piece to complete the puzzle (to me) is enabling that XE session.

[mdaniou]

@wsmelton I understand your need. However I feel like having a resource to enable that XE is not the right to approach this. Instead we could have a resource that would enable any XE. Also other resources could be created to handle any XE needed in a SQL Server installation. I can imagine that there are DBAs out there with homemade XE that they install configure in any SQL instance they intall in there infra.

@johlju Well this is a just an idea... Thinking about it deeper, I would have :

SqlExtendedEvent : Create the session and the events it contains. (cpu, memory, etc) SqlExtendedEventTarget : Add a target to a XE session (file, buffer, etc) SqlExtendedEventState : Disable or Enable a XE session. (This one could be used to answer the @wsmelton 's problem)

[wsmelton]

Are you talking about covering XE for any version of SQL Server that supports it? That would mean 2008 up to 2017 and beyond. Each version has a completely separate list of events, targets only differ between a few.

I can tell you maintaining dbatools that coding for XE is not a simply task. It took us a while to get the commands written around XE in that module. There are no built in commands in SQLPS or SQLSERVER module for managing it so it would be all SMO coding.

[mdaniou]

Yes indeed, it would be SMO only. :)

For the compatibility, the events available can be checked in the DMVs so I suppose that the resource would be able to through an error if the event to set does not exist in the version of the instance.

I agree that it is quite an endeavor !

As a good start, a resource just to allow to disable/enable an XE would help on the issue you highlight.

[johlju]

I'm in favor of having a resource named SqlExtendedEvent with a property State. Then that resource can be extended to handle more scenarios. For example if there are more than one target, it's possible to make a new "subclass" in the schema, so we can send in an array of targets in the SqlExtendedEvent resource. See example of using classes here: https://github.com/PowerShell/xWebAdministration/blob/dev/DSCResources/MSFT_xWebsite/MSFT_xWebsite.schema.mof

If dbatools has this code already then maybe we could borrow code from there, my understanding it is MIT licensed as well?

So a good start would be making SqlExtendedEvent with a property 'Name' and State.