Database Actions

PHD History Archive Management
Honeywell Confidential
Database Actions Copyright © 2013 Honeywell International Inc. PHD History Archive Management
1 9/2/2014
Lesson Objective Honeywell
• Objective
– Recognize the files involved in the data archiving process and the
commands and parameters used to configure and control the archiving
• Topics
– Data Archiving Process
– Logical Archives
– Multi-File, Multi-Layer Archive
– Recover Archive
– PHDMAN Commands
– Configuring Archive Size
– Resample Archive
– Hands-on Exercise
– Review
2 9/2/2014
Data Archiving Process Honeywell
• Data enters the RAW Queue and is evaluated for integrity and
compression capability, then it moves into the DATA Queue for online
memory archiving
• At a user-configurable interval, the Continuous Store Process moves the
data from memory into storage within a Logical Archive
• The Continuous Store Process automatically creates a new archive file
when the current active file is full, and deletes the oldest file when the
logical archive has the maximum number of archive files
3 9/2/2014
Data Archiving Process Honeywell
4 9/2/2014
Logical Archives Honeywell
• In R20x or higher, the
PHD system automatically
splits tags into three
different logical archives:
SCAN, CHAR, and MANV
– SCAN – Archive files
connected to the SCAN
logical archive store data
for collected numeric
type tags for a particular
time range
– CHAR – Archive files
connected to the CHAR
for collected character,
binary and Unicode type
tags for a particular time
range
– MANV – Archive files
connected to the MANV
for all Manual Input tags
for a particular time
range
5 9/2/2014
Related Files For Each Archive Honeywell
• Within each logical archive an archive “file” is made up of three related files:
– The .dat and the .idx file must be backed up to offline storage for data recovery
– The .lck file is created every time PHD is started, so this file does not need to be
backed up
– NOTE: In this training environment Archive files are located in the default install
location C:\Program Files\Honeywell\Uniformance\PHDServer\Archive
– Example file set:
• Scan00024.dat
• Scan00024.idx
• Scan00024.lck
File Description
.dat Data file

