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:
Create a primary server in USA and a secondary replication in Europe
Test the Geo-replica and check the functionality
Failover the servers
An Azure Account
A Facebook Account
A local machine with SQL Server Management Studio (SSMS)
In the Azure Portal, we will have an Azure SQL database named SQLSHACK in a server named usaserver:
Figure 1. The USA Server with the Adventureworks installed
Create another Server named sqleuropserver with a blank database:
Figure 2. The European Server used to store the replica
To replicate, click the usaserver database:
Figure 3. Primary database
In Geo-Replication role, click on Not configured:
Figure 4. Geo-Replication option
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:
- 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
Select the server to be the secondary server. In this example, the sqleuropeserver created on step 2:
Figure 6. Selecting the secondary readable database
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:
Figure 7. The blue hexagon is the main database and the filled green one is the secondary database running
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:
Figure 8. The sqlshack database with the primary copy in South Central US and the secondary database copy in West Europe
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.
Create a table in the primary Database:123create table dbo.sales(id int, productid int, value int)
Figure 9. T-SQL statements to create a table
Verify the table created in usaserver:
Figure 10. Sales table in the Primary Server
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
Insert some data in the primary table:123insert 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
In the secondary database, try to create a stored procedure using the following T-SQL sentence:12345create procedure p_showsalesasselect * from [dbo].[sales]
When you try to create a procedure, the following error message is displayed:
Figure 13. Read-only message
Msg 3906, Level 16, State 2, Procedure p_showsales, Line 3
Failed to update database “sqlshack” because the database is read-only.
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:12345create 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:
Figure 14. Verifying that the stored procedure was created
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)
Figure 15. Common error message
If you have that error, you may need to install the last service pack. I received this error in SQL 2014 without a service pack. I never have this error in SQL 2016.
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:123EXEC [dbo].[p_showsales]
Figure 16. Testing the stored procedure on secondary server
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:123select * from sys.geo_replication_links
Figure 17. System view used to register Geo-replication information
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.
Replication state contains the status of the replication. It has 3 states:
0 means that the replication process is pending. The replication is scheduled, but it is not completed.
1 means that the replication process is seeding, but the primary and secondary databases are not synchronized yet.
2 means that replication is completed.
There are also two possible values in the role column:
0 is the primary database
1 is secondary
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:
Figure 18. Changing the primary server
On the secondary database, right click and select Failover:
Figure 19. Failover in Geo-Replication
You will receive a confirmation message. Click Yes:
Figure 20. Confirmation message
After some few minutes, you will be able to see that the usaserver is now secondary:
Figure 21. The primary server is now the secondary
If you use the system view you can verify that the role also changed:123select * from sys.geo_replication_links
Figure 22. The role is now secondary
Finally, you can monitor replication operations using the sys.dm_operaion_status view:123select * from sys.dm_operation_status
Figure 23. Geo-Replication operations
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.
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.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte