July 2023

There were 2 posts published in July (this is page 1 of 1).

How to scale your azure sql database with SQL

An Azure sql database is super easy and cheap to setup and use. However, if you want to do some serious data manipulation, you will find the cheapest versions are to slow to use, and every upgrade you make on the sql database, is a doubling in price. If you are a data engineer, like me, most of your data work will be in batches, so it will also be annoying to pay for an expensive version, if you only use it for 1 hour/day.

Luckily, there is a solution. You can change the performance / version of the SQL server with SQL.

To check which version you are running, use this SQL

SELECT     
  slo.edition,    
  slo.service_objective    
FROM sys.database_service_objectives slo

And you will get a result like this

You can change the version with this SQL

ALTER DATABASE DSA MODIFY(EDITION='Standard' , SERVICE_OBJECTIVE='S4')

Unfortunately, the change is not instant. My experience is, that on the standard version, the latency is around 5-10 minuttes.

What i normally do, is that I have a job that starts 30 minutes before my batch job, that update the database to a higher version. Then it is updated when the batch starts. And I set the batch job to downscale the database in the end.

For pricing and DTUs, see the screenshot below.

Pricing for all versions can be found here: https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/

Have fun 🙂

How to create sql azure login and user

I use sql azure databases a lot. Both as backend, datamarts and also for data exchange layer with 3. parties.

For that, I need to create users fast and easy. There is no interface for doing that in SQL azure, so you have to do it with SQL.

Step 1: Connect to sql with manegement studio or sql Azure data studio with an admin account, and make sure you are connected to the master database.

Step 2: Create the login like so

CREATE LOGIN Testuser 
   WITH PASSWORD = 'complexpassword'

You can now see the user

Step 3: Switch to the database where you want the user to have access and run this code:

CREATE USER Testuser
    FOR LOGIN Testuser

Step 4: Give the access you want the user to have. For instance reader: 

EXEC sp_addrolemember N'db_datareader', Testuser

And you can now see the new user in the database under users

The default roles you can give to the users can be found here: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

And seen below: