David Bergmeier, Senior Database Consultant MGA

Introduction

This article seeks to introduce Postgres to the Oracle DBA. I will attempt to describe Postgres features using Oracle concepts and terminology. We will only consider the main community edition of Postgres – the free, open source one.

For a much closer Oracle experience with Postgres, have a look at Enterprise DB. Their version of Postgres has an Oracle compatibility layer that provides many features so that Oracle data and code (packages, procedures, triggers, etc.) can be easily converted. They also provide paid support options (for both EDB and the free Postgres) so arguments for not moving to Postgres due to “lack of support” are no longer valid.

Linux Operating System

Oracle only supports a limited number of Linux distributions, and in my experience, RedHat is the most commonly used. I will make a grand assumption that most Oracle DBAs working on Linux will be familiar with RedHat (RHEL-6 or RHEL-7). I will tend to favour RHEL-6. So if you’re an Oracle DBA looking to learn some Postgres, do yourself a favour and learn on a distro you’re already familiar with.

Postgres supports a much wider range of Linux distributions so it’s not unusual to see Ubuntu, Debian or FreeBSD. This article uses examples from Centos 6 to keep things familiar.

If you’re just starting out, I suggest you grab a copy of Oracle VirtualBox and setup a Centos 6 or 7 virtual machine. Postgres can run adequately (for learning) on a VM with as little as 1GB of RAM.

Software owner

Oracle is usually installed under a Linux account called “oracle” (ignoring databases running SAP systems) with primary group “oinstall”.

Postgres is usually installed under a Linux account called “postgres” with a primary group also called “postgres”. Bear in mind, the startup scripts assume this owner/group and don’t change the primary group to something else, like “dba”.

Installation directory

There’s a long tradition of installing Oracle into /u01/app/oracle/product//

Postgres traditionally goes into /var/lib/postgres
However, most RedHat (or similar) implementations I’ve seen generally use a site standard directory like /data or /app instead.

Home Directory

The Oracle installation procedure requires the “oracle” account to be created and the software is installed as “oracle”. Most sites will end up with the oracle account having a default home directory of /home/oracle

Postgres is often installed as root with a package manager (e.g. yum) which creates the postgres Linux account. This means the home directory is frequently /var/lib/postgres also.

As an Oracle DBA, I find this very annoying since I typically have a bunch of bash scripts, a crontab, and a bunch of SQL scripts to make life easy. I don’t like having these in the same directory as the database home. So creating the postgres user prior to installing the software means I can have a more familiar home directory /home/postgres which is different from the database location.

Incidentally, the $ORACLE_HOME equivalent is $PGDATA.

Multiple Homes

Oracle naturally supports multiple homes in the default directory structure. Postgres requires some forethought and planning and will be covered in a later article, but it is possible and quite easy with RedHat.

Configuration Files

Postgres has two main configuration files:
1. $PGDATA/postgresql.conf
This is the init.ora equivalent in text format.

2. $PGDATA/pg_hba.conf
There isn’t really an Oracle equivalent of this. This is the client authentication configuration file that determines who can access the database and how they authenticate. The default file limits connections to only those originating from localhost.

Note: some Linux distros put these files into /etc but RedHat distros are more like Oracle where the configs are with other database files rather than buried in the O/S config directories.

Startup Script

A standard Postgres startup script is automatically placed in /etc/init.d (Centos 6). There is strong temptation to modify this file to customize settings (like PGDATA or PGPORT) for a specific installation. This is a mistake. The proper solution is to create a file in /etc/sysconfig/pgsql/ with the same name as the startup script with the correct settings. This will be automatically sourced by the startup script and will override the default settings.

Terminology

Finally, we come to the terminology that describes the database. Postgres is a mix of both Oracle and Microsoft SQL Server.

The “postmaster” is equivalent to the main Oracle executable – in Linux it’s just called “oracle” but think of it as “oracle.exe”.

There’s no RAC equivalent in Postgres, so a “Postgres cluster” is just a single instance or a cluster of databases rather than a cluster of instances.

Inside a cluster, we have three or more databases (think SQL Server). There’s the default “postgres” database and two template databases, plus any databases created by the DBA.

Each database has one or more schemas (same as Oracle) but unlike Oracle, schemas have a separate namespace from users, so it’s possible to have a schema called “accounts” without the corresponding user of the same name. The default schema is called “public”.

