AnsweredAssumed Answered

Alfresco 3.4 Community support for Microsoft SQL Server 2008

Question asked by smcardle on May 27, 2011
Latest reply on May 28, 2014 by mariuszdawczyk
Hi Everybody

I was recently asked by a client to get Alfresco Community 3.4.d  to run against a Microsoft SQL Server 2008 R2 database.

However, the Alfresco Community editions only support MySQL and PostgreSQL databases so I took a look at what needed to be done to achieve this. Anyway, after a couple of days I had everything running smoothly and without issue.

I would like to create an AMP to update the alfresco.war file to include this support but haven't had time to look at that yet, maybe it's not possible with an AMP!!!!

Anyway, to install the changes you currently need to explode or uncompress the alfresco.war file and then unzip the contents of the SQLServer2008R2.zip file in to the WEB-INF/classes directory.

Then configure your alfresco-global.properties file to use your SQL Server database (it's only been tested with the Open Source jtds-1.2.5.jar, so copy this to either your tomcat/lib or your <jboss>/<server>/lib directory)

    Create an SQL Server DB
    Create an Alfresco user with password and grant DBO to the user
    Execute the query ALTER DATABASE alfresco SET ALLOW_SNAPSHOT_ISOLATION ON;
    Configure alfresco-global.properties
An example of the DB setup for SQL Server in the alfresco-global.properties file:

#
# SQLServer connection
# Requires jTDS driver version 1.2.5 and SNAPSHOT isolation mode
# Enable TCP protocol on fixed port 1433
# Prepare the database with:
# ALTER DATABASE alfresco SET ALLOW_SNAPSHOT_ISOLATION ON;
#
db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://<server>:1433/alfresco
db.username=alfresco
db.password=alfresco
db.txn.isolation=4096
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
You can get the zip file from here
http://www.4shared.com/file/wCSXyag9/SQLServer2008R2.html


Just one more thing…..
In the org.hibernate.dialect.SQLServerDialect directory there is an extra file named EOSS-removeAllTables.sq;
This contains a set of queries that will enable you to remove all of the tables create in the database by Alfresco, including all of the constraints. You probably wont need it but It was incredibly handy as I was trying to get this to run so that I could start over each time, forcing Alfresco to recreate the required tables….


Regards

Steve

Outcomes