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?