Users in Postgres are implemented as roles with a login attribute. Normal roles (same as Oracle roles) are just the collection of grants but with no-login. This is probably why Postgres has not implemented any password expiry functionality. However, to lock a user account it’s simply a matter of changing the role from “login” to “nologin”. Users exist at the Postgres cluster level, but access may be restricted to only some databases.

Views are pretty standard in Postgres.

Stored code (packages, procedures and functions) are only implemented as functions in Postgres. A procedure is merely a function that returns zero or null.

Background Processes

An Oracle instance runs several background processes, each doing a specific task (e.g. SMON, PMON, DBWR, LOGWR, ARCH, RECO, etc.) Plus there’s the listener and a process per session (assuming no MTS – which I haven’t seen used in years).

Postgres also runs several background processes in much the same way. Here are the processes from my version 10 primary database:

• postmaster
• logger process
• checkpointer process
• writer process
• wal writer process
• autovacuum launcher process
• archiver process
• stats collector process
• bgworker: logical replication launcher

Postgres also launches a separate process per user session.

Transactions

Redo logs are called Write Ahead Logs or just WAL.

There are the online WAL files that are a uniform size but managed by Postgres – dynamically adding and removing files as required but within the settings of parameters min_wal_size and max_wal_size.

Then there are the archived WAL files. The big difference is that Oracle allow you simply specify a location for the archived logs. With Postgres, you need to provide the command (generally “cp” with the required arguments) to put the WAL copy into the desired location. Oracle provide all the code to do this plus the handling for any error conditions (like directory does not exist, or filesystem full, etc.). With Postgres you need to worry about all that yourself. Oracle generally tells you what the problem is, with Postgres, you must find it out for yourself.

Memory

Memory parameter setup is quite different between Oracle and Postgres. In Oracle (ignoring ASM) you typically set FILESYSTEMIO_OPTIONS to “setall” which enables both direct and asynchronous IO where possible. The buffer cache is generally sized quite high, sometimes as high as 60-80% of the available RAM.

Postgres is different and relies on the operating system IO buffers. So the general advice is to set the buffer cache (parameter shared_buffers) to 25% of the available RAM.

Storage

A basic (simplistic) storage hierarchy in Oracle starts with a table (non-partitioned). The table belongs to one tablespace. Between the table and the tablespace are many datafiles which can be spread across multiple filesystems. So, spreading a single table across many filesystems without partitioning is nothing special in Oracle.
Postgres is different. There is no equivalent concept of Oracle datafiles and a tablespace is implemented as a filesystem. Hence, there is no way to spread a single table across multiple filesystems. This means your largest table must be smaller than your largest filesystem. If that table grows, then you may need to grow the underlying filesystem.

Thankfully in Postgres version 10, partitioning finally got a decent implementation so the lack of datafiles is no longer the problem it used to be.

UNDO

Consistent reads and readers not blocking writers has long been one of the distinguishing features of Oracle. It is implemented by keeping prior versions of updated rows for in-flight SELECTs on a special “undo” tablespace.

Postgres undo data is stored within the relevant object. This is likely a much easier implementation but leads to table and index bloat. The solution is that a background activity named “vacuum” that removes the undo versions and releases the space for reuse. This is a necessary task and turning it off will result (eventually) in a hung database that won’t do anything until the vacuum has run.

Replication

Another recently new feature in Postgres is streaming replication. This is equivalent to Active Data Guard. The WAL records are streamed asynchronously (or synchronously if required) from primary and applied to a standby replica. The standby can be open for read only transactions.

Logical replication is also supported in Postgres. The WAL records are converted into standard SQL and applied to a read/write replica.

Versions

The default RedHat yum repository for many years only had Postgres version 8.4. If you wanted anything newer, you had to find a different yum repo or install from source. Version 8.4 was first released in July 2009. It is over 10 years old and no longer supported. It should be avoided at all costs.

There is enough new functionality and stability in versions 11 and 10 to recommend their use for production database environments. Of special interest are the partitioning and parallel query enhancements.

Other Postgres Tools

One of the benefits of open source software is that anyone can contribute. Whether that’s a direct contribution to the product, documentation, blogs or related products to enhance Postgres.

Postgres alone is arguably not quite “enterprisey” yet. However, there are a few related products released by the community that enhance Postgres and give it that extra boost in support, monitoring and functionality. Here are a few that I personally have found helpful.

• pgbadger
This is a utility that reads the Postgres alert logs and produces a html report. It’s possibly the closest thing to AWR for Postgres. It does require some of the logging parameters to be set a certain way so that the reports are meaningful.

• Repmgr
If you want to use Postgres streaming replication (think active Data Guard), I strongly recommend setting it up manually and trying some failovers. This will help you understand what it is and how it works. Then install repmgr and see how easy it can be managed. This tool is worth it just for the simple switchover functionality (swapping primary and standby roles).

• Barman
Also by the makers of repmgr – this tool is a simple backup and recovery manager. It works best when setup on a different server from the one you are backing up as it uses streaming replication to take the backup and backup the archive logs (archive WAL files). This allows it to achieve near-zero or zero data loss.

• check_postgres.pl
Vanilla Postgres does not have an OEM equivalent. I guess the attitude is that monitoring is a different problem for which there are numerous open source solutions already (Nagios, MRTG, Cacti, etc.) This perl script bundles a whole stack of Postgres checks into a simple script that easily plugs into these monitoring tools. It can even be used standalone with some simple custom-built bash wrapper scripts.

• pgpool
This is one of the more complex products to setup. It basically provides connection pooling for a streaming replication environment. All database connections are made to pgpool where writes get sent to the primary and reads may go to either the primary or standby. It provides automatic failover if the primary goes down unexpectedly and will promote a standby to be a new primary.
The pgpool servers are also clustered, so if the master pgpool server goes down, the slave can take over. It uses a floating IP address so client connections will only experience a short outage, typically less than 30 seconds.

The post Postgres for an Oracle DBA appeared first on Mark Gurry & Associates.

MGA is a database and data specialist company. We support a broad range of databases including migrating from one database to another, for example, Oracle to SQL Server. We are often approached by clients that have a desire to significantly reduce their database maintenance cost and are considering Postgres (PostgreSQL), MongoDB or other free or low cost databases. Migrating to SQL Server will often reduce cost, but our clients are after a significantly larger saving.

Many of the clients are currently running Oracle databases. Oracle is a well proven, high quality, highly secure, high performance and supported database. It has a broad range of capability such as spatial, XML support as well as tools that assist greatly with high availability, scalability and disaster recovery. This article details our findings in converting from Oracle to EDB Postgres and to Postgres.

EDB Postgres Features Compared to Oracle

EDB Postgres (EnterpriseDB) is an interesting database offering because it is designed to provide an easy migration off Oracle to its own proprietary database which is has many Oracle like features on top of a Postgres database. The migration does not only migrate the data, but also migrates the Oracle stored code. It provides most of the Oracle dictionary views as well as equivalents to Oracles stored procedure offerings such as DBMS_SCHEDULER.

So what is EDB Postgres missing compared to Oracle? EDB Postgres has no RAC, no Compression (Tables, Files, Network, Backups), no Advanced Compression, no Interval Partitioning (auto partition creation), no Partitioned Indexes, no Flashback, no In Memory Database, no Database Vault, no Distributed Transactions, no Apex, no XML DB, no Transportable Tablespaces, no Online Re-organisation, no Automatic Memory Management, no Automatic Storage Management, no Pipelined Functions and no Exadata.

Postgres Features Compared to Oracle and EDB Postgres

EDB Postgres (EnterpriseDB) comes with a license cost, albeit a significantly less cost than databases such as Oracle and SQL Server. Postgres on the other hand is a free database (no license or maintenance). But what features are missing in standard Postgres compared to Oracle and EDB Postgres?

As with EDB Postgres, Postgres has no RAC, no Compression (Tables, Files, Network, Backups), no Advanced Compression, no Interval Partitioning (auto partition creation), no Partitioned Indexes, no Flashback, no In Memory Database, no Database Vault, no Distributed Transactions, no Apex, no XML DB, no Transportable Tablespaces, no Online Re-organisation, no Automatic Memory Management, no Automatic Storage Management, no Pipelined Functions and no Exadata.

Compared to EDB Postgres, there is no conversion from Oracle capability (PL/SQL, functions, triggers, packages, procedures), no Oracle compatibility, less security features (no auditing [see pgaudit issues]), no password strength, no timeouts, no lockouts), less performance features, no hash partitioned indexes, no SQL query hints, no SQL profiler, no index advisor, no Postgres Expert or Oracle Tuning Advisor equivalent (performance advice), no database links (cant take Oracle code and make it work but – uses Foreign Data Wrappers) and there are no incremental backups.

