Installing P11 on Azure SQL Database (Not SQL Managed Instance)

Hello all,


Have anyone been able to (without resorting to the wizard that is present in the Azure Marketplace) been able to install P11 using an Azure SQL Database (not an SQL Managed Instance) using the good old Configuration Tool?


I am always getting the error in attach, and even generating the SQL script doesn't work. When I try to execute the script against the Azure SQL Server "instance" with the Administrator user that I specified when creating the first database under the Azure SQL Server, it gives me a lot of errors:

"

Msg 134, Level 15, State 1, Line 28
The variable name '@cmd' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 40508, Level 16, State 1, Line 62
USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
Msg 15151, Level 16, State 1, Line 73
Cannot alter the role 'db_owner', because it does not exist or you do not have permission.
Msg 15007, Level 16, State 2, Line 80
'hr' is not a valid login or you do not have permission.
Msg 15151, Level 16, State 1, Line 85
Cannot add the principal 'hr', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 86
Cannot add the principal 'hr', because it does not exist or you do not have permission.

"

I naturally was able to solve this using Azure SQL Server Managed Instances, but it seems an unnecessary cost if OutSystems was able to do it using the Wizard...


Solution

Hello all,


Just for those who need it, I was finally able to get a platform running using Azure SQL Databases. How?

1 - Create in Azure portal (or via template, scripting... You pick your poison) the 3 databases (Platform, Logs and Session - from now on, as an example: outsystems, outsystems_log and osState). Be careful to select the correct instance size and collation (CI_AI) as well as other required Azure resources that you might need (Security Groups, vNets, etc.)

2 - Use SSMS to connect to the "master" database of the Azure SQL Server that contains the created databases with your Administrator account and create the required "principals":

CREATE LOGIN OSADMIN
WITH PASSWORD = '<your_OSADMIN_password>'
GO

CREATE LOGIN OSRUNTIME
WITH PASSWORD = '<your_OSRUNTIME_password>
GO

CREATE LOGIN OSADMIN_LOG
WITH PASSWORD = '<your_OSADMIN_LOG_password>'
GO

CREATE LOGIN OSRUNTIME_LOG
WITH PASSWORD = '<your_OSRUNTIME_LOG_password>'
GO

CREATE LOGIN OSSTATE
WITH PASSWORD = '<your_OSSTATE_password>'
GO


3 - Use SSMS to connect to the "platform" database of the Azure SQL Server that contains the created databases with your Administrator account, create the database users and map them to the principals

CREATE USER OSADMIN
FOR LOGIN OSADMIN
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'OSADMIN'
GO


CREATE USER OSRUNTIME
FOR LOGIN OSRUNTIME
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_datawriter', N'OSRUNTIME'
GO
-- Add user to the database owner role
EXEC sp_addrolemember N'db_datareader', N'OSRUNTIME'
GO

4 - Use SSMS to connect to the "log" database of the Azure SQL Server that contains the created databases with your Administrator account, create the database users and map them to the principals

CREATE USER OSADMIN_LOG
FOR LOGIN OSADMIN_LOG
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'OSADMIN_LOG'
GO


CREATE USER OSRUNTIME_LOG
FOR LOGIN OSRUNTIME_LOG
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_datawriter', N'OSRUNTIME_LOG'
GO
-- Add user to the database owner role
EXEC sp_addrolemember N'db_datareader', N'OSRUNTIME_LOG'
GO

5 - Use SSMS to connect to the "session" database of the Azure SQL Server that contains the created databases with your Administrator account, create the database users and map them to the principals


CREATE USER OSSTATE
FOR LOGIN OSSTATE
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'OSSTATE'
GO


6 - Finally, run the configuration tool.

7 - For each tab on the configuration tool, besides the normal configurations you are used to (server, user names and passwords, etc) in the "Advanced Settings", include the following parameter:

Platform tab: Initial Catalog=outsystems
Log tab: Initial Catalog=outsystems_log
Session tab: Initial Catalog=osState

(The catalog names must match the database names you selected previously for your installation in point 1)

8 - On the Platform and Log tab, test the connections to see if everything is working properly and click "Create and Upgrade database" (grant permissions will not work properly)


9 - On the Session tab, unfortunately, the standard "Create Session Database" won't work no matter what you do. As such, go to File->Generate Session SQL Script and save the SQL file. Open it in SSMS with a connection to the "session" database (see point 5).

Edit the script and remove the section that refers to the creation of the OSSTATE user. I recommend you clear everyting until you find the following comment at around line 51 :

-- HubSessionStateRole 

Execute the whole script after you clear every line before that comment.


10 - Finally!!! Go back to the configuration tool and click "Apply and exit". If you did everything right, the Deployment controller and Deployment service will restart, you will get no errors in the event viewer and Service Center will compile and deploy properly.


Hope this helps.


Solution

Hi,

only saw this now because of our talk earlier today :)


Couple of things that we found out during the Marketplace templates development (and really big ones):

  1. the Azure SQL Server admin username cannot be the same as the VMs admin username. It's a really ugly issue on the MS/Windows side. When you try to apply the settings in the conf tool and that pop-up requesting the admin credentials to the DB pops-up, if you have the same username for the DB Server and Windows VM it will always assume that you're passing a windows auth string which will then fail to create the users with the necessary local permissions in the databases.
    In the templates, we resorted to having a sole input for the admin username for VMs and DB Server but then, on our ARM scripting, we add a "-sql" to the username of the DB Server administrator.

  2. We also got into issues when not creating SQL endpoints inside the VNET (one for each subnet where the VM and DBs are):
    These endpoints allow using the SQL server URL in the conf tool with little to no latency between the VM and DB because the name resolution is now completely routed through the backbone of the Azure region you're working in (basically, it's a direct connection).

    Cheers!