We are running an SQL Server Standard 2016. From time to time Microsoft updates need to be installed and the server will be restarted.
We have been asked to look into keeping the database available during that restart time.
So we don't need another database server for performance reasons, the database should "simply" be available during the restart process of the regular Windows Updates.
What would be the preferred setup in this situation?
If you have a running query or an open transaction, SQL Server doesn't have a way to move that query or transaction over to another server.
When your SQL Server instance (we'll call Instance A) restarts:
We can't fix #1 and #2, but we can help #3 in a couple different ways.
If the application needs to do writes, you can lower the amount of downtime by using a high availability feature like failover clustering, Always On Availability Groups, or database mirroring. This lets you patch the standby server (Instance B) first, ahead of time. When it's time to patch Instance A...you don't. You just do a failover from Instance A (unpatched) to Instance B (patched). Your users experience a short 5-60 second outage, depending on server size, running transactions, server tuning, etc. Then, you can take your time patching Instance A, and assuming the servers are the same size, you don't even need to fail back. You can keep running on Instance B until it's the next time for patching.
If the application only needs to do reads, you can use scale-out features like read-only replicas on Always On Availability Groups. Depending on how you configure the AG and your application, connection string changes may be required, like pointing to the AG listener and specifying read-only intent rather than specifying Instance A's name by itself.
If the application does a mix of reads and writes, and you're willing to put in some work on the application side, you can stay online during failovers using the techniques Stack Overflow uses.
If the server is rebooting, the SQL Server instance is not available.
The only way is another SQL Server instance, and setup using Availability Groups.
There will be a short outage (a few seconds) while the AG moves the primary.
There is no "100% uptime" option
As I understand, what you are being asked is to have your databases in high availability (HA) mode.
We have two good technologies available at your case to choose that meet your business Requirement RPO (Recovery Point objective ) and RRO (Recovery time Objective). Now, Numbers of RTO OR RPO you will decide which technology makes most sense for your circumstances.
You Can Choose :
Source : https://www.mssqltips.com/sqlservertip/5737/minimizing-downtime-for-sql-server-upgrades-part-4/
Thanks
