How to Change Batch Size for Oracle to SQL Server Migration using SSMA
Migrating data from Oracle to SQL Server is very complex process. Although, it has become little easy using SQL Server Migration Assistant for Oracle. Last week, i was working on migration project where we were migrating Oracle databases to SQL Server. We had a requirement to change the data batch size as per application vendor pre-requisite.
We can control how much data SSMA will proceed at a time during migration. This way SSMA will process a consistent set of data every time. Batch size that you want to process during migration will be controlled by altering changes in Project Settings option. With the help of Project Settings window, you can customize how SSMA migrates data from Oracle to SQL Server.
Customize Batch Size during Data Processing
You can control batch size either for only current project or for all projects that you will create in SSMA to migrate data. Here I will show you how to change the batch size of data migration for both options.
Change Batch Size that will apply to all projects in SSMA
Launch SSMA for Oracle. Click on Tools tab. You can see there are two options that are highlighted in below image.
- Project Settings
- Default Project Settings
Any changes you will make in Project Settings will apply to only current project of data migration whereas any changes you will make in Default Project Settings will apply on all projects that you will create in SSMA for Oracle.
Now if you want to change the batch size for all projects, click on Default Project Settings. You will get below window. Now click on Migration tab from left side pane. You can see the Batch Size is showing in to right side pane. Change the batch size as per your choice here. Remember, this will apply to all projects that you will create for migration. The value showing in this image is the default value for the Batch Size.
Change Batch Size for Current Projects only
If you want to change it only for current project, then you should click on Project Settings tab from the first image. Once you get Project Settings window, click on General option as highlighted in below image. Once you click on general, select Migration tab from left side pane. Again, you will get Batch Size settings in right side pane. Change as per your requirement and click on OK button to apply the change.
There are lot of options that you can change and customize for your data migration using Project Settings dialog box. Have a look at the all options and make changes that are suitable for your data migration. Make sure to do proper research about the settings that you are going to change and be aware about the impact of that setting on migration if you are changing its value.
- Install Microsoft SQL Server Migration Assistant for oracle to Migrate Oracle Database to SQL Server
- How to change Network Packet Size in SQL Server
- How to Change number of user connections allowed in SQL Server
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018