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).
For Oracle, an initialisation parameter can be found either by executing the SHOW PARAMETER command or viewed from the Enterprise Manager Database Control.