visual - create sql azure database programmatically




How to programatically create Sql Azure database of type Basic/Standard edition through Enity Framework code first (4)

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!!"
         }
}

I use EF 6. My existing code is :

public void CreateOrUpdateCompanyDb(string companyDbName)
{
        try
        {
            string connectionString = _connectionStringProvider.GetConnectionString(companyDbName);
            DbMigrationsConfiguration cfg = CreateMigrationsConfig(connectionString);
            cfg.AutomaticMigrationsEnabled = false;
            cfg.AutomaticMigrationDataLossAllowed = false;
            DbMigrator dbMigrator = new DbMigrator(cfg);

            dbMigrator.Update();
        }
        catch (MigrationsException exception)
        {
            _logger.Error(string.Format("Error creating company database '{0}'",companyDbName), exception);
        }
}

with connection string as follows :

Server=tcp:xxx.database.windows.net,1433;Database=companyDbName;User ID=[email protected];Password=xxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

which creates the database for the particular company. But the problem is that the created database is from the now retired Web Edition but I want to create Basic/Standard/Premium edition.

How should I manipulate the connection string so that the edition of the database is the desired one?


The Edition of Sql Azure Database is something you can specify in Create Database command. AFAIK, you can't specify it using the connection string.

Syntax -

CREATE DATABASE database_name [ COLLATE collation_name ]
{
   (<edition_options> [, ...n]) 
}

<edition_options> ::= 
{
      MAXSIZE = { 100 MB | 500 MB | 1 | 5 | 10 | 20 | 30  150500 } GB  
    | EDITION = { 'web' | 'business' | 'basic' | 'standard' | 'premium' } 
    | SERVICE_OBJECTIVE = { 'shared' | 'basic' | 'S0' | 'S1' | 'S2' | 'P1' | 'P2' | 'P3' } 
}
[;]

Given that, for your scenario, two options come to mind -

  1. Before using DbMigrator, explicitly write code which creates the database, if it does not exist using traditional ADO.Net.

  2. The other option which comes to mind, but I don't know enough about and you could dig into if you want to, is to somehow find a way to hook into EF, so that you could customize the Create Database command it must generate.


How to create a sql database in Basic edition programmatically? in Windows Azure

You specify the edition after the name of the DB:

SqlCommand cmd2 = new SqlCommand(string.Format("CREATE DATABASE [{0:S}] (SERVICE_OBJECTIVE = 'basic');", databaseName), conn);

The documentation for the syntax can be found here


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

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);