dataFileSize and dataFileCount settings ignored in sqlVirtualMachines API

Azure / bicep-types-az

Bicep type definitions for ARM resources

MIT License

84 stars 27 forks source link

Bicep version Bicep CLI version 0.16.2 (de7fdd2b33)

Describe the bug When deploying a SQL Virtual Machine using Microsoft.SqlVirtualMachine/sqlVirtualMachines@2022-07-01-preview, the sqlTempDbSettings > dataFileCount and dataFileSize settings appear to be ignored.

resource sqlVirtualMachine 'Microsoft.SqlVirtualMachine/sqlVirtualMachines@2022-07-01-preview' = {
  name: virtualMachineName
  location: location
  properties: {
    virtualMachineResourceId: virtualMachine.id
    sqlManagement: 'Full'
    sqlServerLicenseType: 'PAYG'
    storageConfigurationSettings: {
      diskConfigurationType: diskConfigurationType
      storageWorkloadType: 'OLTP'
      sqlDataSettings: {
        luns: dataDisksLuns
        defaultFilePath: dataPath
      }
      sqlLogSettings: {
        luns: logDisksLuns
        defaultFilePath: logPath
      }
      sqlTempDbSettings: {
        dataFileCount: 8 // THIS APPEARS TO BE IGNORED
        dataFileSize: 1024 // THIS APPEARS TO BE IGNORED
        luns: tempDbDisksLuns
        defaultFilePath: tempDbPath
      }
    }
    serverConfigurationsManagementSettings: {
      sqlInstanceSettings:{ 
      collation: 'SQL_Latin1_General_CP1_CS_AS'
      }
    }
  }
}

When I create a VM, it always creates tempdb files based on the number of Cores the VM has, with an 8MB file size.

To Reproduce Enter top 5 parameter values and run the following template for a Standard_F2s_v2 VM in Bicep. Check the tempdb file count and size (existing vnet/subnet required):


@description('Specify the name of an existing VNet in the same resource group')
param existingVirtualNetworkName string // ENTER VALUE

@description('Specify the resource group of the existing VNet')
param existingVnetResourceGroup string // ENTER VALUE

@description('Specify the name of the Subnet Name')
param existingSubnetName string // ENTER VALUE

@description('The admin user name of the VM')
param adminUsername string // ENTER VALUE

@description('The admin password of the VM')
@secure()
param adminPassword string // ENTER VALUE

@description('The name of the VM')
param virtualMachineName string = 'vmName'

@description('The virtual machine size.')
param virtualMachineSize string = 'Standard_F2s_v2'

@description('Windows Server and SQL Offer')
@allowed([
  'sql2022-ws2022'
  'sql2019-ws2019'
  'sql2017-ws2019'
  'SQL2017-WS2016'
  'SQL2016SP2-WS2016'
])
param imageOffer string = 'SQL2017-WS2016'

@description('SQL Server Sku')
@allowed([
  'Standard'
  'SQLDEV'
  'Web'
  'Express'
])
param sqlSku string = 'SQLDEV'

@description('Amount of data disks for SQL Data files')
@minValue(1)
@maxValue(8)
param sqlDataDisksCount int = 1

@description('Path for SQL Data files. Please choose drive letter from F to Z, and other drives from A to E are reserved for system')
param dataPath string = 'F:\\DataFiles'

@description('Amount of data disks for SQL Log files')
@minValue(1)
@maxValue(8)
param sqlLogDisksCount int = 1

@description('Amount of data disks for SQL Log files')
@minValue(1)
@maxValue(8)
param sqlTempDbDisksCount int = 1

@description('Path for SQL Log files. Please choose drive letter from F to Z and different than the one used for SQL data. Drive letter from A to E are reserved for system')
param logPath string = 'G:\\LogFiles'

@description('Location for all resources.')
param location string = resourceGroup().location

@description('Size of Data Disks in GB')
param dataDiskSizeGb int = 32

@description('Enter the type of disk (Premium_LRS or Standard_LRS)')
@allowed([
  'Premium_LRS'
  'Standard_LRS'
  'Premium_ZRS'
  'Standard_ZRS'
])
param storageDiskType string = 'Premium_LRS'

var networkInterfaceName = '${virtualMachineName}-nic'
var networkSecurityGroupName = '${virtualMachineName}-nsg'
var networkSecurityGroupRules = [
  {
    name: 'RDP'
    properties: {
      priority: 300
      protocol: 'Tcp'
      access: 'Allow'
      direction: 'Inbound'
      sourceAddressPrefix: '*'
      sourcePortRange: '*'
      destinationAddressPrefix: '*'
      destinationPortRange: '3389'
    }
  }  
  {
    name: 'SQL'
    properties: {
      priority: 400
      protocol: 'Tcp'
      access: 'Allow'
      direction: 'Inbound'
      sourceAddressPrefix: '*'
      sourcePortRange: '*'
      destinationAddressPrefix: '*'
      destinationPortRange: '1433'
    }
  }
]
var diskConfigurationType = 'NEW'
var nsgId = networkSecurityGroup.id
var subnetRef = resourceId(existingVnetResourceGroup, 'Microsoft.Network/virtualNetWorks/subnets', existingVirtualNetworkName, existingSubnetName)
var dataDisksLuns = range(0, sqlDataDisksCount)
var logDisksLuns = range(sqlDataDisksCount, sqlLogDisksCount)
var tempDbDisksLuns = range((sqlDataDisksCount + sqlLogDisksCount), sqlTempDbDisksCount)
var dataDisks = {
  createOption: 'Empty'
  caching: 'None' 
  writeAcceleratorEnabled: false
  storageAccountType: storageDiskType
  diskSizeGB: dataDiskSizeGb
}
var tempDbPath = 'H:\\TempDB'

