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.
Thanks!
Best Answer
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.