Multiple copies Synapse analytics sql pool

Question

HI - To overcome the concurrency limitations with Synapse analytics sql pool i am thinking to keep multiple copies of database so that users will be directed to different read copies when the limit reaches to max limit/sql pool.

Do we have that feature available currently with Synapse analytics.

thanks

Answer 1

Synapse dedicated pools don't currently offer read-only replicas similar to Azure SQL. Feature requests on these forums are shared with the product team, but you can also request or upvote the feature on feedback.azure.com. Following the feature there has the added advantage of ensuring you get any relevant updates from the product team.

There are a few options that might fit this need:

1. Increasing the database tier to increase available concurrent jobs. Currently the number of concurrent slots does max out at 128, so there is a limit to how much this can help.
2. Make use of workload management options and resource classes to ensure that high priority users will receive consistent performance and lower priority users are far more likely to have to wait for slots to open up.
3. Make use of Azure SQL hyperscale tier instead of Synapse for your database engine as it does offer read-only replicas. If you are planning on using the Spark pools within Synapse, Hyperscale does not have the convenience of being integrated within the Synapse umbrella, but Spark pools can certainly still work with it.
4. Probably the least attractive option is if you have a limited number of jobs loading data into your SQL pools, you could have those jobs update multiple pools and manually assign users to use different pools.