I am using always on in sql 2012 and have an AG setup. I would like to know if i were to backup the primary database and restore it would that be allowed without taking the cluster down etc.

Secondly if i have a SSDT project that is deploying my database and i had an AG setup before hand and were to deploy that blank database by way of the project to the primary would it "sync" the changes therefore removing all the data if any was present in the secondary? This is an unlikely scenario just wondering what would happen.



This is actually two separate questions.

1) You cannot restore a database that is part of a database mirroring pair or availability group. If you try to do so, you will receive an error like this:

Msg 3104, Level 16, State 1, Line 2 RESTORE cannot operate on database 'AdventureWorks2012' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.

Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

2) Any changes made to the primary database in an Availability Group WILL be sync'd to the other replicas. This includes DML and DDL changes. However, when doing an SSDT deploy, there are mechanisms to check for possible data loss in the target and prevent the deploy (those settings can be disabled). Essentially, deploying with SSDT to the primary would be like deploying to all of the replicas as well.

Also, certain operations are not allowed on databases that are members of a database mirroring pair or Availability Group (such as database restores, enabling service broker, etc.), so if your SSDT deploy tries to do one of those things, it will cause an error like this:

Msg 1468, Level 16, State 1, Line 1 The operation cannot be performed on database "AdventureWorks2012" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

  • enabling Service broker is allowed in a mirrored database. Aug 22 '12 at 19:09
  • Sorry about that, but I do know it's not allowed in a database that is joined to an Availability Group, because that is how I received the error message in the answer I gave above. Aug 23 '12 at 20:06
  • Also the AG is allowed, see Service Broker with AlwaysOn Availability Groups Sep 11 '12 at 6:48
  • I know you can use Service Broker with AlwaysOn, but you must alter database set new broker before you join the database to an AG, or else you receive the error in my answer. Sep 12 '12 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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