# ******************************************************
# *
# * Name:         five-azure-sql-databases.sql
# *     
# * Design Phase:
# *     Author:   John Miner
# *     Date:     11-28-2016
# *     Purpose:  Create azure sql database using resource manager.
# * 
# ******************************************************



#
# Azure Subscriptions 
#

# Prompts you for azure credentials
Add-AzureRmAccount

# Clear the screen
Clear-Host

# List my subscriptions
Get-AzureRmSubscription

# Pick my MSDN one
Set-AzureRmContext -SubscriptionId '26efaaba-5054-4f31-b922-84ab9eff218e'



#
# Data Centers in US with my service
#

# Clear the screen
Clear-Host

# Data centers with Azure SQL database
$DataCenter = Get-AzureRmResourceProvider -ListAvailable
$HasSql = $DataCenter | Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'} | Select-Object -ExpandProperty Locations | Select-String -Pattern "US" -CaseSensitive
$HasSql | Sort



#
# Create a resource group (1 - east us)
#

# New resource group
New-AzureRmResourceGroup -Name "rg4poc1east" -Location "East US" -Tag @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List resource groups
Get-AzureRmResourceGroup -Name "rg4poc1east"

# Delete resource group
# Remove-AzureRmResourceGroup -Name "rg4poc1east" -Force



#
# Create a resource group (2 - west us 2)
#

# New resource group
New-AzureRmResourceGroup -Name "rg4poc2west" -Location "West US" -Tag @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List resource groups
Get-AzureRmResourceGroup -Name "rg4poc2west"

# Delete resource group
# Remove-AzureRmResourceGroup -Name "rg4poc2west" -Force



#
# Create a resource group (3 - uk south)
#

# New resource group
New-AzureRmResourceGroup -Name "rg4poc3uk" -Location "UK South" -Tag @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List resource groups
Get-AzureRmResourceGroup -Name  "rg4poc3uk"

# Delete resource group
# Remove-AzureRmResourceGroup -Name "rg4poc3uk" -Force



#
# Create a resource group (4 - australia east)
#

# New resource group
New-AzureRmResourceGroup -Name "rg4poc4aus" -Location "Australia East" -Tag @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List resource groups
Get-AzureRmResourceGroup -Name  "rg4poc4aus"

# Delete resource group
# Remove-AzureRmResourceGroup -Name "rg4poc4aus" -Force



#
# Create a resource group (5 - japan west)
#

# New resource group
New-AzureRmResourceGroup -Name "rg4poc5jap" -Location "Japan West" -Tag @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List resource groups
Get-AzureRmResourceGroup -Name  "rg4poc5jap"

# Delete resource group
# Remove-AzureRmResourceGroup -Name "rg4poc4aus" -Force



#
# Show all 5 resource groups 
#

# Clear the screen
Clear-Host

# List resource groups
Get-AzureRmResourceGroup | Where-Object {$_.ResourceGroupName -like "rg4poc*"} | Select-Object ResourceGroupName, Location



#
# Create a new server (1 - East US)
#

# Create a credential
$User = "jminer"
$Pword = ConvertTo-SecureString –String 'MS4sql!tips#16' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword

# New sql server
New-AzureRmSqlServer -ResourceGroupName "rg4poc1east" -ServerName "sql4poc1east" -Location "East US" -ServerVersion "12.0" -SqlAdministratorCredentials $Credential -Tags @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List Sql Servers
Get-AzureRmSqlServer -ResourceGroupName "rg4poc1east"

# Remove the sql server
#Remove-AzureRmSqlServer -ResourceGroupName "rg4poc1east" -ServerName "sql4poc1east" 



#
# Create a new server (2 - west us 2)
#

# Create a credential
$User = "jminer"
$Pword = ConvertTo-SecureString –String 'MS4sql!tips#16' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword

# New sql server
New-AzureRmSqlServer -ResourceGroupName "rg4poc2west" -ServerName "sql4poc2west" -Location "West US" -ServerVersion "12.0" -SqlAdministratorCredentials $Credential -Tags @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List Sql Servers
Get-AzureRmSqlServer -ResourceGroupName "rg4poc2west"

# Remove the sql server
#Remove-AzureRmSqlServer -ResourceGroupName "rg4poc2west" -ServerName "sql4poc2west" 



#
# Create a new server (3 - uk south)
#

# Create a credential
$User = "jminer"
$Pword = ConvertTo-SecureString –String 'MS4sql!tips#16' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword

# New sql server
New-AzureRmSqlServer -ResourceGroupName "rg4poc3uk" -ServerName "sql4poc3uk" -Location "UK South"  -SqlAdministratorCredentials $Credential -Tags @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List Sql Servers
Get-AzureRmSqlServer -ResourceGroupName "rg4poc3uk"

