A High Level Comparison Between Oracle and SQL Server – Part III
and Configuration Parameters
When SQL Server is installed, a number of
Windows registry keys are also created. These registry keys specify various
parameters values required by the instance. For example, one registry key would
specify the location of the error log file; another would contain the location
of the default backup folder and so on. SQL uses these registry key values for
its ongoing operations. The SQLSERVR.EXE can also have a number of start-up
parameters attached to it, including trace flags. These add-on
parameters would control how the instance would behave when it is started.
Apart from start-up parameters and registry keys, SQL Server also has a large
number of internal configuration parameters that can be changed for fine-tuning
the instance. One example of these configuration parameters would be the “max
server memory” that specifies the maximum amount of memory allocated to the
instance. Needless to say, the server configuration parameters are also saved
in system metadata tables.
Changes to SQL Server configuration
parameters can be made by a number of ways:
Using the sp_configure system stored
Using the server properties dialogue box from
the Management Studio or Enterprise Manager
Using various facets (SQL 2008) or Surface
Area Configuration tool (SQL 2005)
For Oracle, configuration parameters come in
two vital operating system files that are needed for starting the instance and
opening the database. The first file is the initialisation parameter file
and the second is the control file. The initialisation parameter file
contains parameter values that dictate how the instance will be created. Bear
in mind that an Oracle instance is made up of memory structures and background
processes. Among other things, the parameter file contains values that tell
Oracle how much memory to allocate, how many user processes can connect to the
database concurrently or what database the instance will connect to. As Oracle
reads this file, it builds the instance in memory.
The parameter file can either be a plain
ASCII text file or a binary file. The static parameter file is called the pfile
and it has a name like init<SID>.ora where SID is the System
Identifier of the instance (see the section titled “Instance Names vs.
SIDs”). The binary parameter file or spfile, has a name like spfile<SID>.ora.
One big difference between the text-based pfile and binary spfile is that when
pfile is used, Oracle reads it only once during the instance start up. Any
subsequent changes to the parameter file would only take effect when the
instance is restarted. For spfile, Oracle can change the contents even when the
instance is running. At the time of this writing, Oracle 11g has more than 250 initialisation
parameters that can be set. Fortunately, the DBA does not need to specify a
value for each of them. Configuring only a dozen or so parameters may be sufficient
for a functional instance. Many of these parameter values would come from the
selections made when Oracle was installed. Changes to the initialisation parameter
can be made either manually or from the Oracle Enterprise Manager Database
Control (more on it later). In the manual mode, the ALTER SYSTEM
command is used for making changes to the spfile. If using pfile, the DBA can
edit the file using a text editor.
Control file is the second file Oracle reads
after it has started the instance. The location of the control file is also
specified as one of the parameters in the parameter file. The control file is a
small, binary file but contains critical information about the database,
including the location and the names of the data files and redo log files,
tablespace information and checkpoint information. Oracle uses the information
from the control file to locate its data files and open them for user access.
In fact it will not start the database if the control file is absent or
damaged. Due to the vital nature of the file, it is thus customary to have
multiple copies of the control file present for fault tolerance. Just like a
spfile, Oracle continuously accesses and updates the control file when the
database is open. When multiple control files are present, all copies are
updated at the same time.
Finally, a third type of file is also used by
the Oracle database: the password file. This file contains usernames and
passwords for user accounts which have been granted privilege to start up and
shutdown the database. More on this file later.
System Metadata and Configuration Parameters
In both SQL Server and Oracle, system
metadata tables, files, registry keys or configuration parameters are
automatically updated by the database engine as users and applications access
the database(s) and changes are made. For example, when a new table is created
in the database, the database engine adds a row to a system table; when a
parameter value is altered by the DBA, that change is captured in another table
For SQL Server, changes to the system
metadata can come from
DDL operation: creating, altering or dropping
databases and database objects
System configuration value change (either
using sp_configure or using the GUI)
DCL (Data Control Language) operation: when
logins, certificates, credentials, proxies or database users are created,
updated and dropped
SQL Server Configuration Manager: when SQL
Server or Agent service properties are changed
Server level changes: when linked servers,
endpoints, service broker components are created, modified or dropped
For Oracle, the changes to data dictionary,
control file or parameter file can come from:
DDL operations: tablespaces, data files or
database objects are created, altered or dropped.
DCL operations: user accounts are created,
altered or dropped; users are granted roles and privileges.
Initialisation parameters are changed using
the ALTER SYSTEM command or the Enterprise Manager Database Control.
Database state is changed using the
Enterprise Manager Database Control or the ALTER DATABASE command.
Backup operations are configured and run etc.
Finally, SQL Server configuration parameters
can be viewed either by executing the sp_configure system stored
procedure or from the server property dialogue box (for some parameters).
For Oracle, an initialisation parameter can
be found either by executing the SHOW PARAMETER command or viewed from
the Enterprise Manager Database Control.