Risks of Converting from Oracle to EDB or Postgres

Resourcing – Can teams be built with adequate skills in PostgreSQL and Enterprise DB? Typically our clients existing skills are in Oracle. Oracle, Postgres and EDB Postgres are all relational and despite their differences are quite similar in many aspects. However, we believe an Oracle DBA or developer would find it easier to move from Oracle to EDB Postgres than to Postgres. This is due to the Oracle dictionary views being available in EDB and the ability to develop PL/SQL and have it run on EDB Postgres.

Robustness – We comprehensively load tested Postgres and EDB Postgres (EnterpriseDB) in an attempt to break them. Both remained extremely robust, but ran slower when under extreme load. This no different to any other database including Oracle and SQL Server. Based on our results, both Postgres EDB Postgres are capable of supporting mission critical systems. This does not mean that you should convert to EDB Postgres or Postgres without performing your own comprehensive load testing.

Backup/Recovery – Oracle has the most advanced backup and recovery capability. However, EDB Postgres and Postgres both support hot backups (see barman tool RMAN equivalent) as well as having an equivalent of the Oracle Hot Standby, with the target database being read only, but can be used for queries.

Performance – Oracle has an abundance of self tuning performance features and EDB has more features than Postgres. Our observations of Postgres sites has been that they often lack the discipline and procedures of Oracle DBAs. They also often lack the tuning knowledge in the group, with the persons managing the database often not from an IT background. As a result of this, Postgres sites often fail due to performance and scalability issues. Example causes of the failure include not partitioning data appropriately and experiencing severe performance degradation during vacuuming. However, Postgres (and EDB Postgres) can be setup for high performance and scalability with the correct assistance the database is being built and the occasional tuning ongoing.

Security – Both EDB Postgres and Postgres have relatively strong security that is sufficient to cope with most mission critical applications. With vanilla Postgres, the site will need to set standards that ensure items such as password strength are secure and that passwords are changed on a periodic basis. Oracle and EDB Postgres will ensure password strength automatically as well as ageing passwords.

Scalability – EDB Postgres and Postgres are limited to single master writeable host and cant write across multiple servers (they do not have a RAC equivalent). However, you can have query only users access a Read Only Hot Standby of the primary database. Without a RAC equivalent, it is important that the database server is adequately sized and can have memory and CPUs added if required.

Support (Patching, Bug Fixes) – EDB Postgres offers full database support, where a user can raise tickets via a phone or online and receive frequent patches to repair any issues as well as advice on workarounds in the interim. The free Postgres does not have a formal support mechanism, but many users respond on online forums. Postgres patches and typically released every 4-6 weeks. Many Postgres sites purchase an inexpensive Postgres support contract with an external service provider.

Indemnification – EDB Postgres indemnifies its license but PostgreSQL doesn’t.

Cost Comparisons

This section talks about the cost comparisons of using Oracle versus EDB Postgres versus Postgres. If your site is currently running its database on Oracle, and wanting to convert, you need to factor in the conversion cost, the functional and technical testing and determine if it is cost justified to undertake the migration. There is also an element of risk in any database migration.

The costings below were obtained at the time of writing of this article from the vendors published pricing. In reality, the pricing may be heavily discounted. It will provide an indication of how prices may compare. Keep in mind that EnterpriseDB includes all add on’s in its price, whereas Oracle may charge an additional license cost.

License – Oracle EE list price $47,599USD per core, Oracle SE2 list price $17,500USD, EnterpriseDB list price $1,950USD, PostgreSQL list price $0.

Resourcing– At the time of writing this article, Postgres professionals cost 10%+ more on average than the equivalent Oracle professionals. There were no pay scales for Enterprise DB that could be compared.

Conversion Effort – EnterpriseDB has a conversion tool that converts data and stored code (packages, functions, procedures, triggers) from Oracle to EDB Postgres. Postgres has tools to migrate the data but requires a re-write of the stored Oracle code to Postgres procedures. EDB Postgres allows you to maintain PL/SQL and it runs as though it is native to the database.

Case Study – Oracle to EDB Postgres Migration

