SQL Best Practices

Question

Hi All,

Need some advice on the below questions please

• Is there any best practices to manage shared DB.
• Just wanted to understand, how patching is managed for shared DB without any downtime. Is there any load balancer used normally

Answer 1

Is there any best practices to manage shared DB.

Every database is "shared" = used by multiple application & user, that's the nature of databases/SQL Server. I don't understand what you want to manage here?

Just wanted to understand, how patching is managed for shared DB without any downtime. I

As other updates/patches it require a downtime to install it. High availability solution like clustering/AlwaysOn helps to keep the downtime short.

Is there any load balancer used normally

The is not really "load balancer" concept in database world. In AlwaysOn or replication solution you can have e.g. a read-only replica of the database(s).

Answer 2

Just to add some to Olaf's reply:

You cannot reach zero downtime for patching. A failover solution such as Availability Groups means that when you fail over, the database is inaccessible for a short period of time. How short depends on some aspects (like how much to rollback, if accelerated database recovery is turned on). You can probably achieve a few seconds in normal circumstances. It is imperative that the application can re-try its work if the database temprarily inaccessible.

Answer 3

Hi,

As Olaf said, the database (SQL Server) allows multiple users/applications to access unless you set the database to only allow single user access.
For some general concepts, you can try to provide some practical examples to describe your problem so that community members can give more accurate suggestions.

If you want to patch/upgrade a SQL Server instance/database, zero downtime is impossible. You can only minimize downtime,HA and DR are important to minimize application downtime, check this article for reference.