-
Notifications
You must be signed in to change notification settings - Fork 224
SqlScript
Parameter | Attribute | DataType | Description | Allowed Values |
---|---|---|---|---|
Id | Key | String | Unique identifier for this resource. Set this to a unique value - allowing multiple resources with only the Variable parameter changing. The information entered is never used during the actual process of running the scripts. | |
InstanceName | Key | String | Specifies the name of the SQL Server Database Engine instance. For the default instance specify the value 'MSSQLSERVER' . |
|
SetFilePath | Key | String | Path to the T-SQL file that will perform Set action. | |
GetFilePath | Key | String | Path to the T-SQL file that will perform Get action. Any values returned by the T-SQL queries will also be returned when calling Get (for example by using the cmdlet Get-DscConfiguration ) through the 'GetResult' property. |
|
TestFilePath | Key | String | Path to the T-SQL file 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 . |
|
ServerName | Write | String | Specifies the host name of the SQL Server to be configured. Default value is the current computer name. | |
Credential | Write | PSCredential | The credentials to authenticate with, using SQL Server Authentication. To authenticate using Windows Authentication assign the credentials to the built-in parameter PsDscRunAsCredential. If neither of the parameters Credential or PsDscRunAsCredential are assigned, then the 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 T-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. | |
DisableVariables | Write | Boolean | Specifies, as a boolean, whether or not PowerShell will ignore Invoke-SqlCmd scripting variables that share a format such as $(variable_name) . 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. |
|
Encrypt | Write | String | Specifies how encryption should be enforced when using command Invoke-SqlCmd . When not specified, the default value is Mandatory . |
Mandatory , Optional , Strict
|
GetResult | Read | StringArray[] | Returns the result from the T-SQL script provided in the parameter GetFilePath when Get was called. |
The SqlScript
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.
-
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.
-
Parameter
Id
is a unique identifier for this resource, allowing multiple resources to be created, with only the Variable parameter changing. Set this to any unique value - the information entered is never used during the actual process of running the scripts. -
Parameter
Encrypt
controls whether the connection used byInvoke-SqlCmd
should enforce encryption. This parameter can only be used together with the module SqlServer v22.x (minimum v22.0.49-preview). The parameter will be ignored if an older major versions of the module SqlServer is used. Encryption is mandatory by default, which generates the following exception when the correct certificates are not present:A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
- There is a known problem running this resource using PowerShell 4.0. See issue #273 for more information.
All issues are not listed here, see here for all open issues.
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
.
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.
The Set T-SQL script performs the actual change when Test T-SQL script fails.
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
{
SqlScript 'RunAsSqlCredential'
{
Id = 'RunAsSqlCredential'
ServerName = 'localhost'
InstanceName = 'SQL2016'
Credential = $SqlCredential
SetFilePath = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript.sql'
TestFilePath = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript.sql'
GetFilePath = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript.sql'
Variable = @('FilePath=C:\temp\log\AuditFiles')
}
}
}
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
{
SqlScript 'RunAsSYSTEM'
{
Id = 'RunAsSYSTEM'
ServerName = 'localhost'
InstanceName = 'SQL2016'
SetFilePath = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript-AsSYSTEM.sql'
TestFilePath = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript-AsSYSTEM.sql'
GetFilePath = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript-AsSYSTEM.sql'
Variable = @('FilePath=C:\temp\log\AuditFiles')
}
SqlScript 'RunAsUser'
{
Id = 'RunAsUser'
ServerName = 'localhost'
InstanceName = 'SQL2016'
SetFilePath = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript-AsUSER.sql'
TestFilePath = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript-AsUSER.sql'
GetFilePath = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript-AsUSER.sql'
Variable = @('FilePath=C:\temp\log\AuditFiles')
PsDscRunAsCredential = $WindowsCredential
}
SqlScript 'RunAsUser-With30SecondTimeout'
{
Id = 'RunAsUser-With30SecondTimeout'
ServerName = 'localhost'
InstanceName = 'SQL2016'
SetFilePath = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript-WithQueryTimeout.sql'
TestFilePath = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript-WithQueryTimeout.sql'
GetFilePath = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript-WithQueryTimeout.sql'
QueryTimeout = 30
Variable = @('FilePath=C:\temp\log\AuditFiles')
PsDscRunAsCredential = $WindowsCredential
}
}
}
This example shows one way to create the SQL script files and how to run those files.
[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification='The variable $ConfigurationData is used by the HQRM test')]
param ()
$ConfigurationData = @{
AllNodes = @(
@{
NodeName = 'localhost'
ServerName = $env:COMPUTERNAME
InstanceName = 'DSCTEST'
DatabaseName = 'ScriptDatabase1'
GetSqlScriptPath = Join-Path -Path $env:SystemDrive -ChildPath ([System.IO.Path]::GetRandomFileName())
SetSqlScriptPath = Join-Path -Path $env:SystemDrive -ChildPath ([System.IO.Path]::GetRandomFileName())
TestSqlScriptPath = Join-Path -Path $env:SystemDrive -ChildPath ([System.IO.Path]::GetRandomFileName())
GetSqlScript = @'
SELECT Name FROM sys.databases WHERE Name = '$(DatabaseName)' FOR JSON AUTO
'@
TestSqlScript = @'
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
'@
SetSqlScript = @'
CREATE DATABASE [$(DatabaseName)]
'@
# Not recommended in production. Only set here to pass CI.
PsDscAllowPlainTextPassword = $true
}
)
}
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'xPSDesiredStateConfiguration' -ModuleVersion '9.1.0'
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
xScript 'CreateFile_GetSqlScript'
{
SetScript = {
$Using:Node.GetSqlScript | Out-File -FilePath $Using:Node.GetSqlScriptPath -Encoding ascii -NoClobber -Force
}
TestScript = {
<#
This takes the string of the $GetScript parameter and creates
a new script block (during runtime in the resource) and then
runs that script block.
#>
$getScriptResult = & ([ScriptBlock]::Create($GetScript))
return $getScriptResult.Result -eq $Using:Node.GetSqlScript
}
GetScript = {
$fileContent = $null
if (Test-Path -Path $Using:Node.GetSqlScriptPath)
{
$fileContent = Get-Content -Path $Using:Node.GetSqlScriptPath -Raw
}
return @{
Result = $fileContent
}
}
}
xScript 'CreateFile_TestSqlScript'
{
SetScript = {
$Using:Node.TestSqlScript | Out-File -FilePath $Using:Node.TestSqlScriptPath -Encoding ascii -NoClobber -Force
}
TestScript = {
$getScriptResult = & ([ScriptBlock]::Create($GetScript))
return $getScriptResult.Result -eq $Using:Node.TestSqlScript
}
GetScript = {
$fileContent = $null
if (Test-Path -Path $Using:Node.TestSqlScriptPath)
{
$fileContent = Get-Content -Path $Using:Node.TestSqlScriptPath -Raw
}
return @{
Result = $fileContent
}
}
}
Script 'CreateFile_SetSqlScript'
{
SetScript = {
$Using:Node.SetSqlScript | Out-File -FilePath $Using:Node.SetSqlScriptPath -Encoding ascii -NoClobber -Force
}
TestScript = {
$getScriptResult = & ([ScriptBlock]::Create($GetScript))
return $getScriptResult.Result -eq $Using:Node.SetSqlScript
}
GetScript = {
$fileContent = $null
if (Test-Path -Path $Using:Node.SetSqlScriptPath)
{
$fileContent = Get-Content -Path $Using:Node.SetSqlScriptPath -Raw
}
return @{
Result = $fileContent
}
}
}
SqlScript 'Integration_Test'
{
Id = 'Integration_Test'
ServerName = $Node.ServerName
InstanceName = $Node.InstanceName
GetFilePath = $Node.GetSqlScriptPath
TestFilePath = $Node.TestSqlScriptPath
SetFilePath = $Node.SetSqlScriptPath
Variable = @(
('DatabaseName={0}' -f $Node.DatabaseName)
)
QueryTimeout = 30
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
This example shows how to run SQL script with disabled variables.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
Node localhost
{
SqlScript 'RunWithDisabledVariables'
{
Id = 'RunWithDisabledVariables'
ServerName = 'localhost'
InstanceName = 'SQL2016'
Credential = $SqlCredential
SetFilePath = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript.sql'
TestFilePath = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript.sql'
GetFilePath = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript.sql'
DisableVariables = $true
}
}
}
- Add-SqlDscNode
- Add-SqlDscTraceFlag
- Complete-SqlDscFailoverCluster
- Complete-SqlDscImage
- Connect-SqlDscDatabaseEngine
- ConvertFrom-SqlDscDatabasePermission
- ConvertFrom-SqlDscServerPermission
- ConvertTo-SqlDscDatabasePermission
- ConvertTo-SqlDscServerPermission
- Disable-SqlDscAudit
- Disconnect-SqlDscDatabaseEngine
- Enable-SqlDscAudit
- Get-SqlDscAudit
- Get-SqlDscConfigurationOption
- Get-SqlDscDatabasePermission
- Get-SqlDscManagedComputer
- Get-SqlDscManagedComputerService
- Get-SqlDscPreferredModule
- Get-SqlDscServerPermission
- Get-SqlDscStartupParameter
- Get-SqlDscTraceFlag
- Import-SqlDscPreferredModule
- Initialize-SqlDscRebuildDatabase
- Install-SqlDscServer
- Invoke-SqlDscQuery
- New-SqlDscAudit
- Remove-SqlDscAudit
- Remove-SqlDscNode
- Remove-SqlDscTraceFlag
- Repair-SqlDscServer
- Save-SqlDscSqlServerMediaFile
- Set-SqlDscAudit
- Set-SqlDscDatabasePermission
- Set-SqlDscServerPermission
- Set-SqlDscStartupParameter
- Set-SqlDscTraceFlag
- Test-SqlDscIsDatabasePrincipal
- Test-SqlDscIsLogin
- Test-SqlDscIsSupportedFeature
- Uninstall-SqlDscServer
- SqlAG
- SqlAGDatabase
- SqlAgentAlert
- SqlAgentFailsafe
- SqlAgentOperator
- SqlAGListener
- SqlAGReplica
- SqlAlias
- SqlAlwaysOnService
- SqlAudit
- SqlConfiguration
- SqlDatabase
- SqlDatabaseDefaultLocation
- SqlDatabaseMail
- SqlDatabaseObjectPermission
- SqlDatabasePermission
- SqlDatabaseRole
- SqlDatabaseUser
- SqlEndpoint
- SqlEndpointPermission
- SqlLogin
- SqlMaxDop
- SqlMemory
- SqlPermission
- SqlProtocol
- SqlProtocolTcpIp
- SqlReplication
- SqlRole
- SqlRS
- SqlRSSetup
- SqlScript
- SqlScriptQuery
- SqlSecureConnection
- SqlServiceAccount
- SqlSetup
- SqlTraceFlag
- SqlWaitForAG
- SqlWindowsFirewall