RQF LEVEL 5

TRADE:SOFTWARAE
DEVELOPMENT

MODULE CODE: SFDSS501

TEACHER’S GUIDE
Module name: Database server setup
 Table of content

Table of content..........................................................................................................................2
Acronyms...................................................................................................................................4
Introduction................................................................................................................................5
Learning Unit 1: Describe database server concepts and standards..........................................2
Learning outcome 1.1 Define database server terms.............................................................3
................................................................................................................................................3
Indicative content 1.1.1: Define database server terms......................................................3
Learning outcome 1.2 Describe database server architecture................................................8
Indicative content 1.2.1: Describe database server architecture........................................8
Learning outcome 1.3 Identify database server properties..................................................12
Indicative content 1.3.1 : Identify database server properties..........................................13
Learning Unit 2: Analyze database server requirements.........................................................16
Learning outcome 2.1 Explore organizational database......................................................17
Indicative content 2.1.1: Explore organizational database...............................................18
Learning outcome 2.2 Acquire database infrastructures......................................................21
Indicative content 2.2.1: Acquire database infrastructures..............................................22
Learning outcome 2.3 Select database server in line with customer needs.........................25
Indicative content 2.3.1: Select database server in line with customer needs..........26
Learning Unit 3: Configure a database server..........................................................................28
Learning outcome 3.1 Install a database server environment..............................................28
Indicative content 3.1.1: Install a database server environment......................................29
Learning outcome 3.2: Backup and restore a database depending on business processes. .58
Indicative content 3.2.1: Backup and restore a database depending on business
processes..........................................................................................................................59
Learning outcome 3.3: Manage a database storage.............................................................62
Indicative content 3.3.1: Manage a database storage.............................................63
Learning outcome 3.4: Upgrade and migrate a database server in accordance with business
rules......................................................................................................................................66
Indicative content 3.4.1: Upgrade and migrate a database server in accordance
with business rules...........................................................................................................67
Learning Unit 4: Perform testing and documentation of work done.......................................78
Learning outcome 4.1 Validate and test a database server..................................................79
Indicative content 4.1.1: Validate and test a database server...........................................80

2
 Learning outcome 4.2: Document database server installation process review...................84
Indicative content 4.2.1: Document database server installation process review............85
Learning outcome 4.3: Report procedures of the tasks accomplished in accordance with the
installation to be done..........................................................................................................91
Indicative content 4.3.1: Report procedures of the tasks accomplished in accordance
with the installation to be done........................................................................................91
References:...............................................................................................................................96

3
 Acronyms

 AMD: Advanced Micro Devices, Inc.

 API: Application Programming Interface
 BAK: Backup
 CD-ROM: Compact Disc-Read Only Memory
 CRUD: Create Read Update Delete
 DB: Database
 DBMS: Database Management System
 DDR4: Double Data Rate 4
 DMA: Data Migration Assistant
 DQS: Data Quality Services
 GB: Gigabyte
 Gbps: Gigabits per second
 HDD: Hard Disk Drive
 HTTP: HyperText Transfer Protocol
 LAN: Local Area Network
 Mbps: Megabits per second
 MDS: Master Data Services
 NAS: Network-attached storage
 NIC: Network Interface Card
 NoSQL: Not only SQL
 RAID: Redundant Array of Inexpensive/Independent Disks
 RAM: Random Access Memory
 RDBMS: Relational database management system
 RTDM: Run Time Database Manager
 SAN: Storage Area Network
 SAS: Serial-Attached SCSI
 SATA: Serial Advanced Technology Attachment
 SCSI: Small Computer System Interface
 SNMP: Simple Network Management Protocol
 SQL: Structured Query Language
 SSD: Solid-State Drive
 SSMS:SQL Server Management Studio
 SSMT:SQL Server Management Tools
 SSRS:SQL Server Reporting Services
 SSRS:SQL Server Reporting Services
 TB: Terabyte
 TCP/IP: Transmission Control Protocol/Internet Protocol
 USB: Universal Serial Bus
 VPN: Virtual Private Network
 Wifi: Wireless fidelity

4
 Introduction

This module describes the skills, knowledge, and attitudes required to setup a database server,
backup and restore a database, and migrate a database from one database server to another
according to user needs.

5
 Module Code and Title: SFDSS501 –DATABASE SERVER SETUP

Learning Units:

1.Describe database server concepts and standards

2.Analyze database server requirements
3.Configure a database server
4.Perform testing and documentation of work done
3
 Learning Unit 1: Describe database server concepts and standards

https://illustoon.com/?dl=7826

STRUCTURE OF LEARNING UNIT

Learning outcomes:
1.1 Define database server terms
1.2 Describe database server architecture
1.3 Identify database server properties

2
 Learning outcome 1.1 Define database server terms

Duration: 5 hrs

Learning outline 1 objectives:

By the end of the learning outline, the trainees will be able to:
1. define correctly the database server.
2. differentiate correctly three types of application tiers.
3. Outline three examples of types of database servers

Resources
Equipment Tools Materials
 Computers  DBMS  Internet
 Projector  Electricity

Advance preparation:
. knowing the definition of server, DBMS

Indicative content 1.1.1: Define database server terms

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Definitions of database servers

3
 Database server

A database server is a computer system that is dedicated to running database management

software and serving data to other computers or applications over a network. It is designed
to handle the storage, retrieval, and organization of large amounts of data and to provide
controlled access to that data.

A database server typically runs a database management system (DBMS) that manages the
database and provides tools for users to access and manipulate data.

Application tiers

Application tiers refer to the logical layers or components that make up a multi-tiered
software application. A multi-tiered application is an application that is divided into two or
more logical layers, each of which provides a specific set of functions and services. These
tiers are typically designed to work together to form a complete application.

The most common application tier model consists of three tiers: presentation, application,
and data.

 The presentation tier, also known as the client tier, is responsible for presenting
the user interface to the user. This tier typically consists of a web browser,
mobile device, or desktop application that communicates with the application
tier to retrieve and display data.

 The application tier, also known as the middle tier or logic tier, is responsible
for processing and managing the application's business logic. This tier typically
consists of one or more servers that receive requests from the presentation tier
and communicate with the data tier to retrieve and store data.

 The data tier, also known as the database tier, is responsible for storing and
retrieving data. This tier typically consists of a database server or servers that
store the application's data and provide access to it through the application tier.

Types of database servers

There are several types of database servers available, and they can be classified based on
various criteria, such as data model, functionality, and deployment method. Here are some
common types of database servers:

 Relational Database Server: This is the most common type of database server,
which uses the relational model to store and manage data. Examples of
relational database servers include Oracle, MySQL, SQL Server, and
PostgreSQL.

 NoSQL Database Server: These database servers use non-relational data models
to store and manage data. They are typically used for handling large amounts of
unstructured or semi-structured data. Examples of NoSQL database servers
include MongoDB, Cassandra, and Couchbase.

4
  Cloud Database Server: These database servers are deployed in the cloud and
are managed by cloud service providers such as Amazon Web Services,
Microsoft Azure, and Google Cloud Platform. They offer high scalability,
availability, and flexibility to handle the increasing demands of modern
applications.

 Object-Oriented Database Server: This type of database server stores and

manages data as objects, rather than tables, which allows for better integration
with object-oriented programming languages. Examples of object-oriented
database servers include ObjectDB and Versant.

 In-memory Database Server: These database servers store and manage data in
memory, rather than on disk, for faster access and processing. Examples of in-
memory database servers include SAP HANA, Oracle TimesTen, and VoltDB.

 Graph Database Server: These database servers use graph-based data models to
represent and manage data, making them suitable for handling complex
relationships and networks. Examples of graph database servers include Neo4j,
ArangoDB, and OrientDB.

 Database server responsibilities

Client request handling
Client request handling refers to the process of managing and responding to requests made
by client applications that are attempting to access or manipulate data stored in the
database.

The client application sends a request to the database server, typically in the form of a
query or command written in SQL or another database language. The database server then
performs a series of steps to handle the request, including:

1. Parsing the request: The database server reads the request and parses it to determine
the intent of the request and the necessary resources needed to fulfil it.

2. Query optimization: The database server optimizes the query by determining the
most efficient way to retrieve the requested data. This may involve choosing the
best indexes to use or optimizing the query execution plan.

3. Executing the request: The database server executes the query or command,
retrieves the requested data, and performs any necessary operations on it.

4. Generating a response: The database server generates a response to the request,

which may include the requested data or confirmation that the requested operation
was successful.

5. Sending the response: The database server sends the response back to the client
application, which can then use the data or information provided by the response as
needed.

Database interaction

5
Data interaction refers to the process of accessing, modifying, and manipulating data stored
in a database. This interaction can occur through various means, including client
applications, queries, and commands.

Data interaction in a database server typically involves the following steps:

1. Establishing a connection: A client application establishes a connection to the

database server to access or manipulate data.

2. Submitting a query or command: The client application sends a query or command

to the database server, which instructs the server to perform a certain action, such as
retrieving data, updating data, or deleting data.

3. Processing the request: The database server processes the request, which may
involve retrieving data from one or more tables, performing calculations or
transformations on the data, or updating the data based on the command.

4. Generating a response: The database server generates a response to the request,

which typically includes the requested data or confirmation that the requested
operation was successful.

5. Sending the response: The database server sends the response back to the client
application, which can then use the data or information provided by the response as
needed.

Client responses

Client response refers to the data or information that is returned to a client application after
it submits a query or command to the database server.

The client response typically includes information about the success or failure of the
request, as well as any data or results that were generated as a result of the query or
command.

Theoretical learning Activity

 Brainstorm about database servers
 Group discussion about applications of a database server

Points to Remember (Take home message)

 Database server
 Presentation tier

6
  Application tier
 Data tier
 Relational Database Server
 NoSQL Database Server
 Cloud Database Server
 Object-Oriented Database Server
 In-memory Database Server
 Graph Database Server
 Data interaction

Learning outcome 1 formative assessment

Written assessment
Q1. Define the following terms.
1) Application tiers
2) Data interaction
Answer:
1) Application tiers refer to the logical layers or components that make up a multi-tiered
software application.
2) Data interaction refers to the process of accessing, modifying, and manipulating data
stored in a database.
Q2. Answer with True or False
1) A database server is a computer system that is dedicated to running database
management software and serving data to other computers or applications over a
network.
2) Example of relational database server is SQL Server.
3) The presentation tier is also known as the application tier
Answer:
1) A database server is a computer system that is dedicated to running database
management software and serving data to other computers or applications over a
network. True
2) Example of relational database server is SQL Server. True
3) The presentation tier is also known as the application tier. False
Q3. Outline three (3) examples of types of database servers.
Answer:
 Relational Database Server
 NoSQL Database Server
 Cloud Database Server
 Object-Oriented Database Server

7
  In-memory Database Server
 Graph Database Server

Learning outcome 1.2 Describe database server architecture

Duration: 5 hrs

Learning outline 2 objectives:

By the end of the learning outline, the trainees will be able to:
1. describe appropriately three types of database server architecture.
2. outline correctly two advantages of types of database server architecture.
3. outline correctly two disadvantages of types of database server architecture.

Resources
Equipment Tools Materials
 Computers  DBMS  Internet
 Projector  Electricity
 Paper
 Pen

Advance preparation:
. knowing the definition of database sever
. having a computer

Indicative content 1.2.1: Describe database server

architecture

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Description of types of database server architecture.

1. Two tier/Client-Server Architecture

8
In this architecture, the database server is responsible for managing the data, while the
client application handles the presentation logic and user interface. This architecture is
more scalable and secure than the centralized architecture but requires more resources to
maintain.

2. Three-tier architecture
Three-tier architecture is a common type of database server architecture used in web-based
applications. It consists of three layers or tiers:

