1

I am use to working with SQL Server Database Mirroring but now we are upgrading to use High Availability Groups.

Whenever I had to restore the mirroring on one of the SQL Server database I use to take a Backup with the "Copy Only" option enabled and restore that along with the transaction files on the other DB Server before configuring the mirroring.

I have found an issue with Availability Groups that a "Copy Only" backup when restored on the other server won't allow me to put the database into "Synchronized" mode. When I use a non "Copy Only" database backup I can put the database into synchronized mode.

Is a non "Copy Only" backup required when restoring on the other server and adding the database into the Availability group? Or, more likely, am I doing something wrong?

Improve this question

1 Answer 1

Reset to default
1

Is a non "Copy Only" backup required when restoring on the other server and adding the database into the Availability group?

You can use either, so long as there is a common point in the log between the replicas.

Or, more likely, am I doing something wrong?

I wouldn't be that hard on yourself, you're doing it right - it truly is almost exactly the same as mirroring. Since you're very familiar with that, AGs should be pretty much cake.

Anyway, my guess is if you execute the ALTER DATABASE command to set the HADR context, you're getting an error stating there isn't enough log to join the replica (common point). Just restore some more log backups (with norecovery as you're used to) and make sure the primary or any other secondary aren't taking log backups (since the last one you've used to bring the new replica up to date). Otherwise, there's a good chance the log will be reused and no common point will exist.

Improve this answer
2
  • Do you get better error messages when using a script? I have been using the wizards in SSMS.
    – LondonKiwi
    Jan 20 at 9:31
  • @LondonKiwi The GUI/Wizards tend to not make errors as easily or readily available or readable, which you do get when you use T-SQL directly. Luckily, the wizards have script buttons which can generate the commands for you. In this case, there are only two commands needed if the replica is already prepped.
    – Sean Gallardy
    Jan 20 at 15:22

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.