Deploy Azure SQL database DR environment (PaaS) using PowerShell

Posted by Barac in Azure, Powershell on Jan 21st, 2019 | 0 comments

Script Download:

The script with usage example is available for download from https://gallery.technet.microsoft.com/Deploy-Azure-SQL-DR-47dfa7f7

Summary:

Use PowerShell to create cloud DR environment using Azure SQL Databases (PaaS) 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)

PowerShell:

Connect to Azure Account,
Use existing or create new Resource Group,
Create primary Azure Logical SQL Server,
Set an admin login and password for your server,
Set Firewall Settings (IP address range),
Create Azure SQL Database,
Create a new login for the Azure SQL Server,
Create a new DB user for the created login,
Create failover Azure Logical SQL Server, including admin login, password and firewall rules (Optional),
Create Failover Group Name (Optional),
Add Database to the existing Failover Group (Optional),
Create a new login for the Failover Azure SQL Server with matching SID (Optional),
Send Email notification with connection string parameters.

Script usage example:

# Azure SQL DB Setup replication Option
[string]$singledatabase = "YES",
[string]$replication = "YES",

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

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

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

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

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

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

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

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

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

Note.

You can repeat the script multiple times. If the resource you are creating already exist script will just skip that part with a message.

Note.
You should use read/write listener endpoint within the connection string.

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

Azure / PowerShell / Deploy Azure SQL database DR environment (PaaS) using PowerShell

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud