Upgrading a Global/Enterprise Link Environment



Reporting Database

Installation

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.

NOTE: All of the processes outlined below must be performed in SQL Server Management Studio on the Delphi database server.

I. Create the Report Database

1. Make sure there is sufficient space on the Data and Log drives to contain at least 2 times the size of the current Delphi database.

2. Create a new folder on each location named NI_REPORT

a. i.e. if D:\NI_Data exists and is holding the NSS_Database files, then create a D:\NI_Report folder to hold the NSS_REPORT files.

3. Create a script to create the second copy of the database. On the Newmarket database right-click and choose Script Database as, CREATE To, New Query Editor Window

a. 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’. This 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.

4. Use Find and Replace to change the Database name (NSS_DATABASE to NSS_REPORT) and the folder location for database files ( NI_Data to NI_REPORT). DO NOT change the name of the files or filegroups. See the script below showing some examples.

USE [master]

GO

/****** Object: Database [NSS_REPORT] Script Date: 09/14/2008 11:34:06 ******/

CREATE DATABASE [NSS_REPORT] ON PRIMARY

( NAME = N'NI_DEFAULT', FILENAME = N'E:\NI_REPORT\NI_DEFAULT.mdf' , SIZE = 3446144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),

FILEGROUP [NI_DATA_FG]

( NAME = N'NI_DATA', FILENAME = N'E:\NI_REPORT\NI_DATA.mdf' , SIZE = 44128256KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),

FILEGROUP [NI_INDEXES_FG]

( NAME = N'NI_INDEXES', FILENAME = N'E:\NI_REPORT\NI_INDEXES.mdf' , SIZE = 26470400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )

LOG ON

