April 18, 2017 at 9:04 pm #68892
I see a number of online posts on the subject of installing SQL using the DSC framework, but it seems to me that the use of a configurationfile.ini file, is useful only in the initial push install of sql, and not in any subsequent state maintenance pull scenario, unless the intention is to have them all checked during a change/repair attempt where no action is taken if all the options are present. I see all these options available in a SQL Server 2016 Configuration.ini File:
; Agree to terms
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
; Detailed help for command line argument ENU has not been defined yet.
; Parameter that controls the user interface behavior.
; Setup will not display any user interface.
; Setup will display progress only, without any user interaction.
; Specify whether SQL Server Setup should discover and include product updates.
; Specifies features to install, uninstall, or upgrade.
; Specify the location where SQL Server Setup will obtain product updates.
; Displays the command line parameters usage
; Specifies that the detailed Setup log should be piped to the console.
; Specifies that Setup should install into WOW64.
; Specify the root installation directory for shared components.
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
; Specify the root installation directory for the WOW64 shared components.
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express Editions.
; Specify the Instance ID for the SQL Server features you have specified.
; Specify that SQL Server feature usage data can be collected and sent to Microsoft.
; Specify if errors can be reported to Microsoft to improve future SQL Server releases.
; Specify the installation directory.
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
; Agent account name
; Auto-start service after installation.
; CM brick TCP communication port
; How matrix will use private networks
; How inter brick communication will be protected
; TCP port used by the CM brick
; Startup type for the SQL Server service.
; Level to enable FILESTREAM feature at (0, 1, 2 or 3).
; Set to "1" to enable RANU for SQL Server Express.
; Specifies a Windows collation or an SQL collation to use for the Database Engine.
; Account for SQL Server service: Domain\User or system account.
; Windows account(s) to provision as SQL Server system administrators.
; Default directory for the Database Engine backup files.
; Default directory for the Database Engine user databases.
; Default directory for the Database Engine user database logs.
; Directory for Database Engine TempDB files.
; Provision current user as a Database Engine system administrator for SQL Server 2012 Express.
; Specify 0 to disable or 1 to enable the TCP/IP protocol.
; Specify 0 to disable or 1 to enable the Named Pipes protocol.
; Startup type for Browser Service.
; Specifies which account the report server NT service should execute under.
; Specifies how the startup mode of the report server NT service.
In the sample scripts in xSQLServer, I see one script with these options which can be included:
SourcePath = $Node.SourcePath
SetupCredential = $Node.InstallerServiceAccount
InstanceName = $Node.InstanceName
Features = $Node.Features
SQLSysAdminAccounts = $Node.AdminAccount
SQLSvcAccount = $Node.InstallerServiceAccount
InstallSharedDir = "G:\Program Files\Microsoft SQL Server"
InstallSharedWOWDir = "G:\Program Files (x86)\Microsoft SQL Server"
InstanceDir = "G:\Program Files\Microsoft SQL Server"
InstallSQLDataDir = "G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
SQLUserDBDir = "G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
SQLUserDBLogDir = "L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
SQLTempDBDir = "T:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
SQLTempDBLogDir = "L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
SQLBackupDir = "G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
(yes, I realize that mssql11.mssqlserver would be mssql13.mssqlserver for SQL Server 2016.)
I have several questions:
How can I find out how many of the options available in a configuration.ini file are also available directly using a DSC resource such as xSQLServer?
Does the use of these options in a DSC script (as they are in the examples) mean they have the "test, set, get" capabilities needed for state maintenance verification and drift control, the same way the windowsfeature, script, file, registry and other types of DSC configuration state settings do ?
I do see that there are options in MSFT_xSQLServerConfiguration.psm1 which have get, test, set capabilities.
I see a couple of examples using the "file" and "script" combination to get the .iso copied, mounted, and sql command executed. Are there any newer methods than that combination?
Last: are there online sources anyone would recommend I where I could find more information on these questions?
April 18, 2017 at 9:29 pm #68895
I'm thinking that the solution long term could be dual path: The initial install configuration PUSH, and a subset of options used for PULL state verification, drift control and auditing, as almost certainly, any significant change in the SQL configuration would break the functionality. Does that scenario make sense?
April 19, 2017 at 2:41 pm #68973
What kind of drift in configuration are you looking for to correct? As you are saying, if somthing changes in the SQL Engine it will probably brake.
I'm using xSQLServer resoruce only to install initial SQL-instance and all other nessesary sql components. I don't think the xSQLServer resoruce is intended to be used to correct drift in your sql installation.
You must be logged in to reply to this topic.