College Papers

1 Oracle database to SQL SERVER 2012 database

1    
Introduction

1.1  Purpose

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

The Northwind Company has made the decision
to migrate their existing database system. As the contracted system provider
has decided not to renew their contract with the Northwind Company for the
existing infrastructure. The contracted system provider will cut off all the
extended support for the Microsoft Server and the Oracle database

 

1.2 System Overview

  

Company’s existing database is Oracle 9e
and maintained on a single Microsoft Server 2003. Company decided to migrate
the Oracle database to SQL SERVER 2012 database management system. Microsoft
SQL SERVER 2012 DBMS should be hosted on a Microsoft server 2008.

 

1.2.1 System Description

 

The company data
is used by internal users from home office or remote region office. All
regional users   can access the database remotely using virtual
private network (VPN) connection. The
system is to also provide limited access to reports for customers, suppliers
and shippers. The reports and queries generated from the system using
various applications like Visual Basic .NET, Java, Crystal Reports, C++ and
C#.  Regional and Home office employees
will be able to modify the data.  Northwind
Company has the condition that all the database tables, relationships will
remain in the current configuration.

 

1.2.2 Assumptions and Constraints

 

Schedule:

·       
Make the plan and diagram
the work flow process.

·       
The current provider will
cut off all support for the system with Northwind at the end of next six
months.

 

Resource availability and skill
sets

 

·       
Pool the team with persons
having required technical skills for migration and testing

·       
Create and distribute the
contact list for easy interactions between the team at any time.

·       
Establish the work schedule so
that the DBA’s are available round the clock.

·       
Create and distribute the
contact list for easy interactions between the team at any time.

·       
Assign the responsibilities
among the pooled team members, establish their roles and their
responsibilities.

 

Software:

·       
Software license will be
purchased by Northwind Company.

·       
Since the migrated system
will allow regional employees to access the system through web so a web server
is required for the web portal.

·       
The migrated system will
be host on a Microsoft server 2008 server

 

Constraints:

·       
Time line is limited to
six months.

·       
In new system there will
be limit access to the users based on their roles and access requirements.

·       
Current database tables
and their relationships will remain in the current design and format.

·       
All the current reporting
and applications which is written in Visual Basic .NET, Java, Crystal Reports,
C++ and C# will be migrated.

·       
All the regional employers
can be access the database through web.

·       
With the migrated system
customers, shippers and suppliers can have limited access with the custom
reporting using web portal.

·       
Initial support of the
system is maintained for 200 users.

·       
Migrated system must be
scalable to support up to 2,000 users of all types.

Budget 

 

There are various components that have been budgeted
in the preparation of the migration plan.

·       
Cost  to maintain 
Microsoft SQL Server  2008

·       
Licensed software
Microsoft SQL 2012

·       
Cost to establish web
portal for web access.

·       
Cost to pay for the
DBA’s  and team members

·       
Hardware components needed to run the software applications like
routers, switches etc.,
bandwidth costs

The approximate budget for purchasing the resources
and completion of the migration process can be  $1, 00,000

 

1.2.3 System Organization

 

Both Home Office users and Regional users use the
Northwind Company migrated database. Regional users use the database using VPN
connection. And the database is host on Microsoft SQL server 2008. Other users
like customers cannot use the database directly but they can access limited and
custom reports using web portal.

 

 

 

 

 

Hardware
Major Components:

 

Switches,
Routers, Network cable

Intel
(x8) processor, 8GB RAM depends upon the application needs.

 

1.3 Glossary

 

SQL:
Structured Query Language

MSSQL
2012   : Microsoft SQL Server 2012
enterprise edition

 

2 Management Overview

 

Implementation of the database migration process
will involve different people each having their own role to make success the
project. DBA plays a crucial role in database migration plan and responsible
for the success of the migration. DBA is responsible for all the technical
aspects of the migration like

·       
Assigning well defined
roles to each person involved in the migration project

·       
Scheduling the test and
production migrations

·       
Performing test migrations

