SQLSATURDAY 866 Auckland, New Zealand / Use PowerShell to deploy and configure Azure SQL Database (PaaS) with good resilience to outages and with integrated Azure SQL Analytics monitoring solution

#Deploy_PaaS_Azure_SQL_Database_DR_Environment_With_Integrated_Azure_SQL_Analitics_Solution
<# 
.SYNOPSIS
    Use PowerShell to create cloud DR environment using Azure SQL Databases (PaaS) with integrated azure SQL Analitics Solution and send Email Report with the connection string details.
.DESCRIPTION
    Use PowerShell to deploy cloud service with Azure SQL Database (PaaS) with good resilience to outages (GEO Replications, Failover Groups) and with integrated azure SQL Analitics Solution.
.NOTES
     Author   : Zoran Barac 
     Email    : zoran.barac.zof@gmail.com  
     Date     : 25 June 2019
#>
#########################################################################
# Connect to Azure Account and select Subscription
#########################################################################
# Connect-AzureRmAccount
# Get-AzureRmSubscription
# Select-AzureRmSubscription -SubscriptionId c3bb981c-2079-4418-8980-6b96e1b81252
# Get-AzureRmContext
#########################################################################
#########################################################################
param (
# Azure SQL DB Setup Replication Options
[string]$singledatabase = "YES", 
[string]$replication = "YES",

# Log File Path
[string]$LogPath = "C:\ScriptLogs",

# Set the resource group name and location 
[string]$resourcegroupname = "resource-group-name",
[string]$location = "Australia East",

# Set an admin login and password for your server
[string]$adminlogin = "admin_login",
[string]$password = "P@ssw0rd1234!",

# Set server name - the logical server name has to be unique
[string]$servername = "primary-sql-server-name",
[string]$serverlocation = "Australia East",

# Set failover server name - the logical failover server name has to be unique
[string]$failoverservername = "secondary-sql-server-name",
[string]$failoverserverlocation = "East US",

# Set failover group name
[string]$failovergroupname='failover-group-name',

# The database name
[string]$databasename = "database_name",
[string]$databasepricingtier="S0",

# SQL login and database user
[string]$sqllogin = "user_login_name",
[string]$sqlloginpassword = "P@ssw0rd1234!",
[string]$databaseuser = "user_login_name",
[string]$databaseuserrole="db_owner",

# The ip address range that you want to allow to access your server
[string]$firewallrulename="Office",
[string]$startip = "your_IP_address",
[string]$endip = "your_IP_address",

# Log Analytics Workspace name
[string]$workspacename = "workspace-name",
[string]$metrics =“Basic”,
[String[]]$categories=(“SQLInsights”,”AutomaticTuning”,”QueryStoreWaitstatistics”,”QueryStoreRunTimeStatistics”,”Errors”,”DatabaseWaitStatistics”,”Timeouts”,”Blocks”,”Deadlocks”)
)
cls

#########################################################################
# Create Lof File name based on the timestamp
#########################################################################
$dt = get-date -format yyyyMMddHHmm 
$LogFileName = $LogPath +"\"+$servername+"_"+$dt +".txt"
Start-Transcript -path $LogFileName -Append
#########################################################################
#########################################################################

#########################################################################
# Create a resource group
#########################################################################
Write-Output ((Get-Date -Format g)+ " >>> RESOURCE GROUP CREATING PROCESS >>>")
# Check if resource group already exist
if (Get-AzureRmResourceGroup | Where ResourceGroupName -eq $resourcegroupname)
{
Write-Output ((Get-Date -Format g)+ " - Resource Group "+$resourcegroupname+" already exist")
}
else
{
Write-Output ((Get-Date -Format g)+ " - Creating "+$resourcegroupname+" Resource Group")
New-AzureRmResourceGroup -Name $resourcegroupname -Location $location
Write-Output ((Get-Date -Format g)+ " - Resource Group "+$resourcegroupname+" successfuly created")
}
Write-Output ("")
#########################################################################
#########################################################################