.idx Index file
.lck Locking file - Used to control which process has exclusive rights to
the data file, as concurrent writes to the file by multiple processes
may result in corrupted data
6 9/2/2014
Multi-File, Multi-Layer Archive Honeywell
• Each Archive File resides in its own layer according to Logical Archive
priorities
• Each archive file
– Holds data at actual scanned frequency for all tags
– Holds data for all tags of the specified type over the time range
• Data requests for a time-range access the data within the higher priority
archives first
7 9/2/2014
Recover Archive Honeywell
• In order for PHD to use data within an archive file, the archive file must
be connected to the system
• An off-line backup of an archive file can be connected as a RECOVER
logical archive file
• Any programs querying data for the appropriate time range automatically
access the recovered archive
– PHDManager> CONNECT SCAN00021 RECOVER
8 9/2/2014
Recover Archive Honeywell
• Connecting as RECOVER prevents PHD from automatically deleting the
archive as the oldest file
Area to retrieve and restore
archives that have been
previously offloaded
9 9/2/2014
Working with PHD Archives Honeywell
• You may use either PHDMAN or USC if you want to work on or review archives:
– Show archive(s)
– Define
• Note: Normally these Logical Archives are setup during installation of PHD
– Connect or disconnect
– Create
• Note: Create a new archive file
– Not normally required since PHD server will automatically roll into a new file
when the current file reaches its maximum size.
– Copy
• Note: Copy a live connected archive to another location for backup purposes
– This command automatically synchronizes itself with the CONSTORE
process
– Waits for a safe opportunity to have PHD close the file so that it can be
copied, and then allow PHD to access the file again
– Move
• Moves a disconnected archive from the standard location to another location for
backup purposes
10 9/2/2014
PHDMAN Commands - Show Archive Honeywell
• PHDManager>SHO ARC
• Displays currently connected archive files
• An asterisk (*) indicates the active archive file for the logical archive
– The constore process inserts any new data into the active archive file
Version:
0 = R150 file
1 = R2xx + file
11 9/2/2014
PHDMAN Commands - Show Archive Honeywell
• PHDManager> SHO LARC
• Displays current logical archive definitions
12 9/2/2014
USC Commands - Show Archive Honeywell
• USC>LocalHost>Uniformance PHD Server>Show Archives
13 9/2/2014
USC Commands - Show Archive Honeywell
• USC>localhost>Uniformance PHD Server>Show Logical Archives
14 9/2/2014
USC Command – Show Data Honeywell
• USC>localhost>Uniformance PHD Server>Show Data
15 9/2/2014
• 1: Click 1
Command
Parameters Link
• 2: Select or enter
Archive Name
• 3: Tag 2
Name/Number
3
required to view
tag archival data
• 4: Click OK to
accept entries
and Show Data in
details pane
4
16 9/2/2014
• Note the
Nominated
Tag and
Archive File
Description of
the Record
List
• Click on a
Record
Number link
for more detail
of this tags
Archives
record
17 9/2/2014
• The link opens
– Note: Archive Record 6 is expanded detailing more tag data
• Click to return to Record List
18 9/2/2014
PHDMAN Defining and Connecting Archives Honeywell
• Archive commands can be
performed manually and are
performed automatically by the
system coldstart procedure
• Defining a logical archive:
– DEFINE LARCHIVE larcname
– SET larcname:parameter value
– CONNECT-larcname ACTIVE
• Defining a physical archive file:
– CREATE ARCHIVE arcname
• Activating the archive file:
– CONNECT arcname larcname
ACTIVE
19 9/2/2014
PHDMAN Defining and Connecting Archives Honeywell
• PARAM:
– NFILE: Number of archive files for the
logical archive
– PRIORITY: Read priority of logical
archive
– RESAMPLEMIN: Resample interval
– OFFSETMIN: Offset for resampling
intervals (minutes)
– COMPRESSTOL: Compression
tolerance multiplier (minutes)
– VSSFILE: Number of file revisions
kept for VSS
– MAX_ARCFILEKB:
• Threshold size of an active
archive file at which it is closed
and a new archive created
• Actual archive file sizes may
exceed this size by a small
margin when they are closed
20 9/2/2014
USC Defining Archives – First Method Honeywell
• Select Set from the
Uniformance PHD Server
Details Pane
21 9/2/2014
USC Defining Archives – First Method Honeywell
• Select Right Click Uniformance
PHD Server>All Tasks> PHD
Commands>Define> Archive
22 9/2/2014
USC Defining Archives –Second Method Honeywell
• In Show Logical Archives
Display in USC:
• 1: Select Define Logical
Archive
• 2: Enter Logical Archive
Name
• 3: Click OK to accept
23 9/2/2014
Configuring Archive Size Honeywell
• The size of the archive file is defined in the
PHDParams.dat file
• MAX_ARCFILEKB specifies the threshold
size of an active archive file at which it is
closed and a new archive created
– Actual archive file sizes may exceed this
size by a small margin when they are closed
• Honeywell recommends a file archive file
size of 600MB to 2GB (61440 – 2097152
KB)
• NOTE:
– MAX_ARCFILEKB can be set for each
logical drive
– If not set then MAX_ARCFILEKB will default MAX_ARCFILEKB *
NFILE =Total
to the global MAX_ARCFILEKB value allocation for each
logical archive
24 9/2/2014
Configuring Archive Size With USC Using Set – First Method Honeywell
• Select Set from the Uniformance
PHD Server Details pane
25 9/2/2014
Configuring Archive Size With USC Using Set – Second Method Honeywell
• Right Click Uniformance PHD
Select Server>All Tasks>
PHD Commands>Set
26 9/2/2014
User Authority Honeywell
• Users can be assigned to a Windows local group
PHD_ARCHIVE_XXXX to give the user the authority to manage a
specific logical archive
– Where xxxx is the logical archive name
• Intended for logical archive groups that ordinary users may need to
manage (other than SCAN, CHAR, and MANV, which should be system
controlled or managed by a knowledgeable PHD administrator)
• Placing a user into group such as PHD_ARCHIVE_SCAN enables that
user to Connect/Disconnect the SCAN archive files from within
PHDMAN
27 9/2/2014
Archive Location Honeywell
• The location of the PHD archives can be changed through a registry key
– Registry Entry:
• HKLM\Software\Honeywell\Uniformance\PHDServer
– The Registry value name is PHD_Archive
– The default value data is:
• [Drive]:\Program
Files\Honeywell\Uniformance\PHDServer\Archive
• Different archive directories can be specified for each logical archive, if
desired, by creating an appropriate registry setting for each logical
archive:
– HKLM\Software\Honeywell\Uniformance\PHDServer\PHD_ARCHIVE_SCAN
• NOTE: If the archive location is changed, you need to ensure the

