1

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

Improve this question
3
  • 1
    mirroring is a deprecated solution , you would reap much better rewards if you spent your time learning Always on Availability Groups (AG) specially since 2016 introduced Basic Availability groups, in any case if you have memory optimized tables you can not use mirroring.
    – entDba
    Mar 5, 2017 at 22:50
  • @entDba Thanks for your input. I'll be looking into AlwaysOn availability groups shortly. The issue with them (correct me if I'm wrong) is that they work with High Availability Cluster service.
    – joebegborg07
    Mar 5, 2017 at 22:54
  • TransLogs required to be verified in both locations from sender to receiver, i.e. from Originating server to Mirror Server. The missing log if copied from Origin server to Mirror server in defined location, and restarted the Log shipping process, should solve the issue.
    – MarmiK
    Mar 7, 2017 at 13:34

2 Answers 2

Reset to default
4

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

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.

The issue with them (correct me if I'm wrong) is that they work with High Availability Cluster service.

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.

Improve this answer
0

The management studio 17.0 have many problems, try with new release 17.6 Anyways I have mirror configured but it do not empty the log. If you have some relating about it, I would appreciate it

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.