6

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.

Chi Wu
  • 83
  • 5
  • 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 Nov 11 '14 at 02:54
  • 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 Nov 11 '14 at 17:03
  • So what appears in the EF connection string in your applications configuration file? – Simon W Nov 11 '14 at 22:57
  • This is what I have in my connection string Server=[removed];Integrated Security=false;User ID=[removed];Password=[removed];Initial Catalog=[removed]" – Chi Wu Nov 11 '14 at 23:11
  • That connection string doesn't align with the comment "creating 100 databases a day". That EF connection string would only leverage the database instance at 'Initial Catalog'. It would only align if your strategy was to drop the DB regularly and recreate it. – Simon W Nov 11 '14 at 23:42
  • My comment means if I create 100 new database in one day then I'll have to manually change the service tiers 100 times to the desire one. I think there is nothing to do w/ the connection string. I'm currently looking more toward to the EF and Azure SQL. – Chi Wu Nov 11 '14 at 23:55
  • 2
    I posted a possible solution to your situation but it will mean a fair amount of work to stand it up this way. http://blog.siliconvalve.com/2014/11/12/use-azure-management-api-sdk-in-an-entity-framework-custom-database-initializer/ – Simon W Nov 12 '14 at 00:40
  • Take a look at [this answer](http://stackoverflow.com/questions/27617969/how-to-programatically-create-sql-azure-database-of-type-basic-standard-edition). – Ognyan Dimitrov Mar 09 '15 at 08:47

2 Answers2

1

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);
Xavier John
  • 8,474
  • 3
  • 37
  • 51
0

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!!"
         }
}
Pradebban Raja
  • 443
  • 5
  • 20