Oracle to SQL Server migration with SSMA: How to convert materialized view with float type
24 Mar 2017

SQL Server has several different ways of implementing the same behavior as an Oracle materialized view. SSMA cannot understand the original optimization reason for why the application needed the materialized view, so SSMA flags certain conditions as an error. This blog post discusses error O2SS0522 where the Oracle materialized view includes FLOAT columns, WHERE or GROUP BY clauses, subqueries, UNION, MINUS, INTERSECT, etc. Currently SQL Server Migration Assistant (SSMA) for Oracle converts materialized views to SQL Server indexed views. While converting […]

Migrating Oracle database to SQL Server for a leading US healthcare company
Migrating Oracle database to SQL Server for a leading US healthcare company
17 Mar 2017

One of the leading US healthcare organizations needed to upgrade their health record system in an effort to drive down costs while enabling new capabilities. We proposed to migrate the Oracle database to SQL Server to build a scalable and secure architecture. We faced a number of Oracle to SQL Server migration challenges, including the huge volume of data along with very large tables aggregated in the original database. As a result, the customer got the modern, secure, platform while saving […]

Oracle to SQL Server migration with SSMA: How to convert Unparsed SQL — Pivot Operator
09 Mar 2017

Oracle 11g introduced pivot operation that allows writing cross tabulation (also called transposed, crosstab and matrix) queries that rotate rows into columns and aggregate results. Pivot rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output and performs aggregations where they are required on any remaining column values that you want to get in the final output. SQL Server Migration Assistant (SSMA) for Oracle cannot correctly parse the pivot […]

Oracle to SQL Server migration with SSMA: How to convert foreign keys with different types of columns and referenced columns
09 Mar 2017

Oracle allows you to create foreign key for table using columns with different data types. But SQL Server Migration Assistant (SSMA) for Oracle cannot convert them to SQL Server correctly because SQL Server doesn’t support the foreign keys that use columns with different data types. So, when you try to convert the original code that includes the foreign key with the columns of different data types, SSMA will generate the following error message: «Error O2SS0231: Foreign keys with different types […]

Oracle to SQL Server migration with SSMA
Oracle to SQL Server migration with SSMA: How to convert cursor or cursor variable as a function or procedure call parameter
09 Mar 2017

Oracle 9i introduced the predefined SYS_REFCURSOR type meaning we no longer have to define our own REF CURSOR types. SQL Server doesn’t support SYS_REFCURSOR type and doesn’t have a direct equivalent for it. Thus, SQL Server Migration Assistant (SSMA) for Oracle converts SYS_REFCURSOR variables into varchar (8000) and generates the appropriate warning message: «Warning O2SS0259: Cursor type was converted to varchar». Oracle’s SYS_REFCURSOR type allows returning recordsets from stored procedures and functions. For example, a procedure can open a cursor […]

Oracle to SQL Server migration with SSMA
Oracle to SQL Server migration with SSMA: How to convert unsupported table expressions
09 Mar 2017

Oracle’s table functions allow you to define a set of PL/SQL statements that will, when queried, behave just as a regular query to the table. You may use the table function to manipulate the individual elements of a collection (user-defined object types) in your SQL code. The problem is that SQL Server Migration Assistant (SSMA) for Oracle can not convert table function and generates a set of errors: O2SS0474: Conversion of user defined type variable is not supported and is […]

Database migration with SSMA: How to handle typical conversion errors
09 Mar 2017

Welcome to our blog post series covering the typical conversion errors of SQL Server Migration Assistant (SSMA). DB Best has a vast experience in database migration, so, we constantly encounter these errors and found the solutions for most of them. We will devote one blog post per common error message that may occur during database migration with SSMA. Each blog post contains a problem statement and our recommendation on how to fix it correctly. Related posts Below you can find the table of contents […]

Reduce the cost of an Oracle database migration project by using SQL Server Linked Servers
21 Dec 2016

When talking about migration from Oracle to SQL Server, we always look for ways to work with our customers to keep costs under control. We recently completed an Oracle to SQL Server migration proof of concept project for a government organization in South America. What made this project unique was the way we used SQL Server Migration Assistant (SSMA) to convert one of eleven Oracle database schemas that were running on a 12-node Oracle RAC cluster to SQL Server. The […]

Global Retailer Tunes up OLAP Performance Ten-fold
15 Dec 2016

When a company grows, it’s no surprise that their database stored data grows as well. This is the case for all companies, and our customer, a global retailer in the beauty industry, was no exception. While working on this project, we managed to increase the average speed of query execution by 30x, saved up to 190 GB of memory and freed up 28 GB of disk storage space. Prior to these great results, there was a hard optimization process performed […]

DB Best upgrades a leading telecom company’s SQL Server 2000 and 2005 databases
11 Nov 2016

Upgrading SQL Server can be a complicated and nuanced operation, it can’t be simply performed with a few mouse clicks. In many ways, upgrading SQL Servers follows a similar process to more general database migration projects. Because of the similarities, we can apply our 12-step database migration methodology to great effect. From analyzing the existing database architecture to consolidating resources for operational cost reduction, DB Best has the tools and know-how to ensure your SQL Server platforms are up to […]