PowerShell operations Azure SQL Active geo-replication

The main features and benefits of Azure SQL Database Active geo-replication are described in more detail in the previous article. Next we'll show you how to implement Active Geo-replication in your project through PowerShell from an automated perspective.

start from Azure PowerShell

MS specifically wrote a set of PowerShell components called Azure PowerShell for managing Azure. We're just going to use the interface provided in this set of components to manipulate the Active geo-replication. Unfortunately, this set of components is not installed by Windows by default. So please refer to the instructions on MSDN to install Azure PowerShell first. Students who do not like to read MSDN can refer to the "Azure Module for installing PowerShell" section of the author's blog, "Azure Basics: Automatically publish Cloudservices with PowerShell", which is a little more concise.

sign in to Azure in PowerShell

Any resource in Azure needs to be authenticated, so the first step is to sign in to Azure in PowerShell and choose the right Azure subscription.
Commands to sign in to Azure:

Login-azurermaccount

please follow the prompts to enter your account number and password. then select the current subscription:

Select-azurermsubscription-subscriptionid xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

The usage scenario for the select-azurermsubscription command is that when you have multiple subscription, you need to tell the PowerShell command exactly which resources to manipulate by setting the current subscription. Of course, if you have only one subscription, you don't need to execute this command.

add a database from the

It's finally getting to the chase! Suppose we have a database called Blogdb, which runs in the database server BLOGTESTSVR, the database server is deployed in East Asia (East Asia), and we intend to create a database server from the database that runs from the database for Blogdb blogtests VR2 is deployed in central US (United States). Both Blogtestsvr and BLOGTESTSVR2 belong to the same resource group Sqltest. If you are not too familiar with the concept of master-slave database or Active geo-replication, please go here first.

new-azurermsqldatabasesecondary Command

New-azurermsqldatabasesecondary is a command that is created specifically for an already existing database from the database, and begins copying data after the database is created. There is also a command called Start-azuresqldatabasecopy can do the same thing, but the function of the start-azuresqldatabasecopy command is too complicated, so need to create from the database when the best or use New-azurermsqldatabasesecondary. The following command creates the first one from the database for BLOGDB:

$db= Get-azurermsqldatabase–databasename"Blogdb"-resourcegroupname"sqltest"-servername"Blogtestsvr"$secondaryLink=$db| New-azurermsqldatabasesecondary–partnerresourcegroupname"sqltest"–partnerservername"BLOGTESTSVR2"-allowconnections" All"

Check the results on the portal first:

The blue hexagon represents the primary database, and the green hexagon indicates the database that we just created, note that the state shown from the database is "readable". At the same time, the location of the database identified on the map is the location of the real data center. Explains that the PowerShell command has been successfully created for BLOGDB from the database. Simply say the above command:
The Get-azurermsqldatabase command is used to obtain information about the primary database and then pass it to the new-azurermsqldatabasesecondary command, otherwise new-azurermsqldatabasesecondary The parameter list is long and unclear.
The parameters of the New-azurermsqldatabasesecondary command are primarily used to indicate the resource group from which the database resides, and the server name. There is also the allowconnections parameter, please configure it as "all".

Precautions

After the successful primary database has been created from the database, let's take a look at some things to be aware of.
Attentive friends may have noticed that the newly created names are the same from the database and the primary database. And there is a less intuitive thing is also the same: Service level, plainly speaking is from the database and the main database is the same money received! Of course you can save your budget by setting the Service level from the database separately.
The creation of a database is divided into two processes, that is, the creation of a database and the replication of data. The creation of a database may be fast, but copying the data is not necessarily the case, mainly looking at the size of the library. Because the New-azurermsqldatabasesecondary command will not return after both steps have been completed, the command will take longer to execute when your database is larger.
Also, when a database with the same name already exists, the New-azurermsqldatabasesecondary command returns an error:

removing from a database

know how to create from a database and of course also be able to remove it, below we use The remove-azurermsqldatabasesecondary command removes from the database that you just created.

  $db  = get-azurermsqldatabase–databasename  blogdb   "-resourcegroupname  " sqltest  "  -servername  " blogtestsvr  "   $secondaryLink  =  $db  | Get-azurermsqldatabasereplicationlink–partnerresourcegroup  " sqltest  "  –partnerservername  " BLOGTESTSVR2   "  $secondaryLink  | Remove-azurermsqldatabasesecondary 

The command itself is nothing to explain, after the completion of the implementation go to the portal to see, from the database has disappeared. Oh, wait ... It seems that the database has not been deleted! Yes, the remove-azurermsqldatabasesecondary command simply removes the relationship between the master and slave databases and does not delete the from database. Instead, the database has become a read-write standalone database at this point.

Disaster Recovery

Let's focus on the main use case for Active geo-replication: disaster recovery. When a failure occurs in the primary database. We need to convert one from the database to the primary database as soon as possible through the set-azurermsqldatabasesecondary command.

$db " Blogdb " " sqltest " "  "sqltest"  -failover

Look at the current situation on the Portal:

Get! Originally read-Only from the database has become a read-write master database. Note that the location of the master-slave database, is not they have converted the role it!
Let's take a quick look at the following set-azurermsqldatabasesecondary commands and what to look for in use.
The execution of the set-azurermsqldatabasesecondary command is also divided into two stages. The first is to switch the synchronization mode of the data replication operation, complete all the data copied from the database (to ensure that the contents of the master and slave libraries are the same before switching roles). The role of the master-slave database is then switched. After the role switch is complete, the data is synchronized from the new primary database to the newer database. Of course, the process of switching is a cost, MSDN said about 0-25 seconds of time the database will not be available, but this time will not exceed 1 minutes.

Summary

This article uses the interfaces provided by Azure PowerShell to create, remove, and restore Azure SQL Database Active geo-replication operations. Although it appears that PowerShell scripts are quite numerous (mostly parametric), there are only three core interfaces involved: New-azurermsqldatabasesecondary,remove-azurermsqldatabasesecondary and Set-azurermsqldatabasesecondary. In view of the limited personal knowledge, many of the details are not involved, all should be a catalyst.

PowerShell operations Azure SQL Active geo-replication