Tags: SQL Server Linked Servers
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 other ten schemas remained in place and we used SQL Server Linked Servers on the new SQL Server instance to connect to the Oracle schema tables. This allowed the customer to use their .NET application against the new SQL Server instance without having to do a full conversion of the ten other Oracle schemas.
Check out the following video to see how we approached this non-trivial task and managed to reduce cost of the Oracle migration project.
Be sure to check out our new Jumpstart Your Database Migration Project with SSMA offer to get you up and running fast for your migration to Microsoft SQL Server.
Schema Conversion
At DB Best, we have a 12-step process to make sure all the project essentials are taken care of for migration projects. For the database migration step, we used the SSMA for Oracle to automate the migration of most of the Oracle databases code related to the main schema to SQL Server. Some source database objects could not be automatically converted, so we had to complete the conversion process manually. We also found a few issues where SSMA generated code that was functionally incorrect as part of our testing phase. Overall, SSMA proved to be a very handy tool as part of the schema migration effort.
After converting the main source Oracle schema to SQL Server, we had to provide the interaction of the new SQL Server database with 10 source database schemas that were left in the Oracle environment. To do so, we’ve used the SQL Server Linked Server feature.
Linked Server
The SQL Server Linked Server feature allows access to data from outside of SQL Server environment to execute transactions with heterogeneous data sources. Usually, the linked servers are used to connect to another instance of SQL Server or another database product such as Oracle. In order to make the connection to the Oracle database instance, you need to install the Oracle OLEDB provider on the SQL Server instance. The easiest way to install the provider is to download the Oracle Database 11g Client and use the Runtime option as part of setup process.
When setting up a linked server connection, you first need to register the Oracle server instance with SQL Server using the sp_addlinkedserver system stored procedure. Here is example of what the command looks like.
1 2 3 4 | EXEC master.dbo.sp_addlinkedserver @server = N'ORA-SERVER', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORA-SERVER'; |
In order to access the server, SQL Server uses the sp_addlinkedsrvlogin system stored procedure to associate the Oracle login credentials with the linked server. Here is an example.
1 2 3 4 5 | EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORA-SERVER', @useself=N'False', @locallogin=NULL, @rmtuser=N'system', @rmtpassword='########'; |
Once you have the linked server setup, you can now reference tables and views on the Oracle server using a four-part name like this:
1 | SELECT * FROM [ORA-SERVER]..[SCHEMA-1].[TABLE1]; |
Note, the .. between the server name and the schema name is used for a catalog which Oracle doesn’t support.
By using the linked server to connect the newly created SQL Server database to Oracle server with the remaining ten Oracle schemas, we minimized the database code upgrade while ensuring proper connectivity.
But the database migration project wasn’t finished yet. We still had to upgrade the customer’s application and provide the scripts for data migration.
Application Upgrade
The application upgrade was performed in 4 sure steps:
- Updated the .NET application code to SQL Server Transact-SQL.
- Changed the .NET connection information to point to the created SQL Server database.
- Updated the queries that accessed the 10 other Oracle schemas to use four part names.
- Updated the previously undetected Java application to use the new SQL Server database.
So, after upgrading the application, we could test it and compare the results of its work with the original Oracle environment and with new SQL Server database. On this stage, we used the test cases, based on real data, provided by the customer.
Data Migration
As you already know, for security reasons the customer didn’t provide access to the original data. So, we provided the scripts for data migration, which the customer executed on his side. These scripts were developed blindly, but they ran smoothly on the production server. As part of the migration testing process, we uncovered several performance issues when performing queries against the linked server tables. For the problematic data, we created ETL jobs using SQL Server Integration Services (SSIS) to import the data using the Linked Server table references into SQL Server using the same schema name as before. We then updated the application to remove the four part name and use the two part name instead like this:
1 | SELECT * FROM [SCHEMA-1].[TABLE1]; |
We also ran into performance issues accessing Oracle views that contained an ORDER BY clause. Generally, this is not a best practice for views. Our team ended up removing the ORDER BY clauses from the view definitions and then updated the queries as needed to add ORDER BY clauses to the SELECT statement the applications used.
After migrating the original data, the customer deployed the newly created system into production.
