Im a complete beginner with SQL server. I'm trying to train myself using the AdventureWorks2016 sample DB on an SQL server 2016. I'm trying to setup mirroring between VM1/SQLServerInstance1 and VM2/SQLServerInstance2. 2 problems which I'm facing:
- The option to backup Transaction logs is missing from properties window of the database (Right click on DB > Properties > Backup).
- Mirror option is greyed out with the following message: "Database mirroring is not supported for a database with a MEMORY_OPTIMIZED_DATA filegroup. Use AlwaysOn Availability Groups instead". I was hoping to stay away from AlwaysOn Availability groups by using Mirroring.
I've tried googling and this is my last resort. Help would be greatly appreciated.
UPDATE
Issue 1 (No transaction log backup option), was resolved by Changing Recovery mode to Full from simple (Right Click on DB name > Properties > Options > Recovery Mode).
Best Answer
The error message is pretty self explanatory. Mirroring doesn't support memory optimized objects (In Memory OLTP you may see it called [IMOLTP] or Hekaton).
Mirroring as noted in the comments is deprecated - it's only AGs going forward. Since 2016 supports a multitude of new options in terms of clustering and availability groups, you should be able to do the exact same thing with AGs as you can with mirroring - albeit a few more steps in setup and configuration.
If you want to use mirroring, then I would suggest using a blank, new, database and configuring it there. Also, don't rely on the GUI.
Yes, you're correct; they do require Windows Server Failover Clustering. However, I don't see how that's a show stopper. Does it take a little extra configuration - yes. Does it require a little more understanding about your actual failure scenarios and overall architecture - sure... but it also beats having to deal with mirroring.
In fact, there were many, many, updates done to availability groups in 2016 that it is much more performant than mirroring.