Is NHibernate and consequently Orchard compatible with SQL Azure

Topics: Installing Orchard, Troubleshooting
Nov 30, 2012 at 9:22 AM
Edited Nov 30, 2012 at 9:22 AM

As I have many problems with Taxonomies  and my database is under SQL Azure I am suspecting some incompatibility between NHibernate and SQL Azure?

Does NHibernate correctly manage the special SQL Azure return codes meaning retry your read request ?

Any feed back concerning Orchard on SQL Azure welcome.

Coordinator
Nov 30, 2012 at 4:19 PM

Not at all, we have lots of sites running on Azure, including orchardproject.net that is running 1.6.

Nov 30, 2012 at 6:35 PM

are you speaking about SQL Azure for the DB ?

Dec 1, 2012 at 5:12 AM
Edited Dec 1, 2012 at 6:08 AM

@Bertrand seems that SQL Azure and NHibernate 3 are not totally compatible due to the transient fault handling pb

see

http://robdmoore.id.au/blog/2012/08/20/nhibernate-sqlazure-transient-fault-handling-nhibernate-driver-for-use-with-windows-azure-sql-database/

This could explain some disagreements I experienced with Taxonomies and SysCache

could we include this library in Orchard distri to solve the pb if impact is low and testing ok ? How to propose this to the comittee ? for 1.7 ?

https://github.com/robdmoore/NHibernate.SqlAzure

Coordinator
Dec 1, 2012 at 5:24 AM

We never noticed anything of the sort but yes, please raise the issue at the next meeting, or directly file a bug.

Dec 1, 2012 at 6:28 AM
Edited Dec 1, 2012 at 6:39 AM

Could you help implementing it, they request to use a call like this

Fluently.Configure()
    .Database(MsSqlConfiguration.MsSql2008.ConnectionString(connectionString).Driver<SqlAzureClientDriver>())

and I found in Orchard AbstractDataServicesProvider class the call which seems to be the only to change (there are also the Test classes, but could wait)

 

  public Configuration BuildConfiguration(SessionFactoryParameters parameters) {            
var database = GetPersistenceConfigurer(parameters.CreateDatabase);
var persistenceModel = CreatePersistenceModel(parameters.RecordDescriptors.ToList()); return Fluently.Configure()
.Database(database)
.Mappings(m => m.AutoMappings.Add(persistenceModel))  
			.ExposeConfiguration(cfg => cfg.EventListeners.LoadEventListeners = new ILoadEventListener[] { new OrchardLoadEventListener() })                
			.BuildConfiguration()                ;        }

How could I adapt Orchard code ?

I understand that their call is only for SQL Server ???? My knowledge of Orchard is too low to understand at which layer I could implement correctly the code branch ?

Coordinator
Dec 1, 2012 at 7:08 AM

I'm sorry, I don't know that part of the code.

Dec 2, 2012 at 8:48 PM
Edited Dec 2, 2012 at 10:39 PM

It seems easy to implement a new driver file SqlAzureDataServicesProvider .cs

(after installing the github package NHibernate.SqlAzure)

 

using System;
using FluentNHibernate.Cfg.Db;
using NHibernate.SqlAzure;

namespace Orchard.Data.Providers
{
    class SqlAzureDataServicesProvider : AbstractDataServicesProvider {
        private readonly string _dataFolder;
        private readonly string _connectionString;

        public SqlAzureDataServicesProvider(string dataFolder, string connectionString)
        {
            _dataFolder = dataFolder;
            _connectionString = connectionString;
        }

        public static string ProviderName {
            get { return "SqlAzure"; }
        }

        public override IPersistenceConfigurer GetPersistenceConfigurer(bool createDatabase) {
            var persistence = MsSqlConfiguration.MsSql2008;
            if (string.IsNullOrEmpty(_connectionString)) {
                throw new ArgumentException("The connection string is empty");
            }
            persistence = persistence.ConnectionString(_connectionString).Driver<SqlAzureClientDriver>();
            return persistence;
        }
    }
}

then add SqlAzure to setup enum
 public enum SetupDatabaseType
    {
        Builtin,
        SqlServer,
        MySql,
        SqlAzure,
    }
and some lines to setupcontroller.cs
switch (model.DatabaseProvider)
                {
                    case SetupDatabaseType.Builtin:
                        providerName = "SqlCe";
                        break;

                    case SetupDatabaseType.SqlServer:
                        providerName = "SqlServer";
                        break;

                    case SetupDatabaseType.MySql:
                        providerName = "MySql";
                        break;

                    case SetupDatabaseType.SqlAzure:
                        providerName = "SqlAzure";
                        break;

                    default:
                        throw new ApplicationException("Unknown database type: " + model.DatabaseProvider);
                }

I have also modified view/setup/index.cshtml (and add/edit in multi-tenancy), must be more complicated to finalize....
My actual problem is I must change the DB to reflect the new enum value, is it the settings.txt file ?
Dec 3, 2012 at 4:07 PM

My implementation seems to be running Ok.

If someone else wants to test I have included the new/modified files in the related Issue I created to ask for including this in 1.7.
Thanks for feedback. 

Jan 23, 2013 at 5:23 PM

Is there any plan to refactor the database code so you don't have to update enums or other code already in the orchard core to add providers for new db's? It would be nice if you could just drop in a module and if you run setup it would pick up all the DataServicesProviders, and display them in the drop list. 

I'm fine with doing this myself, but I wonder if there maybe a reason I'm not aware of that it hasn't already been done. 

Jan 23, 2013 at 6:45 PM

Good move, as an Orchard beginner, I choosed the simplest way.
In fact it seems that Sebastien is not totally sure the Azure SQL modified driver being necessary.
May be the piece of code is not totally efficient because coming fromPatterns and Practice Team :) 

May be he knows something about the evolution of SQL Azure.
I simply found that Microsoft itself has plans to modify Entity Framework to catch the special exceptions and do a retry.... 

On my side I am using it without any problems, but on a very small site....

What would you propose to have an easier pluggable solution ?