Getting Started with Bicep

Install Bicep to your local environment:

# Create the install folder
$installPath = "$env:USERPROFILE\.bicep"
$installDir = New-Item -ItemType Directory -Path $installPath -Force
$installDir.Attributes += 'Hidden'
# Fetch the latest Bicep CLI binary
(New-Object Net.WebClient).DownloadFile("https://github.com/Azure/bicep/releases/latest/download/bicep-win-x64.exe", "$installPath\bicep.exe")
# Add bicep to your PATH
$currentPath = (Get-Item -path "HKCU:\Environment" ).GetValue('Path', '', 'DoNotExpandEnvironmentNames')
if (-not $currentPath.Contains("%USERPROFILE%\.bicep")) { setx PATH ($currentPath + ";%USERPROFILE%\.bicep") }
if (-not $env:path.Contains($installPath)) { $env:path += ";$installPath" }
# Verify you can now access the 'bicep' command.
bicep --help
# Done!

Install Bicep extension on VS Code

Look into Bicep documentations: https://docs.microsoft.com/en-us/azure/azure-resource-manager/bicep/

Deploy your main.bicep file using PowerShell:

Connect-AzAccount
$context = Get-AzSubscription -SubscriptionName 'Concierge Subscription'
Set-AzContext $context

Get-AzSubscription
Set-AzDefault -ResourceGroupName learn-8ea5f9c3-7fab-4c37-8fc7-f424f67ef1a5

New-AzResourceGroupDeployment -TemplateFile main.bicep

Using KeyVault Secrets as Parameter value

Assume you need a login and password in your main bicep resource:

resource sqlServer 'Microsoft.Sql/servers@2020-11-01-preview' = {
  name: sqlServerName
  location: location
  properties: {
    administratorLogin: sqlServerAdministratorLogin
    administratorLoginPassword: sqlServerAdministratorPassword
  }
}

So you define these 2 as parameters like this:

@secure()
@description('The administrator login username for the SQL server.')
param sqlServerAdministratorLogin string

@secure()
@description('The administrator login password for the SQL server.')
param sqlServerAdministratorPassword string

But you can’t pass these sensitive values in your pipeline. Instead you put them in the parameters.json to get the value from KeyVault:

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
      "sqlServerAdministratorLogin": {
        "reference": {
          "keyVault": {
            "id": "/subscriptions/a597e5fe-3c45-4412-b944-53e730b31c57/resourceGroups/learn-bicep-rg/providers/Microsoft.KeyVault/vaults/example-bicep-kv"
          },
          "secretName": "sqlServerAdministratorLogin"
        }
      },
      "sqlServerAdministratorPassword": {
        "reference": {
          "keyVault": {
            "id": "/subscriptions/a597e5fe-3c45-4412-b944-53e730b31c57/resourceGroups/learn-bicep-rg/providers/Microsoft.KeyVault/vaults/example-bicep-kv"
          },
          "secretName": "sqlServerAdministratorPassword"
        }
      }
    }
  }

Using Conditions

Deploying resources may also deppend on a given parameter. In this case put the condition before the object definition:

@allowed([
  'Development'
  'Production'
])
param environmentName string

// define your condition
var auditingEnabled = environmentName == 'Production'

resource auditingSettings 'Microsoft.Sql/servers/auditingSettings@2020-11-01-preview' = if (auditingEnabled) {
  parent: server
  name: 'default'
  properties: {
  }
}

You may also want to use conditions in properties section. Assuming you have defined an auditStorageAccount in this main bicep file, you can use the properties of this storage resource to assign values to auditSettings too:

resource auditStorageAccount 'Microsoft.Storage/storageAccounts@2021-02-01' = if (auditingEnabled) {
  name: auditStorageAccountName
  location: location
  sku: {
    name: storageAccountSkuName
  }
  kind: 'StorageV2'
}
resource auditingSettings 'Microsoft.Sql/servers/auditingSettings@2020-11-01-preview' = if (auditingEnabled) {
  parent: server
  name: 'default'
  properties: {
    state: 'Enabled'
    storageEndpoint: environmentName == 'Production' ? auditStorageAccount.properties.primaryEndpoints.blob : ''
    storageAccountAccessKey: environmentName == 'Production' ? listKeys(auditStorageAccount.id, auditStorageAccount.apiVersion).keys[0].value : ''
  }
}

