dsccommunity / dsccommunity/SqlServerDsc
SqlQuery: New resource proposal
Nobody has claimed this yet.
- Dominant language
- PowerShell
- Stars
- 385
- Forks
- 224
- PR merge metrics
- No merged PRs in 30d
Description
Resource proposal
Suggest adding a new class-based resource SqlQuery that will deprecate SqlScript and SqlScriptQuery.
Proposed properties
| Property | Type qualifier | Data type | Description | Default value | Allowed values |
|---|---|---|---|---|---|
| TaskName | Key | String | Specifies the task name to uniquely identify this resource instance. | None | None |
| InstanceName | Key | String | Specifies the name of the SQL Server Database Engine instance. For the default instance specify the value 'MSSQLSERVER'. |
None | None |
| ServerName | Write | String | Specifies the host name of the SQL Server to be configured. Default value is the current computer name. | None | None |
| Credential | Write | String | Specifies the credential to use to connect to the SQL Server instance. If parameter *Credential' is not provided then the resource instance is run using the credential that runs the configuration. If neither of the parameters Credential or built-in PsDscRunAsCredential are assigned then the SYSTEM account will be used. | None | None |
| GetQuery | Write | String | Specifies the T-SQL query that will be used to get the current state. | None | None |
| GetQueryFilePath | Write | String | Specifies a path to the file that contain the T-SQL query that will be used to get the current state. | None | None |
| TestQuery | Write | String | Specifies the T-SQL query that will be used to test the current state against the desired state. | None | None |
| TestQueryFilePath | Write | String | Specifies a path to the file that contain the T-SQL query that will be used to test the current state against the desired state. | None | None |
| SetQuery | Write | String | Specifies the T-SQL query that will be used to set the desired state. | None | None |
| SetQueryFilePath | Write | String | Specifies a path to the file that contain the T-SQL query that will be used to set the desired state. | None | None |
| Variable | Write | String | Specifies a hash table (kay value pair) of variables with their values that will be replaced in the T-SQL queries. | None | None |
| StatementTimeout | Write | Int32 | Specifies the query timeout in seconds. Default 600 seconds (10 minutes). | None | None |
| Encrypt | Write | Boolean | Specifies whether encryption should be enforced. | None | None |
| Result | Read | System.String | Returns the result from the query when method Get was called. |
None | None |
Special considerations or limitations
As mentioned in the comment https://github.com/dsccommunity/SqlServerDsc/pull/1904#issuecomment-1518551360 it would help using different modules that provides the SMO assemblies if this module is not dependent on a specific command from a specific module (that also is dependent on SMO assemblies).
The new resource should use the command Invoke-SqlDscQuery. For Get() and Test it will pass the PassThru parameter to return the expected values. For Set() it will not pass the parameter PassThru since it will just execute and do not return any values.
If possible the T-SQL statement PRINT should be outputted as verbose messages (similar to what Invoke-SqlCmd does). This might require changes to Invoke-SqlDscQuery.
It should not be possible to specify both a query string and a query file for the same method, e.g. GetQuery and GetQueryFilePath. But it should be possible to specify GetQuery and TestQueryFilePath.
It should throw an exception if a method (Get(), Set(), or Test`) is called without having any of the Query parameters assigned.
Might be worth skipping the parameter Variable in a first iteration of the resource so more feedback can be gathered. 🤔
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the existing Invoke-SqlDscQuery command and the proposed SqlQuery methods Get(), Set(), and Test(). Review how query strings, query files, PassThru, credentials, and timeouts should interact, including the unresolved PRINT and Variable considerations. Done means a class-based SqlQuery resource supports the specified properties and rejects conflicting or missing query parameters.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- powershell, sql
- Domain
- databases, devops
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100