SqlDatabaseFile: New resource proposal

[johlju]

SqlDatabaseFile

I would like to see a new resource SqlDatabaseFile which can add and modify data file and/or log file. Comments on this appreciated.

Description

I can see this resource take multiple names of files. So it can create one file or several files depending on the users requirement. But I think the resource should not create Log and Data files at the same time. User should implement two steps n the configuration file for adding both log and data files. I don't see this module to be able to move data or log file to a new path, maybe that can be a improvement. With modify I mean it can make sure sizes are always the same, if user wants that.

Usage

I personally want to be able to use this to modify the tempdb as well as other databases.

Schema suggestion

ServerName - The host name of the SQL Server. InstanceName - The instance name of the SQL Server. Default is 'MSSQLSERVER'. Ensure - If the file should me present or absent, and if it present if the sizes should be enforced. Valid values are 'Present', 'PresentAndSize', 'Absent'. Name - Name of the file. Will be used as name and display name. Path - The path where file will be created. It defaults to the path of the first file returned. FileType - The type of file to create. Valid values are 'DATA','LOG'. Defaults to 'DATA'. FileGroup - The file group to add the data file to. Defaults to 'PRIMARY'. Ignored if -FileType is 'LOG'. GrowthType - The auto growth type. Valid values are 'Percent' or 'MB'. Growth - The auto growth size for the file. Value is in percent or MB depending of -GrowthType. IntialSize - The initial size of the file in MB. If -Ensure is set to 'PresentAndSize' then the initial size will be tried to be enforced. MaxSize - The max size of the file in MB, or -1for unlimited size. If -Ensure is set to 'PresentAndSize' and -MaxSize does not equal -1 then the max size will be tried to be enforced.

[johlju]

Instead of using 'PresentAndSize' with the -Ensure parameter, which might be non-compliant with the other resources, one could use a switch parameter -EnforceSize instead. If set to $true sizes of initial size and max size will be tried to be enforced.

[Matticusau]

I like this idea and might even put some time to developing it.

I personally don't think there is a need to allow it to handle multiple files though. It really should be an individual file per resource, which would keep it inline with how other DSC resources function. In the case of TempDb what the recommendation could be would be to use a Foreach loop over the resource, this would then mean that the number of files could be dynamic per target too. It is also only a requirement for SQL 2014 and earlier as in SQL 2016 those files are setup correctly out of the box :)

[johlju]

I agree with individual file, that was my first thought as well. Also going with individual file makes the resource less complicated to code. Less logic.

If you want to code it, go ahead :) Let me know if I can be of any assistance.

[typozh]

Any plans on implementing this? I want to pre-stage databases for a SharePoint Farm and want to set the database size and growth settings.

[johlju]

I don't think anyone is running with this one yet. But it surely will be implemented as soon as someone is willing to take it on.

[randomnote1]

Thinking ahead for when this resource is complete...

This could be used in a composite resource for tempdb. This way we wouldn't have code duplication and we could (relatively) easily imitate the functionality that is built into the SQL 2016+ installer.

[randomnote1]

Should also add a parameter for DatabaseName. InstanceName, DatabaseName, and Name should be the key parameters.

[randomnote1]

Also suggest that the size parameters be passed in as bytes. This will easily allow the end user to use the native PowerShell size labels. (eg. 10MB, 1GB, etc.)

[johlju]

@randomnote1 Updated according to your suggestions. Also added a Force parameter instead of the triple value Ensure 😆

