Sql-server – Changing SQL Server installation – Will it bring the server down

SQL server 2012 installation is not supported through terminal services. It causes a lot of strange problems, and I experienced this same issue.

Microsoft states this in their prerequisites:

Link is http://msdn.microsoft.com/en-us/library/ms143506.aspx

This document states:

The installation of SQL Server 2012 fails if you launch the setup through Terminal Services Client. Launching SQL Server Setup through Terminal Services Client is not supported.

1.When and how do we install Microsoft Updates?

Using AOAGs could potentially help with this, depending on how much downtime is considered to be "too much". There is still a blip associated with switching the primary to a synchronous secondary as the clients will be disconnected (this may or may not be an issue in your environment) as the move happens. They will need to reconnect using the listener name (so no connection string changes should be required) and be using a library that supports this.

1.For Windows Updates, bring one server down, install windows updates, bring it back up, and then update the second instance.

That's the basics of how it works. Remove automatic failover (synchronous), install the updates/patches, reboot (in this scenario).

2.How do we apply schema changes?

AOAGs aren't going to help you in this respect. The transaction log is basically replayed from the primary to all of the secondary's. The secondary's could be readable (if you allow them to be) but won't be able to have any write activity (including ddl changes) made to them. A synchronous secondary is a mirror copy of the primary.

Could these changes be made when the application changes are made to minimize downtime? It may make more sense to use a load balanced front end using something such as an F5 and keeping the data in sync through something such as peer to peer replication than use AOAGs. I'm not a replication expert, but it definitely sounds like you will need two distinct systems that have some type of load balanced front end so that changes can be made to a single system at a time to move resources in and out while keeping them in sync.

2.For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.

This can't be done this way (or any way, really) with AOAGs.