Preparing a NAM server database

When you install a NAM Server, you have two database options. The option you choose depends on your local security requirements as they relate to SQL Server database management.

  • Create new database
    Select Create new database to have the installer create a SQL Server database, create a database user for the NAM Server, and set the database user rights. This is the easiest and most reliable of the two options, but your local security requirements may not permit you.

    Tip

    If you are permitted to choose Create new database when you run the installer, you can stop reading this help topic now, go straight to installation, and select Create new database when the option is displayed. The installer will create a suitable database based on the input you give it.

  • Use existing database
    Choose this option if you are not permitted to have the installer to create a SQL Server database.

    Tip

    If your organization's security restrictions concerning SQL Server database management do not permit you to choose the automatic option, you (or your organization's SQL Server administrator) must prepare a database according to the instructions below. Then you can run the installer, select Use existing database, and point to the database you have prepared manually.

Make sure your firewall settings allow the machine on which you install NAM Server to connect to the database server. The default Microsoft SQL Server port is 1433.

Preparing a database

You don't need to do this if you are going to select Create new database when you run the installer. Instead, you can go straight to installation.

But if your local security requirements do not permit the installer to create a SQL Server database and database user for the NAM Server and set the database user rights, you need to ask your local SQL Server database administrator to refer to this help and to create a database, create a user, and set user rights (all as described below).

After that work is completed, run the installer), select Use existing database, and point the installer to the database created by your SQL Server database administrator.

Preparation checklist

  • You are a SQL Server administrator (you have administrator privileges).
  • You have reviewed the hardware and licensing information.
  • You have reviewed the Third-Party Licensing topic regarding SQL Server licensing.

Installation overview

Installing the SQL Server database for a NAM Server has two stages:

  1. Using the SQL Server administrator account, set up the report server database, database user, and database user rights.
  2. Using the new SQL user account, set up and organize the database schema and all necessary tables and indexes.

When you are asked for the sa password, this is because the installer needs it to:

  • Obtain SQL Server information
  • Set up databases
  • Set up a database user
  • Grant permissions to the database user
  • Set memory limit

During report server installation, none of the existing databases or users is modified. The installer modifies only the NAM database you explicitly specify in the installer dialog.

After the report server is installed, you can inspect the SQL scripts that were performed as the sa user. The values you entered in the installer dialogs will be stored in the scripts in the report server's config directory, by default located in the *<installation directory>* \config\sql\ms\setup path.

SQL scripts stored in the report server file system after installation:

C20Database.sql
C30DomainLogin.sql
C30Login.sql
C50DomainUserRights.sql
C50UserRights.sql
SetMaxMemory-curr.sql
SetMaxMemory-max.sql
SetMaxMemory-recc.sql
SetMaxMemory.template

After installation, the database owner password is stored by the report server so that important maintenance tasks can be performed. For more information, see Server database migration.

Creating a database

Network Delta, the main report server database, stores all of the data presented by the report server. The script, C20Database.sql, is executed to create the database on SQL Server. The values of the variables set in this script are gathered by the report server installer in the Database Connection Properties, Database Size, and Database Server Memory Setting dialog boxes.

The database parameter settings configured by the installer program are:

  • Automatic shrinking is turned off

  • Automatic statistics creation is turned on

  • Checking for out-of-date statistics is turned on

  • Simple Recovery model is turned on

  • SQL_Latin1_General_CP1_CI_AS is used as the SQL collation

The variables used in the script are the following:

ND_DB_DATABASE

The name of the database. Enter the name in the installer in the Database Connection Properties dialog box, in the Name of database box.

ND_DB_DATABASE_dat

The name of the database file on the SQL Server file system.

ND_DB_DEVICE

Where the database files are stored. Enter the value as the Database device location.

ND_DB_SIZE

The size of the database. The recommended size is calculated by the installer, but you can override the calculated value in the Database Size dialog box.

ND_DB_DATABASE_log

The name of the database log file on the SQL Server file system. The name is the value of the ND_DB_DATABASE variable with _log appended to it.

ND_LOG_DEVICE

The physical location of the database log file. The location is the same as for the database file.

ND_LOG_SIZE

The database log size is calculated automatically based on the size of the database. This is typically about 0.25% of the database size.

The database remains empty at this point. Tables and other structures are created later, after the sa user logs out and the installer logs in as the default database user to finish setting up the database.

Creating a database user

You can select a Windows domain account or a regular database account to create a new database user.

Depending on your choice, the installer will execute either C30DomainLogin.sql or C30Login.sql . For more information, see Create a Windows Domain User as a Database User (C30DomainLogin.sql) and Create a Database User (C30Login.sql).

Create a Windows domain user as a database user (C30DomainLogin.sql)

If you choose a Windows domain user (when you enter a string of the form domain\user in the Name of database user box) as the default report server database user, the installer automatically recognizes this and tries to set up the provided user as the default database user. The domain\user is split into variables ND_DB_DOMAIN and ND_DB_LOGIN . They are used later by a script that sets the domain user rights for the database. For more information, see Setting database user rights.

Create a database user (C30Login.sql)

The default database user name is entered in the Name of database user box and the password for that user is determined in the Database user password box. The value of ND_DB_LOGIN is substituted by the user name you provided. The user's default database (ND_DB_DATABASE) becomes the database you provided in the installer's Name of database box.

The password is set by a procedure that does not log the password or store it in a file on a hard disk.

Setting database user rights

A database user with certain rights is required during the report server installation and operation. These rights can be assigned by executing one of the following SQL scripts.

To this end, a database schema is created and then a set of rights is granted to the database user. These rights include:

  • Backing up the database
  • Backing up the database log
  • Connecting to the database
  • Verifying the database state
  • Creating tables, procedures, and functions
  • Executing the select command on tables in which information on tables and indexes is kept
  • Checking whether the database needs defragmenting

The database user must be able to verify that the database structure is consistent and that the required tables, columns, and indexes exist. The default database user is also assigned the role of bulkadmin so that records can be inserted into the database in the fastest possible way.

Domain user rights

The C50DomainUserRights.sql script is executed when the database user was set to be a Windows domain user.

Database user rights

The C50UserRights.sql script is executed when the default database user is set up on the database server directly.

Setting memory size

You can define the memory size used by SQL Server during installation.

At installation time, the system checks the available amount of memory and recommends a value, and you can use the recommended setting or enter different one.

The current setting on SQL Server is saved in the file SetMaxMemory-curr.sql, the memory size is increased to the maximum so that the installer (which is run by the script in file SetMaxMemory-max.sql) has the best conditions to proceed, and then either the memory size is restored (with the use of the file SetMaxMemory-curr.sql) or set to a recommended value with the help of the file SetMaxMemory-recc.sql .
This template is used by the installer to create the SetMaxMemory-curr.sql, SetMaxMemory-max.sql and SetMaxMemory-curr.sql scripts.

After the SQL Server environment is ready, the sa user account is logged out and the user account defined during the installation is logged in to finish the database setup.

A log for this part of the database installation is stored in:

<installation directory>\log\install\maintainDBTables_stdout.log