Azure SQL is not supported

Question

Azure SQL is not supported

rouke-broersma opened this issue 3 years ago · 6 comments

See: https://github.com/EdwinVW/pitstop/blob/master/src/NotificationService/Repositories/SqlServerNotificationRepository.cs#L39

DB_ID always returns NULL for azure sql (single database).

We could instead use

"IF NOT EXISTS(SELECT * FROM master.sys.databases WHERE name='Notification') CREATE DATABASE Notification;"

The USE command is also not allowed on azure sql single database which is used by ChangeDatabase: https://github.com/EdwinVW/pitstop/blob/master/src/NotificationService/Repositories/SqlServerNotificationRepository.cs#L44

We can replace this with closing the sql connection to master and creating a new sql connection to the target database.

This does work on azure sql. Would this change be acceptable?

Answer 1 · 2021-07-13T05:19:12.000Z

Yes that's fine - providing it also keeps working on SQL Server (which I assume is the case).

Answer 2 · 2021-07-13T05:32:29.000Z

Fixed in commit 9383def
Successfully tested these changes on SQL Server.

Answer 3 · 2021-07-13T05:33:33.000Z

Can you check whether this is sufficient to fix the issue?

Answer 4 · 2021-07-13T07:45:02.000Z

This is the same change we made to our fork 👍

The only difference is that we did a conn.OpenAsync(); in the new connection scope but if you say the change works fine without it on SQL server then I guess it's not neccesary? 🤷

Answer 5 · 2021-07-13T08:00:30.000Z

Yes, it makes sense to explicitly open the connection. I'll create a fix for that.

Answer 6 · 2021-07-13T08:40:19.000Z

See commit f243358

I've also changed the SqlServerWorkshopPlanningEventSourceRepository of the WorkshopManagementAPI so its structure is identical to that of the other two repos.