1. Presentation Tier or User Interface Tier: This tier is responsible for presenting data
to users through a web browser or other client-side application. It includes web
pages, forms, and other user interface components that allow users to interact with
the application.
2. Application Tier or Middle Tier: This tier is responsible for processing user
requests, performing business logic, and accessing the database. It acts as an
intermediary between the presentation tier and the data tier, processing user input
and returning results to the presentation tier. This tier may include application
servers, web servers, and other middleware components.
3. Data Tier or Database Tier: This tier is responsible for storing and retrieving data
from the database. It includes database servers, database management systems, and
other data storage components. The data tier is responsible for maintaining the
integrity of the data, enforcing business rules and constraints, and providing access
to the application tier.

3. N-tier architecture

9
N-tier architecture is a type of database server architecture that extends the three-tier
architecture to include additional layers or tiers. In this architecture, each tier is responsible
for a specific set of functions, and the tiers communicate with each other through well-
defined interfaces.

Advantages and disadvantages of database server architecture

Two tier/Client-Server Architecture

Advantages

1. Centralized control: The server acts as a central point of control for the database,
making it easier to manage and maintain the data.
2. Scalability: The client-server architecture makes it easy to scale up the database
system by adding more servers or clients as needed.
3. Security: The server can enforce security policies, authentication, and access
control, ensuring that only authorized users can access the database.
4. Backup and recovery: With a centralized database, it is easier to backup and
recovery data in case of a disaster or system failure.
Disadvantages

1. Cost: Setting up and maintaining a client-server architecture can be costly,

especially for small businesses with limited resources.
2. Performance: Client-server architecture may result in slower response times, as the
data has to travel between the client and the server over the network.
3. Network dependency: The client-server architecture depends heavily on the
network, and any issues with the network can affect the performance of the
database system.
4. Single point of failure: The server acts as a single point of failure, and if it goes
down, the entire system may become inaccessible.
Advantages

1. Scalability: Three-tier architecture allows for scalability by distributing the load

among the tiers. As the number of users or requests increases, additional servers
can be added to handle the load.

2. Separation of concerns: By separating the presentation, application, and database

layers, developers can focus on specific tasks and modify them independently
without affecting the other layers.
3. Security: Three-tier architecture provides a secure environment by ensuring that the
database layer is protected from unauthorized access.
4. Flexibility: Three-tier architecture allows for different components to be developed
using different technologies, providing flexibility in development and
implementation.

Disadvantages

1. Complexity: Three-tier architecture adds complexity to the system, which may

make it more difficult to design, develop, and maintain.
2. Cost: Three-tier architecture requires more resources, such as additional servers and

10
 software, which may increase the cost of implementation and maintenance.
3. Performance: Three-tier architecture may lead to slower response times due to the
increased network traffic between the tiers.
4. Network dependency: Three-tier architecture is highly dependent on the network,
and any issues with the network can affect the performance of the database system.

Theoretical learning Activity

 Trainees will participate in group discussions to provide various database server
architectures

Points to Remember (Take home message)

 Client-Server Architecture
 Three-tier architecture
 N-tier architecture

Learning outcome 2 formative assessment

Written assessment
Q1. Define the following term called N-tier architecture.
Answer:
N-tier architecture is a type of database server architecture that extends the three-tier
architecture to include additional layers or tiers
Q2. Outline three (3) types of database server architecture.
Answer:
1. Two tier/Client-Server Architecture
2. Three-tier architecture
3. N-tier architecture
Q3. Answer with True or False
1) Database Tier is responsible for storing and retrieving data from the database.
2) The client-server architecture makes it complex to scale up the database system by
adding more servers or clients as needed.

11
 3) This architecture is more scalable and secure than the centralized architecture but
requires more resources to maintain.
Answer:
1) Database Tier is responsible for storing and retrieving data from the database. True
2) The client-server architecture makes it complex to scale up the database system by
adding more servers or clients as needed. False
3) This architecture is more scalable and secure than the centralized architecture but
requires more resources to maintain. False

Learning outcome 1.3 Identify database server properties

Duration: 5 hrs

Learning outline objectives:

By the end of the learning outline, the trainees will be able to:
1. outline correctly four database server properties.
2. define correctly the term filestream.

Resources
Equipment Tools Materials
 Computers  DBMS  Internet
 Projector  Electricity

Advance preparation:
. knowing the definition of database sever
. having a computer

Indicative content 1.3.1 : Identify database server properties

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

12
 Identification of database server properties
Property Grid
The Property Grid can be used to view and edit properties of the server instance or its
components, such as databases, tables, and columns. For example, if you are using
Microsoft SQL Server Management Studio, you can open the Property Grid for a database
by right-clicking on the database in the Object Explorer and selecting "Properties."

In the Property Grid, you can view and modify properties such as the database name,
collation, recovery model, and file locations.
Memory requirements

A database server requires enough memory to store the database in memory, as well as to
cache frequently accessed data and indexes. The more memory available, the more data
that can be cached in memory, resulting in faster database access times.

Processors
The server may be using a specific processor or a combination of processors, such as Intel
Xeon or AMD EPYC.
Server authentication
It is the process of verifying the identity of a database server before allowing clients to
connect to it. This authentication process is important for ensuring the security and
integrity of the data stored on the server.

There are different types of server authentication methods that can be used, including:

o Windows Authentication: This method uses the credentials of a Windows user

account to authenticate the server. The user must have the necessary
permissions to access the server.

o SQL Server Authentication: This method requires a username and password to

authenticate the server. The credentials are stored in the server and verified
against the user's input.

o Active Directory Authentication: This method uses Active Directory to

authenticate the server. Users must have a valid Active Directory account and
the necessary permissions to access the server.
Connections

The connection property of a database server refers to the ability of a client application to
establish a connection to the server in order to access the data stored on it. This property is
essential for applications that require access to a database for retrieving, manipulating, and
storing data.
Database settings options

The database settings options of a database server refer to the various configurable options
and settings that can be adjusted to optimize the performance, security, and reliability of
the database server. These settings can be used to fine-tune the behaviour of the database
server to meet the specific requirements of an application or organization.

Some common database settings options include:

13
 o Buffer pool size: This option determines the amount of memory allocated to
store data and index pages in memory to improve query performance.

o Backup and recovery settings: This option allows you to configure how often
backups are taken, the types of backups performed, and how backups are stored
and restored in case of data loss.

o Query optimization settings: This option provides options to tune the behavior
of the query optimizer to improve query performance.

o Security settings: This option allows you to set permissions and access controls
to ensure that only authorized users have access to the database server and data.
o High availability settings: This option provides options to configure replication,
failover, and clustering options to ensure that the database server remains
available and responsive even in the event of hardware or software failures.
o Logging and monitoring settings: This option provides options to monitor and
log database activity, track performance metrics, and set up alerts for unusual
activity or errors.

Filestream
Filestream is a database server property that allows SQL Server to store and manage large
binary data, such as documents, images, and video files, directly in the file system of the
server. With filestream, the binary data is stored outside the database file and can be
accessed and manipulated using standard Windows file system APIs.
Database engine
The database engine is the core software component that manages the storage, retrieval,
and manipulation of data within the database. The database engine is responsible for
processing SQL queries, managing transactions, and enforcing data integrity rules.
At least one administrator account was selected when SQL Server was being installed.
Perform the following step while logged into Windows as an administrator.

Theoretical learning Activity

 Ask trainees to brainstorm about database server properties within groups

Practical learning Activity

 Practical exercises to change database properties

14
 Points to Remember (Take home message)

 Property Grid
 Processor
 Server authentication
 Connection
 Filestream
 Database engine

Learning outcome 3 formative assessment

Written assessment
Q1. What is database engine?
Answer:
The database engine is the core software component that manages the storage, retrieval, and
manipulation of data within the database.
Q2. Explain four (4) common database settings options.
Answer:
 Buffer pool size: This option determines the amount of memory allocated to store data
and index pages in memory to improve query performance.
 Backup and recovery settings: This option allows you to configure how often backups
are taken, the types of backups performed, and how backups are stored and restored in
case of data loss.
 Query optimization settings: This option provides options to tune the behavior of the
query optimizer to improve query performance.
 Security settings: This option allows you to set permissions and access controls to
ensure that only authorized users have access to the database server and data.
 High availability settings: This option provides options to configure replication,
failover, and clustering options to ensure that the database server remains available
and responsive even in the event of hardware or software failures.
 Logging and monitoring settings: This option provides options to monitor and log
database activity, track performance metrics, and set up alerts for unusual activity or
errors.
Q3. Which of the following are the right server processors?
1) Intele Xeon
2) Intel Xeon
3) AMD EPYC

15
 4) AMD EPCY
Answer:
2)Intel Xeon
3)AMD EPYC

Please mix different assessment tools for triangulation and relevancy of

assessment

Learning Unit 2: Analyze database server requirements

https://phoenixnap.com/kb/what-is-a-database-server

STRUCTURE OF LEARNING UNIT

Learning outcomes:
2.1 Explore organizational database
2.2 Acquire database infrastructures
2.3 Select database server in line with customer needs

Learning outcome 2.1 Explore organizational database

16
 Duration: 2 hrs

Learning outcome 1 objectives:

By the end of the learning outcome, the trainees will be able to:
1. Explain clearly the priorities and confidentiality used in the organization information
management policies
2. Explain clearly the current information flows as used in the organization information
management policies
3. Explain clearly Risk assessment as used in the organization information management
policies
4. Explain clearly the Information accessibility and Information distribution as used in the
organization Information management policies
5. Explain properly the Monitoring procedures, Enforcement strategies, and Periodic
progress review as used in the Exploration of system status.

Resources
Equipment Tools Materials
 Computers  Database server setup  Electricity
 Projector  Internet
 Paper
 Pen

Advance preparation:
Trainer need to have the following elements:
 Database server setup,
 Computer
 Projector
 PPT

Indicative content 2.1.1: Explore organizational database

17
 Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Explanation of organization Information management policies

Organizations need to have well-defined policies for managing their information

effectively. These policies should cover various aspects of information management, such
as priorities, confidentiality, risk assessment, accessibility, and distribution. Let's take a
closer look at each of these areas:

Priorities and Confidentiality

Information management policies should prioritize data and information based on their
importance to the organization. The policies should also define how confidential
information is classified and protected from unauthorized access, use, or disclosure.

Current Information Flows

It is essential to understand the current information flows within the organization,
including how data is collected, processed, stored, and shared. Information management
policies should provide guidelines for how information should be captured, processed, and
used across different functions, departments, and systems.

Risk Assessment
An essential component of information management policies is risk assessment, which
involves identifying potential threats and vulnerabilities to information systems, data, and
assets. Policies should provide guidelines for how to conduct risk assessments, how often
they should be done, and how the organization should respond to identified risks.

Information Accessibility
Information management policies should ensure that information is accessible to those who
need it, when they need it, while still being protected from unauthorized access. This
includes guidelines for granting access permissions, controlling access to sensitive
information, and ensuring that information is available in formats that are usable by those
who need it.

Information Distribution
Information management policies should also cover the distribution of information,
including how it is shared internally and externally. Policies should outline how
information should be disseminated, who is authorized to share it, and how it should be
shared securely.

 Exploration of system status

Exploration of a system refers to the process of understanding the various components,

subsystems, and interactions that make up the system. This can involve studying the
system's design, reviewing documentation and specifications, and analyzing the system's
behavior under different conditions. The goal of exploration is to gain a comprehensive

18
understanding of the system so that it can be effectively managed and optimized.

Monitoring Procedures
Monitoring procedures involve monitoring the performance and health of a system in real-
time. This can involve collecting data from various sensors and instruments, analyzing the
data, and providing alerts or notifications when certain thresholds are exceeded. The goal
of monitoring procedures is to detect and address issues as soon as possible, to prevent
more serious problems from occurring.

