- How to bulk detach / attach databases in SQL Server - February 2, 2024
- How to enforce password policy in an Oracle database - 4 January 2024
- How do we change the name of the user / schema in an Oracle database - 1 December 2023
In a Managed Instance with multiple databases on Azure we can have high availability. More specifically we can have an asynchronous second read-only Managed instance in the same or even in a different one region. We can at any time do failover to it or to happen automatically if for some reason the primary Managed Instance has a technical problem. This function is called Failover-Group.
In previous article we had seen how to make one Managed Instance. We will keep this as primary and continue the steps from there.
We will have to fix the way the two Managed Instances will communicate. One way is with ExpressRoute and the other one we will see now is in use Vnet-to-Vnet VPN Gateway.
It is very important to follow the steps in order, as if a mistake is made it may not change. e.g. if we create the second Managed Instance in a subnet with the same mask (ip range) it will not work for any reason ip conflict and it will have to be rebuilt from scratch since we cannot change it afterwards.
Also before we start we should know that it is a process that takes many hours as the Managed Instance creation takes 4-6 hours, just as the Vnet gateways creation also takes a long time to deploy.
First, let's go to Virtual Network of the Primary Managed Instance, Subnets, Gateway Subnets. We do it create without disturbing any other parameter.
Then we look for the resource in the search Virtual network gateways and we choose Add.
When creating it, we pay attention to the following fields:
- Name: the gateway name
- Region: where the primary Managed Instance is located
- Gateway type: VPN
- Virtual network: the VNET that has the primary Managed Instance
- Public IP address name: the name that the Primary IP will have
After we are done with the VNET gateway on the primary. Before creating the secondary Managed Instance we should create its Virtual Network.
We define it in the same Resource Group and we put it Region where we want it to be.
Subsequently we should define a different subnet in the address space than what we have in the primary.
Select Add subnet and define a name, e.g. ManagedInstance.
We don't need to change anything else, so if we don't want to, we choose Review+create.
Once created, go to this Virtual Network, Subnets, Gateway subnets without disturbing anything else.
Accordingly, as we did in the primary VNET, we pay attention to the following fields:
- Name: the gateway name
- Region: where the secondary Managed Instance will be located
- Gateway type: VPN
- Virtual network: the VNET that has the secondary Managed Instance
- Public IP address name: the name that the IP of the secondary will have
It's time to create the secondary Managed Instance. We define the Resource Group, the name it will have and the Region we defined in its Virtual Network.
In the next tab we are careful to put as Virtual network the one we created before and not to create a new one.
We put it on Collation same as what we have in the primary Managed Instance, same time zone and as Geo-Replication Use as failover secondary with Primary Managed Instance the name of the primary.
As long as we wait 4-6 hours for the secondary Managed Instance to be created we can build it Connection between Virtual Networks.
We search in search Connections and then we choose Add.
There we choose VNet-to-VNet the same Resource Group and Region of the primary.
Then we will have to select the two Virtual Networks that we had made at the beginning, define one Shared Key which their communication will make use of and OK.
After seeing that the deployment of the secondary Managed Instance is complete we can now create it Failover group.
Let's go to primary Managed Instance, Failover Groups and Add group.
We define a name for it, what the secondary will be, whether the failover will be automatic in case of a technical problem and the grace period.
The grace period is the time it will automatically failover from one instance to another. The minimum time is 1 hour.
After it is created, we see that we have the possibility to make it a manual failover which, unlike the automatic failover, is done almost immediately.
We are also provided with the connection string endpoints to connect to the instance that has the role of primary with read/write capability or to the secondary that will only have read.
If we try to do nslookup on the primary endpoint we will see that it will bring us the IP of the primary instance. If we do manual failover and run nslookup again we will see that this time it will bring the secondary instance.
We must not forget that the Failover Group endpoint forwards us to the private endpoints and not to the public ones. That is, we do not have direct access with this connection via the Internet. In case we wanted this we would have to connect via VPN first.