Lots of our clients use Azure SQL Server, but not many realise that much of the configuation of a database can be changed using standard T-SQL commands using C#, SSMS, etc. rather than needing to go into the Portal. This also means they can be easily automated.


Changing Performance

For example, it is straight forward to dial the performance of a database up or down using a single command. We often use this to turn databases down, to save money, during evenings and weekends.

ALTER DATABASE current MODIFY (SERVICE_OBJECTIVE = 'P6');

See full docs - ALTER DATABASE docs.


Copying a database

Another version useful feature is the ability to make a copy of a database and mount it under a new name. This is ideal for creating testing or development databases from a copy of a production database.

Below is an example C# script that I use for refreshing a mydb-dev database from the production mydb database. The dev database uses a cheaper / lower powerful instance S0. You may need to tweak the CommandTimeout if the database is particularly large.

using (var db = new SqlConnection("Server=tcp:mydb.windows.net"))
{
    db.Open();
    var cmd = new SqlCommand { Connection = db, CommandTimeout = 180 };

    Console.WriteLine("Making a new copy of the database");
    cmd.CommandText = "CREATE DATABASE [mydb-dev-copy] AS COPY OF [mydb] ( SERVICE_OBJECTIVE = 'S0' )";
    cmd.ExecuteNonQuery();

    
    // Wait whilst Azure makes the copy, checking the progress
    cmd.CommandText = "SELECT state FROM sys.databases where name = 'mydb-dev-copy'";
    while (true)
    {
        var state = (byte) cmd.ExecuteScalar();
        if (state == 0) // Online
            break;
        if (state == 7) // Copying
        {
            System.Threading.Thread.Sleep(TimeSpan.FromSeconds(5));
            continue;
        }

        throw new Exception("Database copy failed: " + state.ToString());
    }

    Console.WriteLine("Drop the dev database");
    cmd.CommandText = "DROP DATABASE [mydb-dev]";
    cmd.ExecuteNonQuery();

    Console.WriteLine("Rename copy to dev database");
    cmd.CommandText = "ALTER DATABASE [mydb-dev-copy] MODIFY NAME = [mydb-dev]";
    cmd.ExecuteNonQuery();
}