AAG database snapshots

Question

Hi,

Can someone clarify the following on AAG...

1. Is there a way to take snapshot on all the replica sites at the same time on a database. How does snapshot thing work on AAG databases, particularly when taken through VSS provider?

2. Since only "copy-only" backups are allowed on secondary sites, what is the restore mechanism after that secondary becomes primary and I want to restore it now? When I take a "full backup" snapshot on primary, the log is updated and during the restore applying tail log on top of snapshot is enough. Since I've "copy-only" backup at the secondary site (that became primary now), do I have to apply entire log after the restore.

3. I’ve an AAG created from Server1/Inst1 to Server2/Inst1. When I tried to create one more AAG from the same server but from a different instance, Server1/Inst2 to Server2/Inst1, it throws error “The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process. (Microsoft SQL Server, Error: 9692)”. Is the port used here only for that particular instance, not for the server?

4. Though I set backup preference "secondary-only", I can take snapshots from VSS on primary too. Why does AAG allow this?

Thanks,

Hem

Answer 1

Any reply?

Answer 2

Hi Hem!

I will try to give you some insight, but to be honest I am not completely sure if I understand all questions:

1) The snapshot on a secondary is no different than a snapshot on the primary. If your question was along the lines of how to do "synchronized snapshots" between multiple databases: I don't think there is really an option for this other than doing some kind of a database lock to synchronize... Can't comment at all on VSS, I'm not the expert around this area.

2) The only thing that differentiates "COpy-only" backups from regular full backups is that you can't to differential backups based on a copy-only backup. You can still have transaction log backups (which are supported on the secondary as well) and go through the normal full + tlog restore cycle.

3) Every SQL Instance needs its own listening endpoint for the replication traffic. So if you have two instances on one server only one can bind to port 5022. (That's a TCP rule, has nothign to do with SQL...) That's not a problem though, you just have to specify another port. Nothing is stopping you to create a listener on port 5023 for example.

4) Backup preference does not deny you anything, it is just a way of telling AAG-aware backup solutions to not touch the primary. If your backup solution is not AAG aware it can still work normally as it did before.

Hope that hit the question at least a bit...

Lucifer

Answer 3

Thanks a bunch for the reply, Lucifer.

On the point 2: If I take copy-only backup on database and differential/full backup on log, won't that be on two different time stamps? What I'd like to do is apply same backup type on both database and log at the same time. I might be wrong with my understanding here.

Thanks/Hem

Answer 4

Hi Lucifer

The snapshot on a secondary is no different than a snapshot on the primary. If your question was along the lines of how to do "synchronized snapshots" between multiple databases: I don't think there is really an option for this other than doing some kind of a database lock to synchronize... Can't comment at all on VSS, I'm not the expert around this area

Does that mean that i can do a restore on the secondary databases. If thats the case what happens to primary when the secondary database has some write happening.