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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
Related searches
- english becoming a global language
- what is a global strategy
- linux set global environment variable
- define a global strategy
- is english a global language
- powershell set global environment variable
- what is a global learner
- english as a global language
- developing a global marketing strategy
- english as a global language pdf
- why is english a global language
- link between environment and health