CODEWITHSUNDEEP
sql-serverpowershellazuredsc
WinBoss
WinBoss

Reputation: 923

Troubleshooting DSC configuration

I'm looking for an advice on how to troubleshoot DSC configuration. I'm using Azure DSC Extension and the logs are:

VERBOSE: [2017-09-08 02:56:48Z] [VERBOSE] [sql-sql-0]:                         
   [[xSqlServer]ConfigureSqlServerWithAlwaysOn] The file "MSDBLog" has been 
modified in the system catalog. The new path will be used the next time the 
database is started.
VERBOSE: [2017-09-08 02:56:50Z] [ERROR] Exception setting "StartupParameters": 
"STARTUPPARAMETERS: unknown property."
VERBOSE: [2017-09-08 02:56:50Z] [VERBOSE] [sql-sql-0]:                         
   [[xSqlServer]ConfigureSqlServerWithAlwaysOn] Stopping SQL server instance 
'MSSQLSERVER' ...
VERBOSE: [2017-09-08 02:57:07Z] [VERBOSE] [sql-sql-0]:                         
   [[xSqlServer]ConfigureSqlServerWithAlwaysOn] Starting SQL server instance 
'MSSQLSERVER' ...

From there it just freezes then times out. This is the only part of my DSC configuration that uses xSQLServer:

xSqlServer ConfigureSqlServerWithAlwaysOn
{
    InstanceName = $env:COMPUTERNAME
    SqlAdministratorCredential = $Admincreds
    ServiceCredential = $SQLCreds
    MaxDegreeOfParallelism = 1
    FilePath = "F:\DATA"
    LogPath = "G:\LOG"
    DomainAdministratorCredential = $DomainFQDNCreds
    DependsOn = "[xSqlLogin]AddSqlServerServiceAccountToSysadminServerRole"
}

This is the part of MicrosoftAzure_xSqlServer.psm1 that contains mentions of "startup":

function Alter-SystemDatabaseLocation([string]$FilePath, [string]$LogPath,[PSCredential]$ServiceCredential )
{
    $permissionString = $ServiceCredential.UserName+":(OI)(CI)(F)"
    icacls $FilePath /grant $permissionString
    icacls $LogPath /grant $permissionString

    Invoke-Sqlcmd "Use master"
    Invoke-sqlCmd "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = '$FilePath\tempdb.mdf');"
    Invoke-sqlCmd "ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = '$LogPath\templog.ldf');"

    Invoke-sqlCmd "ALTER DATABASE model MODIFY FILE (NAME = modeldev, FILENAME = '$FilePath\model.mdf');"
    Invoke-sqlCmd "ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = '$LogPath\modellog.ldf');"

    Invoke-sqlCmd "ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = '$FilePath\msdbdata.mdf');"
    Invoke-sqlCmd "ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = '$LogPath\msdblog.ldf');"

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')| Out-Null
    $smowmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer 
    $sqlsvc = $smowmi.Services | Where-Object {$_.Name -like 'MSSQL*'} 
    $OldStartupParameters = $sqlsvc.StartupParameters
    $params = '-d'+$FilePath+'\master.mdf;-e'+$LogPath+'\ERRORLOG;-l'+$LogPath+'\mastlog.ldf'
    $sqlsvc[1].StartupParameters = $params
    $sqlsvc[1].Alter()
}

What should be my next steps to understand the problem? If it makes any difference, I'm trying to create SQL Server Always On Availability group on Windows Server 2016 and SQL Server 2016 SP1, by using templates and DSC code that work with Windows Server 2012 R2 and SQL Server 2014.

Upvotes: 1

Views: 471

Answers (2)

Ji Yong Seong
Ji Yong Seong

Reputation: 11

try this.

$sqlsvc = $smowmi.Services | Where-Object {$_.Name -eq 'MSSQLSERVER'}  
$OldStartupParameters = $sqlsvc.StartupParameters
$params = '-d'+$FilePath+'\master.mdf;-e'+$LogPath+'\ERRORLOG;-l'+$LogPath+'\mastlog.ldf'
$sqlsvc[0].StartupParameters = $params
$sqlsvc[0].Alter()

Upvotes: 1

4c74356b41
4c74356b41

Reputation: 72171

my advice is to use existing working code ;) I don't think it makes sense to copy paste the blog post. But I'll leave links to relevant files here.

Links:
1. DSC modules
2. ARM Template. Note: Arm template that deploys sql needs to have vnet and domain already in place. Also, you will get a hard time trying to deploy this template outside of that framework, so probably just copy out dsc extension\dsc scripts and use in your deployments.
3. Parameters file example

The xSqlServer (the new one) is not capable of moving sql to another disk, so you are stuck with custom script or this old xSql module. Also, please note, DSC modules inside packages are modified. These DSC configurations won't work with not modified versions of modules (xSqlCreateVirtualDataDisk, xDatabase, xSQLServerAlwaysOnAvailabilityGroupListener maybe some other modules, I can't recall at this point).

PS. working that configuration out and patching relevant parts wasn't exactly a pleasant journey... PPS. that repo also contains dsc configuration for ADDS that can also run in parallel (compared to official example).

Upvotes: 1

Related Questions