SQL Server Issues

Accelerus Icon
 

If your school will be using an SQL Server database for Accelerus, you may use Microsoft SQL Server 2008, 2008R2 or 2012, including Express editions of these.

This section summarises the basic SQL Server database and maintenance procedures a school must put in place. The notes Accelerus and SQL Server should be downloaded from the Accelerus website for more information on using Accelerus with an SQL Server database, including instructions on installing SQL Server Express, if your school does not have an SQL Server licence already, SQL maintenance plans and backups.

Where an SQL Server database is to be used, your school is responsible for SQL Server licensing, installation and configuration. The school must have the necessary SQL Server administration expertise to create, maintain and backup its database, and attend to issues directly relating to the use of SQL Server, hardware and other software used in running the SQL Server system.

Our documentation on SQL Server is provided as a guide only. Decisions on the version, configuration, maintenance plans, etc, are the responsibility of the school.

Also, assistance with set up and problems directly relating to SQL Server are not covered by Accelerus support. Schools requesting such assistance may incur additional costs.

 

minusWhen to use SQL Server

An SQL database must be used if your school is:

Using Accelerus Web or Accelerus Goals Management System.
Allowing coordinators and teachers to login directly to the database to work.
A large school or has complex assessment and reporting requirements.
Using an Access database that is approaching 600MB in size and growing significantly each reporting period.

 

minusMinimum requirements

It is recommended that the SQL Server has at least 4GB of RAM, and more than this if the server is under heavy load or you have a large database or large school. SQL Servers work by loading as much of the database into memory as possible to increase performance. Therefore, the more RAM you have available to your SQL Server, the faster you will be able to access your data, run analyses, etc.

 

minusSQL database permissions

Once an Accelerus SQL Server database has been created, you must ensure that appropriate SQL permissions are set up. The following should be given at least db_datareader and db_datawriter permissions to the SQL database:

Accelerus administrators and any others who need to login directly to the Accelerus database.
If your school is using Accelerus Web or Goals Management System, the Accelerus web service.
If using offline files, the account running the Synchroniser or Synchroniser Service.

Teachers do not need direct access the database at all when only using Accelerus Web or synchronising their offline files.

Windows authentication or SQL authentication may be used to access the SQL database by those requiring it. Windows authentication is the preferable method, but in some cases SQL authentication must be used. However, each school may choose their own authentication methods.

Each user or account accessing the SQL Server requires an SQL Server login. Logins may be assigned via:

A specific SQL login, eg SA for System Administrator.
An individual user’s Windows Active Directory account, eg School\msmith.
A Windows Active Directory user group, eg School\AccelerusUsers.

The last of these options is a common way to set up Accelerus users who connect directly to the database, as it does not require constant maintenance of SQL users as teachers come and go.

 

minusSQL database maintenance

It is important that you have a maintenance plan in force for your SQL Server database which ensures your database is being backed up regularly and its performance is optimised.

Note, however, that if you are using an Express edition of SQL Server, you will not be able to do so automatically. SQL Express does not have the ability to create and run maintenance plans. Therefore, you will need to manually backup and maintain your database.

The following frequencies are recommended when setting up the maintenance plan for your Accelerus database:

Check Database Integrity – Weekly
Shrink Database – Monthly
Reorganise Index– Monthly
Update Statistics – Weekly
Clean up history – Yearly
Back Up Database (Differential) – Daily
Back Up Database (Transaction log) – Yearly
Maintenance Cleanup Task – Yearly

 

plusDatabase and log file size

It is important that you allow enough hard drive space on your server for your Accelerus database and the log files created for it by SQL. In addition, free space is required for day to day operations.

You should monitor the database and log file size and, if necessary:

Increase the Auto growth percentage for the database
Limit the size to which log files may grow.
Shrink the log files periodically.