A High Level Comparison Between Oracle and SQL Server – Part III

Start-up
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
procedure

·        
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.

Changing
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
or file.

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).

sp_configure
‘max server memory’

For Oracle, an initialisation parameter can
be found either by executing the SHOW PARAMETER command or viewed from
the Enterprise Manager Database Control.

show
parameter sga_target;


Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |