Delphi Reporting Database
Configuration Guide for Installing on the Same Database Server
Newmarket International, Inc.
January 2, 2004
Table of Contents
Document Control
Introduction and Overview
Requirements
Assumptions
I. Create the Report Database
II. Create Stored Procedure in Master
III. Create the Nightly Job to Restore Production Database to Report Database*
IV.Configuring Delphi, Delphi MPE and Global Sales
V.Configuring Market Vision
VI.Configuring Crystal Reports
Appendix A. Nightly Maintenance Wizard Backup and the Restore Process.
Appendix B. Concepts in Publishing 3rd party applications to use the OLTP database
Crystal Reports
Market Vision
Document Control
2.0 / Re-Release 11/11/032.1 / Incorporated changes suggested by Fred Ransom (PSG) and added clarification for WIN.INI updates.
Introduction and Overview
The purpose of this document is to demonstrate the standards and procedures that should be met when staging a second reporting database for Newmarket International products.
Requirements
- FTP access to NI
- SQL Access to create jobs
- Knowledge of nightly SQL backup job schedule
- Strategy outlined for implementation on same server as OLTP production database.
Assumptions
Installer is familiar with Windows 2000 Server, setup of the Delphi system and Microsoft SQL 2000 Administration. Installer has access to all applicable servers to be configured, SQL and Newmarket’s application on at least one configured client or Terminal Server. Customer has the appropriate infrastructure to complete all tasks.
I. Create the Report Database
A daily restore of the production database will be used to refresh the reporting database on a nightly basis. A pre-requisite would be to have enough space on as the production database as the partition on which the reporting database is to be restored.
From the production instance of SQL Server, make a SQL Script from Enterprise Manager to create the second copy of the database. On the Newmarket database right-click and choose generate SQL script from the all tasks menu. /On the formatting tab, deselect the ‘drop’ option. /
On the options tab, select the script database option. Click ok and choose a location to save the file to. /
Open the script in Query Analyzer. /
At this point you have created a script that has all the information necessary to create the files and filegroups and options to support the restore of the ‘NSS_Database’. The script needs to be modified so that it creates the database named ‘NSS_REPORT’ sets options for and create the same named files and filegroups but in a location specific to the ‘NSS_REPORT’ database.
Change the Database name (NSS_DATABASE to NSS_REPORT) and the default location for database files. For example, change c:\ni_data to c:\ni_report. DO NOT change the name of the files or filegroups. If you do not follow this procedure correctly the database will not restore properly if the physical structure is not the same. For example, see the script below in which the highlighted areas required change.
CREATE DATABASE [NSS_REPORT] ON (NAME = N'NI_DEFAULT', FILENAME = N'D:\NI_REPORT\NI_DEFAULT.mdf' , SIZE = 110, FILEGROWTH = 50) LOG ON (NAME = N'NI_LOG', FILENAME = N'D:\NI_REPORT\NI_LOG.ldf' , SIZE = 350, FILEGROWTH = 100)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
ALTER DATABASE [NSS_REPORT] ADD FILEGROUP [NI_DATA_FG]
GO
ALTER DATABASE [NSS_REPORT] ADD FILE(NAME = N'NI_DATA', FILENAME = N'D:\RPT_DATA\NI_DATA.mdf' , SIZE = 250, FILEGROWTH = 100) TO FILEGROUP [NI_DATA_FG]
GO
ALTER DATABASE [NSS_REPORT] ADD FILEGROUP [NI_INDEXES_FG]
GO
ALTER DATABASE [NSS_REPORT] ADD FILE(NAME = N'NI_INDEXES', FILENAME = N'D:\NI_REPORT\NI_INDEXES.mdf' , SIZE = 150, FILEGROWTH = 100) TO FILEGROUP [NI_INDEXES_FG]
GO
exec sp_dboption N'NSS_REPORT', N'autoclose', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'bulkcopy', N'true'
GO
exec sp_dboption N'NSS_REPORT', N'trunc. log', N'true'
GO
exec sp_dboption N'NSS_REPORT', N'torn page detection', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'read only', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'dbo use', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'single', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'autoshrink', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'ANSI null default', N'true'
GO
exec sp_dboption N'NSS_REPORT', N'recursive triggers', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'ANSI nulls', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'concat null yields null', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'cursor close on commit', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'default to local cursor', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'quoted identifier', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'ANSI warnings', N'false'
GO
exec sp_dboption N'NSS_REPORT', N'auto create statistics', N'true'
GO
exec sp_dboption N'NSS_REPORT', N'auto update statistics', N'true'
GO
if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'NSS_REPORT', N'db chaining', N'false'
GO
use [NSS_REPORT]
GO
Run the modified script against the master to create the ‘NSS_REPORT’ database.
In Enterprise Manager, press F5 to refresh databases and confirm the creation of the NSS_Reports database
II. Create Stored Procedure in Master
This stored procedure is must be created by running the following script against Master database. The script is to create this can be found on the NI FTP site at ftp://12.15.184.150/psg/download/reportdb/NI Kill all Processes.txt. It can then be executed by the following command: exec NI_KillAllProcess nss_database.III. Create the Nightly Job to Restore Production Database to Report Database*
*Note: If the site is using the new maintenance wizard procedure as per standard 912 procedures see Appendix A.
Create a new SQL job that will run the restore procedures. /Give the new job a name and description on the general tab.
Make sure the job Owner is ‘SA’ /
On the steps tab create the first step to set to kill all SPIDs that hold a connection to the database. / exec NI_KillAllProcess NSS_REPORT
Create a second step to complete the restore. In order to use this the nightly backup will need to be renamed, see Appendix A for procedure.
restore database [report database name] -- edit with the correct name of the report DB
from disk = 'drive:\ni_backup\full backup.bak' -- edit with correct drive letter / Here's an example of an over the network restore step
Create a third step to create the database users and rolls
Call SP ‘Proc_createuser’ /
On the schedules tab, schedule the job to run daily after the nightly full backup of the production database has completed. Review the job history for the nightly backup and schedule accordingly. /
IV.Configuring Delphi, Delphi MPE and Global Sales
A change to the WIN.INI on the workstation or Terminal Server needs to be made for each user to take advantage of the standard performance reports. NOTE: THESE LINES ARE SENSITIVE TO LABEL SETS AND MUST BE NUMBERED APPROPRIATELY IF LABEL SETS ARE USED IN THE DEPLHI IMPLEMENTATION
Add the following lines to the WIN.INI for each user:RPTSCHEMA1=NSS_REPORT
RPTDBPATH1=NSS-REPORT /
At this point you will be able to run the following canned reports in Delphi over the secondary database:
GRC Report
Rooms Inventory Report
Backlog Report
Rooms, Rates, Revenue Report
Status Change Report
Daily Transactions Report
COTB Report
Block/Pickup Report
The GRC Report and the Rooms Inventory Report default to the secondary database but give you the option of using the primary database. The remaining six reports output to the secondary database only. All other reports in Delphi use the primary database.
V.Configuring Market Vision
The following steps will point all Delphi Market Vision users to the secondary reporting database.
- Create new System DSN on each workstation (or on each terminal server) to point to the report database. The name of the DSN should be NSS-REPORT.
- Change the default Market Vision connection from the Delphi database to the Report database.
- Open Market Vision from the Delphi scheduler screen.
- After Market Vision opens, select the Host dropdown, then click on Connections.
- In the Connections Windows, click on Breeze.con in the Connection Names box (or whatever is the default connection indicated by the bulleted name), and then click on the edit button.
- In the Edit Connection Window, enter the DSN name that was created on the workstations or terminal server (should have been named nss-report). Leave the Id and Password the same. Click on the Save button.
- In the Connections Windows, click on the Set Default button, and then click on Done.
- At the Market Vision main screen, select the File dropdown, and then click on close.
- Select Yes from the Market Vision/User Window to save the changes to the data model.
VI.Configuring Crystal Reports
All database use for Crystal Reports is defined in dictionary files. In order for the ad hoc Crystal Reports to use the secondary database, you need to use dictionaries that are specified for the secondary database. These dictionaries are hard-coded and cannot be used against a schema that is not named ‘NSS_REPORT’. The dictionaries in use at the site need to be replaced with Offline Reporting Dictionaries. These replacement dictionaries can be downloaded from ftp://12.15.184.150/psg/download/reportdb/dictionaries.zip.
Appendix A. Nightly Maintenance Wizard Backup and the Restore Process.
It will be necessary to rename the file that the nightly maintance wizard names to a filename that is consistent with the file that the restore process calls ‘DR_Full.bak’. So a step must be added to rename the file using a command such as the following:ren c:\ni_backup\nss_database*.bak dr_full.bak /
Appendix B. Concepts in Publishing 3rd party applications to use the OLTP database
It may be necessary to provide access from Market Vision and Crystal Reports back to the OLTP database for certain operational reports. High level steps are provided below but may change depending on product installed and environment being installed into. Both require a knowledge of publishing applications through Citrix and the considerations surrounding access by select users and/or groups. Determining this would be a customer or business consulting team responsibility.
Crystal Reports
This would require a second instance of Crystal Reports to be published with access to the OLTP formatted dictionaries.
Market Vision
Market Vision would need to be published to point to open a second copy of the data model ‘Vision.gql’ named ‘Visionr.gql’. Then a published applicaition would need to be created with the command line to the Market Vision executable that calls the copy of the data model, for example ‘x:\mktvisn\market vision user\mktvisn.exe visionr.gql’.