·       
Performing backups of the
new database

So the DBA should be knowledgeable (professional) in
both Oracle and MSSQL. The overall project is controlled by the Project
manager. The application developer has make sure the applications designed for
the  current database works correctly
with the new database once the migration is complete.

 

2.1 Description of Implementation

 

This Project will follow the parallel phased
approach. Since parallel phase implementation is changeover process that takes
place in stages. Even if the phases of the new system are implemented the old
system will remain until unless the new system satisfies all the system
requirements by the company. This section provides description of the
steps/phases involved in implementation of the project.

 

Phase 1:  Implementation Plan

 

Northwind Company has decided to migrate the
database from Oracle to MSSQL 2012 to implement this first we need is the
Implementation plan.

 

 

Phase 2: Installing the software /
hardware

 

Next step in database migration process after the
implementation plan is to install all the required software / hardware needed
to the project.

 

Phase 3:  Loading the data

Now
Northwind Company’s data migration team will migrate all the data from the
oracle database and load into the MSSQL 2012.

 

Phase 4:  Testing

 

Now
the new system will be testing in order to check the integrity and Quality
assurance.

Data validation should be done to make sure
that whether the data is migrated correctly as per the requirement from the
company.

 

Phase 5: Training

Users
should be provided with appropriate training to use the new system and
performance of the user access controls are tested.

 

Phase 6: Go live

 

This
is the final phase of the process where the new system is fully implemented and
ready to operate.

 

 

 

 

 

2.2 Points-of-Contact

 

 

Role

Name

Contact Number

Business Sponsor

 

 

Project/Program Manager

 

 

Government Project Officer

 

 

System Developer or System Maintainer

 

 

Quality Assurance Manager

 

 

Configuration Management Manager

 

 

Security Officer

 

 

Database Administrator

 

 

Site Implementation Representative

 

 

IV&V Representative

 

 

 

Table
2.2 – Points-of-Contact

 

2.3 Major Tasks

 

Below
are the major tasks in different phases of a database migration

 

Implementation plan:

1.     
Gathering all the information required for database
migration.

2.     
Analysis of the source content, metadata

3.     
Defining the migration strategy and selecting appropriate
tools

4.     
Migration Activities

Installing:

Installing
all the required software /hardware to migrate the data

1.     
Installing MSSQL server 2012 enterprise edition

2.     
Server installation MSSQL server 2008 

3.     
Installing security software(like Norton anti-virus)

4.     
Installing VPN for regional employees

5.     
Setup all the hardware resources required.

6.     
Web portal for custom reports.

Loading the data

Some
of the major tasks during loading the data

1.     
Read the data from the legacy data store

2.     
Reformat the data and merge from multiple sources

3.     
Write the data to the target database

4.     
Manage data errors

5.     
Provide reports on execution

6.     
Synchronize in the event that the source data must be used
during the migration, changes in the source can be updated with the target.

Testing

 Major tasks while testing the migrated system.

1.     
Testing environment setup

2.     
Functionality of the database

3.     
Performance

4.     
Data validation

5.     
Security does the logins and users have been correctly
created?

6.     
VPN database access

7.     
Web portal access

8.     
Backup and Recovery

Training

Major
tasks in Training phase

1.     
Set organizational Training objectives

2.     
Provide appropriate training to the users.

3.     
Trained the employers

Go live

1.     
After the successful completion of all the phases the
migrated database is ready to use and any further enhancements were made based
on the changed in the company requirements.

2.   
Providing in person support for the users.

3.     
Analyze results

4.     
Switch off the old

2.4 Implementation Schedule

 

The
Implementation schedule is a chart that lists all the necessary tasks to
complete the project along with the deadlines to complete the tasks. Below
table is the implementation schedule for the major tasks in the project.

 

 

Major
tasks

Start
date

Finish
date

1

Implementation
Plan

 

 

1.1

Gathering information

 

 

1.2

Analysis source

 

 

1.3

Migration strategy

 

 

1.4

Activities

 

 

