Cost-efficient Oracle database migration project with SQL Server Linked Servers
Preventing clients from overspending is one of our top priorities. Hence, we pay close attention to the cost of Oracle to SQL server migration in order to provide the most economical solutions while keeping the highest level of quality. Over years, we have served many clients and helped them to keep costs under control. For example, we successfully completed an Oracle to SQL Server migration proof of concept project ordered by a governmental organization from South America. While working on the project, our specialists successfully utilized SQL Server Migration Assistant. SSMA helped us to convert one of eleven database schemas running on a 12-node Oracle RAC cluster to Microsoft SQL Server. We left the other ten schemas in place and applied SQL Server Linked Servers on the new SQL Server instance in order to connect to the Oracle schema tables. As a result, the customer was able to use the .NET application against the new SQL Server instance even without fully converting the ten remaining Oracle schemas.
Do you want to know more about how ABCloudz professionals kept the cost of the project under control? Check out the video below for more information.
Read our SSMA support offer to know more about how we help you complete a fast and cost-efficient migration to Microsoft SQL Server.
Converting the schema
ABCloudz database migration specialists implemented a 12-step approach to database migration and delivered a cost-efficient solution of the highest quality. SSMA helped us automate the migration of Oracle databases code to SQL Server. The entire process was not always smooth. For example, we had to manually convert some source database objects since SMMA could not convert them automatically. Also, our tech specialists determined several issues when utilizing SSMA during the testing phase. For example, we found out that it had generated functionally incorrect code. Hence, we had to investigate and fix this problem manually. Despite some minor issues, SSMA proved its relevance as a handy tool for schema migration.
Once we converted the main source Oracle schema to SQL Server, we established the interaction of the new SQL Server database with the remaining 10 Oracle database schemas. At that point, SQL Server Linked Server feature came in handy. Check for additional information on its implementation in the next chapter.
Linked Server
The main purpose of the SQL Server Linked Server is that it enables the user to execute transactions with heterogeneous data sources from outside the SQL Server environment. Most users apply linked servers when they need to connect to another instance of SQL Server or any external database product. In our case, this outside product was Oracle. In order to successfully connect to the Oracle database instance, we needed to install the Oracle OLEDB provider on the SQL Server instance. A tried and trusted solution was downloading the Oracle Database Client and applying the Runtime option as part of the setup process.
You should register the Oracle server instance with SQL Server to set up a linked server connection. The sp_addlinkedserver system stored procedure enables you to set up such a connection. Check the example below to understand this command better:
1 2 3 4 | EXEC master.dbo.sp_addlinkedserver @server = N'ORA-SERVER', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORA-SERVER'; |
SQL Server applies the sp_addlinkedsrvlogin system stored procedure to access the server. This enables SQL Server to associate the Oracle login credentials with the linked server. Check the syntax below:
1 2 3 4 5 | EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORA-SERVER', @useself=N'False', @locallogin=NULL, @rmtuser=N'system', @rmtpassword='########'; |
After you get the linked server setup, start referencing tables and views on the Oracle server with a four-part name. Here is how it should look like:
1 | SELECT * FROM [ORA-SERVER]..[SCHEMA-1].[TABLE1]; |
Note that the .. between the name of a server and the name of a schema is used for a catalog. Meanwhile, Oracle does not support it.
In general, the above-mentioned solution helped us minimize the database code conversion while avoiding connectivity issues. We managed to achieve one of the client’s main goals with great cost-efficiency. However, there was still a long way to go with this project. In particular, the client wanted us to upgrade the application. We also had to provide them with the scripts for data migration.
Upgrading the application
We divided the upgrade of the client’s application into 4 definite steps:
- We divided the upgrade of the client’s application into 4 definite steps:
- We updated the .NET app embedded SQL code to SQL Server Transact-SQL;
- Our specialists changed the .NET connection data to point to the created SQL Server database;
- We updated the queries which accessed the remaining 10 Oracle schemas to use four-part names.
- We updated the previously undetected Java application to fit the new SQL Server database.
Once the application was upgraded, we had to test it and compare how it works with both the original Oracle environment and the newly-established SQL Server database. We used real test cases to successfully complete the project’s testing stage.
Migrating data
The customer paid much attention to information security. Therefore, they did not provide us with access to the original data. To avoid interactions with the protected information, we provided the client with data migration scripts. Even though we developed such scripts blindly, the client did not have any issues with running them on the production server. There were several performance issues with data migration, but we managed to uncover and fix them when performing queries against the linked server tables.
Our specialists also used SQL Server Integration Services (SSIS) to create ETL jobs for the problematic data. As a result, the client could use Linked Server table references to import the data into SQL Server while using the same schema name as previously. After that, we updated the application by replacing the four-part name. Here is how it looked like:
1 | SELECT * FROM [SCHEMA-1].[TABLE1]; |
Our database migration engineers also determined performance issues with accessing Oracle views containing an ORDER BY clause. Hence, we removed such clauses from all the view definitions. After that, we updated the queries that required adding ORDER BY clauses to the SELECT statement used by the application. As a result, the client faced no issues with migrating the original data and implementing the created system.
Are you also interested in a cost-efficient database migration solution of excellent quality? Contact us to discuss your needs and start your migration project.