#########################################################################
# Create a Azure SQL Server, adding admin credentials and firewall rules
#########################################################################
if ($singledatabase -eq "YES")
{
Write-Output ((Get-Date -Format g)+ " >>> AZURE LOGICAL SQL SERVER CREATING AND FIREWALL RULES ADDING PROCESS >>>")
# Check if Primary Azure SQL Server already exist
if (Get-AzureRmSqlServer -ResourceGroupName $resourcegroupname | where ServerName -eq $servername)
{
Write-Output ((Get-Date -Format g)+ " - Azure Logical SQL Server "+$servername+" already exist")
}
else
{
Write-Output ((Get-Date -Format g)+ " - Creating "+$servername+" Azure Logical SQL Server")
# Create a server with admin credentials
New-AzureRmSqlServer -ResourceGroupName $resourcegroupname -ServerName $servername -Location $serverlocation -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
Write-Output ((Get-Date -Format g)+ " - Azure Logical SQL Server "+$servername+" successfuly created")
Write-Output ((Get-Date -Format g)+ " - Adding firewall rules for "+$servername+" Azure SQL Server")
# Create a server firewall rule that allows access from the specified IP range
New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname -ServerName $servername -FirewallRuleName $firewallrulename -StartIpAddress $startip -EndIpAddress $endip
Write-Output ((Get-Date -Format g)+ " - Firewall rules for "+$servername+" Azure SQL Server successfuly added")
}
}
Write-Output ("")
#########################################################################
#########################################################################

#########################################################################
# Create a login for the SQL Server (Primary Server)
#########################################################################
# Create a login
$ConnStrMaster = @{
    'Database' = 'master'
    'ServerInstance' = $servername+'.database.windows.net'
    'Username' = $adminlogin
    'Password' = $password
    'Query' = "IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = N'$sqllogin')
               CREATE LOGIN $sqllogin WITH PASSWORD=N'$sqlloginpassword'"
}
Invoke-Sqlcmd @ConnStrMaster
Write-Output ((Get-Date -Format g)+ " - Login "+$sqllogin+" for "+$servername+" Azure SQL Server is successfuly created")
Write-Output ("")
#########################################################################
#########################################################################

#########################################################################
# Create a blank database with an S0 performance level
#########################################################################
Write-Output ((Get-Date -Format g)+ " >>> AZURE SQL DATABASE CREATING PROCESS >>>")
if (Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername | where DatabaseName -eq $databasename)
{
Write-Output ((Get-Date -Format g)+ " - Azure SQL Database "+$databasename+" already exist")
}
else
{
Write-Output ((Get-Date -Format g)+ " - Creating "+$databasename+" Azure SQL Database")
New-AzureRmSqlDatabase  -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -RequestedServiceObjectiveName $databasepricingtier
Write-Output ((Get-Date -Format g)+ " - Azure SQL Database "+$databasename+" successfuly created")
}
Write-Output ("")
#########################################################################
#########################################################################

#########################################################################
# Create DB user and map created login to DB user (Primary Server)
#########################################################################
$ConnStrDB = @{
    'Database' = $databasename 
    'ServerInstance' = $servername+'.database.windows.net'
    'Username' = $adminlogin
    'Password' = $password
    'Query' =  "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$databaseuser')
                CREATE USER $databaseuser
                FOR LOGIN $sqllogin
                GO
                EXEC sp_addrolemember N'$databaseuserrole', N'$databaseuser'
                GO"
}
Invoke-Sqlcmd @ConnStrDB
Write-Output ((Get-Date -Format g)+ " - Database user "+$databaseuser+" for "+$sqllogin+" sql login with "+$databaseuserrole+" role for "+$databasename +" Azure sql database is successfuly created")
Write-Output ("")
#########################################################################
#########################################################################