Enforcement Strategies
Enforcement strategies refer to the policies, procedures, and mechanisms in place to ensure
that a system is being used and maintained according to its intended purpose. This can
involve setting access controls and permissions, enforcing security policies, and
implementing auditing and logging mechanisms. The goal of enforcement strategies is to
ensure the system's reliability, security, and availability.

Periodic Progress Review

Periodic progress reviews involve assessing the performance and effectiveness of a system
at regular intervals. This can involve analyzing data on system usage, conducting surveys
or interviews with users, and reviewing maintenance logs and other documentation. The
goal of periodic progress reviews is to identify areas where the system can be improved,
and to track progress towards achieving specific goals or objectives.

Theoretical learning Activity

 Group discussion on how an organization can keep information private

Practical learning Activity

 Practical exercises on system enforcement

Points to Remember (Take home message)

 Priorities and Confidentiality

 Current information flows
 Risk assessment
 Information accessibility
 Information distribution
 Monitoring procedures
 Enforcement strategies

19
  Periodic progress review

Learning outcome 1 formative assessment

Written assessment
Q1. Answer with True or False
1) Current information flows within the organization, including how data is collected,
processed, stored, and shared.
2) Monitoring procedures involve monitoring the performance and health of a system in
virtual-time.
3) Periodic progress reviews involve assessing the performance and effectiveness of a
system at regular intervals.
Answer:
1) Current information flows within the organization, including how data is collected,
processed, stored, and shared. True
2) Monitoring procedures involve monitoring the performance and health of a system in
virtual-time. False
3) Periodic progress reviews involve assessing the performance and effectiveness of a
system at regular intervals. True

Q2. What is the role of risk assessment in information management policies?

Answer:
It identifies the potential threats and vulnerabilities to information systems, data, and assets.

Please mix different assessment tools for triangulation and relevancy of

assessment
Learning outcome 2.2 Acquire database infrastructures

Duration:5 hrs

20
 Learning outcome 2 objectives:
By the end of the learning outcome, the trainees will be able to:
1. Define clearly the term database infrastructure
2. To know clearly how to acquire the Specifications of hardware components (like Servers,
Storage subsystems, and Networking devices) as used in database infrastructures.
3. List properly the software components as used in the database infrastructures.

Resources
Equipment Tools Materials
 Computer  Database server setup  Paper
 Projector  Pens
 Internet
 Electricity

Advance preparation:
Trainer need to have the following elements:
 Database server setup,
 Computer
 Projector
 PPT

Indicative content 2.2.1: Acquire database infrastructures

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Specifications of hardware components

Specifications of hardware components can vary widely depending on the specific

manufacturer and model. However, I can provide some general information on each
component :

Servers
Servers are computers that are designed to handle specific tasks, such as hosting websites,
21
running applications, or storing data. They typically have more powerful processors, larger
amounts of memory (RAM), and larger storage capacities than standard desktop
computers. Some common specifications of server hardware include:
 Processor: Intel Xeon or AMD EPYC
 Memory: 16 GB - 512 GB DDR4 RAM
 Storage: 1 TB - 24 TB HDD or SSD
 Network connectivity: Multiple Ethernet ports, typically 1 Gbps or higher
 Power supply: Redundant power supplies for increased reliability
 Form factor: Rackmount or blade servers for efficient use of space in data
centers
Storage subsystems
Storage subsystems are devices or systems that store and manage data. They can be used in
servers, network-attached storage (NAS) devices, or storage area networks (SANs). Some
common specifications of storage subsystems include:
 Disk type: Hard disk drive (HDD), solid-state drive (SSD), or hybrid drives
 Capacity: Usually measured in terabytes (TB)
 Interface: SATA, SAS, or NVMe for high-performance storage
 RAID support: Redundant Array of Independent Disks (RAID) provides data
protection and/or increased performance
 Form factor: 2.5-inch or 3.5-inch drives, or M.2 cards for smaller form factors
 Encryption: Some storage subsystems support hardware encryption for
increased security
Networking devices
Networking devices are hardware components that enable devices to communicate with
each other over a network. Some common networking devices include:
 Switches: Devices that connect devices within a local area network (LAN)
 Routers: Devices that connect networks and enable communication between
them
 Firewalls: Devices that provide security by controlling access to a network
 Wireless access points: Devices that enable wireless devices to connect to a
network
 Network Interface Cards (NICs): Devices that enable devices to connect to a
network, either wired or wireless

Some common specifications of networking devices include:

 Number of ports: The number of devices that can be connected to the device
 Speed: Usually measured in megabits per second (Mbps) or gigabits per second
(Gbps)
 Protocol support: The protocols that the device supports, such as Ethernet, Wi-
Fi, or TCP/IP
 Security features: Firewalls, intrusion detection/prevention, or virtual private
network (VPN) support
 Management features: Remote management, SNMP, or web-based management
interfaces.

 Listing software components

Procedures
Procedures are a type of program stored within the database that can be executed by the

22
DBMS. They are used to automate tasks, enforce business rules, and ensure data integrity.
Examples include stored procedures, triggers, and user-defined functions.

Database Access Language

A database access language is a programming language used to access and manipulate data
in the database. Examples include SQL (Structured Query Language), PL/SQL (Procedural
Language/SQL), and T-SQL (Transact-SQL).

Query Processor
The query processor is responsible for interpreting and optimizing queries written in the
database access language. It analyzes the query and generates an execution plan that will
retrieve the data as efficiently as possible.

Run Time Database Manager

The Run Time Database Manager (RTDM) is a component of the DBMS that manages the
execution of SQL statements and the interaction between the DBMS and the operating
system. It is responsible for allocating memory, managing processes, and ensuring that the
DBMS runs efficiently.

Data Manager
The Data Manager is responsible for managing the storage of data in the database. It
performs tasks such as creating, modifying, and deleting data files, and allocating storage
space to the database.

These components work together to provide a comprehensive database infrastructure that

can store, manage, and analyze large volumes of data.

Theoretical learning Activity

 Ask trainees to brainstorm about database server infrastructure within groups

Points to Remember (Take home message)

 Server
 Processor
 Memory
 Storage
 Power supply
 Disk type
 Capacity
 Firewalls
 Network Interface Cards (NICs)
 Database Access Language
 Query Processor

23
  Run Time Database Manager
 Data Manager

Learning outcome 2 formative assessment

Written assessment
Q1. Write in full words the following terms:
1) GB
2) RAM
3) HDD
4) SAN
5) RAID
Answer:
1) GB : Gigabytes
2) RAM : Random Access Memory
3) HDD : Hard disk drive
4) SAN : Storage area network
5) RAID : Redundant Array of Independent Disks
Q2. List down four (4) software components of database server.
Answer:
 Procedures
 Database Access Language
 Query Processor
 Run Time Database Manager
 Data Manager
Q3. Answer True or False
1) A database access language is a programming language used to access and manipulate
data in the store.
2) The query processor is responsible for interpreting and optimizing queries written in
the database access language.
3) The Run Time Database Manager is a component of the DBMS that manages the
execution of SQL statements and the interaction between the DBMS and the operating
system.
Answer:
1) A database access language is a programming language used to access and manipulate
data in the store. False

24
 2) The query processor is responsible for interpreting and optimizing queries written in
the database access language. True
3) The Run Time Database Manager is a component of the DBMS that manages the
execution of SQL statements and the interaction between the DBMS and the operating
system. True

Please mix different assessment tools for triangulation and relevancy of

assessment
Learning outcome 2.3 Select database server in line with customer
needs

Duration:3 hrs

Learning outcome 3 objectives:

By the end of the learning outcome, the trainees will be able to:
1. Select properly the speed of database server in line with customer needs
2. Select properly the Reliability of database server in line with customer needs
3 Select properly the Market price of database server in line with customer needs.
4. Select properly the Storage and manipulation of database server in line with customer
needs

Resources
Equipment Tools Materials
 Computer  Database server setup  Paper
 Projector  Pens
 Internet
 Electricity

Advance preparation:
. Database server setup
. Computer
. Projector
.PPT

25
 Indicative content 2.3.1: Select database server in line with
customer needs

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Choose of database server to install

Choosing a database server depends on a variety of factors, including the specific needs
and requirements of the user. Here are a few popular options based on the criteria you
mentioned:

Speed
If speed is a primary concern, then you might consider using an in-memory database like
Redis. In-memory databases store data in RAM, which allows for very fast read and write
speeds. Other options for high-speed databases include Apache Cassandra and Apache
Ignite.

Reliability
For reliability, a popular choice is PostgreSQL. It is known for its stability and robustness,
and is widely used in production environments. MySQL is another reliable option, and it is
commonly used in web applications and other high-traffic environments.

Market Price
For a free and open-source database server, PostgreSQL and MySQL are both popular
options. However, there are also commercial options available that may come with
additional features and support, such as Microsoft SQL Server or Oracle Database.

Data Analysis
If data analysis is a primary concern, you may want to consider a database server that
supports analytical processing and querying, such as Apache Hive or Apache HBase.
Another option is Apache Cassandra, which is designed for high-speed, high-volume data
ingestion and querying.

Storage and Manipulation

If you need to store and manipulate large amounts of data, then you might consider using a
distributed database like Apache Hadoop or Apache Spark. These systems are designed to
handle large-scale data processing and storage, and can be used for a variety of
applications. Other options for large-scale data storage and manipulation include
MongoDB and Amazon DynamoDB.

Ultimately, the choice of database server will depend on your specific needs and
requirements. Consider factors like data volume, performance needs, reliability, cost, and
feature set when making your decision.

26
 Theoretical learning Activity
 Ask trainees to brainstorm about qualities of a database server within groups

Points to Remember (Take home message)

 Speed
 Reliability
 Market price
 Data analysis
 Storage

Learning outcome 3 formative assessment

Written assessment
Q1. What are four (4) elements to consider when you are installing database server?
Answer:
 Speed
 Reliability
 Market price
 Data analysis
 Storage
Q2. Choose the right database server of high-speed databases.
a) MS Access
b) Apache Cassandra
c) MySQL
d) MS Excel
Answer:
b) Apache Cassandra

Please mix different assessment tools for triangulation and relevancy of

assessment

Learning Unit 3: Configure a database server

27
 https://sqlcoffee.com/SQLServer2012_0010.htm

STRUCTURE OF LEARNING UNIT

Learning outcomes:
3.1 Install a database server environment
3.2 Backup and restore a database depending on business processes
3.3 Manage a database storage
3.4 Upgrade and migrate a database server in accordance with business rules

Learning outcome 3.1 Install a database server environment

Duration: 5 hrs

Learning outcome 3 objectives:

By the end of the learning unit, the trainees will be able to:
1.Identify properly database servers as used in the database server Installation.
2. Differentiate correctly local database server from remote database server.
3. Install properly the database server.

Resources
Equipment Tools Materials
 Computers  Database server setup  Electricity
 Projector  Storage media  Internet
 Server

28
 Advance preparation:
- Database server setup
- Computers
- Projector
- Internet
- Books
- Storage media

Indicative content 3.1.1: Install a database server

environment

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Identification of database servers

In a database server environment, there are two types of servers: local and remote.

A local database server

It is installed on the same physical machine as the application that uses it. This type of
database server is often used in smaller environments, where the application and the
database are not very complex and there are not many users.

A remote database server

It is installed on a separate machine, usually located in a data center. This type of database
server is used in larger environments, where there are multiple applications and/or users
that need to access the database. The remote database server can be accessed via a network
connection, such as TCP/IP or HTTP.

