SQL Considerations for LAW

<< Click to Display Table of Contents >>

Navigation:  Using CloudNine LAW > Administrating CloudNine™ LAW > Database Installation and Configuration >

SQL Considerations for LAW

The Answer Center provides the minimum and recommended versions of SQL for use with your specific version of CloudNine™ LAW it is recommended to consult the system requirements before installing SQL.

 

Important

The following are considerations and recommendations for a SQL database administrator when setting up an instance of SQL for use with LAW and/ or CloudNine™ Explore.

 

 

Suggestions setting up SQL for use with LAW

•Document your installs so that server instances can easily be reproduced.

1.  Computer/server name where SQL is installed

2.  SQL system administrator password

3.  SQL Instance name

 

Warning

If you write your password down it recommended to keep it in a secure location, possibly off-site.

 

•Install only the SQL Server Services that you are going to need.  LAW only needs the SQL Server Database Engine and SQL Server Browser services.

  hmfile_hash_0c5efe23

Important

Some Services are not needed by LAW and/or Explore

You can install the SQL Server Agent if you intend to run jobs on the server or do maintenance tasks such as automated backups.  However, this function is best handled by a DBA.  

The other services (SQL Server Analysis and SQL Server Reporting Services) are unnecessary for LAW/CloudNine™ Explore and consume valuable resources on your server.

 

•It is best to locate your databases (.mdf and .ldf) and your tempdb on separate physical disks; preferably NOT a USB-connected disk.  This makes it easier to troubleshoot disk access problems, should they occur.

•We do not recommend the use of NTFS data file encryption or compression on SQL server databases and log files.

 

Suggestions for configuring your instance of SQL for use with LAW

•SQL Server max memory allocation is uncapped by default.  This should be capped at approximately 80% of the physical memory allocated to the machine.  For example, a server with 32 GB of physical RAM should be capped in SQL Server Management Studio at 25600 MB.

      hmfile_hash_a3823cfa

Configuring you SQL Server max memory allocation

1. Open the SQL Instance through the SQL Server Managment Studio.

2. Then right click on the Instance name and select Properties.

    sqlserverproperties_zoom15

3.Then select the Memory tab.

  hmfile_hash_e8eab56d

4.The Maximum server memory (in MB): should be approximately 80% of your servers available memory unless you have multiple instances on the same server.

•Leave the “auto create statistics” and “auto update statistics” options on for all user databases. Only in very rare cases should these be turned off, and if they are turned off, then you must manually update the statistics yourself.  Please see Microsoft's website for more information.

 

•Do not use the “auto shrink” database option, as it can waste SQL Server resources unnecessarily and contribute to index fragmentation. Instead, if you need to shrink a database, do so manually. For more information please see Microsoft's website on Considerations for the "autogrow" and "autoshrink" settings.

 

•Do not rely on AUTOGROWTH to automatically manage the size of your databases. Instead, proactively monitor and alter database size as circumstances dictate. Only use AUTOGROWTH to deal with unexpected growth  For more information please see Microsoft's website on Considerations for the "autogrow" and "autoshrink" settings.

 

Suggestions for SQL maintenance

•Set up daily full backups of your databases.  You can set a job to schedule your backups as long as you have SQL Agent service running.

•Perform full backups during periods of low user activity in order to minimize the impact of backups on users.

•Periodically perform "test backup restores" to ensure that your backups are good and can be restored.

•Store backups offsite and in a secure location.