# ******************************************************
# *
# * Name:         step01-create-azure-sql-db.ps1
# *     
# * Design Phase:
# *     Author:   John Miner
# *     Date:     01-17-2018
# *     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 internal subscription
$SubscriptionId = 'cdfb69bf-3533-4c89-9684-1ba6ba81b226'
Set-AzureRmContext -SubscriptionId $SubscriptionId



#
# Data Centers with my service
#

# Clear the screen
Clear-Host

# Data centers with Azure SQL database
$AzureSQLLocations = (Get-AzureRmResourceProvider -ListAvailable | 
    Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'}).Locations
$AzureSQLLocations



#
# Create a resource group
#

# New resource group
New-AzureRmResourceGroup -Name "rg4tips18" -Location "East US"

# Clear the screen
Clear-Host

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

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



#
# Create a new server
#

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

# New sql server
New-AzureRmSqlServer -ResourceGroupName "rg4tips18" -ServerName "svr4tips18" -Location "East US" -SqlAdministratorCredentials $Credential

# Clear the screen
Clear-Host

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

# Remove the sql server
#Remove-AzureRmSqlServer -ResourceGroupName "rg4tips18" -ServerName "svr4tips18" 



#
# Add laptop ip to firewall (todo = code to figure out ip since I am behind a router)
#

# External ip
$MyIp = (Invoke-WebRequest ifconfig.me/ip).Content -replace "`n","" 
$MyIp

# Create new firewall rule
New-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4tips18" -ServerName "svr4tips18" -FirewallRuleName "fr4laptop" -StartIpAddress "$MyIp" -EndIpAddress "$MyIp"

# Clear the screen
Clear-Host

# List firewall rules
Get-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4tips18" -ServerName "svr4tips18"


# Remove firewall rule
# Remove-AzureRmSqlServerFirewallRule -ResourceGroupName "rg4tips18" -ServerName "svr4tips18" -FirewallRuleName "fr4laptop"



#
# Make the new database
#

# Create a new database
New-AzureRmSqlDatabase -ResourceGroupName "rg4tips18" -ServerName "svr4tips18" -DatabaseName "db4primes" -Edition "Basic"

# Clear the screen
Clear-Host

# List the new database
Get-AzureRmSqlDatabase -ResourceGroupName "rg4tips18" -ServerName "svr4tips18"


# Remove the database
# Remove-AzureRmSqlDatabase -ResourceGroupName "rg4tips18" -ServerName "svr4tips18" -DatabaseName "db4primes"



#
# Change the database size (b)
#

Login-AzureRmAccount

# Clear the screen
Clear-Host

# Give database size quota
$TwoGb = (1024 * 1024 * 1024 * 2)
Set-AzureRmSqlDatabase -ResourceGroupName "rg4tips18" -ServerName "svr4ssug" -DatabaseName "db4stocks" -Edition "Basic"  -MaxSizeBytes $TwoGb