AzureRM-AutomationSQLDatabase.ps1
|
<#PSScriptInfo
.VERSION 1.0 .GUID ecaa08ff-eb34-4e45-a972-06318120def8 .AUTHOR Ricardo Cabral .COMPANYNAME .COPYRIGHT Ricardo Ramos Cabral .TAGS Azure, Azure SQL Database, Azure SQL, Azure PowerShell, PowerShell, Automation .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES AzureRM Azure .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES AzCopy (https://docs.microsoft.com/en-us/azure/storage/storage-use-azcopy) .RELEASENOTES #> <# .SYNOPSIS This script was written to explain the advantage to use automation in Azure with Microsoft Azure PowerShell. It was used the Microsoft Azure PowerShell version 3.6.0 (February 2017). .DESCRIPTION The script intends to demonstrate the autonomous creation of a shared infrastructure, development, and production environment for using Microsoft Azure SQL Databases where each environment has it’s own resources. With the use of Microsoft Azure PowerShell allows you to automatically create all the features and services required automatically for each environment. The script also shows that the automation process will automatically create in all available regions in your subscription an Azure SQL Database with free tier. There may be regions that your subscription does not allows you to create the Azure SQL Database service. Script will create: - A Resource Group for each environment (Shared Infrastructure, Development and Production) - A Storage Account for each environment. - One Virtual Network and subnet in Shared Infrastructure environment. - An Azure SQL server for development and production environment. - Create a SQL database using free tier in the development environment. - Create a SQL database using basic tierin the production environment through user defined bacpac. - Create a SQL Server and database using free tier in all possible subscription regions. .PARAMETER AzureRmProfileJson Specifies the Location of the Azure Resource Manager Json Profile. .PARAMETER AzureRMSubscriptionId Specifies the Azure Resource Manager Subscription Id. .PARAMETER SharedInfrastructure Specifies Resource Group Name for Shared Infrastructure. .PARAMETER Development Specifies Resource Group Name for Development. .PARAMETER Production Specifies resource Group Name for Production. .PARAMETER Location Specifies the location of the data center. The default value is ‘NorthEurope’. .PARAMETER StorageSkuName Specifies Pricing Tier or Stock Keeping Unit (SKU). The default value is ‘Standard_LRS’. .PARAMETER SqlSrvDevUsername Specifies the Development SQL Database server username administrator for the new server. The default value is ‘DevAdmin’. .PARAMETER SqlSrvDevPassword Specifies the Development SQL Database server password administrator for the new server. The default value is empty. .PARAMETER SqlSrvProdUsername Specifies the Production SQL Database server username administrator for the new server. The default value is ‘ProdAdmin’. .PARAMETER SqlSrvProdPassword Specifies the Production SQL Database server password administrator for the new server. The default value is empty. .PARAMETER SqlSrDevVersion = Specifies the Development SQL Database server version. The acceptable values for this parameter are: 2.0 and 12.0. Specify 2.0 to create a version 11 server, or 12.0 to create a version 12 server. The default value is ’12.0’ .PARAMETER SqlSrProdVersion Specifies the Production SQL Database server version. The acceptable values for this parameter are: 2.0 and 12.0. Specify 2.0 to create a version 11 server, or 12.0 to create a version 12 server. The default value is ’12.0’ .PARAMETER SqlDatabaseEdition Specifies the edition to assign to the database. The acceptable values for this parameter are: Default, None, Premium, Basic, Standard, DataWarehouse, and Free. The default value is ‘Free’. .PARAMETER SqlDatabaseServiceObjectiveName Specifies the name of the service objective to assign to the Azure SQL Database.The acceptable values for this parameter are: basic, S0 , S1, S2, S3, P1, P2, P3, P4, P6, P11, and P15. The default value is ‘Basic’. .PARAMETER SqlDatabaseMaxSizeBytes Specifies the maximum size for the newly imported database. The default value is ‘5000000’. .PARAMETER SqlCollationName Specifies the name of the SQL database collation https://msdn.microsoft.com/en-us/library/ms143515(v=sql.105).aspx. The default value is ‘SQL_Latin1_General_CP1_CI_AS’ .PARAMETER BacpacLocalFolder Specifies the location of bacpac folder. .PARAMETER BacpacLocalFile Specifies the bacpac file name in the folder .PARAMETER BacpacSqlDatabaseName Specifies the database name from the imported bacpac .PARAMETER StorageKeyType Specifies the type of access key for the storage account. The acceptable values for this parameter are: "StorageAccessKey" Uses the storage account key, "SharedAccessKey" Uses the Shared Access Signature (SAS) key. The default value is ‘StorageAccessKey’. .EXAMPLE The login to Azure is using Azure RM profile, are defined the names of the environments, the location of the bacpac file, and the authentication for SQL server administration in development and production environment. .\DevScriptv5.ps1 -AzureRmProfileJson "C:\A\AzureRmProfile.json" -SharedInfrastructure SharedInfrastructure -Development Development -Production Production -BacpacLocalFolder "C:\Bacpac\SQLDBBacpac\" -BacpacLocalFile AdventureWorksLT.bacpac -BacpacSqlDatabaseName AdventureWorksLT -SqlSrvDevPassword ADB#02mnEM -SqlSrvProdPassword ADB#02mnEM –Verbose. .EXAMPLE To view parameter description use Get-Help .\DevScriptv5.ps1 –Detailed. .EXAMPLE To view full help use Get-Help .\DevScriptv5.ps1 –Full. .NOTES To use this script it requires that you have Azue PowerShell Azure and AZCopy installed. You must define insert the location of AZCopy in the system path of the Microsoft Windows environment variables. AzCopy is installed to "%ProgramFiles(x86)%\Microsoft SDKs\Azure\AzCopy" or "%ProgramFiles%\Microsoft SDKs\Azure\AzCopy". .INPUTS All parameters. Not all are required and some already have values set by default. .OUTPUTS Are shown the verbose of cmdlest used. A report the resource group and teh resources with the tag defined for Shared Infrastructure, Development, and Production. And all the SQL servers and databases in the environment created. .LINK http://blog.rramoscabral.com http://blog.en.rramoscabral.com #> <# +--------------------------------------------------------------------------------------+ | Parameters | +--------------------------------------------------------------------------------------+ #> Param( [alias("AzureProfile")] [string]$AzureRmProfileJson, #Specifies the Location of the Azure Resource Manager Json Profile. [alias("SubscriptionId")] [string] $AzureRMSubscriptionId, #Specifies the Azure Resource Manager Subscription Id [alias("RGShared")] [Parameter(Mandatory=$true, HelpMessage="Resource group name for Shared Infrastructure")] [string]$SharedInfrastructure, #Specifies Resource Group Name for Shared Infrastructure [alias("RDDev")] [Parameter(Mandatory=$true, HelpMessage="Resource group name for Development")] [string]$Development, #Specifies Resource Group Name for Development [alias("RGProd")] [Parameter(Mandatory=$true, HelpMessage="Resource Group Name for Production")] [string]$Production, #Specifies resource Group Name for Production. [alias("LT")] [string]$Location = "NorthEurope", #Specifies the location of the data center. [alias("STSku")] $StorageSkuName = "Standard_LRS", #Specifies Pricing Tier or Stock Keeping Unit (SKU). [string] $SqlSrvDevUsername = "DevAdmin", #Specifies the Development SQL Database server username administrator for the new server. [string] $SqlSrvDevPassword ="", #Specifies the Development SQL Database server password administrator for the new server. [string] $SqlSrvProdUsername = "ProdAdmin", #Specifies the Production SQL Database server username administrator for the new server. [string] $SqlSrvProdPassword ="" , #Specifies the Production SQL Database server password administrator for the new server. [string] $SqlSrDevVersion = "12.0", #Specifies the Development SQL Database server version. The acceptable values for this parameter are: 2.0 and 12.0. Specify 2.0 to create a version 11 server, or 12.0 to create a version 12 server. [string] $SqlSrProdVersion = "12.0", #Specifies the Production SQL Database server version. The acceptable values for this parameter are: 2.0 and 12.0. Specify 2.0 to create a version 11 server, or 12.0 to create a version 12 server. [string]$SqlDatabaseEdition ="Free", #Specifies the edition to assign to the database. The acceptable values for this parameter are: Default, None, Premium, Basic, Standard, DataWarehouse, and Free. [string]$SqlDatabaseServiceObjectiveName = "Basic", #Specifies the name of the service objective to assign to the Azure SQL Database.The acceptable values for this parameter are: basic, S0, S1, S2, S3, P1, P2, P3, P4, P6, P11, and P15. [string]$SqlDatabaseMaxSizeBytes = "5000000", #Specifies the maximum size for the newly imported database. [string]$SqlCollationName ="SQL_Latin1_General_CP1_CI_AS", #Specifies the name of the SQL database collation https://msdn.microsoft.com/en-us/library/ms143515(v=sql.105).aspx .. [string]$BacpacLocalFolder = "", #Specifies the location of bacpac folder [string]$BacpacLocalFile = "", #Specifies the bacpac file name in the folder [string]$BacpacSqlDatabaseName ="", #Specifies the database name from the imported bacpac [string]$StorageKeyType = "StorageAccessKey" #Specifies the type of access key for the storage account. The acceptable values for this parameter are: "StorageAccessKey" Uses the storage account key, "SharedAccessKey" Uses the Shared Access Signature (SAS) key. ) <# +--------------------------------------------------------------------------------------+ | Prepare System and parameters | +--------------------------------------------------------------------------------------+ #> $Error.Clear(); $SharedInfrastructure = $SharedInfrastructure.replace(" " , "") $Development = $Development.replace(" " , "") $Production = $Production.replace(" " , "") $Location = $Location.replace(" " , "") <# +--------------------------------------------------------------------------------------+ | Login in Azure Subscription Using Profile or Manual Credentials | +--------------------------------------------------------------------------------------+ #> try{ IF([string]::IsNullOrEmpty($AzureRmProfileJson)) { Write-Host "Insert your Azure subscription" Login-AzureRmAccount IF([string]::IsNullOrEmpty($AzureRMSubscriptionId)) { }else{ Select-AzureRmSubscription -SubscriptionId $AzureRMSubscriptionId } } else { Select-AzureRmProfile -Path $AzureRmProfileJson } } Catch { Write-Host "Error Loging to Azure" -foreground Yellow throw $Error[0] } <# +--------------------------------------------------------------------------------------+ | Creating Shared Infrastructure, Development and Production Resources Groups | +--------------------------------------------------------------------------------------+ #> try{ Write-Host "`nCreating Resources Groups" -foreground Green $RGSharedInfrastructure = "RG-" + $SharedInfrastructure $RGDevelopment = "RG-" + $Development $RGProduction = "RG-" + $Production $RGSharedInfrastructureHT = @{ Name = $RGSharedInfrastructure; Tag = @{Environment ="Shared Infrastructure"}; Location = $Location; }; $RGDevelopmentHT = @{ Name = $RGDevelopment; Tag = @{Environment ="Development"}; Location = $Location; }; $RGProductionHT = @{ Name = $RGProduction; Tag = @{Environment ="Production"}; Location = $Location; }; #Write-Debug "RGSharedInfrastructure" ($RGSharedInfrastructure | Out-String) #Write-Debug "RGDevelopment" ($RGDevelopment | Out-String) #Write-Debug "RGProduction" ($RGProduction | Out-String) New-AzureRmResourceGroup @RGSharedInfrastructureHT -Verbose New-AzureRmResourceGroup @RGDevelopmentHT -Verbose New-AzureRmResourceGroup @RGProductionHT -Verbose } Catch { Write-Host "Error creating Resources Groups" -foreground Yellow throw $Error[0] } <# +--------------------------------------------------------------------------------------+ | Creating Virtual Network in Shared Infrastructure Resource Group | +--------------------------------------------------------------------------------------+ #> try{ Write-Host "`nCreating Virtual Network" -foreground Green $SBSharedInfrastructure = "SB-" + $SharedInfrastructure $VNETSharedInfrastructure = "VNET-" + $SharedInfrastructure # -- Create Subnet -- $SBSharedInfrastructureHT = @{ Name = $SBSharedInfrastructure; AddressPrefix = "10.0.0.0/24"; }; #Write-Debug "SBSharedInfrastructure" ($SBSharedInfrastructure | Out-String) $Subnet = New-AzureRmVirtualNetworkSubnetConfig @SBSharedInfrastructureHT -Verbose # -- Create VNet with Subnet -- $VNETSharedInfrastructureHT = @{ Name = $VNETSharedInfrastructure; ResourceGroupName = $RGSharedInfrastructureHT.Name; Location = $RGSharedInfrastructureHT.Location; AddressPrefix = "10.0.0.0/16"; Subnet = $Subnet; Tag = @{Environment ="Shared Infrastructure"}; }; #Write-Debug "VNETSharedInfrastructure" ($VNETSharedInfrastructure | Out-String) New-AzureRmVirtualNetwork @VNETSharedInfrastructureHT -verbose } Catch { Write-Host "Error creating Virtual Network" -foreground Yellow throw $Error[0] } <# +--------------------------------------------------------------------------------------+ | Creating Storage Account in Shared Infrastructure, Development and Production RG | +--------------------------------------------------------------------------------------+ #> try{ Write-Host "`nCreating Storage Accounts" -foreground Green $STSharedInfrastructure = "st" + $SharedInfrastructure.ToLower() $STDevelopment = "st" + $Development.ToLower() $STProduction = "st" + $Production.ToLower() # -- Storage account name must be between 3 and 24 characters in length and use numbers and lower-case letters only. -- if ($STSharedInfrastructure.length -le 24) { }else{ $STSharedInfrastructure = $STSharedInfrastructure.Substring(0,24) } if ($STDevelopment.length -le 24) { }else{ $STDevelopment = $STDevelopment.Substring(0,24) } if ($STProduction.length -le 24) { }else{ $STProduction = $STProduction.Substring(0,24) } $STSharedInfrastructureHT = @{ Name = $STSharedInfrastructure; SkuName = $StorageSkuName; ResourceGroupName = $RGSharedInfrastructureHT.Name; Location = $RGSharedInfrastructureHT.Location; Tag = @{Environment ="Shared Infrastructure" ; SKU = $StorageSkuName}; }; $STDevelopmentHT = @{ Name = $STDevelopment; SkuName = $StorageSkuName; ResourceGroupName = $RGDevelopmentHT.Name; Location = $RGDevelopmentHT.Location; Tag = @{Environment ="Development" ; SKU = $StorageSkuName}; }; $STProductionHT = @{ Name = $STProduction; SkuName = $StorageSkuName; ResourceGroupName = $RGProductionHT.Name; Location = $RGProductionHT.Location; Tag = @{Environment ="Production" ; SKU = $StorageSkuName}; }; #Write-Debug "STSharedInfrastructure" ($STSharedInfrastructure | Out-String) #Write-Debug "STDevelopment" ($STDevelopment | Out-String) #Write-Debug "STProduction" ($STProduction | Out-String) New-AzureRmStorageAccount @STSharedInfrastructureHT -Verbose New-AzureRmStorageAccount @STDevelopmentHT -Verbose New-AzureRmStorageAccount @STProductionHT -Verbose } Catch { Write-Host "Error creating Storage Accounts" -foreground Yellow throw $Error[0] } <# +--------------------------------------------------------------------------------------+ | Creating SQL Server in Development and Production RG | +--------------------------------------------------------------------------------------+ #> try{ Write-Host "`nCreating SQL Server" -foreground Green IF([string]::IsNullOrEmpty($SqlSrvDevPassword)) { $SqlSrvDevSecurePassword = ConvertTo-SecureString "SQL+dev-2017" -AsPlainText -Force $SqlSrvDevCredential = New-Object System.Management.Automation.PSCredential ($SqlSrvDevUsername, $SqlSrvDevSecurePassword); }else{ $SqlSrvDevSecurePassword = ConvertTo-SecureString $SqlSrvDevPassword -AsPlainText -Force $SqlSrvDevCredential = New-Object System.Management.Automation.PSCredential ($SqlSrvDevUsername, $SqlSrvDevSecurePassword); } IF([string]::IsNullOrEmpty($SqlSrvProdPassword)) { $SqlSrvProdSecurePassword = ConvertTo-SecureString "SQL+pro-2017" -AsPlainText -Force $SqlSrvProdCredential = New-Object System.Management.Automation.PSCredential ($SqlSrvDevUsername, $SqlSrvProdSecurePassword); }else{ $SqlSrvProdSecurePassword = ConvertTo-SecureString $SqlSrvProdPassword -AsPlainText -Force $SqlSrvProdCredential = New-Object System.Management.Automation.PSCredential ($SqlSrvProdUsername, $SqlSrvProdSecurePassword); } # -- PowerShell One Liner: Get External/Public IP Address by Chrissy LeMaire. -- $ipinfo = Invoke-RestMethod http://ipinfo.io/json $PublicExternalIP = $ipinfo.ip # -- Servername it can only be made up of lowercase letters 'a'-'z', the numbers 0-9 and the hyphen. The hyphen may not lead or trail in the name. -- $SqlSrvDevelopment = "sqlsrv-" + $Development.ToLower() $SqlSrvProduction = "sqlsrv-" + $Production.ToLower() $SqlSrvDevelopmentHT = @{ ServerName = $SqlSrvDevelopment; SqlAdministratorCredentials = $SqlSrvDevCredential; ServerVersion = $SqlSrDevVersion; ResourceGroupName = $RGDevelopmentHT.Name; Location = $RGDevelopmentHT.Location; Tags = @{Environment ="Development"}; }; $SqlSrvProductionHT = @{ ServerName = $SqlSrvProduction; SqlAdministratorCredentials = $SqlSrvProdCredential; ServerVersion = $SqlSrProdVersion; ResourceGroupName = $RGProductionHT.Name; Location = $RGProductionHT.Location; Tags = @{Environment ="Production"}; }; #Write-Debug "SqlSrvDevelopment" ($SqlSrvDevelopment | Out-String) #Write-Debug "SqlSrvProduction" ($SqlSrvProduction | Out-String) New-AzureRmSqlServer @SqlSrvDevelopmentHT -Verbose New-AzureRmSqlServer @SqlSrvProductionHT -Verbose $PublicExternalIPHT = @{ FirewallRuleName = "PublicExternalIP"; StartIpAddress = $PublicExternalIP; EndIpAddress =$PublicExternalIP; }; New-AzureRmSqlServerFirewallRule @PublicExternalIPHT -ServerName $SqlSrvDevelopmentHT.ServerName -ResourceGroupName $RGDevelopmentHT.Name -Verbose New-AzureRmSqlServerFirewallRule -AllowAllAzureIPs -ServerName $SqlSrvDevelopmentHT.ServerName -ResourceGroupName $RGDevelopmentHT.Name -Verbose New-AzureRmSqlServerFirewallRule @PublicExternalIPHT -ServerName $SqlSrvProductionHT.ServerName -ResourceGroupName $RGProductionHT.Name -Verbose New-AzureRmSqlServerFirewallRule -AllowAllAzureIPs -ServerName $SqlSrvProductionHT.ServerName -ResourceGroupName $RGProductionHT.Name -Verbose } Catch { Write-Host "Error creating SQL Server" -foreground Yellow throw $Error[0] } <# +--------------------------------------------------------------------------------------+ | Upload bacpac file to container bacpacs using AZCopy | +--------------------------------------------------------------------------------------+ #> try{ IF([string]::IsNullOrEmpty($BacpacLocalFile)) { Write-Host "`nNo bacpac file was definied." -foreground Green } else{ $AzCopySource = $BacpacLocalFolder $AzCopyDest = "https://" + $STSharedInfrastructureHT.Name + ".blob.core.windows.net/bacpacs/" $AzCopyPattern = $BacpacLocalFile Write-Host "`nThe backpac file" $BacpacLocalFile "will be upload to AZCopy to" $AzCopyDest -foreground Green # -- Delete journal directory because incomplete operation. -- $AzCopyUserDirectory = "C:\Users\" + $env:UserName + "\AppData\Local\Microsoft\Azure\AzCopy\*" Remove-Item $AzCopyUserDirectory -Force # -- Storage Account first Key. -- $STSharedInfrastructurKey = (Get-AzureRmStorageAccountKey -StorageAccountName $STSharedInfrastructureHT.Name -ResourceGroup $RGSharedInfrastructureHT.Name).Value[0] # -- AzCopy is installed to "%ProgramFiles(x86)%\Microsoft SDKs\Azure\AzCopy" or "%ProgramFiles%\Microsoft SDKs\Azure\AzCopy" ; /Y Suppresses all AzCopy confirmation prompts. -- AzCopy.exe /Source:$AzCopySource /Dest:$AzCopyDest /DestKey:$STSharedInfrastructurKey /Pattern:$AzCopyPattern /Y $saContext = New-AzureStorageContext -StorageAccountName $STSharedInfrastructureHT.Name -StorageAccountKey $STSharedInfrastructurKey Write-Host "`n The bacpas container was these files:" -ForegroundColor Yellow Get-AzureStorageBlob -Container bacpacs -Context $saContext | select Name,BlobType,Length | Format-Table -AutoSize } } Catch { Write-Host "Error upload to Storage account using AZCopy" -foreground Yellow throw $Error[0] } <# +--------------------------------------------------------------------------------------+ | Creating Free SQL Database in Development Resource Group | +--------------------------------------------------------------------------------------+ #> try{ Write-Host "Creating SQL Database" -foreground Green # -- Subscription can have only one free database per region. To provision another free database in same subscription, choose a different region. To provision another free database in same region, use different subscription. -- $DatabaseImportStorageUri = $AzCopyDest + $AzCopyPattern Write-Host "Will be created a database with no data with identification 'SqlFreeDB' " -foreground Green $SqlFreeDBHT = @{ DatabaseName = "SqlFreeDB"; ServerName = $SqlSrvDevelopmentHT.ServerName; Edition = $SqlDatabaseEdition; ResourceGroupName = $RGDevelopmentHT.Name; Tags = @{Environment ="Development"; SqlSrv = $SqlSrvDevelopmentHT.ServerName ; SKU = $SqlDatabaseEdition}; }; #Write-Debug "SqlFreeDB" ($SqlFreeDB | Out-String) New-AzureRmSqlDatabase @SqlFreeDBHT -Verbose } Catch { Write-Host "Error creating SQL Database" -foreground Yellow throw $Error[0] } <# +--------------------------------------------------------------------------------------+ | Create a Databse Form Bacpac File in Production Resource Group | +--------------------------------------------------------------------------------------+ #> try{ IF([string]::IsNullOrEmpty($BacpacLocalFile)) { Write-Host "`nNo database will be created from a bacpac." -foreground Green } else{ Write-Host "`nIt will be created created a new database with basic tier (5 DTUs) from the Bacpac file" $BacpacLocalFile "to database '" $BacpacSqlDatabaseName "'" -foreground Green $BacpacSqlDatabaseNameHT = @{ DatabaseName = $BacpacSqlDatabaseName; Edition = "Basic"; ServiceObjectiveName = "Basic" DatabaseMaxSizeBytes = $SqlDatabaseMaxSizeBytes; StorageKeyType = $StorageKeyType; StorageKey = $STSharedInfrastructurKey; StorageUri = $DatabaseImportStorageUri; AdministratorLogin = $SqlSrvProdUsername; AdministratorLoginPassword = $SqlSrvProdSecurePassword; ServerName = $SqlSrvProductionHT.ServerName; ResourceGroupName = $RGProductionHT.Name; }; #Write-Debug "BacpacSqlDatabaseName" ($BacpacSqlDatabaseName | Out-String) $SqlDatabaseImport = New-AzureRmSqlDatabaseImport @BacpacSqlDatabaseNameHT -Verbose Write-host "Import bacpac" $BacpacLocalFile "to database" $BacpacSqlDatabaseNameHT.DatabaseName -ForegroundColor Green Do { $SqlDatabaseImportStatus= Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $SqlDatabaseImport.OperationStatusLink $SqlDatabaseImportStatus.Status Write-host "Importing database..." $SqlDatabaseImportStatus.StatusMessage "," $SqlDatabaseImportStatus.QueuedTime Start-Sleep -Seconds 20 } until ($SqlDatabaseImportStatus.Status -eq "Succeeded") } } Catch { Write-Host "Error import bacpac" -foreground Yellow throw $Error[0] } <# +--------------------------------------------------------------------------------------+ | Free Database In All Azure Provider Form The Subscription | +--------------------------------------------------------------------------------------+ #> try{ Write-Host "`nCreate Free SQL Database in all Regions" -foreground Green $SqlDatabaseEdition ="Free" foreach ($AzureProvider in (Get-AzureRmResourceProvider -ListAvailable | Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'}).Locations) { $AzureProvider = $AzureProvider.replace(" " , "") if ($AzureProvider -ne $Location){ Write-Host "Region" $AzureProvider $SqlSrvDevelopment = "sqldev-" + $AzureProvider.ToLower() $FreeDBName = "freedb" + $AzureProvider.ToLower() $SqlSrvDevelopmentHT =@{ ServerName =$SqlSrvDevelopment; SqlAdministratorCredentials = $SqlSrvDevCredential; ServerVersion = $SqlSrDevVersion; ResourceGroupName = $RGDevelopmentHT.Name; Location = $AzureProvider; Tags = @{Environment ="Production"} }; New-AzureRmSqlServer @SqlSrvDevelopmentHT -Verbose $FreeDBNameHT = @{ DatabaseName = $FreeDBName; ServerName = $SqlSrvDevelopment; CollationName = $SqlCollationName; Edition = $SqlDatabaseEdition; ResourceGroupName = $RGDevelopmentHT.Name; Tags = @{Environment ="Development"; SqlSrv = $SqlSrvDevelopment.ServerName; SKU = $SqlDatabaseEdition}; }; New-AzureRmSqlDatabase @FreeDBNameHT -Verbose $PublicExternalIPHT = @{ FirewallRuleName = "PublicExternalIP"; StartIpAddress = $PublicExternalIP; EndIpAddress =$PublicExternalIP; }; New-AzureRmSqlServerFirewallRule @PublicExternalIPHT -ServerName $SqlSrvDevelopmentHT.ServerName -ResourceGroupName $RGDevelopmentHT.Name -Verbose New-AzureRmSqlServerFirewallRule -AllowAllAzureIPs -ServerName $SqlSrvDevelopmentHT.ServerName -ResourceGroupName $RGDevelopmentHT.Name -Verbose } } } Catch { Write-Host "" -foreground Red Write-Host $Error[0] -foreground Yellow } <# +--------------------------------------------------------------------------------------+ | Final Report | +--------------------------------------------------------------------------------------+ #> try{ Write-Host "`n`n`n`nReport" -foreground Green Write-Host "`n === Shared Infrastructure === " -foreground Green Write-Host "The resource group with the tag ' Shared Infrastructure ' has the following identification:" (Find-AzureRmResourceGroup -Tag @{ Environment="Shared Infrastructure" }).Name | Format-Table -AutoSize Write-Host "And have these services identified:" -foreground yellow Get-AzureRmResource | Where-Object {$_.ResourceGroupName -eq $RGSharedInfrastructureHT.Name} | select Name, ResourceName, Kind, Location, ResourceId | Format-Table -AutoSize Write-Host "`n === Development ===" -foreground Green Write-Host "The resource group with the tag ' Development ' has the following identification:" (Find-AzureRmResourceGroup -Tag @{ Environment="Development" }).Name | Format-Table -AutoSize Write-Host "And have these services identified:" -foreground yellow Get-AzureRmResource | Where-Object {$_.ResourceGroupName -eq $RGDevelopmentHT.Name} | select Name, ResourceName, Kind, Location, ResourceId | Format-Table -AutoSize Write-Host "`nMicrosoft.Sql/servers in development" -foreground Magenta foreach ($mssqlsrv in (Find-AzureRmResource -ResourceType Microsoft.Sql/servers -ResourceGroupNameEquals $RGDevelopmentHT.Name)){ Write-Host "`n The SQL server" $mssqlsrv.Name "was the following database:" (Find-AzureRmResource -ResourceType Microsoft.Sql/servers/databases -ResourceGroupNameEquals $RGDevelopmentHT.Name -ResourceNameContains sqlsrv-psdev ) | select Name, Kind, Location | Format-Table -AutoSize } Write-Host "`n === Production ===" -foreground Green Write-Host "The resource group with the tag ' Production ' has the following identification:" (Find-AzureRmResourceGroup -Tag @{ Environment="Production" }).Name | Format-Table -AutoSize Write-Host "And have these services identified:" -foreground yellow Get-AzureRmResource | Where-Object {$_.ResourceGroupName -eq $RGProductionHT.Name} | select Name, ResourceName, Kind, Location, ResourceId | Format-Table -AutoSize Write-Host "`nMicrosoft.Sql/servers in production" -foreground Magenta foreach ($mssqlsrv in (Find-AzureRmResource -ResourceType Microsoft.Sql/servers -ResourceGroupNameEquals $RGProductionHT.Name)){ Write-Host "`n The SQL server" $mssqlsrv.Name "was the following database:" (Find-AzureRmResource -ResourceType Microsoft.Sql/servers/databases -ResourceGroupNameEquals $RGProductionHT.Name -ResourceNameContains sqlsrv-psdev ) | select Name, Kind, Location | Format-Table -AutoSize } } Catch { Write-Host "Error showing the report" -foreground Red Write-Host $Error[0] -foreground Yellow } |