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:

1. Create a primary server in USA and a secondary replication in Europe

2. Test the Geo-replica and check the functionality

3. Failover the servers

Requirements

1. An Azure Account

2. A Facebook Account

3. A local machine with SQL Server Management Studio (SSMS)

Getting started

Geo-replication creation

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

2. Create another Server named sqleuropserver with a blank database:

   
   

   Figure 2. The European Server used to store the replica

3. To replicate, click the usaserver database:

   
   

   Figure 3. Primary database

4. In Geo-Replication role, click on Not configured:

   
   

   Figure 4. Geo-Replication option

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

6. Select the server to be the secondary server. In this example, the sqleuropeserver created on step 2:

   
   

   Figure 6. Selecting the secondary readable database

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

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

1. Create a table in the primary Database:

   1 2 3

   create table dbo.sales(id int, productid int, value int)

   
   

   Figure 9. T-SQL statements to create a table

2. Verify the table created in usaserver:

   
   

   Figure 10. Sales table in the Primary Server

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

4. Insert some data in the primary table:

   1 2 3

   insert into dbo.sales values (1,1,56)

5. Verify that the table is replicated in the secondary table:

   
   

   Figure 12. Checking the table replicated in the secondary server

6. In the secondary database, try to create a stored procedure using the following T-SQL sentence:

   1 2 3 4 5

   create procedure p_showsales as select * from [dbo].[sales]

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

8. 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:

   1 2 3 4 5

   create procedure p_showsales as select * from [dbo].[sales]

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

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

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

12. 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:

    1 2 3

    EXEC [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.

1. 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:

   1 2 3

   select * 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.

2. In the Azure Portal click the primary database and in Geo-Replication click the primary link:

   
   

   Figure 18. Changing the primary server

3. On the secondary database, right click and select Failover:

   
   

   Figure 19. Failover in Geo-Replication

4. You will receive a confirmation message. Click Yes:

   
   

   Figure 20. Confirmation message

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

6. If you use the system view you can verify that the role also changed:

   1 2 3

   select * from sys.geo_replication_links

   
   

   Figure 22. The role is now secondary

7. Finally, you can monitor replication operations using the sys.dm_operaion_status view:

   1 2 3

   select * from sys.dm_operation_status

   
   

   Figure 23. Geo-Replication operations

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.

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.

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

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

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023