SQL and DSC

Tagged: ,

This topic contains 2 replies, has 2 voices, and was last updated by  Rickard 3 months, 4 weeks ago.

  • Author
    Posts
  • #68892

    Jim Bendtsen
    Participant

    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:

    [OPTIONS]
    ; Agree to terms
    IACCEPTSQLSERVERLICENSETERMS="True"
    ; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
    ACTION="Install"
    ; Detailed help for command line argument ENU has not been defined yet.
    ENU="True"
    ; Parameter that controls the user interface behavior.
    ; UIMODE="Normal"
    ; Setup will not display any user interface.
    QUIET="True"
    ; Setup will display progress only, without any user interaction.
    QUIETSIMPLE="False"
    ; Specify whether SQL Server Setup should discover and include product updates.
    UpdateEnabled="True"
    ; Specifies features to install, uninstall, or upgrade.
    FEATURES=SQLENGINE,RS,CONN
    ; Specify the location where SQL Server Setup will obtain product updates.
    UpdateSource="MU"
    ; Displays the command line parameters usage
    HELP="False"
    ; Specifies that the detailed Setup log should be piped to the console.
    INDICATEPROGRESS="False"
    ; Specifies that Setup should install into WOW64.
    X86="False"
    ; 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.
    INSTANCENAME="MSSQLSERVER"
    ; Specify the Instance ID for the SQL Server features you have specified.
    INSTANCEID="MSSQLSERVER"
    ; Specify that SQL Server feature usage data can be collected and sent to Microsoft.
    SQMREPORTING="True"
    ERRORREPORTING="False"
    ; RSInputSettings_RSInstallMode_Description
    RSINSTALLMODE="DefaultNativeMode"
    ; Specify if errors can be reported to Microsoft to improve future SQL Server releases.
    ERRORREPORTING="True"
    ; Specify the installation directory.
    INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
    ; Agent account name
    AGTSVCACCOUNT="NT Service\SQLSERVERAGENT"
    ; Auto-start service after installation.
    AGTSVCSTARTUPTYPE="Manual"
    ; CM brick TCP communication port
    COMMFABRICPORT="0"
    ; How matrix will use private networks
    COMMFABRICNETWORKLEVEL="0"
    ; How inter brick communication will be protected
    COMMFABRICENCRYPTION="0"
    ; TCP port used by the CM brick
    MATRIXCMBRICKCOMMPORT="0"
    ; Startup type for the SQL Server service.
    SQLSVCSTARTUPTYPE="Automatic"
    ; Level to enable FILESTREAM feature at (0, 1, 2 or 3).
    FILESTREAMLEVEL="0"
    ; Set to "1" to enable RANU for SQL Server Express.
    ENABLERANU="False"
    ; Specifies a Windows collation or an SQL collation to use for the Database Engine.
    SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
    ; Account for SQL Server service: Domain\User or system account.
    SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
    ; Windows account(s) to provision as SQL Server system administrators.
    SQLSYSADMINACCOUNTS="VIAMONSTRA\Administrator" "BUILTIN\Administrators"
    ; Default directory for the Database Engine backup files.
    SQLBACKUPDIR="G:\SQLBCK"
    ; Default directory for the Database Engine user databases.
    SQLUSERDBDIR="G:\SQLDB"
    ; Default directory for the Database Engine user database logs.
    SQLUSERDBLOGDIR="H:\SQLLOG"
    ; Directory for Database Engine TempDB files.
    SQLTEMPDBDIR="F:\SQLTMP"
    ; Provision current user as a Database Engine system administrator for SQL Server 2012 Express.
    ADDCURRENTUSERASSQLADMIN="False"
    ; Specify 0 to disable or 1 to enable the TCP/IP protocol.
    TCPENABLED="1"
    ; Specify 0 to disable or 1 to enable the Named Pipes protocol.
    NPENABLED="0"
    ; Startup type for Browser Service.
    BROWSERSVCSTARTUPTYPE="Disabled"
    ; Specifies which account the report server NT service should execute under.
    RSSVCACCOUNT="NT AUTHORITY\SYSTEM"
    ; Specifies how the startup mode of the report server NT service.
    RSSVCSTARTUPTYPE="Automatic"

    In the sample scripts in xSQLServer, I see one script with these options which can be included:

    xSqlServerSetup ($Node.NodeName)
    {
    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?

  • #68895

    Jim Bendtsen
    Participant

    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?

  • #68973

    Rickard
    Participant

    Hello,

    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.