PowerShell operations Azure SQL Active geo-replication Combat

In the article "Introduction to Azure SQL database active geo-replication ," We have a more comprehensive overview of the key features and benefits of Azure SQL database active geo-replication. Next, we'll show you how to implement Active geo-replication operations through PowerShell in your project from an automated perspective.

one, starting with Azure PowerShell

MS writes a set of PowerShell components specifically for managing Azure, called Azure PowerShell, and we're going to use the interfaces provided in this set of components to manipulate the Active geo-replication. Unfortunately, this set of components is not installed by default in Windows, 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 author's blog, " using PowerShell Automation cloudservices release ", the section on "Azure Module for installing PowerShell" is a little more concise.

Ii. 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.

Third, add a database from the

Get down to the chase! Suppose we have a database called Blogdb, which runs on the database server BLOGTESTSVR, this 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.

1. New-azurermsqldatabasesecondary command

New-azurermsqldatabasesecondary is a command that is created specifically for an already existing database from the database, and the data is copied 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 describe 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 and server name from which the database resides. There is also the allowconnections parameter, please configure it as "all".

2. 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 point not too intuitive: Service level, in fact, from the database and the main database received the same money! Of course you can save your budget by setting the Service level from the database separately.

• Creation from a database is divided into two processes: creating a database and replicating data. The creation of the database may be fast, but the replication data is not determined, mainly to look 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.

• When a database with the same name already exists, the New-azurermsqldatabasesecondary command returns an error:

Iv. removing from the database

Know how to create from the database, of course, to be able to remove it, below we use the remove-azurermsqldatabasesecondary command to remove the database 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 relatively simple, after the completion of the implementation of 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.

v. 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 = Get-azurermsqldatabase–databasename "Blogdb" –resourcegroupname "Sqltest" –servername "BLOGTESTSVR2" $db | Set-azurermsqldatabasesecondary-partnerresourcegroupname "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!

Next, let's take a quick look at the set-azurermsqldatabasesecondary commands and what to look for in use.

The execution of the set-azurermsqldatabasesecondary command is also divided into two stages:

• First, it is the synchronous mode of switching the data copy operation to complete all data copying from the database (to ensure that the contents of the master and slave libraries are the same before switching roles).
• Then, the role of the master-slave database is switched. After the role switch is complete, the new primary database is started, and the data is synchronized 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. The actual operation of many details are not involved, I hope this article can play a role.

PowerShell operations Azure SQL Active geo-replication Combat