SqlSetup: Add support for sysprepped SQL Server. about sqlserverdsc HOT 15 OPEN

@johlju We were using DSC to manage both our actual VMs, as well as our VM image we created VMs from.

This gave us the advantage of being able to incrementally roll out updates to VMs already deployed, while keeping our base image up-to-date, using the same configuration files. The base image was important as optimization - when new VMs were created, if we simply started from scratch and let DSC do everything, it could be hours before the image reached a usable state.

DSC is a really useful tool for bridging the divide between maintaining VM images, and the VMs themselves. We ended up rolling our own in-house that understood the transitions required for not installed->prepped->installed (which would error if trying to move from installed->sysprepped). Hope this helps explain our scenario a bit.

from sqlserverdsc.

Look Examples\SQL-ClusterDB.ps1 -script.

That contains already Prepare and Competele steps.
Maybe you can actually use that with sysprepped SQL image too.

from sqlserverdsc.

Sorry for taking so long to answer your suggestion.

As @olljanat says, there are support for /ACTION=PrepareFailoverCluster and /ACTION=CompleteFailoverCluster in the resource xSQLServerFailoverClusterSetup. But there no support for /ACTION=InstallFailoverCluster. Don't know the reason for supporting PrepareFailoverCluster and CompleteFailoverCluster but not InstallFailoverCluster. Have not used this resource yet or looked at the code before. But it feels like this resource should be merged into xSQLServerSetup and at the same time look into supporting InstallFailoverCluster. I will create an issue for this.

Regarding /ACTION=PrepareImage. The whole purpose for DSC is to make sure that the server is in desired state. PrepareImage is used to pre-install SQL at a later time for example when cloned.
DSC is meant to install a server from the ground up and keep it in desired state. I feel PrepareImage falls out of that scope.

In what production scenario do you see a need for this?

from sqlserverdsc.

@rhencke So that is good production scenario. Since there could be value doing the same for others, I don't see a problem supporting that installation option. If a user wants to use DSC to prep a server, then the user should be able to do that.
That said, it could still be a while before anyone has time to take on this change. But I personally would like to see the oldest issues be closed first, so I hope soon enough :)

from sqlserverdsc.

+1

from sqlserverdsc.

+1

from sqlserverdsc.

What is exactly the issue with sysprepping SQL machines? It seems working for me without problems. I use this for SQL setup (SQL 2016 example):

            SQLSetup SQLSetup
            {
                InstanceName            = "SPIntra01"
                SourcePath              = "S:\"
                Features                = "SQLENGINE,FULLTEXT"
                InstallSharedDir        = "C:\Program Files\Microsoft SQL Server\SPIntra01"
                SQLSysAdminAccounts     = "BUILTIN\Administrators"
                UpdateEnabled           = "False"
                UpdateSource            = "MU"
                SQMReporting            = "False"
                ErrorReporting          = "True"
                BrowserSvcStartupType   = "Automatic"
            }

Then I just run C:/windows/system32/sysprep/sysprep.exe /generalize /oobe /unattend:A:/autounattend_sysprep.xml /quiet /shutdown, take the image of the machine and then create real machines from this image. I use it for installing SharePoint and I did not notice issues so far. The only related stuff I had was when I tried to use reporting services from CRM, but then I used this fix: after creating a machine from the image, I run this script for fixing the name of the instance:

$serverNameResponse = Invoke-Sqlcmd "Select @@SERVERNAME" -ServerInstance "DB01\SPIntra01"
$serverName = $serverNameResponse.Column1
Write-Host "Previous server name was $serverName"
$serverNameResponse = Invoke-Sqlcmd ( "sp_dropserver '" + $serverName + "'" ) -ServerInstance "DB01\SPIntra01"
$serverNameResponse = Invoke-Sqlcmd ( "sp_addserver 'DB01\SPIntra01', 'local'" ) -ServerInstance "DB01\SPIntra01"
$serverNameResponse = Invoke-Sqlcmd "sp_helpserver" -ServerInstance "DB01\SPIntra01"
Write-Host "Restarting instance"
CD SQLSERVER:\SQL\db01
$Wmi = (get-item .).ManagedComputer  
$DBEngineInstance = $Wmi.Services['MSSQL$SPINTRA01']
$DBEngineInstance.Stop();
Sleep 15;
$DBEngineInstance.Refresh();
$DBEngineInstance.Start();
Sleep 15;
$DBEngineInstance.Refresh();

$AgentInstance = $Wmi.Services['SQLAgent$SPINTRA01']
$AgentInstance.Start();
Sleep 15;
$AgentInstance.Refresh();

$serverNameResponse = Invoke-Sqlcmd "Select @@SERVERNAME" -ServerInstance "DB01\SPIntra01"
$serverName = $serverNameResponse.Column1
Write-Host "New server name is $serverName"

where DB01 is a new VM name.

Sorry if this is something completely irrelevant but then what is the issue?

from sqlserverdsc.

The focus of this issue I think is adding the parameter Action options PrepareImage and CompleteImage for SqlSetup resource. See more in SysPrep Parameters.

from sqlserverdsc.

@johlju, correct - adding Action support for PrepareImage/CompleteImage as per sysprep guide.
That's more or less official way to get generalised SQL images, from what can be seen?

from sqlserverdsc.

I will be also interested in this scenario as @rhencke said.
In a huge production environment delivering hundreds of server monthly, having a DSC resource ready to prepare the VM server as a template(PrepareImage) and to complete the installation(CompleteImage) will save a ton of hours making deliveries much quicker.
Is this something that you are planning to develop?

from sqlserverdsc.

Anyone in the community are welcome to send in a pull request that add this functionality. Happy to review a PR adding this

from sqlserverdsc.

Is this something that you are planning to develop?

@da3210 it is we (you and I, and everyone else in the community) that can add this if we want it. 😄

from sqlserverdsc.

Related to the issue of sysprep, I am running into an issue when trying to run PrepareFailoverCluster and CompleteFailoverCluster in the same configuration because the SqlSetup resource uses the instance name as the key and I get a property conflict error. I suppose putting one in a composite resource might be a workaround but that doesnt seem like a great solution.

from sqlserverdsc.

I think you should run those in different configurations. One configuration setups the sql image (prepare) then another configuration (for one or more modes) uses the image to completed the configuration based on the image the first configuration resulted in.

from sqlserverdsc.

@johlju I'd like to run them in the same configuration as I am applying the configuration via an (elaborate) startup script. Would your recommendation be to create 2 configurations in the same file and then apply them one after another, ie

Configuration Prepare {....}
Configuration Complete {...}

Prepare -OutputPath $preparePath
Complete -OutputPath $completePath

Start-DscConfiguration $preparePath
Start-DscConfiguration $completePath

If so, how would I manage dependencies since I only want to complete if the prepare was successful?

My workaround was to fork the repo and add a name param and make that the key. Per https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-sysprep?view=sql-server-ver15 the Prepare and Complete steps need to be performed after the cluster is built so I cant bake prepare into the image.

Also this appears to have been discussed here #664

The argument in my case for not using the install/addnode approach is that it is slower since the cluster must be installed first and then each node added serially. It doesnt take advantage of sysprep images

from sqlserverdsc.