SQL Server installation using DSC on new windows server

[rsganta]

I am looking for sources to automate sql server installation using Azure DSC. Any pointers or references would be greatly appreciated .

[johlju]

If you mean by using Azure Automation then there are probably no guides around that since the resource module recently had problem to compile configurations in Azure Automation. This should be resolved as soon as the new version 10 is released (the one in the dev branch here).
So if you are looking as doing this in Azure, then please use the Dev version and upload that manually to Azure Automation (until we can get it released).

I'm working on an example for deploying a SQL Server AlwaysOn cluster over two subnets in Azure using Azure Automation. But it is slow moving, but I will get there, and it will be available here in one way or the other.

But basically it is the same as deploying on-premises. See examples for each individual resource here
https://github.com/PowerShell/SqlServerDsc/tree/dev/Examples/Resources

And here is some articles from a quick google:
https://blogs.msdn.microsoft.com/powersql/2017/10/10/install-sql-server-2017-using-powershell-desired-state-configuration/
https://blogs.msdn.microsoft.com/troy_aults_blog/2017/10/09/desired-state-configurationdsc-for-sql-server-update/
http://www.tomsitpro.com/articles/automate-sql-server-deployments-dsc,1-3409.html

[rsganta]

Thank you for the suggested links. I will go through them to see if I can write a simple configuration to deploy sql 2017 on the VM. How can I get dev version?

[johlju]

You get the Dev version by going to the code section and click on the green button "Clone or download". Make sure to delete information from the folder that is not necessary.

Name the root folder SqlServerDsc and make sure to only keep the following in the root folder.

Zip the root folder and upload it to Azure Automation.

Please not that I found a bug when verifying this, see issue #934. Make sure to delete those two files mentioned in the issue until we can merge a fix into Dev (working on it now).

[rsganta]

Thank you . Do you want me to remove those 2 lines from readme.md ? Two follow up questions.

1. Should I copy the whole module to both my target machine as well as to Azure (or ) just azure ?
2. do you have new readme.md and hence I can replace the old file instead of deleting the content.

(/Examples/Resources/SqlServerEndpointPermission/3-AddConnectPermissionToAlwaysOnPrimaryAndSecondaryReplicaEachWithDifferentSqlServiceAccounts.ps1)

• Remove connect permission to both an Always On primary replica and an
  Always On secondary replica, and where each replica has a different SQL service
  account

[johlju]

Answers to your questions.

1. If you are using Azure Automation you only need to upload into the Modules section in Azure Automation. When the configuration is applied by Azure Automation it will makesure to download any dependent modules from your "Azure Automation modules store". If you do not use Azure Automation then you need to have the the resource on the target machines.
2. @rsganta I meant the actually files in the file structure. But no need to bother now, I just merged a fix for this into Dev. 😄 So if you download the dev version again then you don't need to remove any files. Just make sure the layout is as above - that will match what the released module will look like.

[rsganta]

thanks John. I was able to upload to azure but compile fails. I think I need to replace modulename with sqlserverdsc instead of xsqlserver ? Below is my config for reference.
Configuration SQLInstall
{
Import-DscResource -ModuleName xSQLServer
node localhost
{
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}

      xSQLServerSetup 'InstallDefaultInstance'
      {
           InstanceName = 'MSSQLSERVER'
           Features = 'SQLENGINE'
           SourcePath = 'C:\SQL2017'
           SQLSysAdminAccounts = @('Administrators')
           DependsOn = '[WindowsFeature]NetFramework45'
      }
 }

}

[rsganta]

This is the error I got it when I compile my configuration.
Exception calling "NewScriptBlock" with "1" argument(s): "At line:13 char:11 + xSQLServerSetup 'InstallDefaultInstance' + ~~~~~~~~~~~~~~~ Undefined DSC resource 'xSQLServerSetup'. Use Import-DSCResource to import the resource." (At line:13 char:11 + xSQLServerSetup 'InstallDefaultInstance' + ~~~~~~~~~~~~~~~ Undefined DSC resource 'xSQLServerSetup'. Use Import-DSCResource to import the resource.)

[johlju]

Yes, in version 10 (the Dev version) all Resources has been renamed, including the module name. So you need to change xSQLServer to SqlServerDsc and xSQLServerSetup to SqlSetup.

[rsganta]

Thank you. I am able to install sql. I have a requirement to install SSMS too and exploring options

[rsganta]

Also John , what would be the equivalent for xSQLServerSetup SQLSetup { } . Is it SqlSetup SQLSetup {} ?

[rsganta]

Please feel free to close this thread as resolved with dev version. In future, I would post questions around sql in a different post. thank you for the support.

[rsganta]

Can I Install SSMS with sqlserverdsc module? If so , does it be included in features in the configuration ?

[randomnote1]

@rsganta, SqlServerDsc does currently have the capability to install SSMS for SQL 2016 and newer. There is a resource proposal for this in issue #125. Additionally, a workaround has been posted here for installing SSMS using DSC.

[rsganta]

Thanks Randomnote1. I have tried using the workaround posted but could not see SSMS got installed on my VM. I also looked into event logs but could not find any error. any insights ?

Script InstallSSMS
        {
            GetScript =
            {
                # Do Nothing 
                # validate if ssms is installed
            
            }

            SetScript = 
            {
            $InstallSSMS = $AllNodes.SSMSPath + "\SSMS-Setup-ENU.exe" + " /Install /passive /norestart"
           
            }

            TestScript = 
            {
        
                $false
            }

            DependsOn = "[SqlSetup]InstallDefaultInstance"           

       }

LCM output:

VERBOSE: [DSC-SP]: LCM:  [ Skip   Set      ]  [[SqlSetup]InstallDefaultInstance]
VERBOSE: [DSC-SP]: LCM:  [ End    Resource ]  [[SqlSetup]InstallDefaultInstance]
VERBOSE: [DSC-SP]: LCM:  [ Start  Resource ]  [[Script]InstallSSMS]
VERBOSE: [DSC-SP]: LCM:  [ Start  Test     ]  [[Script]InstallSSMS]
VERBOSE: [DSC-SP]: LCM:  [ End    Test     ]  [[Script]InstallSSMS]  in 2.2640 seconds.
VERBOSE: [DSC-SP]: LCM:  [ Start  Set      ]  [[Script]InstallSSMS]
VERBOSE: [DSC-SP]:                            [[Script]InstallSSMS] Performing the operation "Set-TargetResource" on target "Executing the SetScript with the user supplied credential".
VERBOSE: [DSC-SP]: LCM:  [ End    Set      ]  [[Script]InstallSSMS]  in 0.5150 seconds.
VERBOSE: [DSC-SP]: LCM:  [ End    Resource ]  [[Script]InstallSSMS]
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 91.696 seconds

[johlju]

@rsganta You are only building a string in $InstallSSMS. Where do you execute the setup?

[johlju]

Closing this as this is answered in issue #125.