Problem
Oracle and SQL Server are both very popular RDBMS systems that are widely used to run enterprise applications. Oracle comes with a heavy price tag along with additional costs if you will use features like Oracle enterprise manager or data guard. Whereas, SQL Server offers all database features in one license cost whether it’s database engine feature or Reporting Services or Analysis Services. Also, SQL Server has proved its robustness and power to handle huge data processing loads and to run mission critical business applications. Now, many customers are preferring to choose SQL Server over Oracle for their applications. If you are looking at migrating your existing Oracle databases to SQL Server, then read below for more info about migration.
Solution
These days lot of customers are looking to save on their technology costs. Adopting economical technologies and software that are equally capable and powerful to fulfill requirements is one of the ways to achieve cost optimization. Databases are one of the tiers which customers spend a lot of money as part of license and support fees. Support staff is an additional cost to maintain these databases. SQL Server is very economical if we compare it with Oracle and that is the main reason for clients to choose SQL Server over Oracle. Here, I am going to guide you how to migrate your existing Oracle databases to SQL Server in a step by step manner.
Below is high level approach you need to follow if you want to migrate your Oracle databases to SQL Server:
- Ensure application is compatible to run and process on SQL Server
- Understand the Oracle database features that are being used and find a feature in SQL Server that can be used for these
- Research data types being used in Oracle and check to ensure there is an option in SQL Server
If you have passed the above three criteria, then you can go ahead and start planning for migration. Microsoft has developed a tool called SQL Server Migration Assistant (SSMA) for Oracle to help such migrations. This tool can be used to migrate databases to SQL Server as well as Azure SQL databases. I have used it to perform our migrations.
Here, I am migrating Oracle 10G database to SQL Server 2014. We used SQL Server 2014 because of some application compatibility issues and we have plans to upgrade this instance post migration. Although, the steps given in this article are very much applicable to other SQL Server versions as well.
I would suggest you perform such migrations in a lower life cycle system first to ensure you understand the process and any potential issues that may arise.
Below is high level approach for this migration:
- First you need to install SQL Server on target server where your migrated database will be hosted. Ensure you have proper connection and accessibility between source Oracle server and target SQL Server system
- Next you need to install Oracle Client and SQL Server Migration Assistance (SSMA) on that server
- Launch SSMA and connect to the source Oracle database. Make sure to check the connectivity due to port or firewall issues
- Connect to the target SQL Server that you installed in step 1
- Map Oracle schema which you want to Migrate to SQL Server database
- Convert Oracle objects to SQL Server
- Load converted objects to SQL Server
- Finally, migrate the loaded data to the target database
I am not covering step 1 and step 2 in this article. See the links below for more information:
- How to Install SQL Server Migration Assistant for Oracle - Part 1
- How to Install SQL Server Migration Assistant for Oracle Extension Pack - Part 2
So let’s start at step 3.
Step 3 – Launch SSMA and connect to source Oracle database
Launch SQL Server Migration Assistant as shown in below screenshot.
Below is the screenshot of SQL Server Migration Assistant for Oracle. There are two boxes you can see in below screenshot. The top box is Oracle Metadata explorer which will be used to access Oracle source database and bottom section will be used to access SQL Server databases. The right side of each box shows details about their respective database systems.
Next step is to create a project inside the SSMA. We need to first create an SSMA project to migrate Oracle databases to SQL Server. This project stores all required information that will be needed for this migration.
Below information will be saved in this project file:
- Metadata about the Oracle databases you want to migrate to SQL Server
- Metadata about the target instance of SQL Server that will receive the migrated objects and data
- SQL Server connection information
- Project settings
Click at “File” tab of above screenshot and choose “New Project” option as shown in below screenshot.
Once you click at “New Project” option, another pop-up window will appear to fill the project related details as shown in below screenshot. Fill name of the project, location where you want to save this file for future access and the target database version. Here you can see I have chosen as SQL Server 2014 as I mentioned earlier. We are using SQL 2014 because we had some issues that prevented us to move to later SQL version.
Once you filled the details, click the OK button to proceed.
Now, you can see the name of the project is showing at the top blue bar of the SSMA tool in the below screenshot. Next, we will connect to source and target database to access them through SSMA. You can see “Connect to Oracle” and “Connect to SQL Server” options are enabled now.
Click on “Connect to Oracle” option in the SSMA tool. You will get another pop-up window to fill all the required details to access the source database. Ensure you have access to the Oracle database and both servers are accessible to each other. You can see the details you need for this access. You need server name, port, Oracle SID, username and password.
You might face some permission issues while accessing the Oracle database so ensure you have the below permissions on the ID that you are using to migrate this database.
- CONNECT
- SELECT ANY DICTIONARY
- SELECT ANY TABLE
- SELECT ANY SEQUENCE
- CREATE ANY PROCEDURE
- CREATE ANY TRIGGER
- CREATE ANY TYPE
- EXECUTE ANY PROCEDURE
- CREATE ANY TABLE and ALTER ANY TABLE
- INSERT ANY TABLE and UPDATE ANY TABLE
- DROP ANY TABLE
- CREATE ANY INDEX and ALTER ANY INDEX
- DROP ANY INDEX
- CREATE ANY TRIGGER and ALTER ANY TRIGGER
- DROP ANY TRIGGER
If you missed any permission, then you will be getting below warning windows to get the required permission.
Once you resolve your permission issues, then again follow the same process to establish the connection to the Oracle database. This time you can see connection the is established successfully in the output window as well. The Output window has two tabs, one is for output and another one for the error list. This tab will list all errors that appear during the migration.
If you notice the two sections that are highlighted in black rectangles, then you can understand that SSMA for Oracle has connected to source database and now it is evaluating the database from a migration standpoint. You can also see a green progress bar as a sign of processing. This green bar will also display the percentage completion of the evaluation. You can see below that 21% has already completed.
During this evaluation phase, SSMA reads the database metadata and then adds this metadata to the project file. This metadata is used by SSMA when it converts objects to SQL Server syntax and when it migrates data to SQL Server. You can browse this metadata in the Oracle Metadata Explorer pane and review properties of individual database objects.
Once the green bar reaches 100%, then the Oracle details will be loaded into Oracle Metadata Explorer section as shown in the below screenshot. You can also see schema level details in the right side pane.
You can expand the schemas option in the top left side pane to see all the schemas running on this SID. You can selectively choose which schema you want to migrate and which one you want to exclude. Have a look at the below image for a better understanding.
Step 4 – Connect to target SQL Server that you installed
Now, it’s time to connect to our target system i.e. SQL Server. Click on the “Connect to SQL Server” option showing on the top ribbon of the SSMA tool. You will get a connection details window. Enter the server details and hit the connect button to establish the connection. Ensure you are using sysadmin account for this migration.
If you have not created a target database on SQL Server instance, then this tool will ask if you want to create it by displaying the below window. If you want to create it during this exercise, then go ahead and click the “Yes” button. It will take some time to create this database along with the schemas.
Again, you will see a progress. Once the connection is established you can see the SQL Server instance in the SQL Server Metadata Explorer as shown below.
You can expand the instance name to see all databases hosted on this instance. You can also see the SQL Server details in the right side pane.
Step 5 – Map Oracle schema to SQL Server schema
The next step is the mapping between Oracle and SQL Server. As we know, an Oracle database has one or more schemas. By default, SSMA migrates all objects in an Oracle schema to a SQL Server database named for the schema. However, we can customize the mapping between Oracle schemas and SQL Server databases.
Expand Schema in Oracle metadata explorer and select the schema which you want to migrate. Click on the check box if you want to migrate the complete schema, if you want to migrate an object select just that one.
There is difference between Oracle and SQL Server if we talk about schema. The Oracle concept of a schema maps to the SQL Server concept of a database and one of its schemas. SSMA refers to the SQL Server combination of database and schema as a schema.
In SSMA, you can map an Oracle schema to any available SQL Server schema. Select Schema that you want to migrate in Oracle Metadata Explorer, here I have selected TF10 which has 71 tables as shown below.
The Schema Mapping tab along with other 3 tabs will also be available when you select an individual database, the Schemas folder, or individual schemas. The list in the Schema Mapping tab is customized for the selected object.
Optional Step – Generate Conversion Assessment Report
SSMA for Oracle also has a feature to generate a conversion report, if we want to analyze the conversion of Oracle objects to SQL Server objects. This is an optional step, but I would recommend generating this report before moving ahead with the other steps.
To generate the conversion report, go to Oracle Metadata Explorer, right click on the identified schema name, TF10 for this example, and chose “Create Report” as shown below.
The moment you click create report, the SSMA tool will gray out and will not let you access anything until the report execution completes. You can see this in the below screenshot that tool has been grayed out. The Output section shows the progress of the report processing.
You will be prompted to open this report on completion as shown below. Choose Internet Explorer to open and view the report.
You can also see in the output section what tables were evaluated by the conversion report and any potential errors.
The Assessment Report contains three panes:
- The left pane contains the hierarchy of objects that are included in the assessment report. You can browse the hierarchy and select objects and categories of objects to view conversion statistics and code.
- The right pane contains details of what is selected in the left pane
- The bottom pane shows conversion messages, errors, warnings, or other info. Click a message to see the details.
You can also see details at a table level as well. I have selected the BGACFOR table as shown below.
These reports can also be accessed in SSMA. You can see there is a report tab and this pane will display similar details of the report.
If you click all tables then you will get more options and report suggestions.
Next Steps
- We have walked through the first few steps of a migration and we have generated the conversion report to see a holistic picture of all source objects.
- The next step is to perform the actual conversion based on this report. Stay tuned for additional articles that cover the rest of the migration steps.
