First of all i have to say that this question comes from a software engineer-perspective. Unfortunately we have no DBA so we need to administer our own databases.
We have SQL Server 2012 Enterprise installed to run a database which should be available 24/7. The database is about 150 GB and some tables are containing billions of rows. Multiple services are hitting the database thousands of times per minute, to insert realtime measurement data. So when the database is down for half an hour, there is loss of measurement data for half an hour, which we can not afford…
In this situation we have two main issues:
- When and how do we install Microsoft Updates?
- How do we apply schema changes?
Windows Updates requires no further explaination i think. When new requirements come or existing requirements change, we sometimes need to do some schema changes. For example adding some extra columns, changing data types, resize varchar fields etc. Some of these changes take a very long time to run, or even timeout, because the database is Always under heavy load.
We are thinking to install an extra instance and enable AlwaysOn, to accomplish the following:
- For Windows Updates, bring one server down, install windows updates, bring it back up, and then update the second instance.
- For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.
Can these two things be accomplished with SQL Server AlwaysOn, and is this a common approach? Does the data even gets synchronized aftwerwards? Or am i completely thinking into the wrong direction and are there better solutions?
Best Answer
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.
That's the basics of how it works. Remove automatic failover (synchronous), install the updates/patches, reboot (in this scenario).
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.
This can't be done this way (or any way, really) with AOAGs.