New resource: SqlDebug

[bozho]

Hi all,

Is there an interest in adding a resource to manage SQL Server startup parameters? I think it would be particularly useful for setting hotfix trace flags (-T).

We have a need for this kind of resource, but I'd be more than happy to implement it as a part of this module.

[johlju]

It would be awesome if you could implement it in this module. Could you suggest a schema for the resource?

Please read the conversation here too https://github.com/PowerShell/xSQLServer/issues/110 if something can be reused.

[bozho]

I've read the #110 discussion and we can certainly use it as a starting point... I'm still not sure which approach would be better.

On one hand, multi-parameter approach makes it easy to specify trace flags. However, I don't see a nice way to handle other parameters this way.

Single-parameter approach seems simpler, but would involve a lot of essentially useless configuration code to specify several parameters.

I'm also thinking about what would be the best way to specify absent parameters in a general case. Trace flags are the only parameter that can be specified multiple times, so when removing a trace flag, you need to specify both the parameter name and value. Removing other parameters only requires their name.

I'd probably go with a MOF-based resource, since I'd like to specify ValueMap for supported parameters (-d, -e, -T, etc). I havent't found a way to use value maps for class-based resources.

Another approach would be to have separate properties for each of the supported parameters (trace flags would then simply be an array of integers). This would give us a bit of "strong typing" and we could easily perform data validation (e.g. we could check paths for -e, -d and -l parameters)

With that approach, Ensure="Present" would ensure that specified parameters are present, Ensure="Absent" would ensure that specified parameters are removed. Parameters that are not specified would be left alone.

Naming the properties might be a bit of a challenge, though :)

[johlju]

I prefer suggestion 3 over at #110.

I'm not fond over the idé of having '-d', '-e' etc in a value map. I much more would like each of these to have their own Parameter names. And I think it would be easier logic to control so each parameter are actually in desired state. But I might be wrong here. In the example of -d and -x I suggest the below.

[ClassVersion("1.0.0.0"), FriendlyName("SqlStartupParameter")]
class MSFT_SqlStartupParameter : OMI_BaseResource
{
    [Write, Description(""), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
    [Write, Description("SQL server host to connect to. Default value $env:COMPUTERNAME.")] String SQLServer;
    [Key, Description("SQL Server instance to connect to.")] String SQLInstanceName;
    [Write, Description("An array of trace flags that startup options must only have, replacing all other traceflags.")] Uint32[] TraceFlag;
    [Write, Description("An array of trace flags to be added to the existing trace flags.")] Uint32[] TraceFlagToInclude;
    [Write, Description("An array of trace flags to be removed from the existing trace flags.")] Uint32[] TraceFlagToExclude;
    [Write, Description("The fully qualified path for the master database file.")] String MasterDatabasePath;
    [Write, Description("Disables the following monitoring features; x, y, z , a, b.")] Boolean DisableMonitorFeatures;
    [Write, Description("Forces a restart of the Database Engine service and dependent services after the desired state is set. Default values is $false.")] Boolean RestartInstance;
}

I this is a good approach, then you could start naming the parameters you need for your particular use case. And the others could be added at a later time. :)

[bozho]

Do we need include/exclude trace flags if we have the Ensure property?

I'm also thinking that maybe we should not support -d, -e and -l parameters, since changing these parameters also involves stopping the SQL service, moving/copying the files and then restarting the service.

Maybe we could implement that functionality in the xSQLServerDatabase resource (error/agent log files location should be a separate resource). xSQLServerDatabase resource would then know how to move particular DBs (or create user DBs on specified paths) and alter startup parameters for the master DB.

[johlju]

Hmm... In this case, isn't it the Ensure parameter that is not needed? Ensure is used where you add an "object" when Ensure -eq 'Present' and remove the "object" when 'Ensure -eq 'Absent'.

I think we need an option to replace all trace flags if the user wants "only these, and no other trace flags" (if someone adds a trace flag, TraceFlag will reset the state again). But we also need to support TraceFlagToInclude to include or/and TraceFlagToExclude to exclude one or more trace flags (keeping the existing). Though TraceFlagToInclude and TraceFlagToExclude can not be used at the same time as TraceFlag.

If we would use only TraceFlag together with Ensure -eq 'Present', then we must either always replace all trace flags or always add the trace flags. If Ensure -eq 'Absent' then in this case of we "replaced all", we would remove all, and there will be no trace flags (even if there were some before). If Ensure -eq 'Absent' then in this case of we "added all", then we remove only those trace flags set in the parameter. Using only TraceFlag and Ensure the user can never say "these, and only these" for one instance, and in another instance "I just want to add this, and remove this (if it exist)".

Regarding -d, -e, and -l. I'm all for that. Let's see if they fit better in another resource.

[bozho]

@johlju I haven't forgotten about this, but I'd like to concentrate on solving #220 first. I could also use a bit more time to think about all the things I'd like to do here, but I generally agree with your latest comment.

[stale[bot]]

This issue has been automatically marked as needs more information because it has not had activity from the community in the last 30 days. It will be closed if no further activity occurs within 10 days. If the issue is label with any of the work labels (e.g bug, enhancement, documentation, or tests) the issue will not auto-close.

[johlju]

The PR #1640 introduced a new resource for trace flags and suggested that the rest of the properties suggested in this issue is added as a resource SqlDebug.

[johlju]

New suggestion moving from SqlStartupParameter to SqlDebug.

[ClassVersion("1.0.0.0"), FriendlyName("SqlDebug")]
class MSFT_SqlDebug : OMI_BaseResource
{
    [Write, Description("SQL server host to connect to. Default value $env:COMPUTERNAME.")] String ServerName;
    [Key, Description("SQL Server instance to connect to.")] String InstanceName;
    [Write, Description("The fully qualified path for the master database file.")] String MasterDatabasePath;
    [Write, Description("Disables the following monitoring features; x, y, z , a, b.")] Boolean DisableMonitorFeatures;
    [Write, Description("Forces a restart of the Database Engine service and dependent services after the desired state is set. Default values is $false.")] Boolean RestartInstance;
}