SqlScriptQuery

SqlScriptQuery

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
InstanceName	Key	String	Specifies the name of the SQL Server Database Engine instance. For the default instance specify 'MSSQLSERVER'.
GetQuery	Key	String	Full Query that will perform Get action. Any values returned by the T-SQL queries will also be returned by the cmdlet Get-DscConfiguration through the 'GetResult' property.
TestQuery	Key	String	Full Query that will perform Test action. Any script that does not throw an error or returns null is evaluated to true. The cmdlet Invoke-Sqlcmd treats T-SQL Print statements as verbose text, and will not cause the test to return false.
SetQuery	Key	String	Full Query that will perform Set action.
ServerName	Write	String	Specifies the host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
Credential	Write	PSCredential	The credentials to authenticate with, using SQL Authentication. To authenticate using Windows Authentication, assign the credentials to the built-in parameter 'PsDscRunAsCredential'. If both parameters 'Credential' and 'PsDscRunAsCredential' are not assigned, then SYSTEM account will be used to authenticate using Windows Authentication.
Variable	Write	StringArray[]	Specifies, as a string array, a scripting variable for use in the sql script, and sets a value for the variable. Use a Windows PowerShell array to specify multiple variables and their values. For more information how to use this, please go to the help documentation for Invoke-Sqlcmd
QueryTimeout	Write	UInt32	Specifies, as an integer, the number of seconds after which the T-SQL script execution will time out. In some SQL Server versions there is a bug in Invoke-Sqlcmd where the normal default value 0 (no timeout) is not respected and the default value is incorrectly set to 30 seconds.
GetResult	Read	StringArray[]	Contains the values returned from the T-SQL script provided in the parameter 'GetQuery' when cmdlet Get-DscConfiguration is run.

Description

The SqlScriptQuery DSC resource provides the means to run a user generated T-SQL script on the SQL Server instance. Three scripts are required; Get T-SQL script, Set T-SQL script and the Test T-SQL script.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server 2012 or later.
• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.

Known issues

All issues are not listed here, see here for all open issues.

Scripts

Get T-SQL Script (GetQuery)

The Get T-SQL script is used to query the status when running the cmdlet Get-DscConfiguration, and the result can be found in the property GetResult.

Test T-SQL Script (TestQuery)

The Test T-SQL script is used to test if the desired state is met. If Test T-SQL raises an error or returns any value other than 'null' the test fails, thus the Set T-SQL script is run.

Set T-SQL Script (SetQuery)

The Set T-SQL script performs the actual change when Test T-SQL script fails.

Examples

Example 1

This example shows how to run SQL script using SQL Authentication.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    Node localhost
    {
        SqlScriptQuery 'RunAsSqlCredential'
        {
            ServerName   = 'localhost'
            InstanceName = 'SQL2016'

            Credential   = $SqlCredential

            SetQuery     = 'Set query'
            TestQuery    = 'Test query'
            GetQuery     = 'Get query'
            Variable     = @('FilePath=C:\temp\log\AuditFiles')
        }
    }
}

Example 2

These two example shows how to run SQL script using Windows Authentication. First example shows how the resource is run as account SYSTEM. And the second example shows how the resource is run with a user account.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $WindowsCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    Node localhost
    {
        SqlScriptQuery 'RunAsSYSTEM'
        {
            ServerName   = 'localhost'
            InstanceName = 'SQL2016'

            SetQuery     = 'Set Query as System'
            TestQuery    = 'Test query as System'
            GetQuery     = 'Get query as System'
            Variable     = @('FilePath=C:\temp\log\AuditFiles')
        }

        SqlScriptQuery 'RunAsUser'
        {
            ServerName           = 'localhost'
            InstanceName         = 'SQL2016'

            SetQuery             = 'Set query as User'
            TestQuery            = 'Test query as User'
            GetQuery             = 'Get query as User'
            Variable             = @('FilePath=C:\temp\log\AuditFiles')

            PsDscRunAsCredential = $WindowsCredential
        }

        SqlScriptQuery 'RunAsUser-With30SecondTimeout'
        {
            ServerName           = 'localhost'
            InstanceName         = 'SQL2016'

            SetQuery             = 'Set query with query timeout'
            TestQuery            = 'Test query with query timeout'
            GetQuery             = 'Get query with query timeout'
            QueryTimeout         = 30
            Variable             = @('FilePath=C:\temp\log\AuditFiles')

            PsDscRunAsCredential = $WindowsCredential
        }
    }
}

Example 3

This example uses the Configuration Data to pass the Query Strings fo SqlScriptQuery Resource.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'PSDscResources' -ModuleVersion '2.12.0.0'
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlScriptQuery 'CreateDatabase_ScriptDatabase1'
        {
            ServerName           = $env:COMPUTERNAME
            InstanceName         = 'DSCTEST'

            GetQuery             = @'
SELECT Name FROM sys.databases WHERE Name = '$(DatabaseName)' FOR JSON AUTO
'@

            TestQuery            = @'
if (select count(name) from sys.databases where name = '$(DatabaseName)') = 0
BEGIN
    RAISERROR ('Did not find database [$(DatabaseName)]', 16, 1)
END
ELSE
BEGIN
    PRINT 'Found database [$(DatabaseName)]'
END
'@

            SetQuery             = @'
CREATE DATABASE [$(DatabaseName)]
'@

            Variable             = @(
                ('DatabaseName={0}' -f 'ScriptDatabase1')
            )

            QueryTimeout         = 30

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}