( NAME = N'NI_LOG', FILENAME = N'F:\NI_REPORT\NI_log.ldf' , SIZE = 10240000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

( NAME = N'NI_Log2', FILENAME = N'F:\NI_REPORT\NI_Log2_Log.LDF' , SIZE = 1599944KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'NSS_REPORT', @new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [NSS_REPORT].[dbo].[sp_fulltext_database] @action = 'disable'

end

GO

ALTER DATABASE [NSS_REPORT] SET ANSI_NULL_DEFAULT ON

GO

ALTER DATABASE [NSS_REPORT] SET ANSI_NULLS OFF

GO

ALTER DATABASE [NSS_REPORT] SET ANSI_PADDING OFF

Note: the above script fragment is not complete.

5. Run the modified script against the master to create the ‘NSS_REPORT’ database.

II. Create Stored Procedure in Master

1. This stored procedure is be created by running the following script against Master database. The script to create this procedure can be found in the appendix of this document.

2. Install the procedure by running this script in Query Analyzer.

3. Test the Procedure by the following command: exec NI_KillAllProcess nss_database.

III. Create the Nightly Job to Restore Production Database to Report Database

The new job will have 5 steps. First, Right click on Jobs and choose New Job.

Name the Job NSS_Report Nightly restore and confirm it will run as the SA

[pic]

For each step, Click on Steps, and then New.

1. Name the job Kill processes connecting to NSS_REPORT

a. In the command field enter the following SQL sommand:

i. exec NI_KillAllProcess NSS_REPORT

[pic]

2. Name the Job - Delete last night's restore

a. Change the Type to Operating System (CmdExec)

b. In the command field enter the following SQL command (adjust file location if necessary):

i. If Exist G:\NI_Backup\NSS_DATABASE\Nightly_Backup.bak del G:\NI_Backup\NSS_DATABASE\Nightly_Backup.bak

c. [pic]

3. Name the Job – Rename Backup

a. Change the Type to Operating System (CmdExec)

b. In the command field enter the following SQL command (adjust file location if necessary):

i. ren G:\ni_backup\NSS_DATABASE\nss_database*.bak Nightly_Backup.bak

[pic]

4. Name the Job – Restore NSS_REPORT

a. In the command field enter the following SQL command (adjust file location if necessary):

i. restore database NSS_REPORT from disk = 'G:\NI_Backup\NSS_DATABASE\Nightly_Backup.bak' with replace

b. [pic]

5. Name the Job – Create Users

c. In the command field enter the following SQL command (adjust file location if necessary):

i. Exec Proc_createuser

[pic]

Choose the scheduling option on the left, modify te schedule to run daily at least an hour after the Nightly Maintenance Plan (i.e. Backup plan)

Click OK when job creation is completed and the schedule is set.

Once the job is created, you can run it to test it.

IV. Configuring Delphi, Delphi MPE and Global Sales

It is necessary to point the user connections to the new Database (varies depending on which version of Delphi in use.)

For Delphi version less than 9.4, Add the following lines to the WIN.INI for each user:

RPTSCHEMA1=NSS_REPORT

RPTDBPATH1=NSS-REPORT

For Delphi version of 9.4 and above, add the following lines to the registry (shows complete key path for a x64 terminal server.)

[pic]

Cut and paste the following into a file named NSS_REPORTS_Fix.reg and import it into each workstation.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Newmarket International\Default Application Settings]

"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

V. Configuring Market Vision

The following steps will point all Delphi Market Vision users to the secondary reporting database.

1. In ODBC, create a new System DSN on each workstation (or on each terminal server) to point to the report database. (The name of the DSN must be NSS-REPORT for the Crystal dictionaries to work correctly.

2. Change the default Market Vision connection from the Delphi database to the Report database.

a. Open Market Vision from the Delphi scheduler screen.

b. After Market Vision opens, select the Host dropdown, then click on Connections.

c. 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.

[pic]

d. 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.

[pic]

e. Click on New and create a new connection:

i. Data Source: NSS-MSSQL

ii. User ID: NI_REPORT

iii. Password: reportuser

f. Click on Save As… and name the new connection MPE.

g. In the Connections Windows, make sure the Breeze.con is highlighted, and click on the Set Default button.

h. Then click on Done.

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 the FTP server.

Appendix

NI_Kill_Process creation script

Cut and paste this script into a Query window

--Proc must be created by running the following script against Master

--It can then be executed by the following command: exec NI_KillAllProcess nss_database

Use MASTER

GO

CREATE PROCEDURE NI_KillAllProcess

@pDbName varchar (100)=NULL,

@pUserName varchar (100)=NULL

AS

SET NOCOUNT ON

DECLARE @p_id smallint

DECLARE @dbid smallint

DECLARE @dbname varchar(100)

DECLARE @exec_str varchar (255)

DECLARE @error_str varchar (255)

IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName is NULL)

BEGIN

Set @error_str='No database '+ltrim(rtrim(@pDbName)) +' found.'

Raiserror(@error_str, 16,1)

RETURN-1

END

Create Table ##DbUsers(dbid smallint,uid smallint)

If @pUserName is not null

BEGIN

--Search for a user in all databases or a given one

DECLARE curDbUsers CURSOR FOR

SELECT dbid,name FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName is NULL

OPEN curDbUsers

FETCH NEXT FROM curDbUsers INTO @dbid,@dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @exec_str='Set quoted_identifier off

INSERT ##DbUsers SELECT '+cast(@dbid as char)+', uid FROM '+@dbname+'.dbo.sysusers

WHERE name="'+ltrim(rtrim(@pUserName))+'"'

EXEC (@exec_str)

FETCH NEXT FROM curDbUsers INTO @dbid,@dbname

END

CLOSE curDbUsers

DEALLOCATE curDbUsers

If not exists(Select * from ##DbUsers)

BEGIN

Set @error_str='No user '+ltrim(rtrim(@pUserName)) +' found.'

DROP TABLE ##DbUsers

Raiserror(@error_str, 16,1)

RETURN-1

END

END

ELSE --IF @pUserName is null

BEGIN

INSERT ##DbUsers SELECT ISNULL(db_id(ltrim(rtrim(@pDbName))),-911),-911

END

--select * from ##dbUsers

DECLARE curAllProc CURSOR FOR

SELECT spid,sp.dbid FROM master.dbo.sysprocesses sp

INNER JOIN ##DbUsers t ON (sp.dbid = t.dbid or t.dbid=-911) and (sp.uid=t.uid or t.uid=-911)

OPEN curAllProc

FETCH NEXT FROM curAllProc INTO @p_id, @dbid

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @exec_str = 'KILL '+ Convert(varchar,@p_id)+ ' checkpoint'

SELECT @error_str = 'Attempting to kill process '+Convert(varchar,@p_id)+' in database '+db_name(@dbid)

RAISERROR (@error_str,10,1)with log

EXEC (@exec_str)

FETCH NEXT FROM curAllProc INTO @p_id, @dbid

END

CLOSE curAllProc

DEALLOCATE curAllProc

DROP TABLE ##DbUsers

SET NOCOUNT OFF

GO

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download