Database performance using an elastic query (external tables)

Jose Perez 136 Reputation points
2020-12-03T16:30:00.233+00:00

Hi,
I have two SQL Server databases on an Azure server. One of the database will contain historical information from the other database which will be the production database.

To move the information from the production database to the historical database I have created an elastic query from the historical database (https://video2.skills-academy.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical) and created the corresponding external tables in the historical database with the same structure and name as the tables in the production database.
I am in testing, and I have both databases at 10 dtus so the initial tests are very slow. I am going to scale the database, but my question is:

What database should I scale? The historical database from where the reading and saving process is carried out in the production database or the production database where the tables are pointed to by the external tables created in the historical database?
Our purpose is that the historical database has the lowest possible cost, and that the production database is the one that users access, so it will have greater capacity in DTUS but also greater workloads by users.

By way of internal reflection, what I would like is that the accesses from the external tables do not influence "at all" the performance of the production database, is it? so that at the time of loading the records, the historical database can be scaled and once the process is finished it can be de-scaled. Is my assumption / expectation correct?

Thank you very much in advance

Azure SQL Database
Accepted answer
  1. Anurag Sharma 17,606 Reputation points
    2020-12-07T10:53:32.76+00:00

    Hi @Jose Perez , welcome to Microsoft Q&A forum.

    A database transaction unit (DTU) represents a blended measure of CPU, memory, reads, and writes. External tables do read the data from the remote databases and hence impact the database performance. You rightly pointed out scaling up the historical database during the migration process will help, but if huge data is being retrieved from the production database, it could throttle the requests still. So scaling has to be done at both for a smoother transition.

    On a side note, I would suggest you to use SQL Data Sync service for migration. You can refer to below links for more details.
    https://video2.skills-academy.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database
    https://video2.skills-academy.com/en-us/azure/azure-sql/database/sql-data-sync-agent-overview

    Please let me know if you need more details and we can discuss further.

    ----------

    If answer helps, please select 'Accept Answer' and it could help other community members looking for similar issues.

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

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