To identify the database servers in a database server environment, you can check the
installation documentation or configuration files of the applications that use the database.
These files typically contain information about the database server's location, IP address,
port number, and other settings. Additionally, you can use tools like network scanners or
port scanners to detect open ports on machines that might be running database servers.

29
  Installation of database server

Download the database server installation files from the vendor's website. Here we
are installing Microsoft SQL Server 2012.
Run the installation wizard, which will guide you through the installation process.
The wizard will typically include the following steps:

Insert the SQL Server installation media. From the root folder, double-click Setup.exe. To
install from a network share, locate the root folder on the share, and then double-click
Setup.exe:

The Installation Wizard runs the SQL Server Installation Center. To create a new
installation of SQL Server, select the Installation option on the left side, and then click
New SQL Server stand-alone installation or add features to an existing installation:

30
Setup is now preparing to launch Setup Support Rules window:

31
Now Setup Support Rules will run to identify problems that may occur during the Setup
Support Files installation:

32
Once this step finishes click OK to proceed to Product Key window, In the Product Key
window, enter the Product license key (if required), and click Next to continue:

33
In the License Terms window, tick the box I accept the license terms and then click Next to
continue:

34
Note: You must accept the license agreement before you can continue the installation of
SQL Server 2012. Send feature usage data to Microsoft option is optional. Click Next and
then click Install to Install Setup Files:

35
These files are necessary to perform the actual installation. Following the installation of the
setup support files, you will be presented with another compatibility check. Following
dialog appears once you successfully pass these checks. You can click the Show Details
button under the green progress bar if you want to see the individual checks listed:

36
Click Next to continue to the Setup Role page:

37
Select SQL Server Feature Installation option and then Next to continue to Feature
Selection page:

38
I’m going to select all features so that we can walk through a complete installation process.
This will install the Database Engine Services, Analysis Services, Reporting Services, and
a number of shared features including SQL Server Books Online. You can also specify the
shared feature directory where share features components will be installed. Click Next to
continue to the Installation Rules page, Setup verifies the system state of your computer
before Setup continues:

39
Click Next to continue to the Instance Configuration page. Each server machine can host
one default instance of SQL Server, which resolves to the server name, and multiple named
instances, which resolves to the pattern ServerName\InstanceName. In this sample
installation, I will install a named instance of SQL Server 2012 called DEV01:

40
Note: This screen also report on other instances installed on this machine. Click Next to
proceed to the Disk Space Requirements page:

41
This is just an information page that does not require you to make any choices. Click Next
to go to Server Configuration page:

42
Here you specify service start-up and authentication. Microsoft recommends that each
service account have separate user accounts as a security best practice as shown in the
following figure:

The SQL Server 2012 Books Online makes the following security recommendations:
 Run separate SQL Server services under separate Windows accounts.
 Run SQL Server services with the lowest possible privileges.
 Associate SQL Server services with Windows accounts.
 Require Windows Authentication for connections to the SQL Server.
For the purposes of this article, we will authenticate all services using LocalSystem
account. You may have noticed the Collation tab here in this page. Click Collation tab and
then click Customize button to specify the collation for your Database Engine and Analysis
Services instance that best matches your application need:

43
44
Click Next to continue to Database Engine Configuration page. The SQL Server 2012
authentication mode was configured for “Windows Authentication Mode” per Microsoft
security best practice. For the purposes of this article, I will be choosing “Mixed Mode
Authentication”. You will also need to specify the SQL Server administrators to be used; in
this example I will use the current logged in user by clicking Add Current User button:

Click Data Dictionary tab and specify default database, log, backup, and tempdb locations:

45
Click FILESTREAM tab and enable this feature as below:

46
FILESTREAM feature is quiet useful when binaries or other data that does not fit neatly in
a table structure. Click Next to proceed to Analysis Services Configuration page, this is
similar to Database Engine Configuration page. Specify users or accounts that will have
administrator permissions for Analysis Services in Account Provisioning page and specify
non-default installation directories in Data Directories page:

47
Click Next to continue to Reporting Services Configuration page, specify the kind of
Reporting Services installation to create. For the purpose of this article, I will use Install
and Configure option:

48
For more information about Reporting Services configuration modes and the options we
have, see Reporting Services Configuration Options (SSRS). Click Next to proceed to
Distributed Replay Controller page, specify the users you want to grant administrative
permissions to for the Distributed Replay controller service and then click Next to
continue:

49
Distributed Replay Client Configuration page appears, here specify setting as shown in
next figure:

50
Click Next to advanced to Error Reporting page, tick the check box if you want to send
Windows and SQL Server error reports to Microsoft:

51
Click Next, Now System Configuration Checker will run some more rules that will validate
your computer configuration with the SQL Server features you have specified:

52
Correct any errors reported in the Installation Rules screen and click on Next to advanced
to Ready to Install page. This page shows a tree view of installation options that were
specified during Setup. On this page, Setup indicates whether the Product Update feature is
enabled or disabled and the final update version:

53
Click Install button to start SQL Server 2012 installation. The Setup will first install the
required prerequisites for the selected features followed by the feature installation. The
Installation Progress page provides status so that you can monitor installation progress as
Setup continues:

54
When the installation is complete, click on Next to Complete page:

55
 Click on the link to review the installation log if appropriate, and then click on Close. This
finalizes the installation process for SQL Server 2012.

Theoretical learning Activity

 Ask trainees to brainstorm about local and remote database servers

Practical learning Activity

 Trainees will be given practical exercises to install SQL server

Points to Remember (Take home message)

 Local database server
 Remote database server
 Product key

Learning outcome 1 formative assessment

Written assessment
Q1. Differentiate local database server from remote database server.
Answer:
A local database server is a database server that is installed and runs on the same machine as
the client application. This means that the database is stored on the same computer as the
application that uses it, and both are accessed through the same network interface. The
database server software is installed and configured locally, and the application connects to it
via a local network or through the loopback interface while
A remote database server is a database server that is installed on a different machine than the
client application. This means that the database is physically located on a different server
machine and is accessed through the network. The database server software is installed,
configured, and maintained on a remote machine, and the application connects to it over a
network connection such as the Internet.
Q2. Which of the following is an open-source, relational database management system
widely used for web applications?
a) Oracle Database

56
 b) Microsoft SQL Server
c) MongoDB
d) MySQL
Answer:
d) MySQL

Please mix different assessment tools for triangulation and relevancy of

assessment
Practical assessment
 Task to be performed
NYCT Ltd. is one of countrywide Construction Company. The company has a problem of
having a centralized database server to manage employees and their payrolls, business project
contracts and assets.
As a database developer, you are hired to setup a SQL server 2016 version 13.0.1601.5 on the
following environment:
- Operating System: Windows server 2016
- RAM: 8GB
- Processor: 1.4 GHz 64-bit processor
- Compatible with x64 instruction set
- Supports NX and DEP
- Supports CMPXCHG16b, LAHF/SAHF, and PrefetchW
- Supports Second Level Address Translation (EPT or NPT)
The task must be completed in 1hours.
 Observation checklist
Checklist Score
Yes No
Indicator: Tools, equipment and materials are selected
Tools
 SQL server setup
 Version
 Operating system
 RAM
 Processor
Materials
 SQL server deployment DVD
Equipment
 Computer
Indicator:SQL server is installed
 Features and services
 Properties
 Storage
57
Indicator: Database server is installed
 Responsiveness
 Run
Observation

Learning outcome 3.2: Backup and restore a database depending on

business processes

Duration: 8 hrs

Learning outcome 2 objectives:

By the end of the learning outcome, the trainees will be able to:
1. Describe clearly the types of backups depending on business processes.
2. Identify properly the backup devices and media depending on business processes
3. Backup and restore a database depending on business processes.

Resources
Equipment Tools Materials
 Computers  Database server setup  Electricity
 Projector  Storage media  Internet
 Server

Advance preparation:
- Database server setup
- Computers
- Projector
- Internet
- Books

58
 - Storage media

Indicative content 3.2.1: Backup and restore a database

depending on business processes

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Description of types of backups

Full Backup

A full backup is a complete backup of all data and files in a particular system, database or
device. It captures everything, including data, files, settings, configurations, operating
system files, and more. Full backups are typically performed on a regular schedule, such as
daily or weekly, to ensure that all data is captured.

Incremental Backup

An incremental backup is a backup of only the data that has changed since the last backup,
whether it was a full or incremental backup. It saves time and storage space by backing up
only the data that has changed, instead of performing a full backup each time. Incremental
backups are usually faster than full backups and require less storage space.

Differential Backup

A differential backup is similar to an incremental backup in that it only backs up data that
has changed since the last full backup. However, it differs in that it backs up all data that
has changed since the last full backup, not just the changes since the last backup. This can
make differential backups larger and slower than incremental backups, but they can be
faster to restore since only the last full backup and the last differential backup need to be
restored.

Mirror Backup

A mirror backup, also known as a "clone," is a complete and exact copy of a system,
device, or database. It mirrors all data and files, including settings, configurations, and
operating system files. A mirror backup is useful for creating an identical copy of a system,
which can be used as a failover or disaster recovery solution. It can be a time-consuming
and resource-intensive process, as it creates a duplicate of all data and files.

 Identification of backup devices and media

Backup devices and media are used to store important data in case of a system failure,
disaster or data loss. Here are some commonly used backup devices and media :

59
 Magnetic tapes
Magnetic tapes are one of the oldest and most reliable forms of backup storage. They use a
magnetic coating to store data and are capable of storing large amounts of data. Magnetic
tapes are ideal for long-term storage and can withstand high temperatures and humidity.

Disk drives
Disk drives are one of the most commonly used backup devices today. They use magnetic
disks to store data and are available in various sizes and storage capacities. Disk drives are
easy to use, provide quick access to data, and are relatively inexpensive.

CD-ROM
CD-ROMs are a popular choice for backing up data due to their low cost, portability, and
ease of use. They are suitable for storing small amounts of data and are ideal for archiving
purposes.

Solid state storage

Solid state storage devices, such as USB flash drives, are becoming increasingly popular
for backup storage due to their small size, portability, and high storage capacity. They use
flash memory to store data and are very durable, making them ideal for transporting data
between locations.

When choosing a backup device or media, it is important to consider the storage capacity,
speed, durability, and cost. It is also important to choose a backup device that is compatible
with your computer system and backup software.

 Backup and restore a database

 To backup a database in SQL Server, you can follow these steps:

1. Open SQL Server Management Studio and connect to the SQL Server instance
where the database you want to backup is located.
2. In the Object Explorer pane, expand the Databases node to display a list of all
databases on the server.
3. Right-click on the database that you want to backup and select Tasks > Backup
from the context menu. This will open the Backup Database window.
4. In the Backup Database window, choose the type of backup you want to perform,
such as Full or Differential.
5. Select a backup destination, such as a disk file or a tape drive.
6. Specify any backup options you want to use, such as compression or encryption.
7. Click the OK button to start the backup process.
 To restore a database from a .BAK file, you can follow these steps:

1. Open SQL Server Management Studio and connect to the SQL Server instance
where you want to restore the database.
2. In the Object Explorer pane, right-click on the Databases node and select Restore
Database from the context menu. This will open the Restore Database window.
3. In the Restore Database window, specify the source of the backup by selecting the
Device option and clicking the ellipsis button to browse to the .BAK file.
4. Select the backup sets to restore from the backup file.

60
 5. Choose the destination database for the restore operation.
6. Specify any restore options you want to use, such as overwriting an existing
database or preserving the original file paths.
7. Click the OK button to start the restore process.

Theoretical learning Activity

 Brainstorm about different types of database backup
 List backup devices and media

Practical learning Activity

 Trainees will be given practical exercises to backup and restore a database

