1

I have been tring to use SQL Server Azure as a data storage for an Atlassian Crowd on premise for a few days and I am encountering huge performance issues.

For example, crowd admin application is extremely slow, almost unusable.

I was wondering if someone who has successfully set up this kind of solution could give me some advices.

What I have done so far :

  1. setting up an on premise Atlassian Crowd 2.4.2 on an on premise SQL2008R2 database
  2. scripting the database and running the script on an azure database (could not set up directly on azure as setup scripts misses an azure-mandatory clustered index on table hibernate_unique_key)
  3. adding the mandatory clustered index to the azure hibernate_unique_key table
  4. setting up the jdbc connection with ssl

I encounter no problem connecting crowd to the db, but everything is very slow. Crowd startup takes something like 5 minutes, when it takes something like 20 seconds with an on premise sql server. Every round trip to the crowd admin web console takes something like 30 seconds.

My database is less than 1Mb in size. Queries execution summary in azure does not show any problematic query.

I forgot to mention that the SQL Azure Db is very reactive with SQL Server Manager or with an on premise .Net Web App

I tried both jtds jdbc driver and MS JDBC driver 4.0, both with data encryption. I tried both sqlDialect offered by crowd. It stays desperatly slow.

I tried setting special registry keys for Azure as stated by MS JDBC 4.0 driver (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveTime , KeepAliveInterval, TcpMaxDataRetransmission)

Maybe it comes from :

  • the fact that I don't start from a clean setup done by crowd on Azure (because of the clustered index problem)
  • Sql Azure using utc time, making "something" expire everytime.

I would be glad if someone had advices on this problem.

Kara
  • 6,115
  • 16
  • 50
  • 57
jbl
  • 15,179
  • 3
  • 34
  • 101

1 Answers1

1

Sorry - I don't have direct experience with Crowd.

I may go on a limb here, but 9 times out of 10, client applications installed remotely fail basic performance tests against SQL Database (that's the way it's called nowadays) when the application layer is exceedingly chatty (or exceedlingly chunky), with dozens or worse roundtrips for every screen/function, or returning all the records all the time. The reason this usually comes into play is due to the fact that SQL Database is far away going through a network link that is usually slower than on a local network, and on top of it the traffic is always encrypted (meaning there are more packets to transfer).

The only way to go around this type of issue, other than rewriting applications with a better design, would be to try to deploy your Crowd console in a VM in the cloud, in the same data center than you SQL Database instance. At that point, your console will be on the same network than you database, and it should, if my theory holds, be much faster.

Herve Roggero
  • 5,149
  • 1
  • 17
  • 11
  • thx for your answer Herve. I will have hard times justifying a migration of the whole crowd instance to Azure, considering Azure SQL Database is not supported OOTB. I'll enter an Atlassian request for the missing clustered index that prevents clean install. The thing that amazes me is that my DB is a very very small one. I read that sys.dm_exec_connections can help monitoring connection opening and closing. Maybe I'll give a last look this way. – jbl Sep 25 '12 at 15:27