Azure ElasticPool “BadRequest” during creation deployment when using ARM template

Question

I have a task where I need to use ARM template to "setup SQL server with elastic pool and 2 databases (imported from bacpac files) use Microsoft Adventureworks samples."

Most of the template I made using "101-sql-elastic-pool-create" template that is available here: https://github.com/Azure/azure-quickstart-templates/tree/master/101-sql-elastic-pool-create I used it for the elastic pool setup and used a sample from this documentation (https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/template-tutorial-deploy-sql-extensions-bacpac) to have the .bacpac imported to the database

But when I try to deploy this template (the template is available to see here: https://github.com/KarlisAG/SQL_ARM/blob/main/azuredeploy.json ) it throws me an error ("Resource Microsoft.Sql/servers/elasticPools 'task4sql/elasticp' failed with message '{ "code": "BadRequest", "message": "An error occurred while processing this request.", "target": null, "details": [], "innererror": [] }' ")

So, the biggest problem is (besides me being an absolute newbie) not knowing what I need to fix, because there is no additional information granted, besides the "BadRequest". For deployment I used two different methods: 1) the automatically generated one from VS when created Azure resource groups, 2) One shown in this documentation, with few of my own changes, so it could work for me ( https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/template-tutorial-deploy-sql-extensions-bacpac#deploy-the-template ), from where I also got the code part for importing .bacpac to my newly created database. [Both of the are available at the same repository, named "Deploy-AzureResourceGroups.ps1" and "DeployScript.ps1" respectively]. Both of the methods return the same error.

I even manually created these things in Azure portal to see what their template looks like and maybe copy some things that are not present in my template, but from what I saw I have exactly what they automatically generate. And when I checked the deployment status in Azure portal I can also see that during the elastic pool creation it fails: screenshot from that section available here

So does anyone know how should I have created this type of template, or did I recreate it poorly, or am I missing some small mistake that keeps blocking me?

Answer 1

So after multiple tries it seems that I finally figured out what I was doing wrong. I am still not sure which of the changes made a difference, besides the last one, so I will write what I changed, so other people who could be facing similar issue know what they could do fix the issue.

At first, after @stringfellow noted, I thought that Free Trial is restricting me from doing anything, but after I lowered my DTU multiple times, to the point where it was at 5 (starting from 100), the same issue persisted, so probably it was not the issue (later on I had to change it at minimum to 50). I also changed the Elastic Pool edition from Standard to Basic. But doing these changes still didn't fix the issue.

At last, after answering @NachoMartínez-Aedo comment about the api version, it got me thinking. Because in the code that I posted I used "2020-08-01-preview" api version, but in reality I used "2014-04-01-preview", because when I changed the api version of all my parts to the 2020 one, then powershell told me that (No registered resource provider found for location 'northeurope' and API version '2020-08-01-preview' for type 'servers/databases/extensions'. The supported api-versions are '2014-01-01, 2014-04-01, 2014-04-01-preview'.). And after reading through that again and remembering that when I was looking at some documentation and Azure portals automatically generated templates I knew that I always say them using the 2020 api, not the 2014, which left me confused as to why I couldn't also use the newest version. And reading that error again I saw, that it specifically told that 2020 api version was not supported for the type "servers/databases/extensions", not that all of the parts must be in 2014 version (which is what I thought when I saw that error), so I tried and changed all the 2014 back to 2020 and left the extension api back at 2014 (previously I thought that all api versions should be the same, not sure why, maybe because most of the examples and documentation had everything have the same api version) and the everything worked, even the import part, about which I was worried.

So in the future I am not too sure if there is a better/newer way to import .bacpac file to newly created database with ARM, or just have to leave it as is and in the future use this same approach. Does anyone know?

I got the import extension from here: https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/template-tutorial-deploy-sql-extensions-bacpac , where they use examples with 2014 api for the extension, so because of that I tried to use it for all my parts, but only during this answer writing did I realize and look through it again, that they used the 2014 api only for the extension part and not other parts, like server, database, etc.

TL;DR So basically I needed to use the '2020-08-01-preview' api for everything besides from the extension part, where the newest api version is only '2014-04-01-preview'

Answer 2

I was able to deploy most of your template and use similar parameters in my own subscription. I saw in your screen capture that you are using a Free Trial subscription. I am curious if that has anything to do with the 'BadRequest' due to any sort of DTU limitation.

Here is my deployment, which failed as expected when it tried to import the databases.