#########################################################################
# Create a Failover Azure SQL Server, adding admin credentials, firewall rules, creating Failover Group and adding Azure SQL DB to the group
#########################################################################
if ($replication -eq "YES")
{
Write-Output ((Get-Date -Format g)+ " >>> AZURE LOGICAL FAILOVER SQL SERVER CREATING AND FIREWALL RULES ADDING PROCESS >>>")
# Check if Failover Azure SQL Server already exist
if (Get-AzureRmSqlServer -ResourceGroupName $resourcegroupname | where ServerName -eq $failoverservername)
{
Write-Output ((Get-Date -Format g)+ " - Azure Logical SQL Server "+$failoverservername+" already exist")
}
else
{
Write-Output ((Get-Date -Format g)+ " - Azure Logical SQL Failover Server "+$failoverservername+" creating")
# Create a failover server with admin credentials
New-AzureRmSqlServer -ResourceGroupName $resourcegroupname -ServerName $failoverservername -Location $failoverserverlocation -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
Write-Output ((Get-Date -Format g)+ " - Azure Logical SQL Failover Server "+$failoverservername+" successfuly created")
Write-Output ((Get-Date -Format g)+ " - Adding firewall rules for "+$failoverservername+" Azure SQL Failover Server")
# Create a server firewall rule that allows access from the specified IP range
New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname -ServerName $failoverservername -FirewallRuleName $firewallrulename -StartIpAddress $startip -EndIpAddress $endip
Write-Output ((Get-Date -Format g)+ " - Firewall rules for "+$failoverservername+" Azure SQL Failover Server successfuly added")
}
Write-Output ("")
#########################################################################
#########################################################################

#########################################################################
# Create failover group on the primary Azure Logical SQL Server
#########################################################################
Write-Output ((Get-Date -Format g)+ " >>> FAILOVER GROUP ON THE PRIMARY AZURE LOGICAL SQL SERVER CREATING PROCESS >>>")
if (Get-AzureRMSqlDatabaseFailoverGroup -ResourceGroupName $resourcegroupname -ServerName $servername | where FailoverGroupName -eq $failovergroupname)
{
Write-Output ((Get-Date -Format g)+ " - Azure Failover Group "+$failovergroupname+" already exist")
}
else
{
Write-Output ((Get-Date -Format g)+ " - Azure SQL Failover Group "+$failovergroupname+" creating")
New-AzureRMSqlDatabaseFailoverGroup –ResourceGroupName $resourcegroupname -ServerName $servername -PartnerServerName $failoverservername –FailoverGroupName $failovergroupname –FailoverPolicy Automatic -GracePeriodWithDataLossHours 1 
Write-Output ((Get-Date -Format g)+ " - Azure SQL Failover Group "+$failovergroupname+" successfuly created")
}

Write-Output ("")
#########################################################################
#########################################################################

#########################################################################
# Adding created database to existing Failover Group
#########################################################################
Write-Output ((Get-Date -Format g)+ " >>> ADDING THE DATABASE TO THE FAILOVER GROUP PROCESS >>>")
if (Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $failoverservername | where DatabaseName -eq $databasename)
{
Write-Output ((Get-Date -Format g)+ " - Azure SQL Database "+$databasename+" already exist within "+$failovergroupname+" Failover Group")
}
else
{
Write-Output ((Get-Date -Format g)+ " - Adding the Azure SQL Database "+$databasename+" to the newly created "+$failovergroupname+" Failover Group") 
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename | Add-AzureRmSqlDatabaseToFailoverGroup -ResourceGroupName $resourcegroupname -ServerName $servername -FailoverGroupName $failovergroupname 
Write-Output ((Get-Date -Format g)+ " - Azure SQL Database "+$databasename+" successfuly added to the "+$failovergroupname+" Failover Group") 
Write-Output ("")
}

#########################################################################
# Find SID for created user (match SID)
#########################################################################
$SIDMatch = @{
    'Database' = $databasename 
    'ServerInstance' = $servername+'.database.windows.net'
    'Username' = $adminlogin
    'Password' = $password
    'Query' =  "SELECT sid FROM sys.database_principals WHERE name = N'$databaseuser'"
                
}
$results=Invoke-Sqlcmd @SIDMatch
$sidBytes = $results.sid
$sidText = "0x"+ (($sidBytes|ForEach-Object ToString X2) -join '') 
$resultsNew=$sidText.Substring(0, 66)
#########################################################################
#########################################################################

#########################################################################
# Create a login for the SQL Server (Failover Server)
#########################################################################
$ConnStrMasterFailover = @{
    'Database' = 'master'
    'ServerInstance' = $failoverservername+'.database.windows.net'
    'Username' = $adminlogin
    'Password' = $password
    'Query' = "IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = N'$sqllogin')
               CREATE LOGIN $sqllogin WITH PASSWORD=N'$sqlloginpassword', SID =$resultsNew"
}
Invoke-Sqlcmd @ConnStrMasterFailover
Write-Output ((Get-Date -Format g)+ " - Login "+$sqllogin+" with matched SID for "+$failoverservername+" Azure SQL Server is successfuly created")
#########################################################################
#########################################################################
}
Write-Output ("")
#########################################################################
#########################################################################

