Diving into Databases

At dbi services, we have a few people passionate about diving. Let me take this opportunity to let you dive into the two databases lakes Oracle and SQL Server to see their similarities and differences. For this first level of diving (named ODD), we will not be going too deep for reasons of narcosis or databases toxicity. As always, we will start with a short briefing to present the environment and the different themes. Have a nice dive !
Briiiefing! 😀
Microsoft SQL Server runs on Microsoft Windows while Oracle runs on Microsoft platform as well as Linux / Unix like and several others (for instance VMS). In this article, we used Oracle 11gR2 and SQL Server 2008 R2 for the comparison.

Note that our objective here is not to tell you which one is the best but just to pin out the differences. The choice then is yours!
We will provide an overview of the architecture, installation, roles installed by default, starting procedure, log and trace files.

Architecture

The basic architecture is the same for both. These are management systems relational database (RDBMS) client-server that uses a structured query language, to pass requests between a client or a client application and the  database server.

They can manage two types of databases:

• The transactional databases or OLTP (OnLine Transaction Processing)
• The decision databases or OLAP (Online Analytical Processing)

Even if the architecture details are different, the global principle is the same.

Here is a glossary for the various terms used in the database made ​​by one of our consultants, Antonio De Santo :

	Oracle : more detail on http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/dist_pro.htm#CNCPT9852
	Sql Server: more detail on http://msdn.microsoft.com/en-us/library/cc280361.aspx

Installation

The two environmments have a wizard for the installation :

• Oracle Universal Installation (OUI) is a program used to install Oracle software and database options
• The SQL Server Installation Wizard provides a single feature tree for installation of all SQL Server components

	Oracle : more information about OUI on http://download.oracle.com/docs/cd/B10501_01/em.920/a96697/oui.htm
	Sql server : Prerequisites on http://msdn.microsoft.com/en-us/library/bb500442.aspx

Default roles

Such as in diving skills, Goldfish, Sharks or Instructors, there are different levels that are defined in the two databases engines called roles.

Predefined Oracle roles (depending on installed options) :

• ADM_PARALLEL_EXECUTE_TASK : Members can update table data in parallel by using the DBMS_PARALLEL_EXECUTE PL/SQL package.
• AQ_ADMINISTRATOR_ROLE : Members can administer Advanced Queuing.
• AQ_USER_ROLE : Obsolete, but kept mainly for release 8.0 compatibility.
• AUTHENTICATEDUSER : Used by the XDB protocols to define any user who has logged in to the system.
• CAPI_USER_ROLE : Members can access to packages used for implementing Information Lifecycle Management (ILM) and hierarchical storage and other applications.
• CONNECT : Members can have the CREATE SESSION system privilege.
• CSW_USR_ROLE : Members can have user privileges to manage the Catalog Services for the Web (CSW) component of Oracle Spatial.
• CTXAPP : Members can create Oracle Text indexes and index preferences, and to use PL/SQL packages.
• CWM_USER : Members can manage Common Warehouse Metadata (CWM), which is a repository standard used by Oracle data warehousing and decision support.
• DATAPUMP_EXP_FULL_DATABASE : Members can export data from an Oracle database using Oracle Data Pump.
• DATAPUMP_IMP_FULL_DATABASE : Members can import data into an Oracle database using Oracle Data Pump.
• DBA : Members can have all system privileges that were created with the ADMIN option.
• DELETE_CATALOG_ROLE : Members can have the DELETE privilege on the system audit table (AUD$).
• EJBCLIENT : Members can connect to EJBs from a Java stored procedure.
• EXECUTE_CATALOG_ROLE : Members can have the EXECUTE privileges on objects in the data dictionary.
• EXP_FULL_DATABASE : Members can have the privileges required to perform full and incremental database exports using the Export utility (later replaced with Oracle Data Pump).
• GATHER_SYSTEM_STATISTICS : Members can update system statistics, which are collected using the DBMS_STATS.
• GLOBAL_AQ_USER_ROLE : Members can establish a connection to an LDAP server, for use with Oracle Streams AQ.
• HS_ADMIN_EXECUTE_ROLE : Members can have the EXECUTE privilege for users who want to use the Heterogeneous Services (HS) PL/SQL packages.
• HS_ADMIN_ROLE : Members can have privileges to both use the Heterogeneous Services (HS) PL/SQL packages and query the HS-related data dictionary views.
• HS_ADMIN_SELECT_ROLE : Members can have privileges to query the Heterogeneous Services data dictionary views.
• IMP_FULL_DATABASE : Members can have privileges required to perform full database imports using the Import utility (later replaced with Oracle Data Pump).
• JAVADEBUGPRIV : Members can run the Oracle Database Java applications debugger.
• JAVASYSPRIV : Members can have the major permissions to use Java2, including updating Oracle JVM-protected packages.
• JAVAUSERPRIV : Members can have the limited permissions to use Java2.
• JAVA_ADMIN : Members can have administrative permissions to update policy tables for Oracle Database Java applications.
• JAVA_DEPLOY : Members can deploy ncomp DLLs into the javavm/admin directory using the ncomp and deployns utilities.
• JMXSERVER : Members can start and maintain a JMX agent in a database session.
• LBAC_DBA : Members can use the SA_SYSDBA PL/SQL package.
• LOGSTDBY_ADMINISTRATOR : Members can manage the SQL Apply (logical standby database) environment.
• MGMT_USER : Members can have the SELECT privilege on the different views used for the SYSMAN schema.
• OEM_ADVISOR : Members can have privileges to create, drop, select (read), load (write), and delete a SQL tuning set through the DBMS_SQLTUNE PL/SQL package, and to access to the Advisor framework using the ADVISOR PL/SQL package.
• OEM_MONITOR : Members can have privileges needed by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.
• OLAPI_TRACE_USER : Members can privileges to perform OLAP API tracing. Contact Oracle Support for more information.
• OLAP_DBA : Members can have administrative privileges to create dimensional objects in different schemas for Oracle OLAP.
• OLAP_USER : Members can have application developers privileges to create dimensional objects in their own schemas for Oracle OLAP.
• OLAP_XS_ADMIN : Members can have privileges to administer security for Oracle OLAP.
• ORDADMIN : Members can have privileges to administer Oracle Multimedia DICOM.
• OWB$CLIENT : Members can privileges to perform standard client-related tasks for Oracle Warehouse Builder, such as creating projects, modules, tables, views, maps, and so on.
• OWB_DESIGNCENTER_VIEW : Members can have privileges from the database level for any registered Oracle Warehouse Builder user to query the Warehouse Builder public views, such as ALL_IV_PROJECTS.
• OWB_USER : Members can have privileges to create and own an Oracle Warehouse Builder workspace.
• RECOVERY_CATALOG_OWNER : Members can have privileges for owner of the recovery catalog.
• RESOURCE : Members can have the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.
• SCHEDULER_ADMIN : Members can execute the procedures of the DBMS_SCHEDULER package.
• SELECT_CATALOG_ROLE : Members can have SELECT privilege on objects in the data dictionary.
• SNMPAGENT : Used by the Enterprise Manager Management Agent.
• SPATIAL_CSW_ADMIN : Members can administrative privileges to manage the Catalog Services for the Web (CSW) component of Oracle Spatial.
• SPATIAL_WFS_ADMIN : Members can administrative privileges to manage the Web Feature Service (WFS) component of Oracle Spatial.
• WFS_USR_ROLE : Members can have user privileges for the Web Feature Service (WFS) component of Oracle Spatial.
• WM_ADMIN_ROLE : Members can have administrative privileges for Oracle Workspace Manage.
• XDBADMIN : Members can register an XML schema globally, as opposed to registering it for use or access only by its owner.
• XDB_SET_INVOKER :Members can define invoker’s rights handlers and to create or update the resource configuration for XML repository triggers.
• XDB_WEBSERVICES : Members can access Oracle Database Web services over HTTPS.
  •XDB_WEBSERVICES_OVER_HTTP : Members can access Oracle Database Web services over HTTP.
• XDB_WEBSERVICES_WITH_PUBLIC : Members can access to public objects through Oracle Database Web services.

Predefined SQL server roles :

There are define in 2 categories :

• Database roles : Defined at the database level and exist in each database.
• Server roles : Defined at the server level and exist outside of users databases.

Predefined database roles :

• db_accessadmin: Members can manage Windows groups and SQL Server logins.
• db_backupoperator: Members can back up the database.
• db_datareader: Members can read all data.
• db_datawriter: Members can add, delete, or modify data in the tables.
• db_ddladmin: Members can run dynamic-link library (DLL) statements.
• db_denydatareader: Members can’t view data within the database.
• db_denydatawriter: Members can’t change or delete data in tables or views.
• db_owner: Members have full access.
• db_securityadmin: Members can modify role membership and manage permissions.
• public: The public role sets the basic default permissions for all users. Every user that’s added to SQL Server is automatically assigned to the public role.
• dbm_monitor : This role is only created in the msdb database when the first database is registered in Database Mirroring Monitor.

