Share via

SQL Best Practices

karthik palani 661 Reputation points
2021-02-16T05:34:33.727+00:00

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
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other
0 comments

Answer accepted by question author

  1. Olaf Helper 47,616 Reputation points
    2021-02-16T06:33:36.503+00:00

    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).

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. tibor_karaszi@hotmail.com 4,316 Reputation points
    2021-02-16T09:58:04.75+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. Cris Zhan-MSFT 6,676 Reputation points
    2021-02-17T02:47:29.76+00:00

    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.

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.