Piwik + Azure Web Site + SQL Azure

Hi,
I’ve been trying to make Piwik work on azure websites plugged to an azure sql database (similar to sql server).

First issue I’ve been facing, during the installation there is no PDO\SQLSRV choice. But the connector appears to be here on azure websites’ environment as when I do that Connection libraries for SQL Database | Microsoft Docs (see scripts at the end) it passes.

So I tried to play with config.ini.php but it kept giving me errors.
Any idea of something I might be doing wrong?
Thanks for your help.

Salut Vincent,

We didn’t try yet to run Piwik on Windows Azure. I don’t think it will “just work” because SQL azure is based on SQL server which Piwik does not support yet.

to make it work we would have to edit these files/functions:

and also add new adapter class in this directory: matomo/core/Db/Adapter at master · matomo-org/matomo · GitHub

Salut Matt,
Ok I’ll definitively look into that. Why isn’t it in the main release? Any solid reason?
Having SQL Azure compatibility would allow us to push Piwik as an app service in Azure (that, plus a way to preconfigure part of Piwik).
BTW I’ve been running Piwik on azure website + cleardb (MySQL third provider on azure), it seems to work properly except the visitors map which never shows up for some reason.
Cheers.

Why isn’t it in the main release? Any solid reason?

See some poointers in How do I use another database like Postgresql, SQLite, Oracle? Will you support Nosql databases like Hadoop, Mongodb? - Analytics Platform - Matomo

BTW I’ve been running Piwik on azure website + cleardb (MySQL third provider on azure), it seems to work properly except the visitors map which never shows up for some reason.

That’s great news! Is it easy to do?

For the visitors map, did you setup Geo Location ?

I understand the difficulty to test and support every feature that users ask for. Being in a software editor company myself I know it represents a lot of time. What we do when we have a feature which is asked by a lot of customers, we provide a first version of it, with minimal testing and we indicate the fact that this feature is in beta mode. Customers are more than welcomed to test it and provide us feedback but it is not production ready nor fully supported (contractually).

Yeah it is really easy.
1 Create an azure subscription (if you don’t have one already)
2 Create an azure website (3 clicks really)
3 Create a clear db MySQL db via the third venders (20MB free) (3 more clicks)
4 Upload Piwik and start the setup

Azure WebSite is a free IIS based hosting. And lot of persons already have some free credit on azure (most of them are MS technologies developers but not only). So if Piwik supported MSSQL they could run Piwik for free on azure, even with big amount of data. It would be a good way to reach more users.

I set up the GeoIp, Piwik control panel is telling me that it is properly configured.

we provide a first version of it, with minimal testing and we indicate the fact that this feature is in beta mode

It can be a good strategy, but in the case of Piwik we avoid doing this. the way we work is that we want to make sure Piwik works perfectly at all times. So we cannot really allow “beta” feature that is broken as it would be against our principles. if we provide something in beta, we will be committed to implement it “out of beta”. in the case of MSSQL driver this could take weeks of work. So it’s safer IMO to not provide any support rather than buggy support :slight_smile:

Summary: i don’t think someone from the team will work on this soon. However if you are interested or someone else, and you would like to provide such beta support for MSSQL then we would be happy to merge the pull request (even if it is only beta state!) :slight_smile:

Yeah it is really easy.
Nice to hear and thanks for the steps. I’m sure other people will find this useful.

I set up the GeoIp, Piwik control panel is telling me that it is properly configured.

Did you setup the “Geoip city” database? Do you see data in Visitors>Location for cities?

Hi matt,
Sorry for the late answer. I’ve been using Piwik on Azure (IIS + clearDB which is a MySQL) for over a year now.
It works pretty well (though I’m pretty sure there is a bug with user map that I cannot investigate right know because I reached my cleardb limit).
I surely don’t want to customize my version of Piwik to support MSSQL (Azure SQL in that case) or maintain my own branch, it would be too much of work.
But I could surely run on the beta version an report MSSQL related bugs on github (my login baywet) if I find some other the time.
Just to let you know, I’m not a huge Piwik user in the way that I have a basic usage of it. (not using goals, conversions or things like that) But I could let a few testers access my installation so they can qualify advanced scenarios. (well in fact everyone with a live account has a free access to Azure websites and 90 days of SQL Azure trial included).
Let me know if you are interested in qualifying Piwik with MSSQL. You already qualified IIS, most of the work is done from my point of view.