Points to Remember (Take home message)

 Full Backup
 Incremental Backup
 Differential Backup
 Mirror Backup
 Restore
 Magnetic tapes
 Disk drives
 CD-ROM
 Solid state storage

Learning outcome 2 formative assessment

Written assessment
Q1. Which of the following is a type of backup that copies all data and marks the files as
backed up?
a) Differential backup
b) Full backup
c) Incremental backup
d) Mirror backup

61
Answer:
b) Full backup
Q2. Which type of backup copies only the data that has changed since the last full or
incremental backup?
a) Differential backup
b) Full backup
c) Incremental backup
d) Mirror backup
Answer:
c) Incremental backup
Q3. Outline four (4) backup devices and media.
Answer:
 Magnetic tapes
 Disk drives
 CD-ROM
 Solid state storage

Please mix different assessment tools for triangulation and relevancy of

assessment
Practical assessment
 Assessment tools
 Task to be performed

X Ltd is a data collection company. The company has a problem of losing some
data, as a database developer, you are hired to backup and restore those lost data
by using SQL server management studio.
 Observation checklist
Checklist Score
Yes No
 SQL server management studio is opened
 Database is selected
 Type of backup is selected
 Back up database is done
 Restore a database is completed
Observation

Learning outcome 3.3: Manage a database storage

62
 Duration:4 hrs

Learning outcome 3 objectives:

By the end of the learning outcome, the trainees will be able to:
1. Define clearly the term database storages as used in database storage management
2. Identify properly the database storages as used in database storage management
3. Differentiate clearly database storages as used in database storage management

Resources
Equipment Tools Materials
 Computers  Storage media  Electricity
 Projector  Internet
 Server

Advance preparation:
- Computers
- Projector
- Internet
- Books
- Storage media

Indicative content 3.3.1: Manage a database storage

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Identify database storages

63
 Table spaces
A table space is a logical storage unit within a database that is used to group related objects
together. Each table space consists of one or more data files.

Temporary table space groups

A temporary table space group is a collection of temporary table spaces that are used for
sorting and other temporary storage needs.

Data files
A data file is a physical file that stores the data for a database object, such as a table or
index.

Rollback segments
Rollback segments are used to maintain the consistency of a database by providing a way
to undo changes made to the database. They are used to support transactions and are
typically created in the temporary table space.

Redo log groups

A redo log group is a set of one or more redo log files that record changes to the database.
Redo logs are used to recover data in the event of a system failure.

Disk groups
A disk group is a collection of physical disks that are managed as a single logical unit. Disk
groups are used to store database files and provide redundancy and performance benefits.

Archive logs
Archive logs are copies of the redo log files that have been archived and stored for future
use. They are used to recover data in the event of a disaster or other catastrophic event.

Theoretical learning Activity

 Brainstorm about database storages

Practical learning Activity

 Trainees will conduct practical exercises on database storage properties

Points to Remember (Take home message)

 Table spaces

64
  Temporary table space groups
 Data files
 Rollback segments
 Redo log groups
 Disk groups
 Archive logs

Learning outcome 2 formative assessment

Written assessment
Q1. Define the following terms:
a) Data file
b) Redo log group
c) Disk group
Answer:
a) Data file is a physical file that stores the data for a database object, such as a table or
index.
b) Redo log group is a set of one or more redo log files that record changes to the
database. Redo logs are used to recover data in the event of a system failure.
c) Disk group is a collection of physical disks that are managed as a single logical unit.
Q2. What is the purpose of a rollback segment?
a) To store temporary data for a transaction
b) To store a log of all database changes
c) To store data backups
d) To store undo information for a transaction
Answer:
d) To store undo information for a transaction
Q3. What is a tablespace in a database?
a) A collection of tables and indexes that belong to the same schema
b) A logical storage unit that contains one or more data files
c) A physical storage unit that contains database objects
d) A backup copy of the database stored on disk
Answer:
b) A logical storage unit that contains one or more data files

65
 Please mix different assessment tools for triangulation and relevancy of
assessment
Learning outcome 3.4: Upgrade and migrate a database server in
accordance with business rules

Duration:8hrs

Learning Outcome 4 objectives:

By the end of the learning outcome, the trainees will be able to:
1.Apply properly the database Version and edition upgrades in accordance with business
rules
2. Apply properly the data migration assistant in accordance with business rules
3. Upgrade properly the database engine in accordance with business rules

Resources
Equipment Tools Materials
 Computers  Database server setup  Electricity
 Projector  Storage media  Internet
 Server

Advance preparation:
- Database server setup
- Computers
- Projector
- Internet
- Books
- Storage media

66
 Indicative content 3.4.1: Upgrade and migrate a database server
in accordance with business rules

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Application for database upgrade and migration

Version and edition upgrades

A version upgrade involves upgrading the version of the database software to a newer
version. This typically involves installing a new version of the database software on the
server and migrating the data to the new version. For example, upgrading from Microsoft
SQL Server 2016 to Microsoft SQL Server 2019 would be a version upgrade. Version
upgrades often include new features and functionality, improved performance, and bug
fixes.

An edition upgrade involves upgrading the edition of the database software to a higher
edition. This typically involves upgrading to an edition of the database software that offers
additional features or functionality. For example, upgrading from Microsoft SQL Server
Standard Edition to Microsoft SQL Server Enterprise Edition would be an edition upgrade.
Edition upgrades often include additional features such as advanced security, high
availability, and scalability.

It is important to note that version and edition upgrades can be complex and may require
careful planning and testing to ensure that the upgrade is successful and minimizes
downtime and disruption to the business. It is important to thoroughly review the
documentation and upgrade guides provided by the database software vendor before
proceeding with an upgrade. Additionally, it may be beneficial to engage with a qualified
database administrator or consultant to assist with the upgrade process.

Using data migration assistant

The Data Migration Assistant (DMA) is a tool provided by Microsoft that can be used to
assess and migrate databases to a newer version of SQL Server or Azure SQL Database.
Here are the general steps for using the DMA tool to migrate a database:

Download and install the DMA tool:

First, download and install the DMA tool from the Microsoft website.

Create a new DMA project:

Open the DMA tool and create a new project. Enter the name of the project, select the type
of migration, and specify the source and target database information.

67
Run the assessment:
Once you have created the project, run the assessment. The DMA tool will analyze the
source database and identify any compatibility issues or potential problems that may arise
during the migration process.

Resolve any issues:

If the assessment identifies any issues or problems, review the recommendations provided
by the DMA tool and take the necessary steps to resolve them. This may involve updating
the database schema, modifying the application code, or changing the database settings.

Run the migration:

Once you have resolved any issues identified by the DMA tool, you can run the migration
process. The DMA tool will migrate the schema, data, and other database objects to the
target database.

Verify the migration:

After the migration is complete, verify that the target database is functioning correctly and
that all data has been transferred successfully.

The DMA tool provides a user-friendly interface and detailed documentation to guide you
through the migration process. However, it is important to thoroughly review the
documentation and understand the migration process before proceeding to ensure a
successful migration.

Upgrade analysis services

Analysis services is a business intelligence and data analytics platform provided by

Microsoft as part of the SQL Server product suite. Upgrading analysis services involves
migrating from an older version of Analysis Services to a newer version, which can
provide access to new features and capabilities, improved performance, and bug fixes.

The Upgrade Analysis Services process involves the following steps:

 Review the upgrade documentation: Before upgrading Analysis Services, it is

important to review the upgrade documentation provided by Microsoft. The
documentation will provide guidance on the upgrade process and highlight any
potential issues that may arise during the upgrade.

 Test the upgrade process: Before upgrading the production environment, it is

recommended to test the upgrade process in a test environment to ensure a smooth
transition.

 Back up the Analysis Services databases: Before upgrading, it is important to back

up the Analysis Services databases to ensure that you have a restore point in case
something goes wrong during the upgrade.

 Upgrade the Analysis Services instance: The next step is to upgrade the Analysis
Services instance to the newer version. This involves installing the new version of
Analysis Services and upgrading the databases.

68
  Verify the upgraded Analysis Services instance: Once the upgrade is complete, it is
important to verify that the upgraded Analysis Services instance is functioning
correctly. This involves testing the performance and functionality of the Analysis
Services instance to ensure that it meets the business requirements.

 Update any dependent applications: After the upgrade is complete, it may be

necessary to update any dependent applications or tools that interact with Analysis
Services.

It is important to thoroughly review the upgrade documentation and test the upgrade
process before proceeding with the upgrade to ensure a successful upgrade with minimal
downtime and disruption to the business.

Upgrade database engine

Upgrading the database engine in a database server involves migrating the database engine
from an older version to a newer version. The database engine is the core component of the
database server that is responsible for managing the storage, retrieval, and processing of
data.

Upgrading the database engine involves the following steps:

 Review the upgrade documentation: Before upgrading the database engine, it is

important to review the upgrade documentation provided by the database software
vendor. The documentation will provide guidance on the upgrade process and
highlight any potential issues that may arise during the upgrade.

 Test the upgrade process: Before upgrading the production environment, it is

recommended to test the upgrade process in a test environment to ensure a smooth
transition.

 Back up the databases: Before upgrading, it is important to back up the databases to

ensure that you have a restore point in case something goes wrong during the
upgrade.

 Upgrade the database engine: The next step is to upgrade the database engine to the
newer version. This involves installing the new version of the database engine and
upgrading the databases.

 Verify the upgraded database engine: Once the upgrade is complete, it is important
to verify that the upgraded database engine is functioning correctly. This involves
testing the performance and functionality of the database engine to ensure that it
meets the business requirements.

 Update any dependent applications: After the upgrade is complete, it may be

necessary to update any dependent applications or tools that interact with the
database engine.

It is important to carefully review the upgrade documentation and test the upgrade process

69
before proceeding with the upgrade to ensure a successful upgrade with minimal downtime
and disruption to the business. Additionally, it may be beneficial to engage with a qualified
database administrator or consultant to assist with the upgrade process.

Upgrade data quality services

Data Quality Services (DQS) is a component of SQL Server that provides data cleansing
and matching functionality to improve the quality of data in a database. Upgrading DQS
involves migrating from an older version of DQS to a newer version, which can provide
access to new features and capabilities, improved performance, and bug fixes.

The upgrade process for DQS involves the following steps:

 Review the upgrade documentation: Before upgrading DQS, it is important to

review the upgrade documentation provided by Microsoft. The documentation will
provide guidance on the upgrade process and highlight any potential issues that
may arise during the upgrade.

 Test the upgrade process: Before upgrading the production environment, it is

recommended to test the upgrade process in a test environment to ensure a smooth
transition.

 Back up the DQS databases: Before upgrading, it is important to back up the DQS
databases to ensure that you have a restore point in case something goes wrong
during the upgrade.

 Upgrade the DQS instance: The next step is to upgrade the DQS instance to the
newer version. This involves installing the new version of DQS and upgrading the
databases.

 Verify the upgraded DQS instance: Once the upgrade is complete, it is important to
verify that the upgraded DQS instance is functioning correctly. This involves
testing the performance and functionality of the DQS instance to ensure that it
meets the business requirements.

 Update any dependent applications: After the upgrade is complete, it may be

necessary to update any dependent applications or tools that interact with DQS.

Upgrade Integration Services

Integration Services is a component of SQL Server that provides data integration and
transformation services.

The upgrade process for integration services involves the following steps:

 Review the upgrade documentation: Before upgrading integration services, it is

important to review the upgrade documentation provided by Microsoft. The
documentation will provide guidance on the upgrade process and highlight any
potential issues that may arise during the upgrade.

70
  Test the upgrade process: Before upgrading the production environment, it is
recommended to test the upgrade process in a test environment to ensure a smooth
transition.

 Back up the integration services packages: Before upgrading, it is important to back

