Tech PostsHow to Migrate Oracle Databases To SQL Server 2016 Using SSMA 7.0 March 11, 20175886 views0Share By IG ShareIf you are looking to Migrate Oracle Databases To SQL Server 2016 using SSMA, then you have come to the right place. The Microsft SQL Server team has provided an easy to use free GUI tool to achieve this. The current version of the tool is called SSMA 7.0. Microsoft currently offers SQL Server migration assistant tool for Oracle, DB2, MySQL, Sybase, and Access. The latest version of the tool provides also supports the latest SQL Server 2016 features such as Column Store Indexes, In Memory OLTP and Temporal. SSMA 7.0 also happens to be the only tool which enables you to move to Azure SQL Database to the cloud directly. Interested? Then read along to know more about how to Migrate Oracle Databases To SQL Server 2016.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.Migrate Oracle Databases To SQL Server 2016In addition to supporting new SQL Server 2016 features, the current version of SSMA 7.0 also fixes critical bugs. For example, converting of “character data types” for Oracle and “global settings” bug have now been fixed. To get started, follow the steps below:Download SQL Server Migration Assistant from the Microsoft Download Site.Download the Oracle Client Tools if you have not already.We have used the Oracle Database Express Edition 11g for our migration example.We will be using the HR sample schema from the Oracle Database as my source and SQL Server 2016 as my destination. The HR schema is not visible by default in SSMA 7.0. You need to change some settings to make it visible. We will discuss this once we get into the details. Let’s look at the migration strategy first. Here are the key points to keep in mind before driving such a project.Every scenario is unique and different. Do not all migrations are equalTechnicalities apart, Not all customers will want to work the same wayThe business cases will differ. Not all clients want the same conclusion or outcomeWork with your stakeholders to develop a mutually agreeable approach and planIt is recommended to treat an Oracle to SQL Server Migration as any other project. Following a standard process workflow is essential for the success of a project. Here are the most important points:DefineAssessDesignMigrateValidateTest &DeployHere is the recommended process workflow: Source: MicrosoftIn the beginning, accessing what’s in scope and how much code needs to be migrated is essential for the project’s success. For example,How long can you afford your systems to be down?Is this a part of a bigger database consolidation project?Performance expectations of the client after when Oracle Database is successfully migrated to SQL Server.Cost Estimation is criticalFind out the inventory of the current environment. Use the Microsoft Assessment and Planning Toolkit (MAP) to achieve this.Not all Enterprise Customers would allow you to run either MAP toolkit or SSMA for Oracle. In that case, what are the alternatives that need to be planned?Capacity planning of the new hardware on the target system. For example, CPU, Storage, RAM. Learn more about SQL Server Disk Configuration best practices.What collations will be used on SQL Server and data access patterns (For example, Cursor, Paging, Collection, etc.)Prepare a Rollback and Mitigation strategy.Post Migration, validate the database objects, row count, and security validation.Perform UAT (User Acceptance Testing)DeploymentNow that we have the project planning and expectations set out, let us focus on the SSMA tool. The device is designed to be project based. Before getting started, you would need to create a project, and store it in the default documents location. You should select the destination SQL Server version at the time of project creation. In our example, we will select “SQL Server 2016”. See below:By default, the HR schema (Database in SQL Server world) will not be visible in the list of available databases at the source. In that case, you will need to change the following settings:Click on tools > Project SettingsClick on the General tabUnder “Mode” select “Loading System Objects.”Check on HR schema and then click on “Apply” & “Ok.”You can refer to the image on the right to follow the instructions mentioned above.Once this step is complete, you should be ready to connect to your source database on Oracle. In our example, we are running the Oracle Express 11g for this example. Usually, installing Oracle express edition on a Windows operating system should be straightforward. However, in our case, we had to do some manual configuration to get full access to our “System” account. After assigning explicit permissions, here is what we got:Once we did this, the rest of the process went smoothly without any hiccups. Here are the steps that you would need to follow:Connect to your Oracle Server with the provided credentials. In our case, we would be using the System account.The initial connection takes a couple of seconds to complete. Once done, expand your server name, expand schema and select the HR schema check box.Next, you can connect to your SQL Server as the destination. Create the target database beforehand to simplify the process.Once both the Source and Destination databases are connected, click on “Create Schema” on the database of origin. It creates a compatible schema for SQL Server.On the destination SQL Server, click on “Synchronize with Database.”On the Oracle HR Schema, Click on “Migrate Data.”Type in the password for both Oracle and SQL Server again and proceed.Here is the step by step screenshots below: Connect to Oracle Select HR Schema Connect to SQL Select “Convert Schema” Select “Synchronize Schema” Migrate DataNote: In Scenario’s where full access to the source and destination is not available, you can always save it as script and run it offline.Once done, you can check the database migration report, which will indicate any issues or errors that might have occurred during the migration process. Here is a quick snapshot:Since this is a demo scenario, we went ahead with the migration straightaway. However, in production scenarios, it is you would need to create a report using SSMA which will let you know how much percentage of code can be natively converted and which ones will need to be manually rewritten. Here is a sample report below:ConclusionThere are many other design considerations and scenarios which are beyond the scope of this article. I hope this article on how to migrate Oracle Databases To SQL Server 2016 was helpful. If you have any comments or suggestions, please drop us a comment below in the comment section. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Tech PostsComparing Bluetooth 5.3 vs 5.4 – Features and Spec DifferencesThis post compares Bluetooth 5.3 vs 5.4 to understand its features and differences better. Note: ...
Tech PostsComparing GDDR7 vs HBM3 Specs – What we know so far!As the demand for high-performance computing continues to grow, so does the need for more ...
Tech PostsGDDR6 vs GDDR7 – Specifications ComparisonThe world of graphics memory is evolving rapidly, with new technologies constantly being developed to ...
Tech PostsList of the Best mmwave 5g PhonesThe upcoming 5G technology is not the same across all networks. There are different ways ...