To truly test the Oracle to EDB Postgres migration capability, we chose an actual mission critical production client site that we manage. We converted its existing Oracle databases to EDB Postgres in our test labs as part of a proof of concept. Each database had around 12500 lines of stored code contained in packages, procedures, functions and triggers. The database also had views that referenced Oracle functions.

MGA Duplica (our replication product) replicates data across 7 states in near real time at this site. The replication was included in the EDB Postgres Proof of Concept. Duplica uses Oracle stored packages and procedures to pull data from the source databases using dynamic SQL. The source databases tracks changes using triggers and log tables. The data replication is master to master with multiple sites able to update the same record requiring conflict resolution.

Our first step was to transfer test data from an Oracle database to EDB Postgres. The data conversion tool was quite fast and we experienced no data issues whatsoever. We then migrated the Oracle stored code. We found that around 80% of the PL/SQL, packages, procedures, triggers and functions ran correctly without any modifications.

Issues with EDB Postgres PL/SQL & Oracle Scheduler

->You cant have high level global declarations in a package
->Compiling only does syntax checking & doesn’t check for dependant objects
->A package is always valid
->Some DBA views are missing (e.g DBA_JOBS_RUNNING, v$instance, v$mystat, and function sys_context)
->DBMS_JOBS initially didn’t work but EDB provided an update and it worked. We were impressed with their support
->Lack of error trapping and reporting makes it harder to find errors and bugs in PL./SQL

From a coding PL/SQL perspective, we modified our stored PL/SQL code to make it operate on EDB Postgres to repair issues. It truly felt as though we were coding in Oracle with no syntax changes required to make it operate on EDB Postgres.

Following the migration we performed comprehensive functional and technical testing to make sure that the data was correct, EDB Postgres provided the required runtimes and remained robust under extreme load. We previously performed the identical tests in Oracle. We found that the data was identical to that in Oracle, performance was almost the same (marginally slower in EDB) and EDB Postgres remained robust, even under extreme load.

Conclusion

Oracle Pros – Proven to satisfy our clients requirements, feature rich, minimal change to existing landscape, internal support strong, Oracle support, indemnification
Oracle Cons – High Cost of ownership

EDB Postgres Pros – Able to migrate DB from Oracle to EDB (including stored code), Oracle skills can use EDB, Oracle like capability, support, indemnification
EDB Postgres Cons – Cost of Ownership, Migration Effort

Postgres Pros – Reduced cost of ownership
Postgres Cons – Large migration cost, tools, no indemnification, minimal support

The post Converting Oracle to Postgres or EDB Postgres appeared first on Mark Gurry & Associates.

Self service reporting typically provides data to a handful of expert Business users who understand the data and the business operations best. The expert business users also understand  how to analyse the data to obtain information that can assist the Company going forward. They can analyse the data in far less time than if they were forced to specify their requirements to an IT professional who goes off and creates a report on the users behalf. And the cost saving and delivery time reduction are significant.

Our experience has been that the Businesses that use Self Service Reporting most effectively have a large percentage of their business users receiving information. We are aware of one business that expanded its reporting usage from around 30 specialist users, many of which were IT users, to over 1,000 Business users (over 25% of all persons in the Company). The data access was personalised to just include what the user wanted to see. The dashboards allowed each user to pick and choose their displays based on their needs and desire.  Reports also presented the data that was just relevant to the business users with most users accessing selective information on me as an employee, my group and my Company as a whole.

Self Service User Types

ANALYTICS Power Users 

• What occurred?
• Why did it occur?
• What will occur going forward?
• How can we influence it?

Ad Hoc Users

• Use a simplified data model (resembles a spreadsheet)
• Often upload data to Excel
• Personalised, contains real time data, runs on iPAD + iPhone and PC

Dashboard Users

• Personalised – add the displays on the dashboard that are relevant to their position
• Typically contains compliance information (Timesheet information)
• Very fast response on iPad, iPhone or PC 

Benefits of Self Service Reporting

• Empowers the Business
• Business experts with sound knowledge access own data
• Allows users with best knowledge of business operation to analyse the data
• Considerable reduction in IT spend
• Pre-canned dashboards run on iPad, iPhone and PC
• Very fast response
• Near Real Time Data capture
• 365 degree view

Major Requirements of Data Provisioning

DATA Capture (replication) – Implemented by IT

