Azure SQL Installation

Tldr

• Naming Convention:
  • Server: xxx-pwrp-dbs
  • Database: xxx-pwrp-dwh-db
  • Schema:
    • config
    • bc
    • odoo
    • ionbiz
    • etc.
• Deployment granularity:
  • database server
    • we typically have a single database server per client
    • if the client has environments, we have a single database server per environment
  • database
    • we typically have a single database per application field. For example, we only have one database per application
• Service Tier:
  • DTU, Standard (Budget Friendly)
  • DTUs: 10-100

Create SQL Server

1. In Azure, create an SQL Database. In the Basics tab

   1. Name of the resource in the marketplace is "Azure SQL"
   2. SQL databases > Single database
   3. name: xxx-pwrp-db
   4. server: create one if required
      1. name: xxx-pwrp-dbs
      2. location: West Europe
      3. Authentication method: Use both SQL and Microsoft Entra authentication
      4. Set our tech user as admin
      5. server admin login: xxx-pwrp-dbs-admin
      6. password: create a safe password in 1password under DWH Admin SQL Login, best from a safe generator. Save it in 1pw.
         
   5. No elastic pool
   6. Workload environment: Development
   7. Compute + storage: Configure database
      1. Service tier: DTU > Standard
      2. Data max size: 2 GB is enough
      3. Check the cost: ca. 15 USD per month is normal
         
   8. Locally-redundant backup storage

2. In the Networking panel, set

   1. Connectivity method: Public endpoint
   2. Allow Azure services and resources to access: Yes
   3. Add current client IP: Yes

3. Security: accept defaults

4. Additional settings: accept defaults

5. Tags: not needed

6. Create

Create Users

1. On the master database, execute the following script:
   

   CREATE LOGIN [db-ddl] WITH password='(generate in 1password)'
   CREATE LOGIN [db-writer] WITH password='(generate in 1password)'
   CREATE LOGIN [db-reader] WITH password='(generate in 1password)'
   

   
   This creates a login to the Server.

2. On the respective database, run:
   

   CREATE USER [db-ddl] FROM LOGIN [db-ddl];
   EXEC sp_addrolemember 'db_datawriter', 'db-ddl';
   EXEC sp_addrolemember 'db_datareader', 'db-ddl';
   EXEC sp_addrolemember 'db_ddladmin', 'db-ddl';
   
   CREATE USER [db-writer] FROM LOGIN [db-writer];
   EXEC sp_addrolemember 'db_datawriter', 'db-writer';
   EXEC sp_addrolemember 'db_datareader', 'db-writer';
   
   CREATE USER [db-reader] FROM LOGIN [db-reader];
   EXEC sp_addrolemember 'db_datareader', 'db-reader';
   

   
   This creates a login and gives it the right permissions.

3. Store all three users and passwords in 1pw

Checklist

• SQL Server deployed
• db-ddl User created, can log in
• db-writer User created, can log in
• db-reader User created, can log in