We have SQL Server 2014 Enterprise installed to run a database which should be available 24/7. Our database is huge enough (200gb +). Also we have a lot of services that hits our database every minute to read, update or insert new data. We want to provide a "hot" redeploy feature for our clients and make our daily updates (.net and schema updates) transparent to clients. We have found a solution based on cluster with load balancer to update binaries of our app, but we still have some misunderstanding about database`updates deployment process and what are the best practices to solve this issue.
For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance. Can it be accomplished with SQL Server tools, and is this a common approach? How sync data after server is backed up? Or am i completely thinking into the wrong direction and are there better solutions?
Our common Schema changes: add/drop column, add/delete stored procedure