Moving to SQL Azure Process/Availability Group

Question

We're moving our on-premises SQL Server database to Azure with failover availability group, split between UKSouth and UKWest.

I initially tried to export the on-premises database to a single instance of SQL Azure using the 'Deploy to SQL Azure' wizard in SSMS. This process worked perfectly.

The failover group has the following structure. sqlbox.database.windows.net is a primary endpoint that points to sqlbox-uksouth.database.windows.net and sqlbox-ukwest.database.windows.net as the secondary.

I tried the 'Deploy to SQL Azure' wizard in SSMS to sqlbox.database.windows.net and I was getting an error. I did try to create this database first via script, assign it to the elastic pool, then try the wizard. I thought this was working when I done it late one night, however I tried it again the next morning and it didn't work - I now assume I was mistaken about it working and checked the wrong database or something.

I then tried the 'Deploy to SQL Azure' wizard to sqlbox-uksouth.database.windows.net and ran the following script.

ALTER DATABASE sqlboxdatabase1
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = [sqlbox-ElasticPool] ) ) ;  
GO

ALTER DATABASE sqlboxdatabase1
ADD SECONDARY ON SERVER [sqlbox-ukwest]
WITH ( ALLOW_CONNECTIONS = ALL )  
GO

This created the database and added the secondary. The database appeared on sqlbox.database.windows.net however I couldn't access it as it was 'unavailable'. Via the Azure Portal the Ops guys could add these to the availability group and everything worked as intended, however I need to do this via script.

I found https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql which I believe shows what I'm trying to do in SQL Server 2012, but I can't find anything for SQL Azure.

Could someone please either help me with this script or guide me to a better way of achieving what I'm trying to achieve? I'm trying to avoid using any other three party tools.

hokkaidi hokkaidi · Accepted Answer · 2018-02-09T22:33:16

Regarding your first question, databases should not be deployed on the end-point listener sqlbox.database.windows.net. They should be deployed on the primary server, sqlbox-uksouth.database.windows.net. This is because the end-point listener is not an Azure SQL server properly speaking and therefore it cannot host a database.

Regarding your second question, we have to remember that active geo-replication and fail-over groups are two distinct things although closely related. Having a database geo-replicated between the primary server and the secondary server of a failover group does not imply that the database is also part of the failover group. Adding the database to the failover group is another operation.

Going back to your example, when you run

ALTER DATABASE sqlboxdatabase1
ADD SECONDARY ON SERVER [sqlbox-ukwest]
WITH ( ALLOW_CONNECTIONS = ALL )

you create a read-only secondary replica of your database on sqlbox-ukwest.database.windows. This replica mirrors its primary sister located on sqlbox-uksouth.database.windows.net. However, the two databases are not part of your fail-over group yet. The fact that they are not part of your failover group yet is the reason why they don't show up on sqlbox.database.windows.net.

To have your newly deployed database and its secondary replica show up on sqlbox.database.windows.net, you have to add them to your fail-over group. This has to be done with the Azure REST API or a PowerShell script. There is currently no T-SQL command to add a database and its geo-replicated replica to a failover group. You have to use the REST API or PowerShell.

Moving to SQL Azure Process/Availability Group

1 Answers