Database Implementation Plan for Riordan Manufacturing
Database Implementation Plan for Riordan Manufacturing Riordan Manufacturing has asked our firm, LTB and Associates, to develop a web-based plan to be used as a Business-to-Business web site in order to purchase materials from their vendors. We have examined Riordan’s products and business systems, interviewed Riordan’s employees, and have determined the best course of action to implement the plan. Riordan Manufacturing is a plastics manufacturer based in several locations across the world.
With three plants based in the continental US, which are located in Albany, Georgia; Pontiac Michigan; and in San Jose, California, which is the company headquarters. A fourth plant is located in Hangzhou, China will not be part of the plan as they purchase materials locally. This where we explain how the database will be set up. Refer to the ERD, Sequence diagram, Use Case diagram, and Class Diagram that follow [pic] [pic] The following paragraphs will describe the considerations that Riordan Manufacturing will need to make in implementing the database plan.
Where ever it is possible to for our firm to do so, LTB and Associates will make a recommendation on the choice to make. In order to implement the database properly, these recommendations should be implemented in order to offer the tightest amount of security that will be possible. Type of Online Processing Riordan Manufacturing will need to decide between real-time asynchronous processing and batch processing. Real-time asynchronous processing is applicable when the processing must be performed immediately or when the results must eventually be communicated back to an on-line user.
One of the problems with real-time processing is that real-time timers and events are required; they are expensive as the computer resources that are used to raise, maintain and check events. Another problem with timer or even processes is that they create locking problems as they may contend with on-line processes for resources and for access the same data. Batch solutions are ideal for processing that is not real-time event based. Batch processes are data-centric and can efficiently process large volumes of data off-line without affecting the company’s on-line system.
Batch processing will allow business function to execute on a periodic or recurring basis; or it can operate over data that matches a given criteria. After studying Riordan Manufacturing’s Inventory and Supply Chain processes, the recommendation is for the company to go with batch processing for replenishment of raw materials for the operations. With daily reporting of the materials used at the end of each manufacturing run as the current method of inventory control, batch processing will work well. While real-time processing works well for online shopping, it is not the best approach to take for this application.
Access Privileges Normally it is not considered a good security risk to grant access to individuals/companies who are located outside of the normal company network and/or firewall. But in order to provide a better integration of the database between Riordan Manufacturing and vendors/suppliers it is necessary to allow limited access. Riordan shall determine the types of privileges to grant to vendors. These privileges include (a) view, (b) insert, (c) update, and (d) delete (logical deletion only). Riordan shall also determine the type of data vendor should be able to access.
The data should only relate to the materials that have been used in the daily manufacturing run; materials that have been previously placed on order; and the estimated projections of materials needed for the upcoming week. This will allow both Riordan Manufacturing and the suppliers to determine the present and short-term inventory needs. Bandwidth High speed access of the Internet has decreased in cost in recent years and the increased utility and the increased ease of use versus the cost of acquiring and maintaining the access has become more financially feasible.
LTB and Associates recommend that Riordan acquire the appropriate bandwidth that will allow for the effective flow of information and which will support concurrent access of the company database. Security With the ever-growing threats that are present in the web-based global economy, there is an increased awareness of security. This includes both corporate and political terrorism as well as the knowledgeable lone user bent on mischief. LTB and Associates recommend that the administration plan should ensure that there are methods of data security implemented as information travels over the internet.
An effective firewall should be implemented to deny access to unauthorized people. Data should be encrypted. Whenever possible, a Virtual Private Network be established between Riordan Manufacturing and the larger and more critical suppliers Database Administration Plan To ensure the productivity, continuity, and performance of the database for Riordan, proper maintenance, database backup, and change management procedures must be developed and implemented. The database must be backed up regularly in case the system needs to be restored due to data corruption or the loss of the data center due to natural or man-made disasters.
Database backups are also critical for testing upgrades, fixes, and enhancements that will need to be migrated into production. Periodic maintenance is also a key to database performance and continuity. Applying operating system patches and security updates to the servers that house the databases help keep the software updated and the servers performing efficiently. Consistent re-indexing of the database can help keep performance high as the database grows in size. Another very important part of administering a database is change management.
Database upgrades, fixes, and enhancements must be tested and implemented in a manner that minimizes potential errors, disruption to users, and preserves an audit trail for troubleshooting and compliance issues. Database Backup Plan In order to minimize potential data loss and downtime, the database will be backed up each night and stored in a secure location on the network. Storing the backups on the network will help facilitate transferring the backup to the disaster recovery site. Once the backup is created and stored on the network, a copy will be encrypted and transmitted to the disaster recovery ite. The backup can then be easily restored at the disaster recovery site as needed, or used in a development environment for testing upgrades, fixes, and enhancements. Database administrators will also have the ability to create backups as needed for testing or emergency purposes. Database backups will be restored to the disaster recovery database at least bi-monthly to verify that the backups are generating correctly and that the disaster recovery-restore process is working as it should. Maintenance Plan Once a month, the database servers will undergo scheduled maintenance.
Operating system patches, security updates, and database application patches will be applied to the hardware housing the database in order to ensure the system is kept up to date. Database hardware will also undergo diagnostic checks to ensure everything is working properly. At this time the database will also be re-indexed to preserve performance as the database grows. The patches, security updates, and database application patches will be applied to the development servers one week prior to implementation on the production server.
Applying the maintenance related patches to the development system a week prior to implementation in the production system will allow time to monitor the development systems for any issues resulting from the patches and ensure the patches are safe to install into production. For emergency maintenance issues such as hardware failures or power outages, the disaster recovery database will be restored from the latest production backup and work should be closely coordinated with the server team to ensure proper procedures are followed. Change Management The backbone to any administration plan is change management.
Maintenance, database backups, upgrades, and other events must be carefully tested, planned, and executed to minimize potential consequences including data corruption and downtime, and changes must be closely tracked to preserve the integrity of the database, maintain a log of changes for troubleshooting potential issues, and ensure proper testing and compliance controls are met. The database team will use Microsoft’s Visual Source Safe (VSS) as the main tool for version control. Patches, fix scripts, and any other code that applies to the databases will be stored in VSS and roper code check in and check out procedures will be followed to ensure the proper code changes are migrated to the database. Only database administrators will have the ability to migrate changes to the databases. Developers can check out and modify code, but cannot implement any changes in the databases. All changes will be properly tested in a development database and signed off on by the appropriate parties. A network of IT and business “approvers” will be established for each business area (for example: accounting, procurement, sales, etc. ) and will be responsible for signing off on changes that affect their particular areas.
There will be at least one main approver and one backup approver that are subject matter experts for their respective business areas so that all areas utilizing the database will have a representative. Approvers will be the preferred testers, but other users may test changes as long as the approvers review the test results prior to signing off on the test. Signoff for changes will be obtained via email using a template that details the change to be made, a tracking number for the change, and the location in VSS where the change is stored, so that the database administrators will be able to pull the change directly from VSS for implementation.
Database administrators will not proceed with migration of a change until all appropriate approvers have responded in the affirmative. Disaster Recovery Plan (DRP) While preparing a disaster recovery plan (DRP), it is essential to think about the hardware, software and data that will be used to allow for an organization to continue operations in the event of a natural disaster or a disaster caused by human hands. In considering the idea of protection of the database, this plan will not only help preserve the company’s investment but also secure the customers faith in Riordan.
Some companies spend some 25% of their information technology budget on disaster recovery plans. LTB and Associates recommends the implementation of a DRP. The cost of implantation and the subsequent maintenance versus the cost of a data loss is negligible in the impact it would have. The first step in drafting a disaster recovery plan is conducting a thorough risk analysis of the database systems. Make a list all the possible threats to system operation and evaluate the likelihood of their occurrence.
The threats can include electronic-based; such as virus attacks or data deletions, deliberate or accidental; and natural disasters, such as fire, flood, storm or earthquake. Once the threats have been identified and ranked in likelihood of occurrence; what can be done to minimize or limit the impact from these disasters. The more preventative actions we can establish in the beginning the better. It is better to take a proactive rather than a reactive approach in determining and documenting the DRP.
The recovery procedure will be written in a detailed plan defining the roles and responsibilities of the IT staff. Defining how to compensate for the loss of various aspects of the network (databases, servers, communications links, etc. ) and specify who will arrange for these repairs and how the data recovery process will occur. Communication Plan During a disaster situation, all parts of the communication plan should be cleared with a central location. All information should be reviewed by management before being released.
LTB and Associates recommend that the following steps be implemented. 1. Communication methods need to be established in advance. (Wireless or satellite telephone systems, email address, etc…) 2. Develop templates for press releases, and include the skill sets of key employees, customer information, supplier information. Include maps of locations that may be difficult to communicate in. Develop logs that can be used to track incoming and outgoing communications. 3. Make sure that these processes are easy to understand.
4. Develop a contingency plan should the computer network where data is stored is damaged. Use text files and several copies in different storage devices and store this information in a remote server or on removable media which is then stored offsite. 5. Determine what is to be considered as privileged/proprietary information; establish which levels of management and the key personnel to have access to the information; and the approvals for accessing the information 6. Develop a set of detailed documents and instructions that can be shared with employees. . Establish a set of technology based tools. 8. Develop a detailed training plan; be sure to include all key personnel, top management and support personnel that can interface with customer and/or suppliers. 9. Develop relationships with News Service agencies. 10. Establish and train employees as observers to watch for individuals with microphones and notepads; make sure they are aware of the proper channels of communications. 11. Do plan periodic mock scenarios and drills of disasters in order to review the DRP.
LTB and Associates recommends that the follow items be purchased, maintained, implemented, and stringently adhered to at every Riordan Manufacturing plant in the continental USA. • Prevention Plan • An electronic manual. • A printed hardcopy manual. • Data and Software Backups • Daily backups, local to each plant and stored offsite. • Weekly backup, to be performed system-wide by the Atlanta, Georgia plant and stored offsite. • Storage area networks. • Surge Protectors and Power Supply Fault Switches. • Anti Virus, Firewall and Security Software. • Fire Prevention and Detection Smoke detectors and fire alarm systems. • Fire extinguishers. • Review and update insurance policies for all facilities and equipment. Disaster Recovery Process. In the event of a disaster occurring at any Riordan Manufacturing plant the following steps should be taken to restore the operation of the system to normal operation. The steps can apply to any and all sites. 1. Contact the proper level of management and notify pertinent personnel a. Determine if the equipment is functional and has suffered no damage. b. Repair or replace the equipment as needed per the situation.
2. Retrieve the latest weekly backup from the Atlanta, Georgia server and/or facility. (Note – if the Atlanta plant is the affected plant, retrieve the last weekly back up from the offsite storage facility) 3. Restore the database to the server. Perform a system check for proper operation. 4. Retrieve the latest daily backup from the offsite storage facility. 5. Restore the database to the server. Perform a system check for proper operation. 6. Retrieve and key in any pertinent data that was recorded on hardcopy sources from the affected plant and double check accuracy with personnel if possible.