• Timely, accurate (fully reconciled), secure
• Supports tools to gather data from variety of data sources
• Low impact in source database & applications
• Handles a large number of source database, data and file types
• Data placed into highly available, high performance, secure and easy to manage target database

DATA Model – Implemented by IT

• Simplified data model (spreadsheet like simplifying hundreds of tables into a handful)
• Comprehensively documented with training
• Quality data, reconciled and audited
• Secure data access
• Exceptionally fast response
• Allows users to upload into Excel

PRE CANNED Reports and Dashboards – Provided by Toolset used by Business Users

• Easy to use dashboards creators that allow end user to pick and choose what they wish to display
• Personalised dashboards that run on the users PC, iPhone and iPad
• Business has ability to easily create own ad hoc reports using latest easy to use technology (Excel)
• Ability to easily share reports across business users

Why do Self Service Reporting Systems Fail

To ensure the success the self service reporting implementation, it is appropriate to observe the major causes of failure and ensure that each is avoided for the successful implementation.

#1. – Performance  (Query performance too slow, Data loads not meeting required delivery timeframe or impact on source systems unacceptable)

#2. – Scalability (Capacity to store data and accommodate load and query usage has exceeded the original forecasts)

#3. – Budget blow out (Typically occurs in the IT delivery cost where skills are not sufficient for a rapid and high quality delivery)

#4. – Inappropriate database or toolset selected (Some databases do not scale as well as others, some replication tools are considerably more complex and costly to set up)

#5. – Data model too complicated for end users (Simplifying data model access requires a special skill set that is different to conventional data modelling techniques)

#6. – Inability to provide near real time data (Modern reporting systems require data in near real time – achieving this can be difficult)

Successful Implementation Checklist

• Performance specialists on the team to ensure low impact on source applications, rapid replication and fast query response
• Infrastructure and capacity planning specialists to ensure that the implementation scales
• Architecture and Product Selection specialists
• Data modelling specialists that can simplify your data access
• Data specialists including replication techniques and products, databases, reconciliation, storage reduction + archive and purge

Power BI Best Approach

• On Premise or Cloud could work equally as well
• Separate core transactional database from BI & Analytics
• Use a combination of facts and dimensions (real time data) and cubes
  • However Columnar in Memory allows data to be stored in first normal form in SQL Server 2016 and later
• Simplified Reporting Data Model is Key
• Create a Group Workspace (share reports)
• Use Enterprise Gateway to assist with security
• Do data cleansing, normalization, and formatting in ETL (PowerQuery) instead of via calculated columns in DAX
• Tables should be tall and skinny whenever possible
  • Unless using columnar in memory SQL Server 2016 or later in which case tables can be very wide
• Create a date dimension table to select all date variations
• Don’t allow Power Query to automatically determine each columns data type – especially dates
• Have a main data table and lookup tables
• Don’t mix data tables

The post Self Service Reporting appeared first on Mark Gurry & Associates.

Our large Australian resource exporting client was heavily reliant on a shipping application that used Oracle as its primary database. The Company had earmarked Microsoft as its preferred direction including SQL Server. It had a skilled internal team that was able to administer the Microsoft servers and databases. Administering and maintaining the single Oracle database had become a key concern with all other applications using SQL Server. The concern was magnified when the client was informed that the database was soon to be out of support and that several of the products used including Advanced Replication were being deprecated and would need to be replaced by an expensive alternative.

An independent audit had also earmarked the Oracle based application as a major risk. A move from Oracle to SQL Server (if possible) would reduce the license cost, ease the maintenance and reduce the support cost by being able to use the internal support team.

MGA was engaged to assist in determining if the migration to SQL Server was possible with minimal risk to the Business that relied heavily on this application. The client had an aggressive timeline that required the migration from Oracle to SQL Server to complete within 6 weeks. The short timeframe was due to an obligation to meet compliance of all databases being at a supported release. The Company required a functionally correct, robust, highly available and highly performant system post the migration. They required minimal downtime during the cutover.

The application used Oracle Tables, Indexes, Views, Triggers, Functions, Procedures and Scheduled Jobs. It also used Oracle Advanced Replication.

MGA Approach

We assigned a team of 2 persons that were highly skilled at Oracle and SQL Server and knew the differences between the two in great detail. SQL Server Migration Assistant for Oracle (SSMA) was used to convert the Oracle objects and data to SQL Server.