@Vincent Thanks for your points. I created an issue at: Support MSSQL database server (Microsoft SQL Server) · Issue #6029 · matomo-org/matomo · GitHub

Maybe some users from the community will be interested to participate in this project!

Ok awesome! I’m pretty new to github. Now that we have this “feature” on the roadmap and the changeset (commit?) of the implementing code, what are the next steps?

On our end we do not have much time or skills to participate in the MSSQL support. So what we need is some one from the community to test it and issue pull requests to make it wor on MSSQL. I think it’s rather big project, not easy, but maybe someone or a business will want to work on it!

Well, I’m not a SQL Server Expert but I’m pretty familiar with it. I think getting Piwik to work on SQL Server with SQL authentication will be pretty easy. (it’s the one used by SQL Azure) Most complicated cases will be to support NTLM and Kerberos which are widely used in corporate companies.
But maybe we can work by iterations: first making sure Piwik works properly with SQL Azure (and therefore with SQL Server and SQL authentication) and then support more advanced scenarios.
I think it only has to be properly documented, which case we support and which we do not.
The code is already here but as far as I can understand it is not in the current development branch is that right? How can I work on that? Should you push this commit to the dev branch or can I take the ownership of this modification and work on it?

The code is already here but as far as I can understand it is not in the current development branch is that right?
No the code is not there yet, we have not tested running SQL server yet.

How can I work on that? Should you push this commit to the dev branch or can I take the ownership of this modification and work on it?

The best would be that you Fork the piwik repository in your own account, work on it,
and when you make some progress we could discuss your progress, questions, etc. in this issue: Support MSSQL database server (Microsoft SQL Server) · Issue #6029 · matomo-org/matomo · GitHub

Hope that helps! If you start working on this project, happy hacking :slight_smile:

Hi all,
I show this post an i thought i could write my problem here.
I have installed pre-installed (appdirect) Piwik installation on MS Azure Web Apps (Specs: Azure Webapps with PHP 5.6.17 FastCGI) and created a MySQL server on ClearDB. Wow!! After half an hour i had running it. But… :slight smile:

  1. The ser ver was very slowly. So i have upgrade de Webapps server switch from one core to quad core CPU. It 's ow faster then before. Hmm…

  2. The pages loading very slowly. It’s still slowly loading.

  3. When i select the Administration and click on System Check… After a few minutes i get MySQL server is gone. I check my php error file and check my application error page.
    I have contact with ClearDB and contact with Azure Support. They think dat application is not ready for Azure platform. Bu i read the post @vincentbiret he had working on it.
    How can fix this?

  4. I see also error by FastGCI.
    Excute_Request_Handler
    Error: 500

I have search and googled but it seems that FastGCI is installed good and working well.

Can you help me to fix this problems and i want also create installation guide for Azure platform.

Hi,
I never got it to work properly, I ended up setting up a LAMP server because I need that for the rest of my project.
ClearDB has very poor performances when on the free SKU.
Most open source solutions don’t have an implementation for SQL Azure, for exemple for WordPress you need to add a file.
https://blogs.msdn.microsoft.com/africaapps/2013/07/30/using-wordpress-on-windows-azure-with-sql-server-instead-of-mysql/
And it seems to be the case for Piwik too.
As I didn’t need to have a SQL adapter anymore and didn’t have time to do it, I didn’t do it. But let me know if you find/implement a solution

Hi Marc, we are interested in doing something similar with running PIWIK on Azure as well. Did you ever write that installation guide, and if so, would be great to follow in your footsteps if you don’t mind sharing.

Hi,
The need for supporting SQL server has probably decreased since a real MySQL on azure is in preview Azure Database for MySQL – Fully-Managed Service | Microsoft Azure
Also Linux app service on azure has been around for a while now. Introduction to App Service on Linux | Microsoft Docs
So you can get a stack very close to a in house lamp on azure as a turn key service.
That should reduce the entry barrier a lot for persons interested in running piwik on azure.
Ideally the piwik documentation should explain how to.
I hope that helps the reflections.

1 Like

Thanks Vincent, that’s good to know. If there is any documentation on getting things up and running on Azure, that would be very helpful