[ClassVersion("1.0.0.0"), FriendlyName("SqlDatabaseFile")]
class MSFT_SqlDatabaseFile : OMI_BaseResource
{
    [Key, Description("The name of the database to be configured.")] String DatabaseName;
    [Key, Description("The host name of the SQL Server to be configured.")] String SQLServer;
    [Key, Description("The name of the SQL instance to be configured.")] String SQLInstanceName;
    [Key, Description("The name of the file. Will be used as name and display name.")] String Name;
    [Write, Description("An enumerated value that describes if the file should be added ('Present') or removed ('Absent'). Default value is 'Present'."), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
    [Write, Description("If the size parameters should be enforced. Default value is $false.")] Boolean Force;
    [Write, Description("The path where file will be created. It defaults to the path of the first file returned.")] String Path;
    [Write, Description("The type of file to create. Valid values are 'Data','Log'. Defaults to 'Data'."), ValueMap{"Data","Log"}, Values{"Data","Log"}] String FileType;
    [Write, Description("The file group to add the data file to. Defaults to 'PRIMARY'. Ignored if parameter 'FileType' is set to 'Log'.")] String FileGroup;
    [Write, Description("The auto growth type. Valid values are 'Percent' or 'Megabyte'."), ValueMap{"Percent","Megabyte"}, Values{"Percent","Megabyte"}] String GrowthType;
    [Write, Description("The auto growth size for the file. Value is in percent or megabyte depending of what value the parameter 'GrowthType' is assigned.")] String Growth;
    [Write, Description("The initial size of the file in bytes. If parameter 'Force' is set to $true then the initial size will tried to be enforced.")] String IntialSize;
    [Write, Description("The max size of the file in bytes, or set to '-1' for unlimited size. If parameter 'Force' is set to $true and parameter 'MaxSize' does not equal '-1' then the max size will tried to be enforced.")] String MaxSize;
};

[randomnote1]

I made a couple tweaks. I updated the numbers to sint64 ([System.Int64] in PowerShell). This will allow some easier parameter validation. Also, the resource should have parameter validation on the parameters that looks like [ValidateRange([System.Int64](-1),[System.Int64]::MaxValue)].

I think the GrowthType property should enumerate all of the supported growth increments so that the expected numbers show up when viewed through SSMS. See this article for details.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options#arguments

[ClassVersion("1.0.0.0"), FriendlyName("SqlDatabaseFile")]
class MSFT_SqlDatabaseFile : OMI_BaseResource
{
    [Key, Description("The name of the database to be configured.")] String DatabaseName;
    [Key, Description("The host name of the SQL Server to be configured.")] String SQLServer;
    [Key, Description("The name of the SQL instance to be configured.")] String SQLInstanceName;
    [Key, Description("The name of the file. Will be used as name and display name.")] String Name;
    [Write, Description("An enumerated value that describes if the file should be added ('Present') or removed ('Absent'). Default value is 'Present'."), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
    [Write, Description("If the size parameters should be enforced. Default value is $false.")] Boolean Force;
    [Write, Description("The path where file will be created. It defaults to the path of the first file returned.")] String Path;
    [Write, Description("The type of file to create. Valid values are 'Data','Log'. Defaults to 'Data'."), ValueMap{"Data","Log"}, Values{"Data","Log"}] String FileType;
    [Write, Description("The file group to add the data file to. Defaults to 'PRIMARY'. Ignored if parameter 'FileType' is set to 'Log'.")] String FileGroup;
    [Write, Description("The auto growth type. Valid values are 'Percent', 'Kilobyte', 'Megabyte', 'Gigabyte', or 'Terabyte'."), ValueMap{"Percent","Kilobyte","Megabyte","Gigabyte","Terabyte"}, Values{"Percent","Kilobyte","Megabyte","Gigabyte","Terabyte"}] String GrowthType;
    [Write, Description("The auto growth size for the file. Value is in the unit of measurement specified in the parameter 'GrowthType'.")] sint64 Growth;
    [Write, Description("The initial size of the file in bytes. If parameter 'Force' is set to $true then the initial size will tried to be enforced.")] sint64 IntialSize;
    [Write, Description("The max size of the file in bytes, or set to '-1' for unlimited size. If parameter 'Force' is set to $true and parameter 'MaxSize' does not equal '-1' then the max size will tried to be enforced.")] String MaxSize;
};

[johlju]

Looking at the link, I see for FileGrowth it defaults to MB if none is set, so we should have MB as default here as well

The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB.

Also suggest changing 'Growth' to 'GrowthSize'.

I suggest adding a parameter 'FileGroupName' to be able to add the data file to a file group. Should this resource create and remove file groups since those need even more more parameters? I suggest No. I suggest that another new resource xSQLServerDatabaseFileGroup resource has the ability to add, remove and set default file group, and any other properties of a file group. If a new resource for file group seems like a good idea, then I can submit an issue for that.

Here is the schema with the suggestions above.

[ClassVersion("1.0.0.0"), FriendlyName("SqlDatabaseFile")]
class MSFT_SqlDatabaseFile : OMI_BaseResource
{
    [Key, Description("The name of the database to be configured.")] String DatabaseName;
    [Key, Description("The host name of the SQL Server to be configured.")] String SQLServer;
    [Key, Description("The name of the SQL instance to be configured.")] String SQLInstanceName;
    [Key, Description("The name of the file. Will be used as name and display name.")] String Name;
    [Write, Description("An enumerated value that describes if the file should be added ('Present') or removed ('Absent'). Default value is 'Present'."), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
    [Write, Description("If the size parameters should be enforced. Default value is $false.")] Boolean Force;
    [Write, Description("The path where file will be created. It defaults to the path of the first file returned.")] String Path;
    [Write, Description("The type of file to create. Valid values are 'Data','Log'. Defaults to 'Data'."), ValueMap{"Data","Log"}, Values{"Data","Log"}] String FileType;
    [Write, Description("The file group to add the data file to. Defaults to 'PRIMARY'. Ignored if parameter 'FileType' is set to 'Log'.")] String FileGroup;
    [Write, Description("The auto growth type. Valid values are 'Percent', 'Kilobyte', 'Megabyte', 'Gigabyte', or 'Terabyte'. Default value is 'Megabyte'."), ValueMap{"Percent","Kilobyte","Megabyte","Gigabyte","Terabyte"}, Values{"Percent","Kilobyte","Megabyte","Gigabyte","Terabyte"}] String GrowthType;
    [Write, Description("The auto growth size for the file. Value is in the unit of measurement specified in the parameter 'GrowthType'.")] sint64 GrowthSize;
    [Write, Description("The initial size of the file in bytes. If parameter 'Force' is set to $true then the initial size will tried to be enforced.")] sint64 IntialSize;
    [Write, Description("The max size of the file in bytes, or set to '-1' for unlimited size. If parameter 'Force' is set to $true and parameter 'MaxSize' does not equal '-1' then the max size will tried to be enforced.")] String MaxSize;
    [Write, Description("The filegroup to which to add the specified file. Ignored if file type is set to 'Log'. If not set, the file will be added to the default file group.")] String FileGroupName;
};

[randomnote1]

I concur that a separate a SqlDatabaseFileGroup resource should be created to manage file groups.

The file path default should be the default data or log file path. This will follow the expected default behavior.

I was thinking about how to designate a "Master Data File" (mdf) versus a "Secondary Data File" (ndf). Possibly another parameter called IsMaster (name is open for discussion). This would ensure the file is created with the "MDF" file extension and the database catalogue is updated to reflect the change. Possibly it should also update the old master file with a "NDF" extension.

[ClassVersion("1.0.0.0"), FriendlyName("SqlDatabaseFile")]
class MSFT_SqlDatabaseFile : OMI_BaseResource
{
    [Key, Description("The name of the database to be configured.")] String DatabaseName;
    [Key, Description("The host name of the SQL Server to be configured.")] String SQLServer;
    [Key, Description("The name of the SQL instance to be configured.")] String SQLInstanceName;
    [Key, Description("The name of the file. Will be used as name and display name.")] String Name;
    [Write, Description("An enumerated value that describes if the file should be added ('Present') or removed ('Absent'). Default value is 'Present'."), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
    [Write, Description("If the size parameters should be enforced. Default value is $false.")] Boolean Force;
    [Write, Description("The path where file will be created. If no value is specified, the file will be created in the default data or log path depending on the FileType specified.")] String Path;
    [Write, Description("The type of file to create. Valid values are 'Data','Log'. Defaults to 'Data'."), ValueMap{"Data","Log"}, Values{"Data","Log"}] String FileType;
    [Write, Description("The file group to add the data file to. Defaults to 'PRIMARY'. Ignored if parameter 'FileType' is set to 'Log'.")] String FileGroup;
    [Write, Description("The auto growth type. Valid values are 'Percent', 'Kilobyte', 'Megabyte', 'Gigabyte', or 'Terabyte'. Default value is 'Megabyte'."), ValueMap{"Percent","Kilobyte","Megabyte","Gigabyte","Terabyte"}, Values{"Percent","Kilobyte","Megabyte","Gigabyte","Terabyte"}] String GrowthType;
    [Write, Description("The auto growth size for the file. Value is in the unit of measurement specified in the parameter 'GrowthType'.")] sint64 GrowthSize;
    [Write, Description("The initial size of the file in bytes. If parameter 'Force' is set to $true then the initial size will tried to be enforced.")] sint64 IntialSize;
    [Write, Description("The max size of the file in bytes, or set to '-1' for unlimited size. If parameter 'Force' is set to $true and parameter 'MaxSize' does not equal '-1' then the max size will tried to be enforced.")] String MaxSize;
    [Write, Description("The filegroup to which to add the specified file. Ignored if file type is set to 'Log'. If not set, the file will be added to the default file group.")] String FileGroupName;
    [Write, Description("Designates the file as the master file (mdf). Ignored if the file type is set to 'Log'. If not set, the file extension will be 'ndf'.")] Boolean IsMaster;
};

[johlju]

An alternative to IsMaster is that we could change FileType to three values; 'Data', 'SecondaryData','Log' or 'PrimaryData','SecondaryData','Log'. But after thinking about it a while longer IsMaster will be more logical, keeping FileType as Data and Log only.

Maybe we should add a FilePath paramater which defaults to the default paths for either data or log depending of the value in FileType?

[johlju]

We need a FilePath parameter to support the below.

FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' }

So adding that to the suggested schema.

[ClassVersion("1.0.0.0"), FriendlyName("SqlDatabaseFile")]
class MSFT_SqlDatabaseFile : OMI_BaseResource
{
    [Key, Description("The name of the database to be configured.")] String DatabaseName;
    [Key, Description("The host name of the SQL Server to be configured.")] String SQLServer;
    [Key, Description("The name of the SQL instance to be configured.")] String SQLInstanceName;
    [Key, Description("The name of the file. Will be used as name and display name.")] String Name;
    [Write, Description("An enumerated value that describes if the file should be added ('Present') or removed ('Absent'). Default value is 'Present'."), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
    [Write, Description("If the size parameters should be enforced. Default value is $false.")] Boolean Force;
    [Write, Description("The path where file will be created. If no value is specified, the file will be created in the default data or log path depending on the FileType specified.")] String Path;
    [Write, Description("The type of file to create. Valid values are 'Data','Log'. Defaults to 'Data'."), ValueMap{"Data","Log"}, Values{"Data","Log"}] String FileType;
    [Write, Description("The file group to add the data file to. Defaults to 'PRIMARY'. Ignored if parameter 'FileType' is set to 'Log'.")] String FileGroup;
    [Write, Description("The auto growth type. Valid values are 'Percent', 'Kilobyte', 'Megabyte', 'Gigabyte', or 'Terabyte'. Default value is 'Megabyte'."), ValueMap{"Percent","Kilobyte","Megabyte","Gigabyte","Terabyte"}, Values{"Percent","Kilobyte","Megabyte","Gigabyte","Terabyte"}] String GrowthType;
    [Write, Description("The auto growth size for the file. Value is in the unit of measurement specified in the parameter 'GrowthType'.")] sint64 GrowthSize;
    [Write, Description("The initial size of the file in bytes. If parameter 'Force' is set to $true then the initial size will tried to be enforced.")] sint64 IntialSize;
    [Write, Description("The max size of the file in bytes, or set to '-1' for unlimited size. If parameter 'Force' is set to $true and parameter 'MaxSize' does not equal '-1' then the max size will tried to be enforced.")] String MaxSize;
    [Write, Description("The filegroup to which to add the specified file. Ignored if file type is set to 'Log'. If not set, the file will be added to the default file group.")] String FileGroupName;
    [Write, Description("Designates the file as the master file (mdf). Ignored if the file type is set to 'Log'. If not set, the file extension will be 'ndf'.")] Boolean IsMaster;
    [Write, Description("Should be set to either an existing path where to create the file, or set to an existing path where FILESTREAM data will be stored , or set to an existing path where memory-optimized data will be stored. Defaults to the default paths for either data or log depending of the value in FileType.")] String FilePath;
};

[rsganta]

Any samples on auto growth settings on databases ?

[johlju]

@rsganta this resource has not yet been created, so there is no resource that does that yet.

[rsganta]

Thank you Johlju. I understand that we can use sql query to set these in automated way. Is there a way to call sql quering using DSC and set these ? or Set this manually until this is being developed ? Thanks in advance.

[johlju]

You could try running a script using SqlScript resource.

[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.

[keenanincome]

I encountered the same problem and discovered the answer https://local-variables-blasted.github.io/pete/