Here we are with the last part of this series of articles! In this article we are going to see two curiosities:
- How does in-memory OLTP work with Availability Group?
- Can I migrate from Log Shipping or Database Mirroring to Availability Groups?
So, let’s start!
How a memory optimized table works with AG?
A new feature released with SQL Server 2014, the in-memory OLTP is still in the “explore phase” and a lot of questions are still being raised about its capabilities and how to integrated this new technology with the existing ones. Here, of course, we are going to see how the AlwaysOn Availability Groups are dealing with the In-memory OLTP feature.
As a short introduction, the In-Memory OLTP is a new feature that takes advantage of the available volatile memory (RAM) in order to entirely store a table and its data. Because the RAM memory is way faster than the traditional non-volatile storage, we can reach a performance gain up to 30x better, when comparing the same table and workload in a disk table and a memory table.
The In-memory OLTP feature have two flavors of table:
- Durable Tables – Tables that are persisting the inserted and modified data in a permanent basis. Meaning that all the data modifications are being logged in the transaction log and in a “special couple” of files called “Checkpoint Files” – the Data and Delta files pairs.
- Non-Durable Tables – This is the faster way to store your data, but as everything has a tradeoff, the data is not safe. When you restart your instance all the data will be lost. Only the table will remain there, empty-
The reason of this this that non-durable tables are not logging its operations and also not using the “Checkpoint Files”. That’s why this is fast. That’s why the data is not safe. But, believe, there are some scenarios where this fits perfectly!
Looking from the transaction log perspective, here is a summary of the difference between durable and non-durable in-memory tables.
If you read the previous articles of this series, you may have noticed that the way the Availability Group is synchronizing the Primary and Secondary replicas is by taking use of the transaction log records. Basically the Primary replica receive all the change requests, log it, and send to the secondary in order to keep all the replicas up to date.
We can see the behavior of the in-memory tables in an Availability Groups environment in the image bellow.
At this stage you should have gotten the message:
- Both tables are supported.
- Only the durable table will be identical on all the replicas.
- The structure of the non-durable table will be available in the secondary, but with no data at all.
- The non-durable table will work normally in the primary replica.
This done, now let’s see the next curiosity…
Can I migrate Database Mirroring or Log Shipping to AG?
This is something that you might be doing in a near future, even more if you are using Database Mirroring, which is deprecated.
Nowadays, on SQL Server 2014, the Availability Groups feature is only supported in the Enterprise Edition, which excludes a lot of potential migrations. However, Microsoft already released a Community Preview of the next version of SQL Server 2016, bringing news about the Availability Groups. In this next version, we will be able to use the Availability Groups in the SQL Server Standard Edition, but wait! This won’t be the full Availability Groups as we have now… unfortunately. The SQL Server 2016 will have something called “Basic Availability Groups”, nicknamed by the SQL Server Community as BAG. In a general perspective, the BAG will be a mirroring with a pinch of difficulty to setup. No listener, no readable secondary…. Well, at least the used engine is better than the database mirroring! I will be doing an article exclusively about this soon.
Going back to the track, independently to the reason for the migration this is possible and doable. We will only need to assure certain prerequisites before start with the migration.
Both the Database Mirroring and the Log Shipping are “kind of” compatible with the Availability Groups way to synchronize. If you stop and think, all those three technologies are using the transaction log record in order to keep the secondary/mirror up to date. So, the migration act will be very easy, as soon as the prerequisites are met.
What are those requisites?
In order to be able to migrate DB Mirroring or Log Shipping to Availability groups, we need to assure that the main pillars are built. So here you go:
- Join the servers to the AD (if not).
You will need to build a cluster and include all the intervenient servers as cluster nodes in order to use the Availability Groups feature. Because of this, the server will need to be part of the domain. - Install the Failover Cluster feature.
Still in preparation to join both nodes to a cluster, we will need to go to the Server Manager and add the Failover Cluster Feature. - Create the cluster containing both standalone servers.
If you have two standalone servers you will need to create cluster with both servers. If you already have one (or even two) clustered instances, you will need to join all the nodes to the same cluster. - Make sure that both servers have the same drive letters, folder structure and disk space.
Differently from the DB Mirroring or Log Shipping, the database files need to be placed in the same path on all the replicas. You need to assure that all the servers have the same path available. The disk size is not a constraint, but is a best practice to have the same disk size on all the replicas, otherwise you may have problems in the future. - Enable AlwaysOn Availability Groups feature.
This option is available in the SQL Server Engine service properties, accessible from the SQL Server Configuration Manager.
- Have a SQL Server 2012+ installed
Yes, Availability Groups is a 2012+ feature.
Having the prerequisites done, you just need to break the DB Mirroring / Log Shipping and create the Availability Group including the desired database.
Here are the steps…
….for the database mirroring:
- Disable the backups (log and full).
- Break the mirroring session.
- Create the Availability Group including the mirrored database.
- Use the option to join the database.
- Enable the backups.
- Remove mirroring remaining jobs.
- Point the applications to the listener (if created).
…for the log shipping:
- Disable the backups (log and full).
- Disable the log shipping.
- This will remove all the created jobs.
- Create the Availability Group including the database.
- Use the option to join the database.
- Enable the backups.
- Point the applications to the listener (if created).
That’s it! I hope that this article was useful!!
Thank you for reading 🙂
With experience working in Portugal, Holland, Germany and United Kingdom, he's always available to learn and share his knowledge, in order to contribute to SQL Server community,
View all posts by Murilo Miranda
- Understanding backups on AlwaysOn Availability Groups – Part 2 - December 3, 2015
- Understanding backups on AlwaysOn Availability Groups – Part 1 - November 30, 2015
- AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4 - October 13, 2015