up the integration services packages to ensure that you have a restore point in case
something goes wrong during the upgrade.

 Upgrade the integration services instance: The next step is to upgrade the
integration services instance to the newer version. This involves installing the new
version of integration services and upgrading the integration services packages.

 Verify the upgraded integration services instance: Once the upgrade is complete, it
is important to verify that the upgraded integration services instance is functioning
correctly. This involves testing the performance and functionality of the integration
services instance to ensure that it meets the business requirements.

 Update any dependent applications: After the upgrade is complete, it may be

necessary to update any dependent applications or tools that interact with
integration services.

Upgrade master data services

Master Data Services (MDS) is a component of SQL Server that provides a centralized
system for managing master data entities and hierarchies. Upgrading MDS involves
migrating from an older version of MDS to a newer version, which can provide access to
new features and capabilities, improved performance, and bug fixes.

The upgrade process for MDS involves the following steps:

 Review the upgrade documentation: Before upgrading MDS, it is important to

review the upgrade documentation provided by Microsoft. The documentation will
provide guidance on the upgrade process and highlight any potential issues that
may arise during the upgrade.

 Test the upgrade process: Before upgrading the production environment, it is

recommended to test the upgrade process in a test environment to ensure a smooth
transition.

 Back up the MDS databases: Before upgrading, it is important to back up the MDS
databases to ensure that you have a restore point in case something goes wrong
during the upgrade.

 Upgrade the MDS instance: The next step is to upgrade the MDS instance to the
newer version. This involves installing the new version of MDS and upgrading the
databases.

 Verify the upgraded MDS instance: Once the upgrade is complete, it is important to
verify that the upgraded MDS instance is functioning correctly. This involves
testing the performance and functionality of the MDS instance to ensure that it

71
 meets the business requirements.

 Update any dependent applications: After the upgrade is complete, it may be

necessary to update any dependent applications or tools that interact with MDS.

Upgrade Power Pivot for SharePoint

Power Pivot for SharePoint is a feature in SharePoint Server that allows users to share and
collaborate on Power Pivot workbooks, which are Excel workbooks that contain data
models created using Power Pivot. Upgrading Power Pivot for SharePoint involves
migrating from an older version of Power Pivot for SharePoint to a newer version, which
can provide access to new features and capabilities, improved performance, and bug fixes.

The upgrade process for Power Pivot for SharePoint involves the following steps:

 Review the upgrade documentation: Before upgrading Power Pivot for SharePoint,
it is important to review the upgrade documentation provided by Microsoft. The
documentation will provide guidance on the upgrade process and highlight any
potential issues that may arise during the upgrade.

 Test the upgrade process: Before upgrading the production environment, it is

recommended to test the upgrade process in a test environment to ensure a smooth
transition.

 Back up the Power Pivot workbooks and data: Before upgrading, it is important to
back up the Power Pivot workbooks and data to ensure that you have a restore point
in case something goes wrong during the upgrade.

 Upgrade Power Pivot for SharePoint: The next step is to upgrade Power Pivot for
SharePoint to the newer version. This involves installing the new version of Power
Pivot for SharePoint and upgrading the data.

 Verify the upgraded Power Pivot for SharePoint instance: Once the upgrade is
complete, it is important to verify that the upgraded Power Pivot for SharePoint
instance is functioning correctly. This involves testing the performance and
functionality of the Power Pivot for SharePoint instance to ensure that it meets the
business requirements.

 Update any dependent applications: After the upgrade is complete, it may be

necessary to update any dependent applications or tools that interact with Power
Pivot for SharePoint.

Upgrade replicated databases

Replication is a feature in SQL Server that allows database objects to be copied and
synchronized across multiple database instances. Upgrading replicated databases involves
migrating from an older version of SQL Server to a newer version while maintaining the
replication configuration and ensuring that the replicated data remains synchronized.

The upgrade process for replicated databases involves the following steps:

72
  Review the upgrade documentation: Before upgrading replicated databases, it is
important to review the upgrade documentation provided by Microsoft. The
documentation will provide guidance on the upgrade process and highlight any
potential issues that may arise during the upgrade.

 Test the upgrade process: Before upgrading the production environment, it is

recommended to test the upgrade process in a test environment to ensure a smooth
transition.

 Determine the replication topology: It is important to determine the replication

topology, which refers to the specific configuration of replication in the
environment, including the type of replication used and the roles of the publisher,
distributor, and subscriber.

 Upgrade the publisher: The next step is to upgrade the publisher, which is the
database instance that initiates the replication process. This involves upgrading the
SQL Server instance and upgrading the publisher database.

 Upgrade the distributor: After upgrading the publisher, the distributor, which is
responsible for managing the replication process, needs to be upgraded. This
involves upgrading the SQL Server instance and the distribution database.

 Upgrade the subscribers: Once the distributor is upgraded, the subscribers, which
are the database instances that receive the replicated data, can be upgraded. This
involves upgrading the SQL Server instances and the subscriber databases.

 Verify replication is working correctly: Once the upgrade is complete, it is

important to verify that replication is working correctly. This involves checking that
the replicated data is synchronized across all instances and that there are no errors
or issues.

Upgrade and migrate reporting services

SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows
users to create, manage, and deliver reports to various stakeholders within an organization.
Upgrading and migrating SSRS involves moving from an older version of SSRS to a newer
version while ensuring that the existing reports, data sources, and subscriptions are
preserved and continue to function as expected.

The upgrade and migration process for SSRS involves the following steps:

 Review the upgrade and migration documentation: Before upgrading and migrating
SSRS, it is important to review the upgrade and migration documentation provided
by Microsoft. The documentation will provide guidance on the upgrade and
migration process and highlight any potential issues that may arise during the
process.

 Test the upgrade and migration process: Before upgrading and migrating the
production environment, it is recommended to test the upgrade and migration

73
 process in a test environment to ensure a smooth transition.

 Prepare the new environment: The next step is to prepare the new environment,
which involves installing the newer version of SSRS and configuring it to match
the existing environment. This includes configuring data sources, security, and
other settings.

 Backup existing SSRS content: Before migrating SSRS content to the new
environment, it is important to back up the existing SSRS content, including
reports, data sources, and subscriptions.

 Migrate SSRS content: The next step is to migrate the SSRS content from the
existing environment to the new environment. This can be done using the Reporting
Services Configuration Manager, which allows you to move content between SSRS
instances.

 Verify the migrated content: Once the content is migrated to the new environment,
it is important to verify that the migrated content is working correctly. This
involves testing the reports, data sources, and subscriptions to ensure that they
function as expected.

 Update any dependent applications: After the upgrade and migration is complete, it
may be necessary to update any dependent applications or tools that interact with
SSRS.

Upgrade SQL Server management tools

SQL Server Management Tools (SSMT) are a set of applications and utilities used to
manage and administer SQL Server instances and databases. These tools include SQL
Server Management Studio (SSMS), SQL Server Profiler, SQL Server Configuration
Manager, and others.

Upgrading SQL Server Management Tools involves updating the tools to a newer version
to take advantage of new features and bug fixes, and to ensure compatibility with the latest
versions of SQL Server. The upgrade process for SQL Server Management Tools typically
involves the following steps:

 Review the upgrade documentation: Before upgrading SQL Server Management

Tools, it is important to review the upgrade documentation provided by Microsoft.
The documentation will provide guidance on the upgrade process and highlight any
potential issues that may arise during the upgrade.

 Test the upgrade process: Before upgrading the production environment, it is

recommended to test the upgrade process in a test environment to ensure a smooth
transition.

 Determine which tools need to be upgraded: Depending on the specific

environment, it may not be necessary to upgrade all of the SQL Server
Management Tools. It is important to determine which tools need to be upgraded
and to ensure that they are compatible with the version of SQL Server being used.

74
  Upgrade the tools: Once the tools to be upgraded have been identified, the next step
is to upgrade the tools themselves. This typically involves running the installation
program for the new version of the tool and following the prompts to complete the
installation.

 Verify the upgraded tools: Once the tools are upgraded, it is important to verify that
they are working correctly. This involves testing the functionality of the tools to
ensure that they are functioning as expected.

Upgrade SQL server using the installation wizard (setup)

Upgrading SQL Server using the installation wizard, involves upgrading an existing SQL
Server instance to a newer version. This process typically involves the following steps:

 Review the upgrade documentation: Before upgrading SQL Server using the
installation wizard, it is important to review the upgrade documentation provided
by Microsoft. The documentation will provide guidance on the upgrade process and
highlight any potential issues that may arise during the upgrade.

 Backup the existing databases: Before upgrading SQL Server, it is important to

backup all existing databases to ensure that they can be restored in case of any
issues during the upgrade process.

 Launch the installation wizard: Once the databases are backed up, the next step is to
launch the installation wizard for the newer version of SQL Server. The installation
wizard can be launched by running the setup.exe file included in the installation
media for the new version of SQL Server.

 Select the upgrade option: After launching the installation wizard, select the
upgrade option from the list of available options. The wizard will detect the existing
version of SQL Server and prompt for confirmation to upgrade to the newer
version.

 Review and configure installation options: Next, review and configure the
installation options as necessary. This includes selecting the SQL Server features to
be upgraded, specifying the installation directories, and configuring any additional
options.

 Perform the upgrade: Once the installation options are configured, the upgrade
process can be initiated by clicking the "Install" button. The installation wizard will
guide the user through the upgrade process, which may take some time depending
on the size of the databases being upgraded.

 Verify the upgrade: After the upgrade is complete, it is important to verify that the
upgraded SQL Server instance is functioning correctly. This involves testing the
databases and SQL Server features to ensure that they are working as expected.

Upgrade to a different edition of SQL server (setup)

75
Upgrading to a different edition of SQL Server involves upgrading an existing instance to a
higher or lower edition of SQL Server. This process can be performed using the installation
wizard (Setup).
The steps involved in upgrading to a different edition of SQL Server using Setup include:

 Review the upgrade documentation: Before upgrading to a different edition of SQL

Server using Setup, it is important to review the upgrade documentation provided
by Microsoft. The documentation will provide guidance on the upgrade process and
highlight any potential issues that may arise during the upgrade.

 Backup the existing databases: Before upgrading to a different edition of SQL

Server, it is important to backup all existing databases to ensure that they can be
restored in case of any issues during the upgrade process.

 Launch the installation wizard: Once the databases are backed up, the next step is to
launch the installation wizard for the new edition of SQL Server. The installation
wizard can be launched by running the setup.exe file included in the installation
media for the new edition of SQL Server.

 Select the upgrade option: After launching the installation wizard, select the
"Upgrade from a previous version of SQL Server" option. The wizard will detect
the existing version of SQL Server and prompt for confirmation to upgrade to the
new edition.

 Enter the new product key: When prompted, enter the product key for the new
edition of SQL Server.

 Review and configure installation options: Next, review and configure the
installation options as necessary. This includes selecting the SQL Server features to
be upgraded, specifying the installation directories, and configuring any additional
options.

 Perform the upgrade: Once the installation options are configured, the upgrade
process can be initiated by clicking the "Install" button. The installation wizard will
guide the user through the upgrade process, which may take some time depending
on the size of the databases being upgraded.

 Verify the upgrade: After the upgrade is complete, it is important to verify that the
upgraded SQL Server instance is functioning correctly. This involves testing the
databases and SQL Server features to ensure that they are working as expected.

Theoretical learning Activity

 Ask trainees to brainstorm about database upgrade and migration

76
 Practical learning Activity
 Trainees will conduct practical exercises on database upgrade and migration

Points to Remember (Take home message)

 Version upgrade
 Edition upgrade
 Data migration assistant
 Analysis services
 Database engine
 Integration Services
 Power Pivot for SharePoint
 Replicated databases
 SQL Server Reporting Services