permissions are set appropriately on the new directory
28 9/2/2014
Hands-on Exercises - PHDMAN and Archives Honeywell
• Exercise 1
– In this exercise, you will examine the archive configuration of the training system
• Using PHDMAN, display the list of archive files currently connected to this system:
– SHO ARC
• What are names of the currently connected archive
files?________________________________________
• What are the names of currently connected logical
archives?_______________________________________
• What is the minimum, maximum, and default archive file size on this system?
– SHO PAR
– MAX_ARCFILEKB min= _______ max=_______ default =_______
• Using the Windows Explorer, determine the current size of the archive files in use at this time:
– phdserver\archive\
– SCAN0000n.DAT current size = _______
– MANV0000n.DAT current size = _______
– CHAR0000n.DAT current size = _______
• Display the list of logical archives defined on this system: SHO LARC (use PHDMAN or USC
Show Logical Archives)
– What is the maximum number of archive files in each logical archive?
– NFILE=___________
29 9/2/2014
Hands-on Exercises – USC and Archives, continued Honeywell
• Exercise 2
– In this exercise, you create new archives
– The commands you manually perform here are the same commands
performed in the PHD coldstart.dat file
– Review slide 16 before you start this exercise
• Ensuring USC is open and that you are at Uniformance PHD
Server>Show Logical Archives
• Define a new logical archive called GUEST
• Use the PHD SET command and enter (Hint: Right Click on
Uniformance PHD Server>All Tasks>PHD Commands>Set)
– Parameter: GUEST:NFILE
– Value: 3
• Select Show Archives
• Click on Create Archive
• Enter Archive Name: GUEST00001
• Enter Max Size: 50KB
30 9/2/2014
Hands-on Exercises – USC and Archives, continued Honeywell
• Exercise 2
– Click on Connect Archive
– Fill out the Connect Archive Dialog box so that it
is the same as the figure on this page:
– Verify the newly created archive file is now
connected review Show Archive details pane
– Navigate to Archives folder and review your new
archive files details are now visible
– In Show Archives details pane Disconnect your
newly created archive file and verify the status
– Delete the archive files for GUEST00001
– Stop Uniformance and Coldstart to Refresh
Global Sections
– NOTE: You may need to Refresh the details
pane to see details as you apply changes
31 9/2/2014
Review: PHDMAN Commands for Managing Archives Honeywell
• What is the PHDMAN command used to do each of the following functions?
– Command used to create archive files
– Command used to configure PHD named parameters that are needed to tailor the
logical archives and CONSTORE process for a particular PHD installation
– Command used to define logical archives
– Command used to make archive files and logical archives available to the PHD
system
– Command used to view archive files and logical archives
32 9/2/2014
Hands-on Exercises - USC Commands for Managing Archives Honeywell
• Exercise 3
– In this exercise, you will see how different types of data are stored in the
different archive files review slide 12 for more details on the actions required
for this activity
• Select an active collected TAG
• Use USC Uniformance PHD Server >Show Data
• Use the Command Parameters to enter Tag Name (for example TAG01)
• Ensure that you are using Archive: SCAN00001
• Drill down through the presentation layers and review the data
• Use a manual input tag (for example TAG.MI.TRAIN.001) and do the
same as the sets above review the data the archive presents ensure you
use the MANV0001 archive
• Try the same thing with an enumerated or character tag (create one if
you can’t find one)
33 9/2/2014
Honeywell
This page intentionally left blank
34 9/2/2014
PHD / SQL Backup Strategies
Database Actions Copyright © 2013 Honeywell International Inc. PHD / SQL Backup Strategies
35 9/2/2014
Lesson Objective Honeywell
• Objective
– Build familiarity with creating a backup copy of the SQL Server 2008
database
• Topics
– Detaching a database
– Copy off detached version
– Re-attaching database
36 9/2/2014
Lesson Topic – Database Management Honeywell
• Occasionally there may be a requirement to move the database to
another drive or directory location
– These steps – while fairly straight forward looking – must be followed closely
• Should any issue be encountered the database is at risk of being lost
• While performing this activity access to the database will be not
available to users or running applications
• For more information on SQL 2008 Server it is recommended that you
complete an official Microsoft Training course like: 6231A Maintaining a
Microsoft SQL Server 2008 Database
37 9/2/2014
• When logged on as a SQL Server Administrator run the Microsoft SQL
Server Management Studio
– Launching Microsoft SQL Server Management Studio
38 9/2/2014
• Connecting to the database host
– Entering the computer name
39 9/2/2014
• Navigating into the databases and right clicking upon PHDCFG select
Tasks, Detach
40 9/2/2014
• Ensure that Drop has been selected
• Click OK
41 9/2/2014
• PHDCFG is now no longer connected to SQL Server
• Now perform the same actions for the PHDAPP instance
42 9/2/2014
• With both instances removed the desired actions should now be
performed
• NOTE:
– However, there may be a requirement to restart the computer before the
system will properly close any and all database connections
– This may be the best approach to perform before doing any of the desired
actions
– The database will remain detached on the restart
43 9/2/2014
• With the database activities completed the next step is to re-attach the
instances
– After running Microsoft’s SQL Server Management Studio again right click
upon Databases and select the attach option
44 9/2/2014
• Click on Add to choose the instances
45 9/2/2014
• Navigate to the location for one of the instances, selecting PHDAPP.mdf
and click on OK
46 9/2/2014
• Similarly, perform previous steps to add PHDCFG instance
• Now with both instances identified click on OK in the Attach Databases
utility
47 9/2/2014
• To help ensure the instances are back in proper operation navigate
down to Security>Logins
48 9/2/2014
• Selecting “Product Administrators” Under “General”
it confirms the
• And look at the Properties for the group “Login Name”
49 9/2/2014
• Under “User Mapping” selecting PHDAPP should display:
50 9/2/2014
• And PHDCFG should display:
51 9/2/2014
• Back under Logins
– Select “Local
Engineers” and
select Properties
– As per the
previous slides,
view the roles
under PHDAPP
52 9/2/2014
• And also for PHDCFG
53 9/2/2014
• Back under Logins
– Select “Local Engineers”
and select Properties
– As per the previous slides,
view the roles under
PHDAPP
54 9/2/2014
• And also for PHDCFG
55 9/2/2014
Lesson Topic – impact of moving the database Honeywell
• CRITICAL INFORMATION:
– Should the database being attached have been moved to a new directory or
copied from another computer there will be a requirement to perform
additional activity
– Scripting will need to be acquired and then run under the SQL Server
Management Studio against the databases that will rebuild the permissions
and access
• This scripting will be available through the support organization.
– When copying the database from another computer the need will be to install
additional components for the database itself
• This can be accomplished by simply running the original database
installation from the distribution media onto the new host computer
• Then follow the steps to detach the PHDAPP and PHDCFG databases,
copy in the database from the old host computer
• The same scripts will need to be acquired and run in the same fashion to
rebuild the permissions and access
56 9/2/2014
Lesson Topic – Impact of moving the database Honeywell
• SQL Server 2008 does have it’s own built in backup functionality that
can be utilized to perform similar activities for moving the database from
one computer location to another location
– The full impact of how this functionality can be utilized has not currently been
followed up on
• Future activities should see this tested and additional documentation put
together to assist in describing any other activities that may need to be
included in that effort
• Similarly – should a requirement exist to move a database from a 32bit
SQL Server on a 32bit operating system to a 64bit SQL Server on a
64bit operating system these activities can also be performed
– Essentially the same type of actions will work as the database itself can be
run under either environment
57 9/2/2014
Presentation Overview Honeywell
• Overview of SQL Server Backup and Restore
58 9/2/2014
Check Database Integrity Honeywell
• It is recommended to check database integrity before performing a
backup
– Execute:
• DBCC CHECKDB
– Executing these command executes the following in the background:
• DBCC CHECKALLOC - Checks the consistency of disk space allocation
structures for a specified database
• DBCC CHECKTABLE – on every table and view in the database. This
Checks the integrity of all the pages and structures that make up the
table or indexed view
• DBCC CHECKCATELOG – Checks for catalog consistency within the
specified database
59 9/2/2014
Database Recovery Model Honeywell
• Full
– Requires log backups
– No work is lost due to a lost or damaged data file
– Can recover to an arbitrary point in time
• Bulk Logged
– Requires log backups
– Similar to Full recovery model, but has better performance for bulk copy
operations
– Reduces log space usage by using minimal logging for most bulk operations
– Can not recover to an arbitrary point in time
• Simple
– No log backups
– Automatically reclaims log space to keep space requirements to a minimum
• Note:
– The Recovery Model is set as a property of the database
60 9/2/2014
Overview of Backup Types Honeywell
• Full
– All data files and part of the transaction log
• Differential
– The parts of the database that have changed since the last full database
backup
• Transaction Log
– Any database changes recorded in the log files
• Trail-Log Backup
– Log backup taken of the tail of the log just before a restore operation
• File
– Specified files or filegroups
• Copy Only
– The database or log (without affecting the backup sequence)
61 9/2/2014
Full Backup Strategy Honeywell
62 9/2/2014
Transaction Log Backup Strategy Honeywell
63 9/2/2014
File Backup Strategy Honeywell
64 9/2/2014
Backup Compression Honeywell
• Backup Compression:
– New in SQL Server 2008
– Compresses backup size on device
– increases I/O performance
– increases CPU usage
• Restrictions:
– Can not co-exist on same media as uncompressed backups
– Can not be read by earlier versions of SQL Server
– Can not share a tape with NT Backups
65 9/2/2014
Ways of restoring a Database Honeywell
• Normally restore a database from any of the backup files generated from
the backup types is described so far
• Alternate methods for restoring a database by not using backup files:
– Detach / Attach actual database files
– Restore from Snapshot
66 9/2/2014
Types of Restores Honeywell
• Complete Database Restore
• File Restore
• Page Restore
• Piecemeal Restore
• Online Restore
67 9/2/2014
Honeywell
This page intentionally left blank
68 9/2/2014

Database Actions

Uploaded by

Copyright:

Available Formats

Database Actions

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Actions

Uploaded by

Copyright:

Available Formats

PHD History Archive Management

.dat Data file

• NOTE: If the archive location is changed, you need to ensure the

This page intentionally left blank

This page intentionally left blank

You might also like