NAM database management

The following sections will guide you through database migration, upgrade, and maintenance tasks.

Server database migration


When migrating your report server's database to another physical location, it is important to remember the database owner (the sa user) password change. When you migrate a report server database to another Microsoft SQL Server, re-configure the report server's access to the databases.

Why the database owner password must be re-entered

After the database is moved to a machine other than the one specified during the NAM Server installation process, provide the database owner password again to maintain the contiguity of task scheduling. Otherwise, the database maintenance task responsible for shrinking the tempdb and the NAM Server database transaction log file will stop working.

When this occurs, it is indicated by log message TempdbShrinker: Login failed for user 'sa' .

For more information on changing the database owner password on the report server, see Updating database owner password

What to do when the database owner password is not re-entered after migration

The report server is configured to perform maintenance of the tempdb database and transaction log shrinking. This is done every night to prevent rapid growth of the temporary database. By default, the task MsSqlShrinkDB is enabled and performs operations that require the database owner password. If the report server database is moved to another machine and sa user password is not updated, the task will fail to execute.

To prevent the tasks from being performed, edit the *[NAM Server_INSTALLATION]* \config\tasks-100.hcbs.xml file and disable the MsSqlShrinkDB task by surrounding it with an XML comment.

Note that when this task is disabled, the database server administrator will be responsible for manually shrinking the tempdb database and the NAM Server database transaction log.

Figure 1. Example of a Disabled MsSqlShrinkDB Task

 <!--<task ID="MsSqlShrinkDB" name="Shrinks tmpdb and db transaction log. Works only on MS SQL Server" periodType="DAY" period="1" timeLine="SERVER" offsetTime="03:00" timeout="03:00">
        <command ID="0" name="Shrinks tmpdb and its transaction log" timeout="02:00">
            <class>adlex.delta.server.repository.scheduledTasks.MsTempdbShrinker</class>
        </command>
        <command ID="1" name="Shrinks database transaction log file" timeout="03:00">
            <class>adlex.delta.server.repository.scheduledTasks.MsLogShrinker</class>
        </command>
</task>-->

New default database location


To create a new default location for database files:

Create the database folder Drive_ID:\mssql\data.

Open SQL Server Management Studio: Start ► Programs ► Microsoft SQL Server ► SQL Server Management Studio.

Connect to the local server as the sa user or local machine administrator.

Right-click the local SQL server tab and select Properties.

Specify default locations.

Type Drive_ID:\mssql\data in the Database default locations field, both for Data and Log.

Click OK to confirm the settings.

Renaming databases


We recommend that you keep the default database names, but the following procedures show how to modify them if your NAM deployment requires database names that are different from the installation defaults.

Back up your database

Because each of the following procedures requires manual editing of product files, it is important that you back up any files that you plan to modify.

Renaming the report server database

Stop the watchdog service.

Using MS SQL Server Management Studio, highlight the NAM Server (or delta in older installations) database, right-click it and rename it.

Find the repository.properties file. This is typically located in <installation directory>\CAS\config.

In the properties file, update hostname (if necessary) and databasename:

JDBC_URL=jdbc:jtds:sqlserver://hostname:1433//databasename;useNTLMv2=true;tds=8.0;lastupdatecount=true;

Restart the watchdog service and wait until the NAM Console service is started.

Renaming the NAM Console database

Stop the watchdog service.
Wait for both services (watchdog and console) to stop.

Using MS SQL Server Management Studio, highlight the cva database, right click it, and rename it.

Find the database.properties file. This is typically located in <installation directory>\workplace\configuration

In the properties file, update hostname (if necessary) and databasename:

database.databasename=databasename
database.serverName=hostname

Restart the watchdog service and wait until the NAM Console service is started.

Moving the tempdb database


If your tempdb database is heavily used by many applications, moving the database to a new location may improve its overall performance.

To change the SQL Server tempdb location:

Open SQL Server Management Studio by selecting Start ► Programs ► Microsoft SQL Server ► SQL Server Management Studio.

Connect to the local server as the sa user or local machine administrator.

In the Object Explorer, select Databases ► System databases.

Choose a new query.

Right-click tempdb and choose new query.

Modify the location of the tempdb database files.

Do this by typing in a query such as:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'e:\mssql\data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'e:\mssql\data\templog.ldf')

The paths in this example may not match your database installation, so make sure to adjust them as required.

Stop SQL Server service.

Move the tempdb.mdf and tempdb.ldf files from <installation directory>\MSSQL.1\MSSQL\Data to e:\mssql\data.

Start SQL Server service.

Verify your changes.

Use the following query to display the names, location, and state of the tempdb database files:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

Table 1. Example Output of the Query

Name CurrentLocation state_desc
tempdev E:\mssql\data\tempdb.mdf ONLINE
templog E:\mssql\data\templog.ldf ONLINE

Close SQL Server Management Studio.

Restart the system.

Updating database owner password


When you move the NAM Server database to another SQL Server or change the sa user password, you must re-enter the password on the report server. This step is necessary to keep task scheduling contiguous and avoid database maintenance problems.

To notify the report server about the SQL Server administrator password change:

From the report server menu, choose Tools ► Admin Console.

Click Commands console in the Diagnostics column and search for SET DB SUPERUSER.

Add the database owner name (sa, if the default SQL Server settings are preserved) as Parameter 1 and a new password matching the one on the SQL Server as Parameter 2.

Click Execute to replace the old SQL Server password.