2

Installing                   

 

 

2.1

Setup all the hardware resources

 

 

2.2

MSSQL server installation 2008

 

 

2.3

MSSQL 2012 enterprise edition installation

 

 

2.4

Installing security(like Norton anti -virus)

 

 

2.5

Installing VPN

 

 

2.6

Webportal

 

 

3

Loading
the data

 

 

3.1

Read the data from data store

 

 

3.2

Merge the data from multiple sources

 

 

3.3

Write the data to target database

 

 

3.4

Manage data errors

 

 

3.5

Provide reports on execution

 

 

3.6

Synchronization

 

 

4

Testing

 

 

4.1

Testing Environment setup

 

 

4.2

Functionality of the database

 

 

4.3

Performance

 

 

4.4

Data validation

 

 

4.5

VPN data access

 

 

4.6

Checking the logins and users

 

 

4.7

Web portal access

 

 

4.8

Backup and Recovery

 

 

5

Training                     

 

 

5.1

Set
Organizational Training objectives

 

 

5.2

Trained
employers

 

 

5.3

Provide training to users

 

 

6

Go live                       

 

 

 

 

 

 

 

 

 

 

 

 

2.5 Security and Privacy

 

Security
is one of the most important step in the entire project. The DBA group applies
and administers database security. Data security is like a layered cake. i.e.;
there is a security at every layer. Norton antivirus security is used for the
security of the server. To use the database there should be a secured windows
authentication or database connections. Regional employees use VPN connection
to access the database so there should be a secured connection in order to
connect to database i.e.; logins and roles. From all these the security of this
project is in 3 layer, 1. Server 2. Database connections 3. Web access

 

 

 

2.5.1 System Security
Features

 

At
the system security features the Norton anti-virus firewalls and safety
standards are imposed by security administrator on the server system.

For
database layer there should be security for the different roles and users
authentication of the database connections. At web access level for there
should be a security for web portal authentications for custom reports and VPN
authentication to access the database securely.

 

 

2.5.2 Security Set Up During
Implementation

1.     
Granting necessary privileges to the users or roles.

2.     
Granting specific permission on database to the different
users. There will be a different permission for the different users/employees
of the database like system admins should have permission to access all the
objects like tables, etc. in the database.

3.     
Creating proper credentials (login and password) to the web
users and VPN users.

 

 

 

3 Implementation Support

3.1 Hardware,
Software, Facilities, and Materials

 

Before begin the database migration
project the hardware, software, facilities requirements must be clearly
identified for the migrated system.

 

3.1.1
Hardware

1.     
Connecting devices :Routers, Switches, Wi-Fi routers

2.     
Storage devices: hard drives, discs space for installing
Microsoft SQL server

3.     
Database server

4.     
Web server

5.     
Processor: Intel (x86), CPUs

 

3.1.2 Software

1.     
Microsoft windows server 2008

2.     
Microsoft SQL server 2012 enterprise edition

3.     
VPN software

4.     
Norton Security Software

5.     
SQL server Data tools like SSSM

 

3.1.3 Facilities

 

A server room for maintain the server
Data center for disaster recovery
A class room for providing the
training on new database to employees.

 

3.2
Documentation

Documentation
is used to solve the problems while using the new system. It demonstrate how to
perform tasks involved in different phases of the migration process. Different
types of documentation need for this project are

1.     
Server Documentation

2.     
Database(Microsoft SQL server 2012 Enterprise Edition)
Documentation

3.     
VPN software Documentation

4.     
Web access Documentation

5.     
Security Documentation

6.     
Appropriate training documentation

 

3.3 Personnel

A team
with different roles like manager, developer, tester etc. are required to
implement the database migration project.

 

3.3.1 Staffing Requirements

1.      Project sponsor: Project sponsor has the responsibility with overall accountability of
the project.

2.      Project Manager:  Project manager has overall
responsibility for the successful initiation, planning, design, execution,
monitoring, controlling and closure of a project.  Project Manager take the sponsors decisions
on the scope, schedule and budget and delivers what they required.

