SQL Server - How to keep DB access up while creating a bacpac file

Question

First, we are a startup with a relatively big (14 GB) database and a small budget. We need a current, weekly would be ok, copy of the database for local use. The bacpac method works well, except that during the export process DB access slows to a crawl, or worse, and that really does not work for us.

We have a Standard S0: 10 DTUs.

Is there any way to throttle the export process? It does not need to be fast but the DB needs to be responsive at all times.

Other suggestions?

Answer 1

You could try other option to export bacpac here. If they don't work, you might need to scale up the DTUs

Reference - database-import-export-hang

=========================================================

Please don't forget to "Accept Answer" and upvote if the response helped -- Vaibhav

Answer 2

Hi @Kal Kallevig ,

For your case, if this happens regularly, I recommend you consider sql server database read/write split tech.
Read-write separation is a common design scheme for database systems for medium-scale applications. By synchronizing data from the main server to other SQL Server servers, it provides non-real-time query functions, expands performance and improves concurrency.

The benefits of separation of database read and write are as follows:

1. By separating the "read" operation and "write" operation on different database servers, reduce contention for the main server's CPU, memory, storage, and network resources;
2. When the main server is added, deleted, or modified, it does not affect the query of the query server, reduces the occurrence of blocking, and improves concurrency;
3. When the application submits a report request or an unreasonable query request, it will not cause a long time to lock the table;
4. Establish disaster tolerance copies or even realize remote disaster tolerance, which can reduce data loss in the event of a disaster;

For most internal enterprise applications, a main database server and a query server can usually meet the requirements of read-write separation. And the application is very convenient to adjust:

1. Set two database connection strings in the configuration file of the application, one pointing to the main server and one pointing to the query server;
2. Add, delete, modify or use the connection string pointing to the main server for real-time query;
3. Allow non-real-time queries and report requests to use the connection string pointing to the query server.

SQL Server provides three technologies that can be used to implement read-write separation: log shipping, transaction replication, and Always On technology. Each of these three technologies has advantages and disadvantages, and you can choose according to your situation.

BR,
Mia
If the reply helped, please do “Accept Answer ” and upvote it.--Mia