The mlSQLServerDSC module is a complimentary DSC Module to add resources and functionality missing from xSqlServer. Over time I will try and move these resources into the official xSqlServer but this gives me a way of supporting these until such time.
To manually install the module, download the source code and unzip the contents directory to the '$env:ProgramFiles\WindowsPowerShell\Modules\mlSqlServerDSC' folder.
To install from the PowerShell gallery using PowerShellGet (in PowerShell 5.0) run the following command:
Find-Module -Name mlSqlServerDSC -Repository PSGallery | Install-ModuleTo confirm installation, run the below command and ensure you see the SQL Server DSC resources available:
Get-DscResource -Module mlSqlServerDSCThe minimum Windows Management Framework (PowerShell) version required is 5.0 or higher, which ships with Windows 10 or Windows Server 2016, but can also be installed on Windows 7 SP1, Windows 8.1, Windows Server 2008 R2 SP1, Windows Server 2012 and Windows Server 2012 R2.
Unless otherwise stated the resources are supported on SQL 2016 and higher. Refer to the individual resources for specifics regarding SQL Server version support.
You can review the Examples directory in the mlSQLServerDSC module for some general use scenarios for all of the resources that are in the module.
A full list of changes in each version can be found in the change log.
- xSQLServerDefaultDir resource to set the default directories.
- xSQLServerMoveDatabaseFiles resource to ensure a database has been moved from one location to another.
- xSQLServerSQLDataRoot sets the data root which is critical if you move the master database location after installation.
- xSQLServerStartupParam resource to manage the startup parameters for the SQL Server Data Engine.
- xSQLServerTempDB resource to manage the tempdb configuration (e.g. number of file) - in development
Resource to set the default directories for data files, log files, or backup files
- TBA
[String]SQLServer (Key): The name of server hosting the sql instance to manage.[String]SQLInstanceName (Key): The SQL Server instance to manage.[String]Name (Key): The setting to manage {BackupDirectory | DefaultData | DefaultLog}.[String]Ensure (Write): Included for consistency with other resources but esentially ignored.[String]Path (Write): The path to set the setting to.[Boolean]RestartService (Write): If $true will restart the server if the setting is changed. Allows you to minimise restarts.
Resource to move database files from one location to another. Designed with the purpose of relocating the databases from the default location to a custom location.
- TBA
[String]SQLServer (Key): The name of server hosting the sql instance to manage.[String]SQLInstanceName (Key): The SQL Server instance to manage.[String[]]Database (Key): The database(s) to move files for. Separate multiple databases with commas.[String]Ensure (Write): Included for consistency with other resources but esentially ignored.[String]DataPath (Write): The path to move data files.[String]LogPath (Write): The path to move trans log files.
Resource to set the data root which is critical if you move the master database location after installation.
The importance of this setting is explained in https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases
At this point SQL Server should run normally. However Microsoft recommends also adjusting the registry entry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup, where instance_ID is like MSSQL13.MSSQLSERVER. In that hive, change the SQLDataRoot value to the new path. Failure to update the registry can cause patching and upgrading to fail.
- TBA
[String]SQLServer (Key): The name of server hosting the sql instance to manage.[String]SQLInstanceName (Key): The SQL Server instance to manage.[String]Ensure (Write): Included for consistency with other resources but esentially ignored.[String]Path (Write): The path to set as default.[Boolean]RestartService (Write): If $true will restart the server if the setting is changed. Allows you to minimise
- TBA
Resource to manage the start up parameters of a SQL Server Instance. This can include traceflags and also setting the location of the master data and log files or the error log fle.
- TBA
[String]SQLServer (Key): The name of server hosting the sql instance to manage.[String]SQLInstanceName (Key): The SQL Server instance to manage.[String]ParameterName (Key): The parameter to configure (e.g. -t, -d).[String]Ensure (Write): Included for consistency with other resources but esentially ignored.[String]ParameterValue (Write): The value to set the parameter to.
- TBA
Resource to configure the TempDB in accordance to best practices. The SQL 2016 install wizard now sets this, however this resource will be used to retrofit older releases or post install configuration.
Resource is still in development and unreleased
- TBA
- TBA
- TBA