Allow cross database SQL queries for databases within an elastic pool

Question

Hello, I have created multiple databases for my application. Some databases store master data, while I have multiple databases for storing transaction data for each financial year. Have tagged all such databases to an elastic pool. My application creates transaction database for each new financial year.

I am not allowed to execute SQL queries across such databases. Can:

1. Cross database SQL queries (accessing tables across more than 4 databases) be enabled within a given elastic pool?
2. Database be allowed to be created by an application within such elastic pool?

Regards
Premal

Answer 1

Hi @Premal Mehta - The best solution for your scenario is to use the Single Tenant per Database list mapping Data Shard Map Manager (framework) where your specific solution would leverage the List Shard Maps (link) with the shard key placed on a timespan or datetime column from your solutions source schema. The shard map manager (horizontal partitioning) allows for the appropriate privileges (read-only/elevated) based upon the credentials used.

It is built around the elastic query concept. Please see: Getting Started Guide where there is a .NET and a Java example application. This is what you are essentially attempting to do from scratch. The shard map manager is a working solution that can be adapted for your solution.

If you are simply looking to resolve/address the cross-database queries, please see the vertical partitioning option: Get started with cross-database queries (vertical partitioning) (preview) (link)

Both Elastic Pools and Single Database instance will support the above scenarios: Scaling out with Azure SQL Database. For a SaaS tutorial using elastic pools, see Introduction to the Wingtip SaaS application