Documentation Center

Setting up an SQL Server Database

WorldServer can store all of its internal information (workflow definitions, terminology database, and so on) in a SQL Server database.

Setting up WorldServer to use SQL Server is divided into two phases:

  • Creating the SQL Server database to hold the WorldServer data.
  • Populating the schema with WorldServer content.

Each of these steps requires a different set of database permissions and is outlined separately.

The WorldServer installation process on Windows can create and populate a SQL Server database for you. However, there may be cases where you need to manually create and populate the SQL Server database outside of the installation process. For example:

  • If you are installing WorldServer on Solaris or Linux, you will need to manually create the SQL Server database.
  • If your SQL Server database administrator (DBA) does not allow other users permission to create a new database.

For these situations, follow these instructions before installing WorldServer. Otherwise, skip to Installing WorldServer on Windows.

Prerequisites

This process assumes that you already have an existing Microsoft SQL Server 2005/2008/2012 instance up and running.

It also assumes that you have adequate permissions to access this instance and perform various operations; each section below will list the necessary permissions.

Tuning an SQL Server Database

This section contains some general guidelines for configuring and maintaining WorldServer SQL Server 2005 databases to enhance performance.

SQL Server, in most cases, knows how to configure itself dynamically for optimum performance, and generally does a good job of it. In some cases, changing SQL Server configuration settings may cause more problems than they fix and is not recommended. When the SQL Server service is first started, SQL Server begins with its default configuration settings. But as activity begins in the databases and as queries are run, SQL Server will fine-tune itself and performance will improve with time.

The easiest way to view your SQL Server's configuration settings is to run the sp_configure command in Query Analyzer. See your SQL Server Books Online for more information about this command.

  • Memory Requirements

    For best SQL Server performance, it is recommended that SQL Server run on a dedicated machine with plenty of physical memory. Your production SQL Server should not be sharing resources with any other major application. It should be the only application running on the server and it should usually be configured with its default memory options to dynamically manage the memory on that server.

    In most cases, the SQL Server settings for the maximum server memory and the minimum server memory should be left to their default values. This is because the default values allow SQL Server to dynamically allocate memory on the server for the best overall optimum performance.
    • The maximum server memory setting, when set to the default value of 2147483647 (in MB), tells SQL Server to manage the use of memory dynamically, and if it needs it, to use as much RAM as is available (while leaving some memory for the operating system).
    • The minimum server memory setting, when set to the default value of 0 (in MB), tells SQL Server to manage the use of memory dynamically. This means that SQL Server will start allocating memory as is needed, and the minimum amount of RAM used can vary as SQL Server's needs vary.
  • Parallelism

    The max degree of parallelism option allows you to specify if parallelism is turned on or off, or only turned on for some CPUs on your server. Parallelism refers to the ability of the Query Optimizer to use more than a single CPU to execute a single query. By default, parallelism is turned on and can use as many CPUs as there are running on your server. This default setting is the best general setting and works for most cases.

  • Optimistic Concurrency
    There are significant advantages in using the new Optimistic Concurrency feature in SQL Server 2005, including:
    • Faster project segmentation
    • Several improvements in blocking issue seen in SQL Server 2000/2005

    To enable Optimistic Concurrency, turn on the READ_COMMITTED_SNAPSHOT option on the SQL Server 2005 database.

  • I/O Subsystem

    For information about the I/O subsystem, see: http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx.

  • SQL Server Connection Pool

    WorldServer uses a connection pool of JDBC connections. Connections are initialized at WorldServer startup and are shared by all threads within one WorldServer instance. Each connection consumes one SQL Server connection process. The size of the connection pool is controlled by the general.properties setting: database_connection_pool_size.

    By default, SQL Server allocates only as many user connections as it needs. This allows those who need it to connect, while at the same time minimizing the amount of memory used. When the user connections setting is set to its default value of 0, user connections are dynamically set. In nearly all cases, this default setting is the ideal setting, and should not be changed.

  • Updating Statistics

    Keeping statistics up to date is crucial for optimal performance of a WorldServer SQL Server database. Bad statistics lead to bad execution plans. By default, SQL Server automatically creates and maintains the statistics without any user intervention. The majority of SQL Server users will achieve optimal performance without any changes to this default. This helps to ensure that the optimizer statistics are valid, and that queries are properly optimized when they are run.

    When a SQL Server database is under very heavy load, sometimes the Auto Update Statistics feature can update the statistics at inappropriate times, such as the busiest time of the day. But as a rule of thumb, if your server is not under maximum load, then leaving this option on is the best decision.

    To check whether Auto Update Statistics is turned on for your WorldServer database, run this:
    sp_dboption databasename
    To check whether Auto Update Statistics is turned on for a particular table, run this:
    sp_autostats tablename
    To turn on the automatic updates for all statistics in WorldServer database, if not already on, execute the following statement:
    USE master
    EXEC sp_dboption 'MyWorldServerDatabase', ' auto update statistics', 'true'

    However, be aware that this option is not a full solution:

    It is possible that the statistics created by the auto-update may not be accurate enough because of the sample rows used. If the table in question is greater than 8 MB, when SQL Server automatically creates statistics for the table, it does not examine every row. Instead, it takes a sample of the available rows. This sample may or may not be good enough to produce accurate enough statistics. One way to get better statistics is to manually update the statistics for the table using the UPDATE STATISTICS command, and using either the FULLSCAN or SAMPLE command options.

    If your SQL Server maintenance window allows for it, you should manually update statistics for all tables in your WorldServer database on a schedule that is suitable for you. This is because the auto-update option is less than perfect, and accurate and up-to-date statistics is so important to overall performance of SQL Server.

  • Automating Reindexing

    WorldServer SQL Server indexes will become fragmented over time. Nearly all UPDATE, INSERT, or DELETE activity will cause indexes to become less well organized than they were when they were first created. There will be more page splits, there will be a greater number of pages with less data on them, and consequently, there will be more I/O required to satisfy each SELECT. The more fragmented your data and indexes become, the slower your WorldServer application will be, and the more space your data will take up. So you will need to reindex your WorldServer database on a regular basis.

    Basically, you can use the Database Maintenance Wizard to perform reindexing, and create Maintenance Plans to do the job. But there are some limitations. First, the reindexing done by the Maintenance Wizard will reindex everything, regardless of whether it needs it or not. If you have a large WorldServer database with large tables and lots of indexes, indiscriminately reindexing the entire database will take longer than your available maintenance window.

    WorldServer can make available a maintenance script, rebuild_indexes.ms.sql, for figuring out which indexes need to be rebuilt above a requested scan density threshold and rebuilding only those ones. This script can also be added as a scheduled task, either as a standalone task or included as a step in existing maintenance jobs. Contact SDL Technical Support for assistance.

    This WorldServer script uses the following SQL Server 2005 commands.
    DBCC SHOWCONTIG command checks how fragmented an index is. For example:
        DBCC SHOWCONTIG ('dbo.tasks') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
    DBCC DBREINDEX command rebuilds indexes for tables. For example:
        DBCC DBREINDEX ('dbo.tasks', taskPk) WITH NO_INFOMSGS

