Do you get elastic pools and elastic queries confused within Azure SQL Database? Many people do, probably because of the word elastic. Elastic Pools and Elastic Queries are two very different things and I want to clear up any confusion today.
Recently, someone said to me, I want to do some elastic queries in Azure SQL Database, so I set up my elastic pool and I need help with the next steps. Well, first let me say, you don’t need an elastic pool to do elastic queries in Azure SQL DB. Let me explain what each is:
Elastic Pool
- An elastic pool is a set (or pool) of shared resources, or DTUs, that could be used by many databases in that pool.
- In a pool I set the number of DTUs and databases in that pool can scale up or down to use the available DTUs up to the maximum I’ve set for that pool.
- The advantage is the scaling up/down happens automatically. I don’t have to set the service tier of each individual database to use a certain number of DTUs. By putting the databases in the pool, they have the pool DTUs available to them and as the workload increases or decreases, it will scale up or down the number of DTUs it uses.
- Pools are ideal when you have an application that is used by multiple customers. It’s designed so each customer has its own database in the backend. Each database has the same identical schema and depending on how heavy or light each customer uses it, the pool will manage how the resources are used in each of the databases throughout the day.
Elastic Query
- Elastic query is cross database queries. This is similar to how we used to do this with SQL Server on premises with linked services.
- We do elastic queries through external tables.
- To see what this looks like, the screenshot below is from SQL Server Management Studio and on it I have 2 sample databases; one is a sample OLTP and one is a sample DW.
- If I want to use my data warehouse database to query data that’s in my sample OLTP, I set up a cross database query or an external table from my data warehouse. To do this:
- I first need to create an external data source in my data warehouse database. You can do that from the external resource folder, and it generates a sample script for you, there’s no UI for you.
- I would script and create a data source and that will then make a reference, in this case to my OLTP database.
- Once I’ve got that set up, I can create an external table. In Management Studio, your external tables are managed in a folder under Tables.
- When I create an external table, I’m creating a pointer to a table (in my sample OLTP in this case). It’s important to understand that when I create the external table in the data warehouse, it does not load the data from the OLTP, it’s simply a pointer to it.
- Once I’ve created that external table in my data warehouse pointing to the OLTP, a query executed from my data warehouse referencing this external table will read the data from the OLTP in this example – this is an elastic query.
If you’ve had any confusion about elastic pools vs elastic queries, I hope this cleared it up. If you have any questions about Azure SQL Database or the Azure Data Platform, we’re here to help. Click the link below or contact us, our Azure experts can help with all your questions or help you in any way to get started with Azure.
Also, if there is any topic you’d like us to cover in this series, please jot that down in the comments below.