3.      Database Administrator: DBA is responsible for ensuring the success of the migration process.
Responsible for data backup and data restore.

4.      Web Developer: Responsible to create user interfaces and integrating data from
database. Maintain the web portal.

5.      System Maintainer: Responsible for maintaining all the systems by monitoring and
correcting software detects involved in data migration process.

6.      Quality Assurance : Responsible for ensuring a project meets  reliability, usability, integrity , security
and performance

7.      Trainer:
Responsible to provide training about migrated system to the employees and the
end-users.

 

3.3.2 Training of
Implementation Staff

 

Different types of training should be given for
different employees in the team. Below are the 4 course programs that should be
given to the team members to complete the migration project

 

1.   
Overview Session: The team members are trained on
the general theories related to the migrating system

2.   
Navigate session: The team members should be
trained to hands-on exercise the new software like how to manipulate the
fields, sessions etc.

3.   
Functional Training: Each team is formed with
different departments. Each department should be trained on the specific
modules that relate to their department

4.   
Administrative Training: The team members should be
trained on software administration like upgrades, security access

 

3.4 Outstanding Issues

This subsection of the Project
Implementation Plan states any known issues or problems relevant to
implementation planning. This section answers the question, “Are there any
specific issues, restrictions, or limitations that must be considered as a part
of the deployment?”

 

If issues are site-specific, provide this information in Section 4,
Implementation Requirements by Site.

3.5 Implementation
Impact

 

With the new system implementation new server MSSQL windows server 2008
is maintained. The Regional users connect through a VPN. Internal users will
connect the database through windows authentication. Expected to have higher
storage for backup resources. New system ensures the scalability to accept up
to 2,000 users of all types. In case of system failure or crashes or problems
in server there is less time to implement new server or restore the backup
database file.

 

3.6 Performance
Monitoring

 

Performance
monitoring is an important tool for ensuring that implementation and migration
of the project is successful. There are many tools that monitor the Performance
of the project for SQL server like SSMS, SQL Trace etc. SQL server performance
monitoring involves Disk Activity, Process Utilization, Memory, Monitor SQL
server.

 

Disk Activity: It deals
with Physical Disk. The number of I/O operations waiting, % age of Disk time
i.e.; which indicates so many system requests are waiting for disk access.

 

Process Utilization: It
shows performance of processer like % Processor time, % Privileged time (Time
spent on SQL Server I/O requests), % user time (CPU spends on user processes),
Queue Length (No of threads waiting for processor time)

 

Memory: How much memory is
available for new processes, Pages/sec (how many times the virtual memory is
accessed?)

 

Monitor SQL server: It
involves Access methods, Buffer Manager, Memory Manager, Locks.

Access methods: It
measures the allocation of SQL server database Objects.

 

Buffer Manager: It says
how SQL server used the memory to store data pages and the monitor the physical
I/O as SQL server reads and writes.

 

Memory Manager: It
provides the estimate of how much memory SQL server is using.

 

Locks- Average Wait time:
Shows the average time needed to lock.

 

3.7 Configuration
Management Interface

This subsection of the Project Implementation Plan describes
Configuration Management, such as when versions will be distributed. Reference
the Configuration Management Plan.

 

4 Implementation Requirements by Site

 

Here it describes
all the implementation requirements which are used in successful migration of
Northwind Company. These are the requirements which are used at Northwind
Company Main Office.

 

4.1 Site
Name or Identification for Site X(Northwind Company Main Office)

4.1.1 Site Requirements

 

Below are the
hardware/software/database requirements which are needed at Northwind Database
Company to complete the migration project successfully.

 

Hardware Requirements:

 

Routers

Switches

LAN connection

Database server

Web server

 

Software Requirements:

 

Microsoft SQL
windows server 2008

Microsoft SQL
server Enterprise Edition 2012

CISCO VPN software

Norton Anti-virus
software

 

 

Database Requirements:

 

Northwind Company
database.