SQL Database

Azure SQL Database provides managed SQL Server in the form of an individual database or a logical server which can host multiple databases.

Differences from SQL Server

SQL Database shares its heritage with SQL Server and the two products are largely indistinguishable. A complete list of differences can be found in the feature comparison.

Backup and restore

Unlike many other Azure services backup and restore for SQL Database is achieved entirely within the SQL Database service itself, supporting full PITR with second granularity. These PITR backups can be retained for a maximum of 30 days, after which you can use longer term retention of daily, weekly monthly and yearly backups for stronger archival.

Note that restores must always take place to a copy of a database, and because of the provisioning overhead can take some time. Azure SQL Database does not support SQL Server snapshots outside of database copy operations. These may offer a quicker restore time.

Common administration tasks

For the most part Azure SQL Database behaves like a managed SQL Server instance, but there are some differences to how access control works:

  • Databases must be selected at connection time. Issuing a USE [database] command within a session will trigger disconnection.
  • SQL Server "logins" are scoped to the logical server. The logical server gets a single administrative user (analogous to the "sa" user of an on-premises deployment), but all other security objects are contained within the database.

Creating users

Azure AD authentication (preferred)

Using Azure AD authentication means that we can safely skip the setup of a user with a matching SID on the secondary server, and eases our administration burden by centralising authentication.

CREATE USER [username@domain.com]
    FROM EXTERNAL PROVIDER;

EXEC sp_addrolemember 'role', 'username@domain.com'

Since there's no need for a SQL Server login, there's no need for any manual setup on the secondary servers.

SQL Server authentication

Connect to the master database (either by specifying it explicitly or not specifying an initial database), and issue:

CREATE LOGIN [name] WITH password = 'password'

Then, in a connection to the target database create a user and assign it an appropriate role:

  • db_datareader -- data warehouse
  • db_owner -- platform users
CREATE USER [name] FROM LOGIN [login]
EXEC sp_addrolemember 'role', 'name'

Secondary servers and SQL logins

Since SQL logins are server-level objects they're not subject to database replication. In-database user objects are related to logins by way of the SID field. As a result it's important what you include the WITH sid = '' clause when creating the logins against secondary servers in failover groups.

First, identify the SID of the login on the primary:

SELECT sys.fn_varbinarytohexstr(sid)
FROM sys.sql_logins
WHERE name = 'name'

Then create the login on the secondary:

CREATE LOGIN [name] WITH
    PASSWORD = 'password',
    SID = 0xFF

Working with schemas

Schemas (sometimes referred to as object namespaces) provide a means of separating logical sections of a database, both in terms of structure and permissions. We use them to provide delegated control of resources within one team's database to another team without allowing them to modify production resources.

Schemas can be created as follows:

CREATE SCHEMA MySchema
GO

Access can then be delegated via GRANT:

GRANT CONTROL ON SCHEMA :: MySchema
    TO [username@domain.com]
GO

Note that managing some types of objects still requires CREATE to be granted at the root of the database in addition to ALTER on the schema:

GRANT CREATE PROCEDURE ON DATABASE :: MyDatabase
    TO [username@domain.com]
GO

PITR after deletion

Although not exposed in the Portal UI, it is possible to perform a restore of a database from a point in time of a deleted database. The restore points associated with a database aren't immediately purged upon deletion, they're aged out gradually as they expire.

First, get the deleted database object:

$src = Get-AzSqlDeletedDatabaseBackup `
        -ResourceGroupName resource-group `
        -ServerName the-server `
        -DatabaseName source-database

Now get a DateTime object (in UTC) for the point in time that you'd like to restore from:

$pit = Get-Date -Date YYYY/MM/DD -Hour HH -Minute MM

Bring it all together now, optionally including -ElasticPoolName $src.ElasticPoolName if you'd like to match the elastic pool of the source database:

Restore-AzSqlDatabase `
        -FromDeletedDatabaseBackup `
        -DeletionDate $src.DeletionDate `
        -ResourceGroupName $src.ResourceGroupName `
        -ServerName $src.ServerName `
        -TargetDatabaseName target-database `
        -ResourceId $src.ResourceId `
        -PointInTime $pit.ToUniversalTime()

Backlinks