resource networkSecurityGroup 'Microsoft.Network/networkSecurityGroups@2022-01-01' = {
  name: networkSecurityGroupName
  location: location
  properties: {
    securityRules: networkSecurityGroupRules
  }
}

resource networkInterface 'Microsoft.Network/networkInterfaces@2022-01-01' = {
  name: networkInterfaceName
  location: location
  properties: {
    ipConfigurations: [
      {
        name: 'ipconfig1'
        properties: {
          subnet: {
            id: subnetRef
          }
          privateIPAllocationMethod: 'Dynamic'
        }
      }
    ]
    enableAcceleratedNetworking: true
    networkSecurityGroup: {
      id: nsgId
    }
  }
}

resource virtualMachine 'Microsoft.Compute/virtualMachines@2022-03-01' = {
  name: virtualMachineName
  location: location
  properties: {
    hardwareProfile: {
      vmSize: virtualMachineSize
    }
    storageProfile: {
      osDisk: {
        createOption: 'FromImage'
        managedDisk: {
          storageAccountType: 'Premium_LRS'
        }
      }
      imageReference: {
        publisher: 'MicrosoftSQLServer'
        offer: imageOffer
        sku: sqlSku
        version: 'latest'
      }
      dataDisks: [for j in range(0, (sqlDataDisksCount + sqlLogDisksCount + sqlTempDbDisksCount)): {
        lun: j
        createOption: dataDisks.createOption
        caching: dataDisks.caching
        writeAcceleratorEnabled: dataDisks.writeAcceleratorEnabled
        diskSizeGB: dataDisks.diskSizeGB
        managedDisk: {
          storageAccountType: dataDisks.storageAccountType
        }
      }]
    }
    networkProfile: {
      networkInterfaces: [
        {
          id: networkInterface.id
        }
      ]
    }
    osProfile: {
      computerName: virtualMachineName
      adminUsername: adminUsername
      adminPassword: adminPassword
      windowsConfiguration: {
        enableAutomaticUpdates: false
        provisionVMAgent: true
      }
    }
  }
}

resource sqlVirtualMachine 'Microsoft.SqlVirtualMachine/sqlVirtualMachines@2022-07-01-preview' = {
  name: virtualMachineName
  location: location
  properties: {
    virtualMachineResourceId: virtualMachine.id
    sqlManagement: 'Full'
    sqlServerLicenseType: 'PAYG'
    storageConfigurationSettings: {
      diskConfigurationType: diskConfigurationType
      storageWorkloadType: 'OLTP'
      sqlDataSettings: {
        luns: dataDisksLuns
        defaultFilePath: dataPath
      }
      sqlLogSettings: {
        luns: logDisksLuns
        defaultFilePath: logPath
      }
      sqlTempDbSettings: {
        dataFileCount: 8 // THIS APPEARS TO BE IGNORED
        dataFileSize: 1024 // THIS APPEARS TO BE IGNORED
        luns: tempDbDisksLuns
        defaultFilePath: tempDbPath
      }
    }
    serverConfigurationsManagementSettings: {
      sqlInstanceSettings:{ 
      collation: 'SQL_Latin1_General_CP1_CS_AS'
      }
    }
  }
}

Deploy command using Azure CLI:

  az deployment group create `
    --name "templatedeploy" `
    --resource-group "$resourceGroup" `
    --template-file .\template.bicep `
      existingVirtualNetworkName="$vnetName" `
      existingVnetResourceGroup="$vnetResourceGroup" `
      existingSubnetName="$subnetName" `
      adminUsername="$adminUsername" `
      adminPassword="$adminPassword" `

Additional Information

This is important for us, as we would like to provision the machines at a smaller size, then eventually scale the machines up when running performance tests on them. Our Production machines have 8 data files for TempDB and 1024MB file size. We're provisioning a new environment with like-for-like Production infrastructure to allow us to run these tests.

Author:	MPowellFDB
Assignees:	-
Labels:	`Needs: Triage :mag:`, `Service Attention`, `SQL - VM`
Milestone:	-

Author:

MPowellFDB

Assignees:

Labels:

`Needs: Triage :mag:`, `Service Attention`, `SQL - VM`

Milestone:

Azure / bicep-types-az

dataFileSize and dataFileCount settings ignored in sqlVirtualMachines API #1551