PowerShell: Initialize Raw Disk, Partition, Assign Drive Letter and Format

Here’s a quick script that initializes a raw disk, then partitions, assigns a drive letter, and formats the drive with NTFS.

Get-Disk | `
Where PartitionStyle -eq 'RAW' | `
Initialize-Disk -PartitionStyle MBR -PassThru | `
New-Partition -AssignDriveLetter -UseMaximumSize | `
Format-Volume -FileSystem NTFS -NewFileSystemLabel "Data01" -Confirm:$false
Posted in PowerShell | Leave a comment

T-SQL: Count of Tables, Stored Procedures, and Functions in All User Databases

This query uses the undocumented Microsoft stored procedure master.sys.sp_MSforeachdb to iterate over all user databases (user databases always start with IDs > 4) and count the number of tables, stored procedures and functions. You can use this same pattern to count other object types as well.

EXECUTE master.sys.sp_MSforeachdb '
IF (DB_ID(''?'') > 4)
BEGIN
  SELECT 
    ''?'' As DatabaseName, 
    CASE xtype
      WHEN ''u'' THEN ''Table''
      WHEN ''p'' Then ''Procedure''
      WHEN ''fn'' THEN ''Function''
    END
    As Type, 
    Count(*) As Count 
  FROM ?.sys.sysobjects 
  WHERE 
    xtype IN (''u'', ''p'', ''fn'')  
  GROUP BY xtype
END
'

Hope this helps!

Posted in T-SQL | Leave a comment

PowerShell DSC: Script Resources to Download and Install .Net 4.6.2

Here’s a snippet from a PowerShell Desired State Configuration file that uses a Script Resource to download and install the .Net Framework 4.6.2. You can incorporate this into an existing DSC configuration file to ensure .Net 4.6.2 is installed.

    ....
    ....
    Script DownloadDotNet462
    {
      TestScript = {
        Test-Path "C:\WindowsAzure\NDP462-KB3151800-x86-x64-AllOS-ENU.exe"
      }
      SetScript ={
        $source = "http://download.microsoft.com/download/F/9/4/F942F07D-F26F-4F30-B4E3-EBD54FABA377/NDP462-KB3151800-x86-x64-AllOS-ENU.exe"
        $dest = "C:\WindowsAzure\NDP462-KB3151800-x86-x64-AllOS-ENU.exe"
        Invoke-WebRequest $source -OutFile $dest
      }
      GetScript = {@{Result = "DownloadDotNet462"}}
    }
    Script InstallDotNet462
    {
      TestScript = {
        $dotNetFull = Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -recurse |
        Get-ItemProperty -name Version,Release -EA 0 |
        Where { $_.Version -match '4.6.01590' -and $_.PSChildName -eq 'Full' }
        If ($dotNetFull -eq $null) { $false } Else { $true }
      }
      SetScript ={
        $proc = Start-Process -FilePath "C:\WindowsAzure\NDP462-KB3151800-x86-x64-AllOS-ENU.exe" -ArgumentList "/quiet /norestart /log C:\WindowsAzure\NDP462-KB3151800-x86-x64-AllOS-ENU_install.log" -PassThru -Wait
        Switch($proc.ExitCode)
        {
          0 {
            # Success
          }
          1603 {
            Throw "Failed installation"
          }
          1641 {
            # Restart required
            $global:DSCMachineStatus = 1                
          }
          3010 {
            # Restart required
            $global:DSCMachineStatus = 1                
          }
          5100 {
            Throw "Computer does not meet system requirements."
          }
          default {
            Throw "Unknown exit code $($proc.ExitCode)"
          }
        }
      }
      GetScript = {@{Result = "InstallDotNet462"}}
      DependsOn = "[Script]DownloadDotNet462"
    }
    ....
    ....

If you don’t mind reboots during the process, another helpful tip is to set the LocalConfigurationManager to reboot when needed, e.g.

    ....
    ....
    LocalConfigurationManager
    {        
      RebootNodeIfNeeded = $true # This is false by default
    }
    ....
    ....

