Avoid downtime of Microsoft SQL Server during restarts 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 &quot;simply&quot; 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:

Running queries will be aborted
Open, uncommitted transactions will be rolled back
New queries will get a connection timeout

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.