question

JamesHackney-9611 avatar image
0 Votes"
JamesHackney-9611 asked AmeliaGu-msft edited

After migration from Oracle to Azure SQL Database

Please excuse my ignorance but I am not a DBA and until my now database experience has been relegated to basic CRUD operations. I just used SSMA to migrate an Oracle database to an Azure SQL Server instance and have some questions

Q1. Every SQL Server database I have ever used has the dbo schema. This one contains the name of the Oracle schema it came from. So now instead of tables or procs being dbo.<table name or proc name> it is SDCS.<table name or proc name>.

Am I supposed to change all of these to dbo or just leave them as they are?

Q2. All of my procs are now in this format: SDCS.INSERT_BMA$INSERT_SDCS_PRIME and has several things such as SDCS.INSERT_SDCS$SSMA_Initialize_Package

Am I supposed to change the name to <schema>.INSERT_BMA and then just fix any errors within the procs?

Q3. I have a ton of procs related to Oracle such as ssma_oracle.db_clean_storage, ssma_oracle.db_check_init-packages, ssma_oracle.db_error_exact_once_row_check, etc.

What, if anything am I supposed to do with these?



azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlexanderIvanov-MSFT avatar image
1 Vote"
AlexanderIvanov-MSFT answered AlexanderIvanov-MSFT commented

Hello,

Q1. Every SQL Server database I have ever used has the dbo schema. This one contains the name of the Oracle schema it came from. So now instead of tables or procs being dbo.<table name or proc name> it is SDCS.<table name or proc name>.

Am I supposed to change all of these to dbo or just leave them as they are?

This is configurable in SSMA, by default we map Oracle's schema to SQL Server schema. It depends on how you want your database to be accessed. If you migrate multiple Oracle schemas to one SQL Server database, it makes sense to map them to separate schemas (which is the default).

Q2. All of my procs are now in this format: SDCS.INSERT_BMA$INSERT_SDCS_PRIME and has several things such as SDCS.INSERT_SDCS$SSMA_Initialize_Package

Am I supposed to change the name to <schema>.INSERT_BMA and then just fix any errors within the procs?

These are packaged procedures. SQL Server does not have a concept of packages, so during conversion the names of these procedures get prepended with the package name to eliminate the name collision with procedures defined outside of the package.
There is no configuration related to this, this is how SSMA does the conversion for the reason outlined above. If you want you can change the names and update hte references after the conversion.

Special ..$SSMA_Initialize_Package procedure is added to initialize packaged variables values with the defaults. Again, there is no concept of packages (and therefore packaged variables) in SQL Server, so SSMA emulates them using special db_storage table.

Q3. I have a ton of procs related to Oracle such as ssma_oracle.db_clean_storage, ssma_oracle.db_check_init-packages, ssma_oracle.db_error_exact_once_row_check, etc.

What, if anything am I supposed to do with these?

In a continuation of the previous answer - these procedures and functions support the emulation of some of the Oracle's behavior related to packages and they will be needed at runtime when you use your database.

In addition to the routines you have listed, there are also emulation procedures and functions that directly map to the Oracle's procedures and functions that are not available in SQL Server natively or behave differently. For example, Oracle's SUBSTR function allows negative position, while SQL Server's SUBSTRING does not. This difference in behavior is covered by the provided a set of [ssma_oracle].[substr*] emulation functions.

Hope this helps,
Alex.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AlexanderIvanov-MSFT It sounds like I just need to focus on the errors and warnings found in the Analysis Report then. Thank you Alex, you have been a tremendous help.

0 Votes 0 ·

Hello,

Yes, conversion errors and warnings is what SSMA does not convert automatically today and it has to be addressed manually. Everything else is presumably converted using native SQL Server functionality or emulation functions that we provide.

Regards,
Alex.

0 Votes 0 ·