SqlServerDsc: Add a new pipeline job to test SQL Server 2019 family #1310

johlju · 2019-03-17T15:41:45Z

Details of the scenario you tried and the problem that is occurring

Suggest we add another job that can be run in the AppVeyor build worker to test the SQL Server 2019 family. For example SQL Server 2019, Reporting Services 2019, Power BI Reporting Services 2019. Or at least for the last two, maybe different builds than what are installed on the two existing jobs.

SQL Server 2019 is not yet available, but as soon as they are we should look into this.

Verbose logs showing the problem

n/a

The DSC configuration that is used to reproduce the issue (as detailed as possible)

n/a

SQL Server edition and version the target node is running

n/a

SQL Server PowerShell modules present on the target node

n/a

The operating system the target node is running

n/a

Version and build of PowerShell the target node is running

n/a

Version of the DSC module that was used ('dev' if using current dev branch)

Dev

SphenicPaul · 2021-01-10T16:38:17Z

@johlju - How quickly can SQL2019 be added to the integration tests (presumably now in Azure Pipelines)? - I guess more people/companies will be using this now and this will only increase (I'm starting to install these more so would be good to know this was being tested on that version).

My thoughts are that it would just be an update to the azure-pipelines.yml file, maybe adding additional conditions in this where they exist in the tests and fixing issues off the back of failing tests on SQL2019 (although there is no visablity of what these may be without running the tests for the first time).

It also looks like there are also ISOs for SQL2016 and SQL2017 specified in config within the SqlSetup, downloaded as part of the integration tests. Guess the SQL2019 equivalent needs adding also.

SphenicPaul · 2021-01-10T16:40:38Z

Additionally, are there any plans to setup integration tests on resources (where relevant) against Azure SQL Database instances?

johlju · 2021-01-11T15:05:05Z

@johlju - How quickly can SQL2019 be added to the integration tests (presumably now in Azure Pipelines)? - I guess more people/companies will be using this now and this will only increase (I'm starting to install these more so would be good to know this was being tested on that version).

I updated the issue title. To get a new job to run in Azure Pipelines:

Copy this section and replace job name and display name, also change the key configuration: to Integration_SQL2019.

SqlServerDsc/azure-pipelines.yml

Lines 183 to 249 in 5ab05bd

    
                 - job: Test_Integration_SQL2017 
        
                   displayName: 'Integration (SQL2017)' 
        
                   pool: 
        
                     vmImage: 'windows-2019' 
        
                   timeoutInMinutes: 0 
        
                   variables: 
        
                     # This sets environment variable $env:CI. 
        
                     CI: true 
        
                     # This sets environment variable $env:CONFIGURATION. 
        
                     configuration: Integration_SQL2017 
        
                   steps: 
        
                     - task: DownloadBuildArtifacts@0 
        
                       displayName: 'Download Build Artifact' 
        
                       inputs: 
        
                         buildType: 'current' 
        
                         downloadType: 'single' 
        
                         artifactName: $(buildArtifactName) 
        
                         downloadPath: '$(Build.SourcesDirectory)' 
        
                     - task: PowerShell@2 
        
                       name: configureWinRM 
        
                       displayName: 'Configure WinRM' 
        
                       inputs: 
        
                         targetType: 'inline' 
        
                         script: 'winrm quickconfig -quiet' 
        
                         pwsh: false 
        
                     - powershell: | 
        
                         ./build.ps1 -Tasks test -CodeCoverageThreshold 0 -PesterScript @( 
        
                             # Run the integration tests in a specific group order. 
        
                             # Group 1 
        
                             'tests/Integration/DSC_SqlSetup.Integration.Tests.ps1' 
        
                             # Group 2 
        
                             'tests/Integration/DSC_SqlAgentAlert.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlLogin.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlEndpoint.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlDatabaseMail.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlRSSetup.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlDatabaseDefaultLocation.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlDatabase.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlAlwaysOnService.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlAgentOperator.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlServiceAccount.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlAgentFailsafe.Integration.Tests.ps1' 
        
                             # Group 3 
        
                             'tests/Integration/DSC_SqlRole.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlRS.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlDatabaseUser.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlReplication.Integration.Tests.ps1' 
        
                             # Group 4 
        
                             'tests/Integration/DSC_SqlScript.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlDatabasePermission.Integration.Tests.ps1' 
        
                             # Group 5 
        
                             'tests/Integration/DSC_SqlSecureConnection.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlScriptQuery.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlProtocol.Integration.Tests.ps1' 
        
                             # Group 6 (tests makes changes that could make SQL Server to loose connectivity) 
        
                             'tests/Integration/DSC_SqlProtocolTcpIp.Integration.Tests.ps1' 
        
                             'tests/Integration/DSC_SqlDatabaseObjectPermission.Integration.Tests.ps1' 
        
                         ) 
        
                       name: test 
        
                       displayName: 'Run Integration Test' 
        
                     - task: PublishTestResults@2 
        
                       displayName: 'Publish Test Results' 
        
                       condition: succeededOrFailed() 
        
                       inputs: 
        
                         testResultsFormat: 'NUnit' 
        
                         testResultsFiles: '$(buildFolderName)/$(testResultFolderName)/NUnit*.xml' 
        
                         testRunTitle: 'Integration (Windows Server Core)'

2, For each integration tests that should run for SQL2019 change the list of categories to include Integration_SQL2019). It checks the environment variable set in the pipeline job.

if (-not (Test-BuildCategory -Type 'Integration' -Category @('Integration_SQL2016','Integration_SQL2017', 'Integration_SQL2019')))

It also looks like there are also ISOs for SQL2016 and SQL2017 specified in config within the SqlSetup, downloaded as part of the integration tests. Guess the SQL2019 equivalent needs adding also.

Yes, a public link to the evaluation or developer ISO media is needed. Preferably a a link to a Microsoft CDN which probably download quicker in Azure Pipelines (if it uses the same CDN), but otherwise AWS is always helpful in the quickstart-microsoft-sql project which has a link to AWS cloud: https://github.com/aws-quickstart/quickstart-microsoft-sql/blob/1c13ae4bc55fd5ac13e407724a5f3b802ddb23fd/ci/sql-master-ds-defaults.json#L36

johlju · 2021-01-11T15:06:32Z

Additionally, are there any plans to setup integration tests on resources (where relevant) against Azure SQL Database instances?

The problem here is that we need access to a tenant where we can create the instances, but if we have that then it would be great to test that too.. But I happily review integration tests that can be run manually by a user to their own tenants.

johlju · 2021-01-11T15:19:42Z

The exe can be download with this link:

https://download.microsoft.com/download/d/a/2/da259851-b941-459d-989c-54a18a5d44dd/SQL2019-SSEI-Dev.exe

Then run with this to download the iso:

.\SQL2019-SSEI-Dev.exe /ENU /Quiet /HideProgressBar /Action=Download /Language=en-US /MediaType=ISO /MediaPath=C:\Temp

Full help from the .exe:

SQL2019-SSEI-Dev.exe [/ENU] [/Help] [/IAcceptSqlServerLicenseTerms] [/Quiet] [/HideProgressBar] [/Verbose] [/ConfigurationFile=C:\Configuration.ini] [/Action=Install] [/Language=en-US] [/MediaType=ISO] [/InstallPath=c:\Program Files\Microsoft SQL Server] [/MediaPath=C:\SetupMedia]

ACTION                    Indicates the workflow when performing a unattended operation.
                          Allowed Values      : Install, Download
                          Supported Actions   : Any

CONFIGURATIONFILE         Specifies the ConfigurationFile to use.
                          Supported Actions   : Install

ENU                       Use this parameter to install the English version of SQL Server on a localized operating system.
                          Supported Actions   : Any

HELP                      Displays the usage options for unattended operations.
                          Alias               : ?
                          Supported Actions   : Any

HIDEPROGRESSBAR           Indicates a progress bar should not be displayed in an unattended install.
                          Alias               : HP
                          Supported Actions   : Any

IACCEPTSQLSERVERLICENSETERMS Required to acknowledge acceptance of the license terms.
                          Supported Actions   : Install

INSTALLPATH               Specifies the data directory for SQL Server data files.
                          Supported Actions   : Install

LANGUAGE                  Language choice for Install or Download operations.
                          Alias               : Lang
                          Allowed Values      : zh-CN, zh-TW, en-US, fr-FR, de-DE, it-IT, ja-JP, ko-KR, pt-BR, ru-RU, es-ES
                          Supported Actions   : Any

MEDIAPATH                 Location where SQL Server setup media will be downloaded and extracted to.
                          Supported Actions   : Any

MEDIATYPE                 Indicates the type of media to download when /Action=Download
                          Alias               : MT
                          Allowed Values      : ISO, CAB
                          Supported Actions   : Download

QUIET                     Specifies that SQL Server Installer runs in a quiet mode without any user interface. This is used for unattended operations.
                          Alias               : Q
                          Supported Actions   : Any

VERBOSE                   Logs verbose output to the console.
                          Alias               : V
                          Supported Actions   : Any

SphenicPaul · 2021-01-11T18:25:53Z

Great. Thanks for those.

I might try and pick this up when PR #1652 is merged/closed.

Finally, are there any concerns/considerations around build time/minutes/cost with having 3 SQL versions running/building in parallel? i.e. There's not a requirement to replace SQL2017 with SQL2019, for example?

johlju · 2021-01-11T19:09:54Z

No there is no problem running more jobs. I think we can have 6-8 jobs in parallel and then it starts queuing. Every job can run up to 6 hours. It’s all at no cost for public repos.

So no need to replace any older OS. Would be awesome to have all current (of a Microsoft) supported versions of SQL in the pipeline. But adding SQL 2019 is high priority as it is the one starting to be used more as you mentioned.

- SqlServerDsc - Added unit tests and integration tests for SQL Server 2019 (issue #1310). - SqlSetup - Added/corrected `InstallSharedDir`, property output when using SQL Server 2019.

…not load SMO (#1684) - SqlServerDsc - Added a new script analyzer rule to verify that `Import-SQLPSModule` or `Connect-SQL` (that implicitly calls `Import-SQLPSModule`) is present in each `Get-`, `Test-`, and `Set-TargetResource` function. If neither command is not needed then the analyzer rule should be overridden ([issue #1683](#1683)). - Added a new pipeline job that runs Script Analyzer on all PowerShell scripts in the source folder. The rules are defined by the Script Analyzer settings file `.vscode\analyzersettings.psd1` (which also the Visual Studio Code PowerShell extension uses). - Added unit tests and integration tests for SQL Server 2019 ([issue #1310](#1310)). - Suppressed new custom Script Analyzer rule `SqlServerDsc.AnalyzerRules\Measure-CommandsNeededToLoadSMO` for `Get-`, `Test-`, and `Set-TargetResource` functions in the resources. - Minor lint errors throughout the repository. - SqlSetup - Minor refactor due to source code lint errors. The loop what evaluates the configuration parameters `*FailoverCluster` was change to a `foreach()`.

johlju added help wanted The issue is up for grabs for anyone in the community. tests The issue or pull request is about tests only. labels Mar 17, 2019

johlju changed the title ~~SqlServerDsc: Add a new AppVeyor job to test SQL Server 2019 family~~ SqlServerDsc: Add a new pipeline job to test SQL Server 2019 family Jan 11, 2021

SphenicPaul mentioned this issue Jan 23, 2021

SqlServerDsc: Added SQL Server 2019 Integration Tests (and unit test) plus additional, 'SqlSetup' resource InstallSharedDir fix. #1676

Merged

9 tasks

johlju added in progress The issue is being actively worked on by someone. and removed help wanted The issue is up for grabs for anyone in the community. labels Jan 24, 2021

This was referenced Jan 24, 2021

SqlServerDsc: Add a new pipeline job to test SQL Server 2014 family #1677

Open

SqlServerDsc: Add a new pipeline job to test SQL Server 2012 family #1678

Open

johlju closed this as completed in #1676 Jan 24, 2021

johlju removed the in progress The issue is being actively worked on by someone. label Jan 24, 2021

SphenicPaul mentioned this issue Jan 28, 2021

AzureDevOpsDsc: Running integration test in pipeline dsccommunity/AzureDevOpsDsc#9

Open

johlju mentioned this issue Jan 30, 2021

SqlServerDsc: Add Script Analyzer rule to help detect if resource do not load SMO #1684

Merged

9 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SqlServerDsc: Add a new pipeline job to test SQL Server 2019 family #1310

SqlServerDsc: Add a new pipeline job to test SQL Server 2019 family #1310

johlju commented Mar 17, 2019

SphenicPaul commented Jan 10, 2021 •

edited

Loading

SphenicPaul commented Jan 10, 2021

johlju commented Jan 11, 2021 •

edited

Loading

johlju commented Jan 11, 2021

johlju commented Jan 11, 2021

SphenicPaul commented Jan 11, 2021

johlju commented Jan 11, 2021

SqlServerDsc: Add a new pipeline job to test SQL Server 2019 family #1310

SqlServerDsc: Add a new pipeline job to test SQL Server 2019 family #1310

Comments

johlju commented Mar 17, 2019

Details of the scenario you tried and the problem that is occurring

Verbose logs showing the problem

Suggested solution to the issue

The DSC configuration that is used to reproduce the issue (as detailed as possible)

SQL Server edition and version the target node is running

SQL Server PowerShell modules present on the target node

The operating system the target node is running

Version and build of PowerShell the target node is running

Version of the DSC module that was used ('dev' if using current dev branch)

SphenicPaul commented Jan 10, 2021 • edited Loading

SphenicPaul commented Jan 10, 2021

johlju commented Jan 11, 2021 • edited Loading

johlju commented Jan 11, 2021

johlju commented Jan 11, 2021

SphenicPaul commented Jan 11, 2021

johlju commented Jan 11, 2021

SphenicPaul commented Jan 10, 2021 •

edited

Loading

johlju commented Jan 11, 2021 •

edited

Loading