DBBest - How to Migrate from Oracle to SQL Server

Since 2003 our team of world class DBAs supported and helped to deliver thousands of migration projects, including migrating an Oracle Database to Microsoft SQL Server Database. Learn more: http://www.DBbest.com/

---

Closed Caption:

So, say you face an Oracle migration project.
What do you do? Let me give you a couple of
points that you may want to consider before
you decide to go for the actual migration.
As an example, you probably have in your database
Oracle sequences. Sequences traditionally
it's been one of these types of objects that
were not trivial to convert to SQL Server.
And so for SQL Server 2008 and prior we had
two main ways to convert sequences. One was
emulation and the other one to use identities
properties of SQL Server columns. In SQL Server
2012 this kind of new feature, new type of
object called Sequences, surprise, was introduced.
And so conversion of sequences objects became
much easier. There are still a lot of considerations
about how sequences are being used from inside
the application, whether allocate arranged,
do increment the sequence right away or do
you wait until the end of the transaction.
But in any case this is one of the core things
how to do with the sequences during the conversion.
Another example which is very important and
this is for those of you who are kind of hardcore
Oracle DBAs. There are a number of objects
or type of objects very proprietary for Oracle.
As an example, packages, particular packages
variables. Packages variables do not exist
in SQL Server. There is absolutely no way
to easily reproduce the same functionality.
What we’ve done when we developed and designed
SSMA we created an emulation, an emulation
where we capture the value of these package
variables into tables and then we have two
types of functions – Set_PV, get_PV – depending
on the type of this package variable: varchar,
numeric and so forth, which essentially produces
the same results, the performance may not
be as great as it is in Oracle but it gives
the results fairly good. Now, one of the recommendations,
one of the real advice that we provide to
the customers is to say “All right. Let
us perhaps take a look at the system, let
us take a look at the database and the application
code that uses these packages, and actually
come up with a mechanism that would be as
native as possible for SQL Server.” And
so “being native” is one of these core
and critical approaches for the migration
that allows you to create an app that would
be optimal to run on top of SQL Server.