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
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….
hello every body I followed this guide forcing alfresco 3.4 community to use SQL Server as its database. there was no problem until first restart and now I can't access alfresco share and also alfresco explorer. the error is Failed to login. The remote server may be unavailable or your authentication details have not been recognized. Should I use any services and tools to start SQL Server? my OS is windows7
hello every body I followed this guide forcing alfresco 3.4 community to use SQL Server as its database. there was no problem until first restart and now I can't access alfresco share and also alfresco explorer. the error is Failed to login. The remote server may be unavailable or your authentication details have not been recognized. Should I use any services and tools to start SQL Server? my OS is windows7
If you are using SQL Server 2005+, locate the following registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
Verify that TcpDynamicPorts has no value, and TcpPort has a value of 1433. You can comma separate additional static ports to load balance the ports (recommended to use 1 port per SQL instance).
By default, TcpDynamicPorts will have a value in it and the above will always give you the login issue. I had same problem. Once I resolved port connectivity (and make sure firewall has that port opened too), you should be able to refresh the sql management studio on tables and see them being created.