Predefined Server roles:

• bulkadmin: Any member can run the bulk insert command.
• dbcreator: Any member can create, alter, drop, and restore databases.
• diskadmin: Any member can manage SQL Server disk files.
• processadmin: Any member can kill processes running on SQL Server.
• public: The public role sets the basic default permissions for all users. Every user that’s added to SQL Server is automatically assigned to the public role.
• securityadmin: Any member can manage server security.
• serveradmin: Any member can set configuration options on the server.
• setupadmin: Any member can manage linked servers and SQL Server startup options and tasks.
• sysadmin: Any member can perform any action on the server.

	Oracle : http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/authorization.htm#BABCADIB
	Sql Server : for databases roles http://msdn.microsoft.com/en-us/library/ms189612.aspx and for Server roles : http://msdn.microsoft.com/en-us/library/ms175892.aspx

startup

Oracle is a multilevel startup but for sql server just a service startup is good to have an instance available.
For SQL server service use every time the sql Server Configuration Manager to start and stop.

	Oracle : For more information, see the article “Oracle Basics (3) – Database startup and shutdown” from David Hueber
	Sql Server : Boot options are available on http://msdn.microsoft.com/en-us/library/ms190737.aspx

Log and trace

During a dive, a diver use a dive computer that traces the dive profile and gives all the parameters. You use it, if you need to analyze the dive to see what happened when a problem occurs. In the database, for the same reason, we use the log and traces.

Oracle :

By default the server is configured to generate a log file (alert log), which centralizes the start, stops and main database activities. This log file is stored in the Automatic Diagnostic Repository (ADR), located by default in ORACLE_BASE/diag

The trace type specifies whether the trace is enabled for client identifier, session, service, database, or a combination of service, module, and action.
The trcsess utility consolidates trace output from selected trace files based on several criteria such as Session ID,Client ID,Service name,Action name and Module name.

SQL Server :

For the log, you can with Microsoft SQL Server Manament Studio open the Log File Viewer in the menu Management.
You can access to information about errors and events that are captured in the following logs:

• Audit Collection
• Data Collection
• Database Mail
• Job History
• Maintenance Plans
• Remote Maintenance Plans
• SQL Server
• SQL Server Agent
• Windows NT

2 methods are available for tracing :

• Microsoft SQL Server provides system stored procedures, Transact-SQL, to create traces on an instance of Database Engine SQL Server. These system stored procedures are provided to create traces manually from your own applications instead of using the SQL Server Profiler. You can write custom traces specific to your business needs.
• SQL Server Profiler is a tool that captures SQL Server events from a server. These events are saved in a trace file which can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. Use SQL Server Profiler to:
  • Monitor the performance of an instance of the SQL Server Database Engine, Analysis Server, or Integration Services (after they have occurred).
  • Debug Transact-SQL statements and stored procedures.
  • Analyze performance by identifying slowly executing queries.
  • Perform stress testing and quality assurance by replaying traces.
  • Replay traces of one or more users.
  • Perform query analysis by saving Showplan results.
  • Test Transact-SQL statements and stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected.
  • Troubleshoot problems in SQL Server by capturing events on a production system and replaying them on a test system. This is useful for testing or debugging purposes and allows users to continue using the production system without interference.
  • Audit and review activity that occurred on an instance of SQL Server. This allows a security administrator to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects.
  • Save trace results in XML to provide a standardized hierarchical structure to trace results. This allows you to modify existing traces or manually create traces and then replay them.
  • Aggregate trace results to allow similar event classes to be grouped and analyzed. These results provide counts based on a single column grouping.
  • Allow users who are not administrators to create traces.
  • Correlate performance counters with a trace to diagnose performance problems.
  • Configure trace templates that can be used for tracing later.

	Oracle : more detail about trace on http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/sqltrace.htm#PFGRF94977
	Sql Server : more detail about SQL server Profiler on http://technet.microsoft.com/en-us/library/ms187929.aspx

Debriefing

With this first dive in the databases, we can virtually certify you OPEN DATABASES DIVER (ODD) by dbi services.

It’s just the beginning of your database diver life… See you soon in the databases Big Blue :-D!