AbleCommerce Shopping Cart Software

By the end of 2015 we'll be hosting at least two dozen AbleCommerce sites for our customers and we're trying to reduce our hosting cost. While discussing the need to renew/upgrade our SQL server license with our IT department the question came up if Gold is compatible with AzureSQL (we know that it is not right now) and if AbleCommerce has any plans to make it able to run on AzureSQL. Could you please tell me if that's on the development plan for 2015 or 2016?

Hi Karin,

From what I'm reading, it looks like it might already work. According to this article -

http://social.technet.microsoft.com/wik ... abase.aspx

Similar to an instance of SQL Server on your premises, Azure SQL Database exposes a tabular data stream (TDS) interface for Transact-SQL-based database access. This allows your database applications to use an Azure-based SQL Database in the same way that they use your on-premise SQL Server.

I haven't tried it myself, but I'm guessing that you can try to install to AzureSQL and report back your findings. If you have any trouble, then I'd be more than happy to see if our dev team can help.

Thanks
Katie

Thanks for the quick reply, Katie! We've tried with AC 7.0.7 and got a ton of errors for various issues. We can try with Gold 9 again but I don't expect the results to be much different and I don't have the resources right now to go into that again. I was just hoping you guys had some information.

AC 7.0.7 has only been tested with SQL server 2008, so it's possible that AzureSQL is running a newer version. Maybe we can look into this and report back with our findings. We're going to be fairly busy until end of Feb though.

Thanks, Katie. Same here; we're pretty swamped as well into Q2 with migrations from the old shopping cart sites to the new AC sites and custom development for those. I'll see if we can find a little slot to simply try it without spending time trying to debug any issues. I'll let you know if I have some updates. BTW... Happy 2015!

This is an interesting question. Since Gold is written around Nhibernate so we need to check if Nhibernate can work with SQL Azure. As suggested in following article from developer of Nhibernate http://ayende.com/blog/4177/nhibernate- ... -sql-azure nhibernate should work. So higher chances are Gold will be compatible with SQL Azure.

Thanks for that additional bit of information, Mazhar! I'll pass it on to our development lead who has done all the Azure investigations so far.

Mazhar,

Our chief developer squeezed an install attempt into his day. There were a lot fewer errors, indeed, but it still failed on a new piece of SQL code. AzureSQL does not support newsequentialid() which is used for the ac_Wishlists. It throws this error on the ac_Wishlist Create:

Error: Built-in function 'newsequentialid' is not supported in this version of SQL Server.

Seems like Azure has no intention of ever supporting that http://feedback.azure.com/forums/217321 ... quentialid.

His biggest concern, however, is transient fault handling for connection failures that are common in any cloud development. Can you tell us how Gold handles retries due to db connection failures?

We are really grateful for any information you can provide so we can determine if moving to Azure by end of 2015 is even a remote possibility.

Best Regards,

I had a misunderstanding with my developer. I thought he had run the full installed but he only tested the SQL code.

When I the actual install it throw an exception after entering the store data and asking to insert the sample data.

The app.log contained these errors (ignoring anything ac_Wishlists related):

2015-01-16 17:16:20,591 WARN AbleCommerce Could not validate unique order number index
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'sysindexes'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at CommerceBuilder.Licensing.d.c(SqlConnection A_0, String A_1, String A_2)
at CommerceBuilder.Licensing.g.e(SqlConnection A_0)
at CommerceBuilder.Licensing.g.a(String A_0)
ClientConnectionId:6b99061e-7e47-46f9-a260-42c93807eb39
Error Number:208,State:1,Class:16
2015-01-16 17:16:24,804 WARN AbleCommerce Could not validate ac_ReviewReminders table
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'sysindexes'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at CommerceBuilder.Licensing.d.c(SqlConnection A_0, String A_1, String A_2)
at CommerceBuilder.Licensing.g.o(SqlConnection A_0)
at CommerceBuilder.Licensing.g.a(String A_0)
ClientConnectionId:6b99061e-7e47-46f9-a260-42c93807eb39
Error Number:208,State:1,Class:16
2015-01-16 17:16:26,217 WARN AbleCommerce Could not validate ac_ProductGroups table
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'sysindexes'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at CommerceBuilder.Licensing.d.c(SqlConnection A_0, String A_1, String A_2)
at CommerceBuilder.Licensing.g.q(SqlConnection A_0)
at CommerceBuilder.Licensing.g.a(String A_0)
ClientConnectionId:6b99061e-7e47-46f9-a260-42c93807eb39
Error Number:208,State:1,Class:16
2015-01-16 17:16:40,430 WARN AbleCommerce Could not validate foreign key indexes
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'sysindexes'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at CommerceBuilder.Licensing.d.c(SqlConnection A_0, String A_1, String A_2)
at CommerceBuilder.Licensing.R9UpgradeService.b(SqlConnection A_0)
at CommerceBuilder.Licensing.R9UpgradeService.HandleR9Updates(SqlConnection connection)
ClientConnectionId:6b99061e-7e47-46f9-a260-42c93807eb39
Error Number:208,State:1,Class:16

