Server-Side Configuration in DBMS
Server-side configuration means all the settings, files, memory structures, storage locations, and
security rules that must be set up on the database server so that the DBMS works efficiently.
These settings affect how the database starts, runs, stores data, manages users, and handles
client requests.
1. What is a Database Server?
A database server is a powerful computer system that is responsible for storing and managing
databases. It includes:
a. Stores the Database
All the actual data is saved in special files called datafiles, log files, and control files.
b. Runs the DBMS Software
The server runs database software such as:
MySQL Server
Oracle Database
Microsoft SQL Server
This software manages storage, memory, users, and transactions.
c. Accepts Requests from Clients
Clients (applications, websites, users, etc.) send SQL queries to the database server.
The server receives these requests, processes them, and returns results.
d. Performs All SQL Operations
The server handles:
SELECT (read data)
INSERT (add data)
UPDATE (modify data)
DELETE (remove data)
e. Manages Resources Internally
It manages:
Memory
CPU
Storage
Connection sessions
Security
This makes sure the database runs fast and safely.
2. What is Server-Side Configuration?
Server-side configuration refers to all backend settings a DBA manages to make the DBMS
function properly.
Why it is Needed?
To ensure:
Faster performance
High-level security
Efficient use of memory and disk
Stable database behavior
Proper management of client connections
Reliable backups and recovery
Who Does It?
A Database Administrator (DBA) is responsible for configuring and maintaining the server.
3. Key Components of Server-Side Configuration
Below are the important server-side configurations that every DBMS requires:
A. Memory Configuration
Memory is one of the most important components of DBMS performance.
Databases use RAM to store frequently accessed information, which makes queries much faster.
Memory Configuration in MySQL
1. Buffer Pool
Stores frequently used data pages.
Improves speed by reducing disk reads.
The larger the buffer pool, the faster the database.
Parameter Example:
innodb_buffer_pool_size = 2G
2. Query Cache (Older MySQL versions)
Stores results of queries that are repeated often.
Speeds up performance for repetitive SELECT statements.
query_cache_size = 256M
3. Sort Buffer
Used whenever MySQL needs to sort data during queries.
sort_buffer_size = 4M
Memory Configuration in Oracle
Oracle divides memory into two major areas:
1. SGA – System Global Area
Shared memory used by all users and processes.
Includes:
Shared pool
Buffer cache
Redo log buffer
Large pool
2. PGA – Program Global Area
Memory used by a single user session.
Handles:
Sorting
Hashing
Session data
B. Storage Configuration
Storage configuration controls how and where the database stores its physical files.
Important Storage Components:
1. Data Files
Contain actual tables, indexes, and user data.
2. Log Files
Record all changes for recovery.
3. Temporary Files (TEMP)
Used for sorting and temporary tables.
4. Undo Files
Store “before images” so transactions can be rolled back.
MySQL Example:
datadir = /var/lib/mysql
innodb_data_file_path = ibdata1:1G:autoextend
Oracle Storage Files:
Datafiles → store actual data
Redo logs → store transaction logs
Control files → maintain database structure
C. Network Configuration
This determines how users and applications connect to the database.
Key Network Settings:
1. Hostname
The server’s name or IP address.
2. Port Number
Unique communication port:
MySQL → 3306
Oracle → 1521
SQL Server → 1433
3. Maximum Connections
Controls how many users can connect simultaneously.
max_connections = 200
4. Timeout Settings
Disconnect idle or slow connections to save resources.
connect_timeout = 30
Oracle Listener ([Link])
The listener accepts client connections and routes them to the correct Oracle instance.
D. User & Security Configuration
Security is a critical part of server-side configuration.
Main Security Tasks:
1. Create and manage user accounts
2. Set passwords and authentication rules
3. Assign privileges
4. Protect data from unauthorized access
5. Configure encryption
MySQL Example:
CREATE USER 'sadia'@'localhost' IDENTIFIED BY '12345';
GRANT ALL PRIVILEGES ON school.* TO 'sadia'@'localhost';
Security Options Include:
SSL encryption for client connections
Password expiration rules
Account lockout policies
Blocking unknown IP addresses
Limiting remote login
E. Logging & Monitoring Configuration
Logging helps DBAs track problems and performance issues.
Types of Logs:
1. Error Log
Shows server start/stop, errors, crashes.
2. Slow Query Log
Helps identify slow-running SQL queries.
slow_query_log = 1
long_query_time = 2
3. Binary Logs / Redo Logs
Used for:
Recovery
Replication
Point-in-time restore
Monitoring Tools:
MySQL Workbench
Oracle Enterprise Manager
SQL Server Profiler
F. Backup & Recovery Configuration
Backups protect data from accidental loss, system failure, or corruption.
MySQL Backup Tools:
mysqldump (logical backup)
Binary logs
Physical file copy
Example:
mysqldump -u root -p school > school_backup.sql
Oracle Backup Tools:
RMAN (Recovery Manager)
Data Pump export/import
G. Performance Configuration
Performance tuning ensures the database runs efficiently even with many users.
Common Performance Settings:
1. Caching
Stores frequently accessed data.
2. Index Configuration
Indexing speeds up searches.
3. Query Optimization
Using EXPLAIN or execution plans.
4. Thread/Process Management
Example:
thread_cache_size = 100
table_open_cache = 2000
5. Storage Optimization
Proper tablespace and datafile allocation.
4. Why Server-Side Configuration is
Important?
Proper configuration ensures:
Stable database operation
Fast query processing
Secure data access
Efficient resource usage
High reliability and uptime
Faster response for applications
Reduced chances of crashes or data corruption
It is a core responsibility of every DBA to configure and maintain the server correctly.