# Remove the sql server
#Remove-AzureRmSqlServer -ResourceGroupName "rg4poc3uk" -ServerName "sql4poc3uk" 



#
# Create a new server (4 - australia east)
#

# Create a credential
$User = "jminer"
$Pword = ConvertTo-SecureString –String 'MS4sql!tips#16' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword

# New sql server
New-AzureRmSqlServer -ResourceGroupName "rg4poc4aus" -ServerName "sql4poc4aus" -Location "Australia East" -SqlAdministratorCredentials $Credential -Tags @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List Sql Servers
Get-AzureRmSqlServer -ResourceGroupName "rg4poc4aus"

# Remove the sql server
#Remove-AzureRmSqlServer -ResourceGroupName "rg4poc4aus" -ServerName "sql4poc4aus" 



#
# Create a new server (5 - japan west)
#

# Create a credential
$User = "jminer"
$Pword = ConvertTo-SecureString –String 'MS4sql!tips#16' –AsPlainText -Force
$Credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $Pword

# New sql server
New-AzureRmSqlServer -ResourceGroupName "rg4poc5jap" -ServerName "sql4poc5jap" -Location "Japan West" -SqlAdministratorCredentials $Credential -Tags @{Project="MsSqlTips"}

# Clear the screen
Clear-Host

# List Sql Servers
Get-AzureRmSqlServer -ResourceGroupName "rg4poc5jap"

# Remove the sql server
#Remove-AzureRmSqlServer -ResourceGroupName "rg4poc5jap" -ServerName "sql4poc5jap" 



#
# Show all 5 sql servers 
#

# Clear the screen
Clear-Host

# List sql servers
$List = Get-AzureRmResourceGroup | Where-Object {$_.ResourceGroupName -like "rg4poc*"} 
$List | Get-AzureRmSqlServer | Select-Object ResourceGroupName, ServerName, Location | FL



#
# Add firewall rule (1 - East US)
#

# Create new firewall rule
New-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc1east" -ServerName "sql4poc1east" -FirewallRuleName "fr4desk1east" -StartIpAddress "24.250.26.103" -EndIpAddress "24.250.26.103"

# Clear the screen
Clear-Host

# List firewall rules
Get-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc1east" -ServerName "sql4poc1east"

# Remove firewall rule
# Remove-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc1east" -ServerName "sql4poc1east" -FirewallRuleName "fr4desk1east"



#
# Add firewall rule (2 - west us 2)
#

# Create new firewall rule
New-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc2west" -ServerName "sql4poc2west" -FirewallRuleName "fr4desk2west" -StartIpAddress "24.250.26.103" -EndIpAddress "24.250.26.103"

# Clear the screen
Clear-Host

# List firewall rules
Get-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc2west" -ServerName "sql4poc2west"

# Remove firewall rule
# Remove-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc2west" -ServerName "sql4poc2west" -FirewallRuleName "fr4desk2west"



#
# Add firewall rule (3 - uk south)
#

# Create new firewall rule
New-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc3uk" -ServerName "sql4poc3uk" -FirewallRuleName "fr4desk3uk" -StartIpAddress "24.250.26.103" -EndIpAddress "24.250.26.103"

# Clear the screen
Clear-Host

# List firewall rules
Get-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc3uk" -ServerName "sql4poc3uk"

# Remove firewall rule
# Remove-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc3uk" -ServerName "sql4poc3uk" -FirewallRuleName "fr4desk3uk"



#
# Add firewall rule (4 - australia east)
#

# Create new firewall rule
New-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc4aus" -ServerName "sql4poc4aus" -FirewallRuleName "fr4desk4aus" -StartIpAddress "24.250.26.103" -EndIpAddress "24.250.26.103"

# Clear the screen
Clear-Host

# List firewall rules
Get-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc4aus" -ServerName "sql4poc4aus"

# Remove firewall rule
# Remove-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc4aus" -ServerName "sql4poc4aus" -FirewallRuleName "fr4desk4aus"



#
# Add firewall rule (5 - japan west)
#

# Create new firewall rule
New-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc5jap" -ServerName "sql4poc5jap" -FirewallRuleName "fr4desk5jap" -StartIpAddress "24.250.26.103" -EndIpAddress "24.250.26.103"

# Clear the screen
Clear-Host

# List firewall rules
Get-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc5jap" -ServerName "sql4poc5jap"

# Remove firewall rule
# Remove-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4poc5jap" -ServerName "sql4poc5jap" -FirewallRuleName "fr4desk5jap"