Sql-server – Copy-only as the routine backup

Schema changes and data changes are essentially the same. It works like traditional mirroring today : what happened in the log on the primary happens on the secondary. Not everything that happens in Vegas has to stay in Vegas. :-)

Where you may want to be careful, is when you have an application that points to the primary, and you update that to match schema changes. But you may have a different application that points to the secondary (e.g. with read-only intent), and that app change would have to be synchronized as well.

Another potential gotcha is when your database that is part of an availability group has references to objects in other databases (e.g. a static lookup table that is stored in a utility database). If those change and the AG depends on those objects, you will have to push those changes manually. The same is true for jobs, server-level logins, linked servers etc. - anything that lives outside of the database and/or is not transactionable. Database users can get orphaned (contained users aside). I know this is probably obvious but wanted to list it explicitly for completeness.

There's two parts to this question:

First, can you use the Failover Partner connection string tip with AlwaysOn Availability Groups? No. AlwaysOn AG's "Listener" technology is the replacement. Have your connection strings point to the listener name and they'll always get the primary replica. (For the next part of this answer, I'm assuming you're using the listener name - if you're not, start, heh.)

Second, why do some queries fail to connect to the listener? This has to do with the number of DNS entries for the listener. All possible subnets for the listener will be in DNS at all times. If you've got a listener in 192.168.1.X and another in 192.168.100.x, both listeners will always be in DNS. By default, your clients will try connecting to each of the DNS entries serially, and not always in numeric order. If you've got a 30-second connection timeout, it's possible that your app will only try one of the IPs and then fail before it has the time to try the second one.

If you want to try connecting to all possible IPs simultaneously, check out the MultiSubnetFailover = True options for the SQL Server client as described here: http://msdn.microsoft.com/en-us/library/hh205662.aspx

Otherwise, you'll need to increase your connection timeout to account for the multiple IPs.

Update Feb 27: the question added, "I was therefore trying to come up with a way to avoid manual intervention for these (replicated) DBs should a failure of the replica occur and the other databases which are part of an AG failover."

Ooo, unfortunately, no, if the databases aren't part of the Availability Group, you're going to be doing manual work in order to fail over. One popular option is to use a DNS record, and just repoint the DNS record at whatever server is currently hosting the primary copy of the databases.