Creating the SQL Server Database

The first step is to create the physical SQL Server database. This process requires a SQL Server user in the “Database Creator” role and should generally be performed by a DBA.

  1. Start SQL Server Enterprise manager and connect to your SQL Server instance as a user in the "Database Creator Role".
  2. Create a new database under the SQL Server instance. Do not include a hyphen in your database name as this may result in issues during the database population process.

    You can optionally control the location of the default data file and transaction log for this database. For the best performance, these two files should be on different physical disks (for example, D: and E:) connected to different I/O controllers.

  3. Create a new user that will be used by WorldServer.

    Make sure that you assign this user the db_owner role for the WorldServer database.

  4. Exit SQL Server Enterprise Manager.

Populating the Database Schema

When the SQL Server database has been created, the database schema can be populated by following these instructions:

  1. Copy the following scripts from the sql directory of your WorldServer distribution into a working directory (for example, C:\ws_install) on your SQL Server client machine:
    create.ms.sql
    setup.ms.sql
    create_sp.ms.sql
    create_tr.ms.sql
  2. Start the SQL Server Query Analyzer and log on using the WorldServer database user.
  3. Use the pull-down to change to the WorldServer database.
  4. Load and execute create.ms.sql..

    You can disregard any "MAX row size exceeded" warnings you see in the output as they are expected.

  5. Load and execute setup.ms.sql.
  6. Load and execute create_sp.ms.sql.
  7. Load and execute create_tr.ms.sql.
  8. Exit SQL Server Query Analyzer.
At this point, your database schema has been populated and can be used by WorldServer.

You can now install WorldServer. Depending on your operating system, proceed to Installing WorldServer on Windows or Installing WorldServer on Solaris or Linux.