Set Service Tiers when create Azure SQL database from VS C#

6
votes

Is it possible to set the Microsoft Azure SQL Database service tiers when creating a new database from Visual Studio in C#? Currently, I can connect to the Azure SQL server and create table with no problem but for some reason (maybe Microsoft Default) the databases will be created in Web which is the service tier that going to be retired. I would like to set the default service tiers to either be Basic, Standard, or Premium depends on the needs.

What I found so far is when I call this method Database.Initialize(true) <--EF http://msdn.microsoft.com/en-us/library/system.data.entity.database.initialize(v=vs.113).aspx It would create the database and set it to Web service tier.

2
c#entity-frameworkvisual-studioazureazure-sql-database
You can change the tier the Azure SQL Database runs on at any point after it has been provisioned. This includes shifting it to the new tiers as well. – Simon W
Thanks Simon, but if I'm creating 100 of database a day then I'll need to do that 100 per day? I hope there is a better way. – Chi Wu
So what appears in the EF connection string in your applications configuration file? – Simon W
This is what I have in my connection string Server=[removed];Integrated Security=false;User ID=[removed];Password=[removed];Initial Catalog=[removed]" – Chi Wu
I posted a possible solution to your situation but it will mean a fair amount of work to stand it up this way. blog.siliconvalve.com/2014/11/12/… – Simon W

2 Answers

1
votes

With Azure SQL v12 you have the option to specify the SKU. Example:

var dbCreationCmd = $"CREATE DATABASE [{databaseName}] (MAXSIZE={maxSize}," +
                            $"EDITION='{edition}'," +
                            $"SERVICE_OBJECTIVE='{serviceObjective}')";

// With Azure SQL db V12, database creation TSQL became a sync process. 
// So we need a 10 minutes command timeout
ExecuteNonQuery(connectionString, dbCreationCmd, commandTimeout: 600);
0
votes

As mentioned by Simon, the SQL data-tiers can be done only after provisioning the DB.

From Powershell, here is a function which can be called post provisioning of the Database

Function Update-DatabaseServiceTier
{
    Param
    (
        [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=0)][ValidateNotNullOrEmpty()]
        [String]$databasename,
        [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=1)][ValidateNotNullOrEmpty()]
        [String]$PerformanceLevel,
        [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=2)][ValidateNotNullOrEmpty()]
        [String]$Edition,
        [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=3)][ValidateNotNullOrEmpty()]
        [String]$MaxSize,
        [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=4)][ValidateNotNullOrEmpty()]
        [String]$SQLServerName,
        [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=5)][ValidateNotNullOrEmpty()]
        [String]$userId,
        [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=6)][ValidateNotNullOrEmpty()]
        [String]$password
    )  
         # Get current Database Details
         $DatabaseDetails = Get-AzureSqlDatabase -ServerName $SQLServerName -DatabaseName $databasename -ErrorAction Stop -WarningAction SilentlyContinue
         $currentEdition = $DatabaseDetails.Edition
         $currentSize = $DatabaseDetails.MaxSizeGB

         if (($currentEdition -ne $Edition) -or ($currentSize -ne $MaxSize))
         {
             Write-Verbose " Upgrading the Database Edition - Database Size"

             # Set SQL Server Connection Context
             $server = Get-AzureSqlDatabaseServer $SQLServerName -ErrorAction Stop -WarningAction SilentlyContinue
             $servercredential = New-object System.Management.Automation.PSCredential($userId, ($password | ConvertTo-SecureString -asPlainText -Force))
             $ctx = $server | New-AzureSqlDatabaseServerContext -Credential $serverCredential

             $db = Get-AzureSqlDatabase $ctx –DatabaseName $databasename -ErrorAction Stop -WarningAction SilentlyContinue
             $PL = Get-AzureSqlDatabaseServiceObjective -Context $ctx -ServiceObjectiveName $PerformanceLevel -ErrorAction Stop -WarningAction SilentlyContinue

             # Update SQL Server Properties (Service Objective, Edition and Size)
             Set-AzureSqlDatabase -ConnectionContext $ctx –Database $db -ServiceObjective $PL -Edition $Edition -MaxSizeGB $MaxSize -Force -ErrorAction Stop -WarningAction SilentlyContinue

         }
         else
         {
            Write-Verbose ""
            Write-Verbose " Database Edition and Size upto date!!"
         }
}