We performed various checks in the Oracle database prior to running SSMA. We are aware of the differences in the two products and were keen to see how SSMA would convert them. The following list shows some of the major differences between Oracle and SQL Server and we were keen to see how SSMA would handle them.

SQL Server does NOT have:

• packages
• sub partitions
• ROW level triggers and BEFORE triggers
• DUAL table
• Same Dictionary tables and views
• Database links (SQL has a linked server)
• Various data types e.g. BFILE, CLOB and BLOB datatypes (SQL has VARBINARY)
• Many functions including NVL, TO_DATE, DECODE and others
• DBMS procedures e.g. DBMS_JOBS and DBMS_SHEDULER (SQL has SQL Agent Jobs)
• NULLS in Unique indexes
• Oracle Advanced Replication, Advanced Queues and Materialized Views

If the source database contains any of those in the list, the post conversion result in SQL Server needs to be looked at carefully to ensure it has converted OK.

Another check that we made was for any invalid Oracle Objects. There was no point in migrating objects which are already known not to work.

SQL Server Migration Assistant for Oracle (SSMA) Effectiveness

SSMA is a tool provided to automatically convert and Oracle database to a SQL Server database. The question was “How much of the conversion is fully automated”? and “How do we identify the parts that are not working as expected and may cause issues?”

In this case study, we estimated that SSMA was able to perform around 80% of the conversion. The data portion of the conversion was fine, but issues occurred with triggers and other objects stored within the Oracle database that required similar logic in the SQL Server database. The process of migrating the logic requires a thorough understanding of how it is constructed in Oracle PL/SQL and how to rewrite the logic in Microsoft T-SQL to exactly retain the logic’s intention. Our highly skilled staff members where able perform this over numerous occurrences, in an economical timeframe.

Some items could not be converted by SSMA including User Logins, Scheduled Jobs and Database links. These items need to be set up manually by the team performing the migration.

Adjustments Made After SSMA Migration

This section shows the adjustments that were required to be made to make the application work after the SSMA migration.

Note that these changes were in addition to the User Creation, Database Link Creation and Scheduled Jobs which had to be performed by the migration team.

Triggers

• SQL Server doesn’t have before triggers and SSMA generates INSTEAD OF triggers using cursors.
  • Tested OK and no action required
• SSMA generates various triggers where there are logic issues inside the trigger. The application functionality was broken.
  • Fixed and tested logic issues
• When the Oracle trigger has a single “AFTER INSERT, UPDATE, DELETE”, SSMA will create 3 separate triggers with the same trigger content and an additional variable to categorise the Insert / Update / Delete
  • Tested OK and no action required
• Some basic before update row triggers were created as row triggers with cursors and some code was commented.
  • Required consolidation of the triggers
• Functionality differs between Oracle UPDATE () and SQL UPDATE () – SQL Server’s UPDATE () will return TRUE even if the column is touched with the same value but Oracle doesn’t return TRUE. This broke the application functionality
  • Fixed the Triggers by adding a conditional statement to compare the previous & current values
• Triggers with DBMS_REPUTIL.FROM_REMOTE, even though SQL Server has NOT FOR REPLICATION keyword, SSMA didn’t convert the scripts and commented the statement block
  • Included the NOT FOR REPLICATION

Stored Procedures

• Converted SQL Server Procedure returns 0 instead of NULL. Oracle Procedure returns NULL
• Fixed the logical issue inside the stored procedure
• Oracle Exception trapping – SSMA converted the script with raise exception without masking
• Commented the Raise Exception on SQL Stored Procedure

Conclusion

While SQL Server Migration Assistant is a valuable tool in migration from Oracle to SQL Server, it can only typically help with perhaps 80% of the overall migration. It does a great job of converting data types and moving data, but lacks finesse when it comes to migrating the logic contained in stored procedures, functions and triggers. Logic migration is a task that often holds customers back as it requires deep knowledge of both PL/SQL and T-SQL and is very much a manual process, meaning that many customers are unable to realise the cost and support benefits of moving to SQL Server.

Our customer achieved all of its requirements, including a migration that was achieved within the 6 weeks. But meeting the tight delivery timeframe required experts in both technology platforms. MGA had the in depth expertise of both Oracle and SQL Server to achieve this highly successful outcome. Not having the extensive skills in both would have made the migration particularly difficult.

The post Converting Oracle to SQL Server Using SSMA – Case Study appeared first on Mark Gurry & Associates.