Use Copy loops

You can use an array to create multiple resources in a loop

param storageAccountNames array = [
  'saauditus'
  'saauditeurope'
  'saauditapac'
]

resource storageAccountResources 'Microsoft.Storage/storageAccounts@2021-01-01' = [for storageAccountName in storageAccountNames: {
  name: storageAccountName
  location: resourceGroup().location
  kind: 'StorageV2'
  sku: {
    name: 'Standard_LRS'
  }
}]

Loop based on a count

Bicep provides the range() function, which creates an array of numbers. 

resource storageAccountResources 'Microsoft.Storage/storageAccounts@2021-01-01' = [for i in range(1,4): {
  name: 'sa${i}'
  location: resourceGroup().location
  kind: 'StorageV2'
  sku: {
    name: 'Standard_LRS'
  }
}]

Access the iteration index

When you need both the value in each iteration and index of that value you can specify the index variable with the array iterator:

param locations array = [
  'westeurope'
  'eastus2'
  'eastasia'
]

resource sqlServers 'Microsoft.Sql/servers@2020-11-01-preview' = [for (location, i) in locations: {
  name: 'sqlserver-${i+1}'
  location: location
  properties: {
    administratorLogin: administratorLogin
    administratorLoginPassword: administratorLoginPassword
  }
}]

Filter items with loops

Sometimes one value in a given array is enough, but we might need more associated parameters:

param sqlServerDetails array = [
  {
    name: 'sqlserver-we'
    location: 'westeurope'
    environmentName: 'Production'
  }
  {
    name: 'sqlserver-eus2'
    location: 'eastus2'
    environmentName: 'Development'
  }
  {
    name: 'sqlserver-eas'
    location: 'eastasia'
    environmentName: 'Production'
  }
]

resource sqlServers 'Microsoft.Sql/servers@2020-11-01-preview' = [for sqlServer in sqlServerDetails: if (sqlServer.environmentName == 'Production') {
  name: sqlServer.name
  location: sqlServer.location
  properties: {
    administratorLogin: administratorLogin
    administratorLoginPassword: administratorLoginPassword
  }
  tags: {
    environment: sqlServer.environmentName
  }
}]

Use loops with resource properties

param subnetNames array = [
  'api'
  'worker'
]

var subnetCount = 2

resource virtualNetworks 'Microsoft.Network/virtualNetworks@2020-11-01' = [for (location, i) in locations : {
  name: 'vnet-${location}'
  location: location
  properties: {
    addressSpace:{
      addressPrefixes:[
        '10.${i}.0.0/16'
      ]
    }
    subnets: [for j in range(1, subnetCount): {
      name: 'subnet-${j}'
      properties: {
        addressPrefix: '10.${i}.${j}.0/24'
      }
    }]
  }
}]

Variable loops

The next example creates an array that contains the values item1item2item3item4, and item5.

var items = [for i in range(1, 5): 'item${i}']

Don’t use outputs to return secrets, such as access keys or passwords. Outputs are logged, and they aren’t designed for handling secure data.

Getting started with Python

This article is about all you need to setup Python on Windows for a .Net Developer working on Windows using SQL Server.

Install Latest Python for Windows: https://www.python.org/downloads/windows/
-> My choice today: 3.10.3 Windows installer (64-bit)

Installing Python extension for Visual Studio Code which enables Jupiter Notebook
Installing Python for VSCode which brings syntax highlighting
Installing Python Extension Pack which supports intelliSense
Installing ODBC driver
pip install pyodbc

Sample code to run:

import pyodbc 
conn_str = ("Driver={SQL Server};"
            "Server='tcp:myserver.database.windows.net;"
            "Database=mydb;"
            "UID=sqlUser;"
            "PWD=myPassWord;"
            "Trusted_Connection=no;") 
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
sql_str = ("SET NOCOUNT ON; \n"
           "select * from myTable"
          )
cursor.execute(sql_str)
for row in cursor:
    print(row)

Nice to read more: https://wiki.python.org/moin/BeginnersGuide/Programmers

Next:

  • Installing Jupiter Notebook from https://jupyter.org/install
  • Installing Pandas as part of Anaconda:
    https://pandas.pydata.org/pandas-docs/stable/getting_started/install.html