Hope this helps!

Posted in PowerShell, PowerShell DSC | Leave a comment

Azure PowerShell: Set Custom Script Extension on VM

Here’s a script that sets/runs an existing custom script extension on a VM in Azure:

$ResourceGroupName = "MyResourceGroup"
$StorageAccountName = "mystorage"
$VmName = "MyWinVm01"
 
$ExtensionName = "TestScriptExtension"
$ExtensionContainerName = "extensions"
$ExtensionFileName = "test\TestScriptExtension.ps1"
 
$ResourceGroupLocation = (Get-AzureRmResourceGroup -Name $ResourceGroupName)[0].Location
$StorageAccountKey = (Get-AzureRmStorageAccountKey -ResourceGroupName $ResourceGroupName -Name $StorageAccountName)[0].Value
 
Set-AzureRmVMCustomScriptExtension -ResourceGroupName $ResourceGroupName `
-Location $ResourceGroupLocation `
-VMName $VmName `
-Name $ExtensionName `
-StorageAccountName $StorageAccountName `
-StorageAccountKey $StorageAccountKey `
-ContainerName $ExtensionContainerName `
-FileName $ExtensionFileName 
 
#To remove the script extension from the VM, you would run...
#Remove-AzureRmVMCustomScriptExtension -ResourceGroupName $ResourceGroupName -VMName $VmName -Name $ExtensionName -Force

Hope this helps!

Posted in Azure, PowerShell | Leave a comment

Azure PowerShell: Copy Local File to Blob Storage

Here’s a simple script that copies a local file to Azure blob storage:

$ResourceGroupName = "MyResourceGroup"
$StorageAccountName = "mysstorageaccount"
$File = "C:\TestExtension.ps1"
$Container = "extensions"
$Blob = "test\TestExtension.ps1"
 
$StorageAccountKey = (Get-AzureRmStorageAccountKey -ResourceGroupName $ResourceGroupName -Name $StorageAccountName)[0].Value
$StorageContext = New-AzureStorageContext –StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
Set-AzureStorageBlobContent -File $File -Container $Container -Blob $Blob -Context $StorageContext

The script assumes you have already logged in and set your subscription context.

A parallelized, multi-file version can be found in the azure-sdk-tools-samples on GitHub:
https://github.com/Azure/azure-sdk-tools-samples/blob/master/solutions/data-management/CopyFilesToAzureStorageContainer.ps1.

Hope this helps.

Posted in Azure, PowerShell | Leave a comment

Azure PowerShell: Attach New Data Disk to Existing VM

Here’s a script that attaches a new data disk to an existing VM in Azure. It determines the appropriate LUN based on the existing data disks associated to the VM.

$ResourceGroupName = "MyResourceGRoup"
$StorageAccountName = "mysstorageaccount"
$VmName = "vm01"
$DiskSizeInGB = 512
 
$Vm = Get-AzureRmVM -ResourceGroupName $ResourceGroupName -Name $VmName
$MaxLun = 0
$VmDataDisks = $vm.StorageProfile.DataDisks
ForEach ($VmDataDisk in $VmDataDisks)
{
  $VmDataDiskLun = $VmDataDisk.Lun
  If ($VmDataDiskLun -gt $MaxLun)
  {
    $MaxLun = $VmDataDiskLun
  }
}
$Lun = $MaxLun + 1
 
$DiskName = "Data0" + ($Lun + 1).ToString()
$VhdUri = "https://" + $StorageAccountName + ".blob.core.windows.net/vhds/" + $VmName + $DiskName + "Disk.vhd"
 
$Vm = Add-AzureRmVMDataDisk -VM $Vm -Name $DiskName -CreateOption Empty -Caching None -Lun $Lun -DiskSizeInGB $DiskSizeInGB -VhdUri $VhdUri
Update-AzureRmVM -VM $Vm -ResourceGroupName $ResourceGroupName

Hope this helps!

Posted in Azure, PowerShell | Leave a comment