none
Azure SQL database deployment task - Create with specific pricing tier RRS feed

  • Question

  • Hi, 

    We have been using Azure SQL Database Deployment task in AzureDevOps release pipelines, and it used to create the database (if does not exist) with standard pricing tier (DTU based S0), however with this latest announcement Azure SQL Database default configuration changing soon it seems that the activity is now creating the database by default using General Purpose Gen5 pricing tier. 

    Is there any option to control the behavior? Does the Azure SQL database deployment task provide any configuration option to specify the database creation with specific pricing tier?

    Thanks in advance.


    Bhushan Gawale | RapidCircle

    Thursday, July 18, 2019 4:55 AM

All replies

  • Hi Bhushan,

    Yeah, you can specify GEN4 if that is what you mean?  The below are the options for this.  Although this is for a create database command, the tiers remain the same for what is available:

    CREATE DATABASE database_name  
        AS COPY OF [source_server_name.] source_database_name  
        [ ( SERVICE_OBJECTIVE = 
          {  'basic' | 'S0' | 'S1' | 'S2' | 'S3' | 'S4'| 'S6'| 'S7'| 'S9'| 'S12' |  
          | 'GP_GEN4_1' | 'GP_GEN4_2' | 'GP_GEN4_4' | 'GP_GEN4_8' | 'GP_GEN4_16' | 'GP_GEN4_24' |
          | 'BC_GEN4_1' | 'BC_GEN4_2' | 'BC_GEN4_4' | 'BC_GEN4_8' | 'BC_GEN4_16' | 'BC_GEN4_24' |
          | 'GP_GEN5_2' | 'GP_GEN5_4' | 'GP_GEN5_8' | 'GP_GEN5_16' | 'GP_GEN5_24' | 'GP_GEN5_32' | 'GP_GEN5_48' | 'GP_GEN5_80' |
          | 'BC_GEN5_2' | 'BC_GEN5_4' | 'BC_GEN5_8' | 'BC_GEN5_16' | 'BC_GEN5_24' | 'BC_GEN5_32' | 'BC_GEN5_48' | 'BC_GEN5_80' |
            | { ELASTIC_POOL(name = <elastic_pool_name>) } } )  
       ]  
    [;] 

    Thanks,

    Matt


    Friday, July 19, 2019 9:45 AM
  • Can I ask what the reason is for wanting GEN4 though?  GEN5 is hyper-threaded and so this should give you better performance?
    Friday, July 19, 2019 9:49 AM
  • As Matt has suggested, you need to explicitly call out a Gen4 DTU-based deployment.

    If you prefer to continue using the DTU-based pricing model, be sure to select DTU options from within Azure portal or via scripts. If you rely upon scripts to automate the creation of new databases, update your scripts to create the database with your preferred DTU options.  See our documentation for sample scripts: T-SQLCLIPowerShell.


    Please let us know if you have additional questions. 

    Regards,

    Mike

    Monday, July 22, 2019 11:33 PM
    Moderator
  • Thanks everyone for valuable comments, however it seems that question got misunderstood and apologies for not being clear earlier. 

    It is not about we wanting to provision the database with DTU based pricing tier or choosing general purpose pricing tiers, it is related to the behavior of the task named 'Azure SQL Database Deployment task' which you use in the release pipeline of the Azure DevOps.

    When you have a build and release pipeline based on Visual Studio's SQL Server database project template - then while releasing the build to SQL Azure server - it does create a new database if it does not exists or updates an existing database and applies changes to it. 

    When it creates the new database - the behavior of this task seem to have changed because previously it used to create the new database with DTU based pricing model and create it with S0 tier however with recent updates to SQL Azure (announcement here) - it has now started to create the database with general purpose pricing model and G5 tier. The question is - how do we configure the activity or DB project in VS so that it creates the SQL azure database with specific pricing tier while deploying as not every db needs to be created with general purpose pricing tier unless it is really necessary. 

    Any clue on this would be appreciated. 

    Thanks in advance.


    Bhushan Gawale | RapidCircle

    Tuesday, July 23, 2019 3:25 PM
  • Please detail the region you are attempting to deploy to, as the desired pricing tier may not be available. You could always test your solution in another region as a means to identify the behavior. 
    Tuesday, July 23, 2019 8:08 PM
    Moderator
  • Its North Europe region where both the pricing tiers are available i.e. DTU based S0 and General Pricing G5. We always have been deploying to the same region and the point is, it used to create the new database with S0 pricing tier and recently it has now started creating it with G5 tier by default.

    Bhushan Gawale | RapidCircle

    Wednesday, July 24, 2019 11:55 AM
  • My thinking here was that DTU (specifically S0) were all provisioned but I just went through the portal and was successful provisioning a DTU based S0 instance in N. Europe. The default behavior you are experiencing is a decision by the product group to move as many new deployments to the newer pricing tier (based upon Gen5 versus Gen4 hardware) so as to yank the Gen4 hardware and replace it with the newer G5 hardware. Each data center has a max capacity for electricity and cannot simply add new hardware, without removing existing.

    With this, you are going to need to be specific if you want to stick with Gen4/S0 DTU deployments.

    Regards,

    Mike

    Wednesday, July 24, 2019 6:43 PM
    Moderator
  • Yes, we understand the decision of the product team - however it has a direct impact on existing or new release pipelines which are using the Azure SQL Database Deployment task as currently it does not offer any configuration options which will let you create database with specific pricing tier, not sure if this is something which is already on the road map of the AzureDevops's tools team?

    Bhushan Gawale | RapidCircle


    Thursday, July 25, 2019 4:50 AM
  • The solution here is to work directly with the repo owners for the azure-pipelines-tasks to have this issue addressed through contributions and feedback (link). There is specifically an issues catalog to log details to have this addressed. I believe you would use the Publish Profile optional argument to pass fine grained detail with regard to database creation but I don't see much about what additional detail could be passed with that argument.

    Regards,

    Mike

    Thursday, July 25, 2019 2:41 PM
    Moderator