azure云数据库
介绍 (Introduction)
If you have an Azure SQL Database, it is possible to replicate the data in multiple locations. In this new chapter, we will explain how to do it. This article has 3 sections:
如果您有Azure SQL数据库，则可以在多个位置复制数据。 在这一新的章节中，我们将说明如何做到这一点。 本文分为三个部分：
Create a primary server in USA and a secondary replication in Europe
在美国创建主服务器，在欧洲创建辅助复制
Test the Geo-replica and check the functionality
测试地理副本并检查功能
Failover the servers
对服务器进行故障转移
Requirements
要求
An Azure Account
Azure帐户
A Facebook Account
一个Facebook帐户
A local machine with SQL Server Management Studio (SSMS)
具有SQL Server Management Studio（SSMS）的本地计算机
入门 (Getting started)
地理复制创建 (Geo-replication creation)
In the Azure Portal, we will have an Azure SQL database named SQLSHACK in a server named usaserver:
在Azure门户中，我们将在名为usaserver的服务器中具有一个名为SQLSHACK的Azure SQL数据库：
Figure 1. The USA Server with the Adventureworks installed图1.安装了Adventureworks的USA服务器
Create another Server named sqleuropserver with a blank database:
使用空白数据库创建另一个名为sqleuropserver的服务器：
Figure 2. The European Server used to store the replica图2.用于存储副本的欧洲服务器
To replicate, click the usaserver database:
要复制，请单击usaserver数据库：
Figure 3. Primary database图3.主数据库
In Geo-Replication role, click on Not configured:
在“地理复制”角色中，单击“未配置”：
Figure 4. Geo-Replication option图4.地理复制选项
Click on regions where you want to replicate (in this case, Europe). The number of regions available may change in the future, but by the moment these are the regions available where Microsoft have the Data Centers:
单击要复制的区域（在本例中为欧洲）。 可用区域的数量将来可能会更改，但是目前这些是Microsoft拥有数据中心的可用区域：
USA (East, West, North, South) 美国（东，西，北，南） Canada (East, Central) 加拿大（东部，中部） Australia (East, Southeast) 澳大利亚（东，东南） Japan (East, West) 日本（东，西） Brazil (South) 巴西（南部） Asia (East, Southeast) 亚洲（东，东南） Europe (North, West) 欧洲（北部，西部）
Figure 5. The regions available to create servers图5.可用于创建服务器的区域
Select the server to be the secondary server. In this example, the sqleuropeserver created on step 2:
选择服务器作为辅助服务器。 在此示例中，在步骤2中创建的sqleuropeserver：
Figure 6. Selecting the secondary readable database图6.选择辅助可读数据库
Depending on the size of your database, it will take some time to replicate the database in Europe. In this example, the Adventureworks database was not so big. It took 3-5 minutes to copy a replica of the database. If everything is OK you will have an image similar to this one:
根据数据库的大小，在欧洲复制数据库将花费一些时间。 在此示例中，Adventureworks数据库不是很大。 复制数据库副本花费了3-5分钟。 如果一切正常，您将获得与此图像相似的图像：
Figure 7. The blue hexagon is the main database and the filled green one is the secondary database running图7.蓝色的六边形是主数据库，填充的绿色的六边形是正在运行的辅助数据库
That is all and now you have a replica already running! You can go to SQL databases in the Azure Portal and check that there are two databases. The primary database in South Central USA and the replica in West Europe. It is extremely easy compared with the local Replications in traditional SQL Server Database or Log Shipping:
仅此而已，现在您已经有一个副本正在运行！ 您可以转到Azure门户中SQL数据库，并检查是否有两个数据库。 美国中南部的主要数据库，西欧的副本。 与传统SQL Server数据库或日志传送中的本地复制相比，这非常容易：
Figure 8. The sqlshack database with the primary copy in South Central US and the secondary database copy in West Europe图8. sqlshack数据库，其中主副本位于美国中南部，而辅助数据库副本位于西欧
测试地理复制 (Testing the Geo-Replication)
We have the replication ready to use. In this section, we will create a table with data, stored procedures to test if the Geo-Replication is working. For this section, we will use the SSMS. You may need to enable the local IP in the Azure Portal to enable the access to SSMS to the primary and secondary server.
我们已准备好使用复制。 在本节中，我们将创建一个包含数据，存储过程的表，以测试Geo-Replication是否正常工作。 在本节中，我们将使用SSMS。 您可能需要在Azure门户中启用本地IP，以启用对主服务器和辅助服务器对SSMS的访问。
Create a table in the primary Database:
在主数据库中创建一个表：
create table dbo.sales(id int, productid int, value int)
Figure 9. T-SQL statements to create a table图9.创建表的T-SQL语句
Verify the table created in usaserver:
验证在usaserver中创建的表：
Figure 10. Sales table in the Primary Server图10.主服务器中的销售表
Verify in the secondary Server in Europe if the new table is replicated. As you can see, the table was replicated successfully:
在欧洲的辅助服务器中验证是否复制了新表。 如您所见，该表已成功复制：
Figure 11. European secondary table图11.欧洲二级表
Insert some data in the primary table:
在主表中插入一些数据：
insert into dbo.sales values (1,1,56)
Verify that the table is replicated in the secondary table:
验证表是否在辅助表中复制：
Figure 12. Checking the table replicated in the secondary server图12.检查在辅助服务器中复制的表
In the secondary database, try to create a stored procedure using the following T-SQL sentence:
在辅助数据库中，尝试使用以下T-SQL语句创建存储过程：
create procedure p_showsalesasselect * from [dbo].[sales]
When you try to create a procedure, the following error message is displayed:
当您尝试创建过程时，将显示以下错误消息：
Figure 13. Read-only message图13.只读消息
Msg 3906, Level 16, State 2, Procedure p_showsales, Line 3
Failed to update database “sqlshack” because the database is read-only.
消息3906，第16级，状态2，过程p_showsales，第3行
由于数据库是只读的，因此无法更新数据库“ sqlshack”。
This is the expected behavior. You cannot create or insert objects on the secondary database because it is read-only. Now, we will try to create a stored procedure in the primary database:
这是预期的行为。 您不能在辅助数据库上创建或插入对象，因为它是只读的。 现在，我们将尝试在主数据库中创建一个存储过程：
create procedure p_showsalesasselect * from [dbo].[sales]
In the SSMS, select the database and go to Programmability ➜ Stored Procedures to verify that the stored procedure is created:
在SSMS中，选择数据库，然后转到Programmability➜Stored Procedures以验证是否创建了存储过程：
Figure 14. Verifying that the stored procedure was created图14.验证存储过程已创建
It is possible that you will receive the following error message:
您可能会收到以下错误消息：
Failed to retrieve for this request (Microsoft.SqlServer.Management.Sdk.Sfc)
unknown property IsNativelyCompiled (Microsoft.SqlServer.Management.Sdk.Sfc)
无法检索此请求（Microsoft.SqlServer.Management.Sdk.Sfc）
未知属性IsNativelyCompiled（Microsoft.SqlServer.Management.Sdk.Sfc）
Figure 15. Common error message图15.常见错误消息
If you have that error, you may need to install the last service pack. I received this error in SQL without a service pack. I never have this error in SQL .
如果出现该错误，则可能需要安装最后一个Service Pack 。 我在没有Service PackSQL 中收到此错误。 我在SQL 中从未遇到此错误。
As you can see, Geo-Replication replicates not only tables, but also any object like stored procedures. Run the stored procedure in the secondary server:
如您所见，Geo-Replication不仅复制表，而且还复制任何对象，例如存储过程。 在辅助服务器上运行存储过程：
EXEC[dbo].[p_showsales]
Figure 16. Testing the stored procedure on secondary server图16.在辅助服务器上测试存储过程
地理复制中的故障转移 (Failover in Geo-Replication)
Finally, we will Failover. We will convert the secondary server in the primary and vice versa.
最后，我们将进行故障转移。 我们将在主要服务器中转换辅助服务器，反之亦然。
We recommend using the Azure Portal, but if you want to automate tasks using T-SQL, it is a good practice to know system views that contain Geo-Replication information. We will introduce the use of the sys.geo_replication_links. To learn it, run this query:
我们建议您使用Azure门户，但是如果您想使用T-SQL自动执行任务，则最好的方法是了解包含Geo-Replication信息的系统视图。 我们将介绍sys.geo_replication_links的用法。 要学习它，请运行以下查询：
select * from sys.geo_replication_links
Figure 17. System view used to register Geo-replication information图17.用于注册地理复制信息的系统视图
This view contains the link_guid, which is an identifier, the modify_date that contains the date when it was modified the last time. Partner_server is the name of the server. The Partner database is the database used in the Geo-replication.
该视图包含link_guid（它是一个标识符）和Modify_date（包含上次修改日期）。 Partner_server是服务器的名称。 合作伙伴数据库是在地理复制中使用的数据库。
Replication state contains the status of the replication. It has 3 states:
复制状态包含复制的状态。 它具有3个状态：
0 means that the replication process is pending. The replication is scheduled, but it is not completed.
0表示复制过程处于挂起状态。 复制已安排，但尚未完成。
1 means that the replication process is seeding, but the primary and secondary databases are not synchronized yet.
1表示复制过程正在播种，但是主数据库和辅助数据库尚未同步。
2 means that replication is completed.
2表示复制已完成。
There are also two possible values in the role column:
角色列中还有两个可能的值：
0 is the primary database
0是主数据库
1 is secondary
1是次要的
We will use this table to verify that the Failover is completed.
我们将使用此表来验证故障转移是否已完成。
In the Azure Portal click the primary database and in Geo-Replication click the primary link:
在Azure门户中单击主数据库，然后在“地理复制”中单击主链接：
Figure 18. Changing the primary server图18.更改主服务器
On the secondary database, right click and select Failover:
在辅助数据库上，右键单击并选择故障转移：
Figure 19. Failover in Geo-Replication图19.地理复制中的故障转移
You will receive a confirmation message. Click Yes:
您将收到确认消息。 单击是：
Figure 20. Confirmation message图20.确认消息
After some few minutes, you will be able to see that the usaserver is now secondary:
几分钟后，您将可以看到usaserver现在是辅助服务器：
Figure 21. The primary server is now the secondary图21.主服务器现在是辅助服务器
If you use the system view you can verify that the role also changed:
如果使用系统视图，则可以验证角色也已更改：
select * from sys.geo_replication_links
Figure 22. The role is now secondary图22.角色现在是第二角色
Finally, you can monitor replication operations using the sys.dm_operaion_status view:
最后，您可以使用sys.dm_operaion_status视图监视复制操作：
select * from sys.dm_operation_status
Figure 23. Geo-Replication operations图23.地理复制操作
结论 (Conclusion)
Geo-replication is a very cool feature for high availability. Compared with the local traditional Replication, Log Shipping, and Always On it is extremely easy, intuitive to apply and learn it. I cannot imagine an easier way to create it.
地理复制是一项非常酷的功能，可实现高可用性。 与本地传统的Replication ， Log Shipping和Always On相比，它非常容易，直观地应用和学习。 我无法想象更简单的方法来创建它。
In this article, we learned how to implement Geo-replication, how to test that it works fine, we verified that the secondary is just read-only and then we failover to switch roles between primary and secondary servers.
在本文中，我们学习了如何实现地理复制，如何测试它是否正常运行，我们验证了辅助数据库是只读的，然后我们进行了故障转移以在主服务器和辅助服务器之间切换角色。
翻译自: /geo-replication-azure-sql-database/
azure云数据库
