Azure SQL

Create Server and Database

Despite the promise of a ‘fully managed cloud database’ – the concept of a server is still used by all the cloud providers I’ve seen. Azure is no exception, to create an Azure SQL Database, you must create an Azure SQL Server. This is more of a logical grouping rather than an actual server in the traditional sense. It provides a good way to assign resources (like ram and cpu) to a database, and provides a place for the system databases (master,model,msdb/postgres) to live.

Authentication

Your Azure subscription will have a subscription administrator account, which is most likely your Microsoft account. This account (as with all ‘azure guest accounts’) is not supported for Azure AD (AAD) authentication. So, you have to create a user in the directory associated with your subscription.

In my case, the directory i created is mwild.onmicrosoft.com. I created a new user for my admin purposes, and added this to the Administrators group. Then configure the Azure SQL Server ‘Active Directory admin’. I mapped this to the Administrators group. (You would probably create a ‘DBA’ group in real deployment scenarios). Now test you can login to the database using the admin user you created using SSMS/LINQPad/etc. (Connection strings are available on the database page in the Azure Portal).

Assuming everything went well, we now have a database server and database, and we can login with an AAD account.

Service Accounts

Create a service account in AAD for the application that wants to talk to your database. In my case this is something like `[email protected]. Then we can create a __contained database user__ for the service account. This is basically just a sqlUSERin a database, with no sqlLOGIN` in master. I also just add read/write permission here as they will not be saved in source control.

USE <db_name>;
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datareader] ADD MEMBER [[email protected]];
ALTER ROLE [db_datawriter] ADD MEMBER [[email protected]];

Database Projects

You can manage schema changes in your database however you want. I usually use flyway for simple schema migration deployments, all you need to do is create a folder and put some sql scripts inside, then run flyway migrate e.g.

$ tree
my_project/
    sql/
        V1__baseline.sql
        V2__create_users_table.sql

$ flyway migrate

Another option is to use SSDT in Visual Studio. You write scripts for each object in your database, then click “Publish” in VS.

$ tree
my_db_project/
    tables/
        users.sql
    views/
        accounts.sql
my_db_project.sqlproj
# right click --> publish

Both of these options allow you to keep all sql changes in source control, as well as easily target a local db for development.