What is Azure Transformation¶
The PAFnow Azure Transformation is an Azure Marketplace offer, intended to simplify your SQL data transformation. Like the SSIS Package, Azure Transformation is used to automatically generate a PAFnow Data Model from your event log.
You will be able to use the Azure Transformation once you have an active PAFnow Enterprise licence and having stored your event log on a Azure hosted service like SQL Server on Azure Virtual Machines, Azure SQL Managed Instance or an Azure SQL Database.
If you are using an older version of Microsoft SQL Server (e.g. 2017 or lower), please refer to the SSIS section of this documentation.
How it works¶
With Azure Transformation, a Docker container is deployed on Azure that acts as a manager for your transformation. The container then validates your credentials and connects to your database, where it coordinates the transformation.
Just enter your settings and let Azure Transformation do the rest.
After the transformation is complete, you may delete the created resource.
How to use the Azure Transformation¶
Using our Azure Transformation works as follows:
Review our Azure Marketplace offer here
Click on Get It Now.
A pop-up window will appear asking for your consent. By clicking Continue, you agree to the terms and will be redirected to the Azure Portal.
Click on Create.
Fill out the first step of the wizard by selecting a resource group for deployment under Resource Group, a preferred region under Region, and a unique container name under Container Name.
Due to restrictions of Azure, you will deploy the Azure Transformation to an empty resource group.
Proceed to the next tab and enter your PAFnow credentials there.
Your password is not directly validated in the user interface. Double check that you have not made any mistakes, to prevent the additional work of having to do it again.
Switch to the next tab and enter the details of your SQL instance connection.
Getting the FQDN
If you have an Azure SQL Server but do not know its connection details, check the resource in the Azure Portal. On the overview page, you will find the Fully Qualified Domain Name under Server Name
It should look something like this:
Proceed to the next step and enter your transformation settings.
- EventLogTable (mandatory): Specify the name of the database table which contains the event log.
- SchemaIn (mandatory): Specifies the table schema where the event log is located.
- CaseAttributesTable (optional): If you have case attributes in a seperate table, specify their name here.
- SchemaOut (optional): Specifies the table schema where the output tables should reside.
- TablePrefix (optional): Specifies a table prefix, e.g., DepartmentX_EventLog which will be used for all created tables.
Continue to the next step, double-check all the settings, and then click Create to start the transformation.
Check the newly created Docker container in the Azure Portal. Navigate to the resource group where you deployed the package and switch to the new container group.
On the left side tabs, select Containers and then click Logs.
Check the last message in the log. If you see the message
[Main] Finished transformation. Cleaning up, it means that the transformation was successful.
When you see
[Main] Finished transformation. Cleaning upin the log of when the docker containers state has switched to Terminated you may delete the container group from the Azure Portal.
To connect your transformed data please read SQL Data Import
If you have problems with the transformation, please check the container log as described in step 8 of How to use Azure transformation. Most problems are caused by typos in credentials or connection details.
A typical error message might look like this:
[ConnectionManager] Error connecting to the server. Source: Core Microsoft SqlClient Data Provider Number: 18456 State: 1 Class: 14 Server: paf-testing-sql.database.windows.net Message: Login failed for user 'pafnow_user'. Procedure: LineNumber: 1
[Authentication] Authentication failed. Check your credentials
The following table will give a brief overview of the most common errors:
| ||There was a problem with the database users credentials. Double check if you entered them correctly.|
| ||Could not connect to the SQL Server instance. Check that you entered the FQDN correctly. (Are you using a non-standard port for your SQL Server?)|
| ||The SQL Servers firewall does not permit access from this IP address. Check below on how to solve this problem.|
| ||Check your PAFnow credentials.|
| ||It seems that your PAFnow account is not associated with a valid PAFnow Enterprise licence. If this is wrong, please contact support.|
| ||The table name you specified does not exist in the given database. Check for typos.|
Opening access to your SQL Server
If you don't personally have access to manage your companies Azure resources, please contact an administrator to do this for you:
- Navigate to the resource hosting your SQL Database on the Azure Portal (If applicable: Don't select the database itself, but the server hosting it)
- Go to the Firewalls and virtual networks tab
- Either turn on Allow Azure services and resources to access this server or manually add the IP address specified in the error message to the rules down below
- Save the changes and restart your Container Instance to retry connection
If you are reaching out to support, please provide a copy of the whole execution log in your inquiry.
The duration of the transformation proccess heavily depends on your server performance settings. If you are running a large dataset make sure to scale your Azure Resource to an adequate performance level. If you are using an Azure SQL Database we recommend to use Premium performance tier because of its greater IO performance. Choose an equivalent performance tier if you are running another type of SQL Server, paying special attention to providing sufficent IO throughput.