Documentation Home

MS SQL Server

Installation

Download and install SQL Server (http://www.microsoft.com/sqlserver/en/us/editions/express.aspx)

Setup a Database

Create a new database and a user capable of accessing this database with privileges for creating tables included (see SQL Server documentation if you have questions about how to administrate databases and users).

Update the poms to use MS SQL instead of HSQL

In each of your project runnable module's pom.xml (admin, site, api), find the following in the <dependencies> section:

<dependency>
    <groupId>com.broadleafcommerce</groupId>
    <artifactId>broadleaf-boot-starter-hsql-database</artifactId>
</dependency>

and replace it with

<dependency>
    <groupId>com.broadleafcommerce</groupId>
    <artifactId>broadleaf-boot-starter-database</artifactId>
</dependency>

Finally add the MS SQL JDBC connector dependency to the <dependencies> section:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
</dependency>

Update the Hibernate Dialect

Update the runtime properties to use the correct MS SQL dialect. In core/src/main/resources/runtime-properties/common-shared.properties, you will want to update the three persistence unit dialects to say:

blPU.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect
blSecurePU.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect
blCMSStorage.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect

Enterprise and Multi-Tenant

You will need to update a 4th location as well:

blEventPU.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect

Update the SQL Command Extractor

If you wish to utilize the heat clinic demo import scripts, edit core/src/main/resources/runtime-properties/common-shared.properties. Update the following properties to cause the system to modify the import scripts at runtime for SQL Server compatibility:

blPU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoSqlServerSingleLineSqlCommandExtractor
blSecurePU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoSqlServerSingleLineSqlCommandExtractor
blCMSStorage.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoSqlServerSingleLineSqlCommandExtractor

Enterprise and Multi-Tenant

You will need to update a 4th location as well:

blEventPU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoSqlServerSingleLineSqlCommandExtractor

Add the database connection properties

In core/src/main/resources/runtime-properties/common-shared.properties, add your database connection properties like so:

# your local database username, just a user that has readwrite permissions
database.user=root
# local database password
database.password=
database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
# this connection URL assumes that it is connecting to a schema called broadleaf
database.url=jdbc:sqlserver://localhost;databaseName=broadleaf

Note: I named my database broadleaf, make sure you use your database name in the url.

Update runtime properties to create the initial schema

It is likely that you will still need to initialize and seed the new MS SQL schema. Ensure that these properties are set in admin/src/main/resources/runtime-properties/default.properties:

blPU.hibernate.hbm2ddl.auto=create
blEventPU.hibernate.hbm2ddl.auto=create
blSecurePU.hibernate.hbm2ddl.auto=create
blEventPU.hibernate.hbm2ddl.auto=create

After starting up the admin once, you can change all of these back to update so that any data changes stay intact. Also, add the same properties set to update to site/src/main/resources/runtime-properties/default.properties so that starting up site doesn't wipe your changes.

And that's it! You should now be up and running with MS SQL.