Learning outcome 4 formative assessment

Written assessment
Q1. What are the steps used to upgrade database engine?
Answer:
1) Review the upgrade documentation
2) Test the upgrade process
3) Back up the databases
4) Upgrade the database engine
5) Verify the upgraded database engine
6) Update any dependent applications
Q2. What is the purpose of upgrading data quality services in a database server?
a) To improve the performance of the database
b) To enhance the accuracy and reliability of data
c) To reduce the cost of maintaining the database
d) All of the above
Answer: b) To enhance the accuracy and reliability of data
Q3. Which of the following is a benefit of upgrading the version of a database server?
a) Improved data quality
b) Increased database performance
c) Decreased database size
d) Simplified database management
77
Answer: b) Increased database performance

Please mix different assessment tools for triangulation and relevancy of

assessment

Practical assessment
 Assessment tools
 Task to be performed

ITEL ltd is a company that develop powerful databases. They need to hire a
person to install MDA (Microsoft data migration assistant) tool that will help in
the migration of database from Source server called ABC server to the target
server called DEF Server. Suppose that you are hired, install the tool and migrate
database.

 Observation checklist
Checklist Score
Yes No
Indicator: Installation of DMA
 Data migration assistant setup is downloaded
 Microsoft Data migration assistant is completely installed
 DMA is opened
 Migration is selected
 Source and target server type is selected
 Source and target server details is well done
 Migration complete within a given time
Observation

Learning Unit 4: Perform testing and documentation of work done

78
https://www.softwaretestinghelp.com/database-testing-process/

STRUCTURE OF LEARNING UNIT

Learning outcomes:
4.1 Validate and test a database server
4.2 Document database server installation process review
4.3 Report procedures of the tasks accomplished in accordance with the
installation to be done

Learning outcome 4.1 Validate and test a database server

Duration:3 hrs

Learning outcome 1 objectives:

By the end of the learning outcome, the trainees will be able to:
1. Run properly SQL server features and recovery report as used in validating and testing
database server
2. Apply properly of database server updates as used in validating and testing database
server

79
 3. Test clearly database server functionality and configuration correctness

Resources
Equipment Tools Materials
 Computer  Database server setup  Internet
 Projector  Electricity
 Paper
 Pen

Advance preparation:
- Database server setup
- Computers
- Projector
- Internet
- Books

Indicative content 4.1.1: Validate and test a database server

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Run SQL server features and recovery report

SQL server installation centre installed

1. Open SQL Server Installation Center on the server where SQL Server is installed.
2. Click on "Tools" in the left-hand pane.
3. Click on "Installed SQL Server features discovery report" in the right-hand pane.
4. Follow the prompts to generate the report.

This report will provide you with a list of all installed SQL Server components, their
versions, and their current status.

SQL Server features discovery report

80
To check the recovery status of your SQL Server, you can use SQL Server Management
Studio (SSMS) by following these steps:

1. Open SSMS and connect to your SQL Server instance.

2. Right-click on the instance name in Object Explorer and select "Properties."
3. In the "Properties" window, select "Database Settings" from the left-hand pane.
4. Under "Recovery model," you will see the current recovery model set for your
database.
The recovery model determines how SQL Server handles transactions and backups. There
are three recovery models in SQL Server: Simple, Full, and Bulk-Logged. Simple recovery
model does not allow for point-in-time recovery and only allows for restoring the latest
backup. Full and Bulk-Logged recovery models allow for point-in-time recovery but
require more frequent backups to be taken.

 Application of database server updates

Database server updates are essential for maintaining the performance and stability of the
database server. These updates often include bug fixes, feature enhancements, and
performance improvements. It is important to apply these updates regularly to ensure that
the database server is running efficiently and is free from vulnerabilities.

Security updates

Security updates are critical updates that address security vulnerabilities in the database
server. These updates are essential to protect the database server from malicious attacks,
data breaches, and other security threats. Security updates are typically released as soon as
possible after a vulnerability is discovered, and it is important to apply these updates as
soon as possible to minimize the risk of a security breach.

Critical updates

Critical updates are updates that address critical issues in the database server. These issues
may include performance problems, data corruption, or other critical issues that can cause
the database server to fail or become unstable.

 Test database server functionality and configuration correctness

To test database server functionality and configuration correctness, you can perform the
following steps:

1. Check server logs: Check the server logs for any errors or warnings related to the
configuration or functionality of the database server. This can provide you with
insight into any issues that might be affecting the performance of the server.

2. Monitor server resources: Monitor the server resources such as CPU, memory, and
disk usage to ensure that the server has enough resources to handle the workload. If
the server is running low on resources, this can cause performance issues.

3. Test database connectivity: Test the database connectivity to ensure that clients can
connect to the server and perform operations on the database. You can use a

81
 database client tool such as SQL Server Management Studio or MySQL
Workbench to test the connectivity.

4. Test database functionality: Test the functionality of the database by performing

basic operations such as inserting, updating, and deleting data. This can help you
identify any issues with the database schema or data integrity.

5. Test query performance: Test query performance by running queries against the
database and measuring the time it takes to return the results. You can use database
profiling tools such as SQL Server Profiler or MySQL Query Analyzer to analyze
the performance of queries and identify any bottlenecks.

Query optimization

To optimize queries, you can perform the following steps:

 Use indexes: Indexes can help speed up query performance by allowing
the database to quickly find the data that is being queried. Make sure
that you have the appropriate indexes created for the tables that are
being queried.
 Use parameterized queries: Parameterized queries can help prevent SQL
injection attacks and improve query performance by allowing the
database to reuse query plans.
 Use stored procedures: Stored procedures can help improve query
performance by allowing the database to reuse query plans and reducing
network traffic.
 Optimize query structure: Optimize the structure of your queries by
using efficient joins, filtering conditions, and aggregations.

Theoretical learning Activity

 Ask trainees to brainstorm about database server updates

Practical learning Activity

 Trainees will be given practical exercises to locate database server installed
features

Points to Remember (Take home message)

 SQL Server Management Studio (SSMS)
 Security updates
 Critical updates

82
  Query optimization

Learning outcome 1 formative assessment

Written assessment
Q1. Define the following terms:
1) Security updates
2) Critical updates
Answer:
1) Security updates are critical updates that address security vulnerabilities in the
database server. These updates are essential to protect the database server from
malicious attacks, data breaches, and other security threats.
2) Critical updates are updates that address critical issues in the database server. These
issues may include performance problems, data corruption, or other critical issues that
can cause the database server to fail or become unstable.
Q2. Answer with True or False
a) The SQL Server features discovery report provides information about the features
installed and enabled on the SQL Server instance.
b) The SQL Server features discovery report can be generated using SQL Server
Management Studio.
c) The SQL Server features discovery report includes information about the usage of
each feature.
d) The SQL Server features discovery report can be used to identify features that are not
compatible with the current version of SQL Server.
e) The SQL Server features discovery report can be customized to include additional
information about the SQL Server instance.
f) The SQL Server features discovery report can only be generated by a user with
administrative privileges on the SQL Server instance.
Answer:
a) The SQL Server features discovery report provides information about the features
installed and enabled on the SQL Server instance. True
b) The SQL Server features discovery report can be generated using SQL Server
Management Studio. True
c) The SQL Server features discovery report includes information about the usage of
each feature. False
d) The SQL Server features discovery report can be used to identify features that are not
compatible with the current version of SQL Server. True
e) The SQL Server features discovery report can be customized to include additional
information about the SQL Server instance. False

83
 f) The SQL Server features discovery report can only be generated by a user with
administrative privileges on the SQL Server instance. True
Q3. Which of the following is a valid method for monitoring a database server?
A. Using performance monitoring tools
B. Reviewing server logs
C. Setting up alerts for critical events
D. All of the above
Answer: D

Please mix different assessment tools for triangulation and relevancy of

assessment
Practical assessment
 Assessment tools
 Assay
 Task to be performed
 Observation checklist
Learning outcome 4.2: Document database server installation process
review

Duration:3 hrs

Learning outcome 2 objectives :

By the end of the learning outcome, the trainees will be able to:
1. Develop properly the guidelines for configuring database server as used in Document
database server installation process review
2. Review clearly the database server release notes as used in Document database server
installation process review.
3. Describe clearly the of database server installation wizard procedures as used in
Document database server installation process review
4. Write properly the technical journal and recommendation as used in document database
server installation process review.

Resources
Equipment Tools Materials
 Computer  Database server setup  Internet
 Projector  Electricity
 Paper

84
  Pen

Advance preparation:
- Database server setup
- Computers
- Projector
- Internet
- Books

Indicative content 4.2.1: Document database server

installation process review

Summary for the trainer related to the indicative content (key notes using bullets such
as ticks etc)

 Development of the guidelines for configuring database server

Operational guides
Operational guides in configuration of database server refer to a set of instructions or best
practices that help optimize the performance, security, and reliability of a database server.

Some common operational guides for configuring a database server include:

 Hardware and software requirements: This includes specifying the minimum

and recommended hardware and software requirements for the database server,
such as CPU, RAM, disk space, and operating system.
 Security considerations: This includes guidelines for securing the database
server, such as setting up user accounts and permissions, configuring firewalls,
and implementing encryption.
 Database design: This includes recommendations for designing and structuring
databases for optimal performance, such as defining indexes and partitions, and
selecting appropriate data types.
 Backup and recovery: This includes setting up backup and recovery procedures
to ensure that data can be recovered in the event of a disaster or system failure.

 Monitoring and optimization: This includes ongoing monitoring and

optimization of the database server, such as identifying and resolving
performance bottlenecks, tuning database parameters, and maintaining system
logs.

85
 Training manuals

Training manuals for configuring a database server typically provide instructions and
guidelines on how to set up and maintain a database server. They may cover topics such as
installation and configuration of the database software, database schema design, backup
and recovery procedures, performance tuning, and security considerations.

These manuals are designed to help database administrators and other IT professionals
understand the details of configuring and managing a database server. They may include
step-by-step instructions, screenshots, and examples to illustrate how to perform various
tasks.

A well-written training manual can be an essential resource for ensuring that the database
server is properly configured, secure, and optimized for performance. It can also be a
useful reference tool for troubleshooting issues that may arise during the operation of the
server.

 Review of database server release notes

Hardware and software requirements
This includes specifying the minimum and recommended hardware and software
requirements for the database server, such as CPU, RAM, disk space, and operating
system.

Getting started with database server

Reviewing the release notes of a database server can be a helpful way to get started with a
new version of the database server. Here are some steps you can follow:

 Identify the new features and changes: Review the release notes carefully to
identify the new features and changes that have been introduced in the new version
of the database server. Pay attention to any changes in the syntax of SQL
commands or any new options that have been added.

 Verify compatibility: Check if the new version of the database server is compatible
with your existing applications, libraries, and drivers. If there are any compatibility
issues, you may need to modify your code or upgrade your drivers.

 Plan the upgrade process: Plan the process of upgrading to the new version of the
database server. Consider factors such as downtime, backup and recovery
procedures, and the impact on your users.

 Perform a test upgrade: Before performing the actual upgrade, perform a test
upgrade in a non-production environment. This will help you identify any issues or
conflicts that may arise during the upgrade process.

Updated compiler

In the release notes of a database server, an updated compiler may be mentioned as a new
feature or improvement. It is important to review the release notes carefully to understand
the changes made to the compiler, as these changes may impact the performance or

86
behavior of the database server.

Some potential benefits of an updated compiler may include faster query processing times,
improved memory management, better support for new programming languages or
technologies, and increased security.

 Description of database server installation wizard procedures

Add features to database server instance

To add features to a database server instance, follow these steps:

1. Open the database management console or control panel.