I don't know if others would show up later if these got fixed and I can't put more time into it. But it's clear that Gold 9 is not able to run on AzureSQL as it's now. Too bad.

Minh couldn't let it go and did some fix magic and got the install to complete . I'll run some tests the next few weeks as I find time and see how it works.

Any updates @kstevenson? We've co-located our servers at one of the data centers in town for the past 11 years. Would be awesome if we could get out of the hardware/os maintenance game altogether and just focus on developing great sites. From what I've read, Azure can give that to us at a reasonable cost, and allow us to readily scale.

@sweeperq: We've left the shopping cart business and did not pursue Azure installation because of that. Our developer was able to do a proof of concept but we never formally QA'ed it. Sorry, I'm not able to help anymore.

We have to go through strict testing and compliance for our PA-DSS (PCI) certification. AbleCommerce is certified to work with MS SQL 2005, 2008 R2, and 2012. The PCI counsel and the testing companies are fanatical about making sure the server and application environment is foolproof by making sure that your SQL data is on a separate server, behind a firewall, and that it is not even on the same network as the application server. That said, we would not be able to 'officially' support Azure because it is in a cloud hosted environment.

That's not to say it wouldn't work. If you have the ability to give it a quick installation, then you never know...

Katie

Not sure I understand your "We would not be able to 'officially' support Azure because it is in a cloud hosted environment."? All web servers are in the cloud, it is just a matter of whose cloud. Are you saying that I could do everything wrong from a PCI standpoint on my own servers and it would be supported because I am using MS SQL 2012 and IIS, but if I lease out space on someone else's server it is not supported? I trust Microsoft and Amazon to have a more secure and robust environment than most people hosting their own server. I can say this b/c I've been doing it for over 10 years and the amount of knowledge needed to keep servers/databases/networks secure and up to date is more than most e-commerce startups understand.

But since we're talking specifically about Azure, here are some resources for you to check out regarding PCI-Compliance on Azure. Microsoft Azure completes an annual PCI DSS audit using an approved Qualified Security Assessor:

[*]https://www.microsoft.com/en-us/TrustCe ... liance/PCI
[*]http://aka.ms/Customer_PCI_Guide

While the general statement "everything is in the cloud" is accurate in a broad sense, you're making a pretty big assumption in how 'cloud' is defined.

Microsoft offers 'cloud' SQL through Azure. But only because it's a SQL install virtualized by user account and accessible by anyone on the planet. Not because you can connect to it over the internet. When any SaaS implementation is deployed, certain additional security measures must be implemented because of that very fact. These same security measures would not be necessary in a private networking environment where firewalls, packet filtering and VLAN implementations are in place.

sweeperq wrote:Are you saying that I could do everything wrong from a PCI standpoint on my own servers and it would be supported because I am using MS SQL 2012 and IIS, but if I lease out space on someone else's server it is not supported?

Again, leasing space on a server doesn't make it a 'cloud' implementation. It just makes it accessible over the internet. Meeting PCI compliance is very different from meeting the system requirements of an application. You could easily be way out-of-compliance, but still have a compatible environment for the .Net application to run. The two are not interconnected nor should they be.

Azure is a complex animal. Try pulling the server date/time in an Azure install. The result is completely different from any other non-cloud implementation of SQL server. Subtle differences like that can break even the best designed application.

Hello,

I spun up an Azure 2008 installation and don't see issues.

Feel free to give it a test, pm me if you'd like admin access.

http://40.76.217.60/Default.aspx

Hello All,

We setup a dedicated Microsoft Azure installation for testing. It's was an easy install, just be sure all of your servers are in the same location.

Location (East US)
Windows 2012 R2 Currently on Basic A2 (2 cores, 3.5 GB memory)
SQL Server 2012 Currently on S1 Standard (20 DTUs)

http://azure.ablecommerce.com/Default.aspx

It may be worthwhile to you Mike to work with Microsoft to have your shopping cart as an option for anyone to spin up and you can get revenue from the use of AbleCommerce as a platform. It would be cool to see AC Gold as an Azure Web App and be a huge hit for you!

https://azure.microsoft.com/en-us/servi ... rvice/web/

Hi Neal,

We're continuing to move forward with Azure to fully leverage the platforms opportunities!

Just wanted to say that this is great and should certainly position AC well for the future. It also brings AC back into the fold for us when it comes to future upgrades. Agree 100% with what Neal said about AC Web App.

Hello All,

For comparison we've spun up an Amazon EC2 instance as well.

EC2/RDS Settings: Windows 2012 R2 (t2.micro 1 core, 1gb) & SQL Server Web 12.00.4422.0.v1(db.t2.small)
http://amazon.ablecommerce.com/Default.aspx

Windows 2012 R2 Currently on Basic A1* (1 cores, 1.75 GB memory) & SQL Server 2012 Currently on S1 Standard (20 DTUs)
http://azure.ablecommerce.com/Default.aspx

*Just resized to be more similar to Amazon's settings