#########################################################################
# Create the workspace
#########################################################################
Write-Output ((Get-Date -Format g)+ " - Creating the workspace "+$workspacename)
New-AzureRmOperationalInsightsWorkspace -Location $Location -Name $workspacename -ResourceGroupName $resourcegroupname
Set-AzureRmOperationalInsightsIntelligencePack -ResourceGroupName $resourcegroupname -workspacename $workspacename -IntelligencePackName 'AzureSQLAnalytics' -Enabled $true
try {
$sqlDbInfo = Get-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $resourcegroupname
$sqlresourceID = $($sqlDbInfo).ResourceId.ToString()
$getworkspaceid =Get-AzureRmOperationalInsightsWorkspace -Name $workspacename -ResourceGroupName $resourcegroupname 
$workspaceid=$($getworkSpaceid).ResourceId.ToString()
Set-AzureRmDiagnosticSetting -ResourceId $sqlresourceID -WorkspaceID $WorkspaceID -MetricCategory $Metrics -Enabled $true -RetentionInDays 20 -Categorie $categories
}
Catch {$ErrorMessage = $_.Exception.Message 
“Write-Error Creating Analytics. Error was $ErrorMessage”}
Write-Output ((Get-Date -Format g)+ " - Workspace "+$workspacename+" successfuly created")
#########################################################################
#########################################################################

#########################################################################
# Send LogFile in the email attachment
#########################################################################
$EmailFrom = "from.email.address@gmail.com"
$EmailTo = "to.email.address@gmail.com"
$EmailSubject = "Azure DB Deployment for '"+$databasename+"' Database"
$SMTPServer = "smtp.gmail.com"
$SMTPAuthUsername = "from.email.address@gmail.com"
$SMTPAuthPassword = "YourEmailPassword"
$mailmessage = New-Object system.net.mail.mailmessage 
$mailmessage.from = ($emailfrom) 
$mailmessage.To.add($emailto)
$mailmessage.Subject = $emailsubject
$mailmessage.Body = "Primary Logical SQL Server: "+$servername+".database.windows.net"+ "`n" + "`n"+"Failover Logical SQL Server: "+$failoverservername+".database.windows.net"+ "`n" + "`n"+"Read/write listener endpoint: "+$failovergroupname+".database.windows.net"+ "`n" + "`n"+ "Azure Database Name: "+$databasename+ "`n" + "`n"+ "Azure Database User: "+$databaseuser+ "`n" + "`n"+ "Azure Database Role: "+$databaseuserrole
#$attachment = New-Object System.Net.Mail.Attachment($emailattachment, 'text/plain')
#$mailmessage.Attachments.Add($attachment)
$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, "587") 
$SMTPClient.EnableSsl= $true 
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("$SMTPAuthUsername", "$SMTPAuthPassword") 
$SMTPClient.Send($mailmessage)
#########################################################################
# End Sending Notification Email
#########################################################################
Stop-Transcript