2. Choose the database server instance that you want to add features to.
3. Look for the option to add new features or components, usually under the
"Installation" or "Configuration" menu.
4. Select the features that you want to add from the list provided.
5. Follow the prompts to install the selected features.
6. Wait for the installation process to complete.
7. Verify that the new features have been added and configure them as needed.

Repair a failed feature

If a feature in the database server has failed, you can try repairing it by following these
steps:

1. Open the database management console or control panel.

2. Choose the database server instance that has the failed feature.
3. Look for the option to repair the installation or configuration, usually under the
"Maintenance" or "Repair" menu.
4. Select the option to repair the failed feature or component.
5. Follow the prompts to complete the repair process.
6. Wait for the repair process to complete.
7. Verify that the failed feature has been repaired and is functioning correctly. If not,
consider reinstalling or troubleshooting the issue further.

 Write technical journal and recommendation

Sections of technical journal
 Introduction to the problem
In technical journal, we review the installation process of a document database server. We
focus on the material and methods used during the installation, the results obtained, and the
discussions that follow.

 Material and methods to solve the problem

We used a set of instructions provided by the document database server vendor to install
the software on an operating system. We followed the instructions step-by-step, noting any
deviations from the instructions or issues encountered during the installation process.
 Results

The installation process was successful, and the database server was installed on the
operating system. We tested the functionality of the server by performing basic CRUD

87
(Create, Read, Update, Delete) operations on a sample database. The server responded
promptly to the queries, indicating that the installation was successful.

 Discussions
The installation process was straightforward, and the provided instructions were clear and
easy to follow. However, we encountered some issues during the installation process, such
as missing dependencies and outdated package versions. We had to troubleshoot these
issues by consulting online forums and documentation, which added to the installation
time.

We recommend that the vendor provides a list of system requirements and dependencies
needed for the installation process, as this would make the process more seamless for users.
Additionally, the instructions could be updated to include troubleshooting tips for common
issues encountered during the installation process.

 Acknowledgements to supporters
We would like to acknowledge the technical support provided by the vendor's customer
support team during the installation process.

Theoretical learning Activity

 Ask trainees to brainstorm about guidelines to configure database server

Practical learning Activity

 Trainees will be given practical exercises on technical journal
 Trainees will be given practical exercises on new feature addition and repair a
failed feature

Points to Remember (Take home message)

 Operational guides
 Training manuals
 Hardware and software requirements
 Compatibility
 Updated compiler

Learning outcome 2 formative assessment

88
Written assessment
Q1. Outline all steps used to add features to database server instance.
Answer:
a) Open the database management console or control panel.
b) Choose the database server instance that you want to add features to.
c) Look for the option to add new features or components, usually under the
"Installation" or "Configuration" menu.
d) Select the features that you want to add from the list provided.
e) Follow the prompts to install the selected features.
f) Wait for the installation process to complete.
g) Verify that the new features have been added and configure them as needed.
Q2. If the server logs show an error related to a specific database file, what should you
do?
A. Delete the file and recreate it.
B. Restore the file from a backup.
C. Ignore the error and try starting the server again.
D. Reinstall the database software.
Answer: B. Restore the file from a backup.
Q3. Answer with True or False the following statements:
a) Reviewing database server release notes requires only software, not any hardware.
b) The hardware and software requirements for reviewing database server release notes
are the same as those for actually running the database server.
c) Reviewing database server release notes requires a web browser to access the release
notes online.
d) There are no specific software requirements for reviewing database server release
notes.
e) The hardware and software requirements for reviewing database server release notes
vary depending on the specific database server being used.
Answer:
a) Reviewing database server release notes requires only software, not any hardware.
False
b) The hardware and software requirements for reviewing database server release notes
are the same as those for actually running the database server. False
c) Reviewing database server release notes requires a web browser to access the release
notes online. True
d) There are no specific software requirements for reviewing database server release
notes. False
e) The hardware and software requirements for reviewing database server release notes
vary depending on the specific database server being used. True

89
 Please mix different assessment tools for triangulation and relevancy of
assessment
Practical assessment
 Assessment tools
 Task to be performed
AB Ltd. is one of countrywide Construction Company. The company has a problem of
having a centralized database server to manage employees and their payrolls, business project
contracts and assets.
As a database developer, you are hired to setup a SQL server 16 version 13.0.1601.5 on the
following environment:
- Operating System: Windows server 2016
- RAM: 8GB
- Processor: 1.4 GHz 64-bit processor
- Compatible with x64 instruction set
- Supports NX and DEP
- Supports CMPXCHG16b, LAHF/SAHF, and PrefetchW
- Supports Second Level Address Translation (EPT or NPT)
Write technical journal and recommendation of Microsoft SQL server 2016 installation.
The task must be completed in 4hours.
 Observation checklist
Checklist Score
Yes No
 Introduction to the problem
 Material and methods to solve the problem
 Results
 Discussions
 Acknowledgements to supporters
Observation

Learning outcome 4.3: Report procedures of the tasks accomplished

in accordance with the installation to be done.

Duration:4 hrs

90
 Learning outcome 3 objectives:
By the end of the learning outcome, the trainees will be able to:
1. Review clearly installed database server and previous versions according to the Report
procedures of the tasks accomplished
2. Suggest clearly the database server improvements according to the Report procedures of
the tasks accomplished
3. Describe the database server solutions and benefits according to the Report procedures of
the tasks accomplished
4. Propose properly the new technology of database server according to the Report
procedures of the tasks accomplished

Resources
Equipment Tools Materials
 Computer  Database server setup  Internet
 Projector  Electricity
 Paper
 Pen

Advance preparation:
- Database server setup
- Computers
- Projector
- Internet
- Books

Indicative content 4.3.1: Report procedures of the tasks

Summary for theaccomplished
trainer related tointheaccordance
indicative content
with(key
the notes using bullets
installation to besuch
done
as ticks etc)

 Review of installed database server and previous versions

To review the installed database server and previous versions, you can follow these steps:

91
  Check the system documentation: Check the documentation of your operating
system to find out which database servers are installed by default.

 Check the installation directory: Check the installation directory of the database
server to find out which version is installed. This information can usually be
found in the readme file or the documentation.

 Check the system environment variables: Check the system environment

variables to see if the database server is included in the PATH variable. This
will give you a clue about which version of the database server is being used.
 Use command line tools: Use command line tools to check the version of the
database server. For example, for MySQL, you can use the command "mysql --
version" to check the version of the MySQL server installed on your system.
 Check the database logs: Check the database logs to see which version of the
database server is running. The version information is usually included in the
log files.
 Check the database management tools: If you are using a database management
tool like phpMyAdmin, you can check the server version in the user interface.

 Suggestions on database server improvements

There are several suggestions for improving database server performance and reliability

 Upgrade hardware: Upgrading the hardware on the server can improve

performance. For example, upgrading the CPU, RAM, or storage can increase
processing power, memory capacity, and storage space respectively.

 Optimize database design: Properly designed databases can improve

performance and reliability. Normalize the database structure, use appropriate
data types, and avoid redundant data.

 Use indexes: Indexes speed up queries by allowing the database server to

quickly find the data it needs. Ensure that the appropriate columns are indexed.

 Optimize queries: Optimize queries to minimize the number of database

accesses and reduce the amount of data returned. Use join operations only when
necessary and avoid using wildcard characters in searches.

 Use caching: Use caching to reduce the number of database accesses. Caching
stores frequently accessed data in memory, which reduces the time required to
access the database.

 Optimize storage: Optimize the storage system to ensure that the database
server can access data quickly. Use RAID or other fault-tolerant storage
systems to prevent data loss.

 Implement backup and recovery procedures: Implement backup and recovery

procedures to ensure that data is not lost due to hardware failures or other
issues. Regularly backup data to a separate physical or cloud storage location.

92
  Implement security measures: Protect the database server from unauthorized
access, ensure that sensitive data is encrypted, and implement appropriate
access controls.

 Monitor performance: Regularly monitor the performance of the database

server to identify potential issues and optimize performance. Use monitoring
tools to track server resources, query performance, and other metrics.

 Description of database server solutions and benefits

There are various database server solutions available, including:

1. Relational database management system (RDBMS): This type of database server

uses tables to store data and uses SQL (Structured Query Language) to retrieve and
manipulate data. Popular RDBMS solutions include Oracle, MySQL, and Microsoft
SQL Server.

2. NoSQL database management system: This type of database server is designed to

handle large volumes of unstructured and semi-structured data, and can be more
flexible and scalable than RDBMS solutions. Popular NoSQL solutions include
MongoDB, Cassandra, and Redis.

3. Cloud database solutions: These are database servers that are hosted in the cloud,
and can provide benefits such as automatic backups, scalability, and easy
accessibility from anywhere with an internet connection. Examples of cloud
database solutions include Amazon RDS, Google Cloud SQL, and Microsoft Azure
SQL Database.

Benefits of using a database server solution include:

 Data organization: A database server can help organize data in a structured way,
making it easier to search, retrieve, and analyze data.

 Data security: A database server can provide data security features, such as access
control, encryption, and data backups, which can help protect against data breaches
and loss.

 Scalability: Many database server solutions can scale to handle large volumes of
data and high traffic, which can be important for growing businesses.

 Improved performance : A well-designed database server can improve the

performance of applications that rely on the database, by optimizing queries and
reducing latency.

 Collaboration: A database server can facilitate collaboration among multiple users,

by allowing multiple users to access and update data simultaneously.

 Propositions on database server

Database server alternatives

93
There are various alternatives to traditional relational database servers, including NoSQL
databases, graph databases, and object-oriented databases.

 NoSQL databases are designed to handle large volumes of unstructured or semi-

structured data, and they provide high availability and scalability.
 Graph databases are optimized for storing and querying relationships between data
entities, such as social networks or recommendation systems.
 Object-oriented databases store data as objects, which can be easily manipulated
and extended, making them suitable for complex data structures.

New technologies

 Blockchain technology is becoming increasingly popular for storing and managing

decentralized and immutable data.
 In-memory databases store data in memory instead of on disk, providing faster
access and processing times.
 Serverless databases, such as AWS DynamoDB or Google Firestore, allow
developers to build applications without worrying about server infrastructure.
 Multi-cloud databases, such as CockroachDB, can replicate data across multiple
cloud providers, ensuring high availability and resilience.

Theoretical learning Activity

 Ask trainees to brainstorm about current database server and previous versions

Points to Remember (Take home message)

 System documentation
 Directory
 Command line tools
 Database management tools
 Caching
 Relational database management system
 Cloud database solutions
 Data organization
 Data security
 Blockchain
 Serverless databases

94
 Learning outcome 3 formative assessment
Written assessment
Q1. Give three (3) database server alternatives.
Answer:
 NoSQL databases
 Graph databases
 Object-oriented databases
Q2. List down four (4) database server new technologies.
Answer :
 Blockchain technology
 In-memory databases
 Serverless databases
 Multi-cloud databases
Q3. What is a benefit of using a database server solution ?
a. Improved security
b. Increased processing speed
c. Better data organization
d. All of the above
Answer: d. All of the above

Please mix different assessment tools for triangulation and relevancy of

assessment

References:
1. https://www.ibm.com/topics/three-tier-architecture
2. https://www.sqlshack.com/how-to-install-sql-server-2012-express-edition/
3. https://www.techtarget.com/searchdatamanagement/definition/database
4. https://www.cliffsnotes.com/file/188815761/Unit-9-Database-administration-and-
securitypdfdocx/
5. https://freecontent.manning.com/wp-content/uploads/backup-types.pdf
6. https://sqlcoffee.com/SQLServer2012_0010.htm
7. https://www.tutorialspoint.com/ms_sql_server/ms_sql_server_tutorial.pdf

95