locked
Migrate from Oracle to SQL server and removing the SYSDB and ssma_oracle schema and all SSMA objects from target SQL Server RRS feed

  • Question

  • Hi,

    We are trying to migrate an oracle schema from Oracle 11gR2 database to SQL Database (SQL Server 2017 Azure Database).

    We were able to convert the schema and synchronize it the target SQL server DB. We also manually converted some objects which the tool (SSMA) did not convert. We are now looking to cleanup SSMA  for oracle components and have the following questions.

    According to the note from microsoft docs(below) the ssma_oracle schema should have been created in the sysdb but in our case it did not ?  In our case the ssma_oracle schema got created inside the new target SQL server database and not in the sysdb? Can this ssma_oracle schema be removed from the target SQL server database?

    Also,how can we validate that the migrated database no longer uses functions(or has any dependencies on any objects) in the ssma_oracle schema? So it can safely be removed?

    Is there any doc which goes through the steps of cleaning up and common issues faced during clean up?

    We also plan to migrate some more schemas in the future in that can I just remove the extention pack from this target sql server but retain the SSMA client ? 

    Appreciate your assistance is resolving the above questions.Thanks!


    Removing SSMA for Oracle Components

    When you have finished migrating databases from Oracle to SQL Server, you might want to uninstall SSMA components. You can uninstall the client components at any time. However, you should not uninstall the extension pack from SQL Server unless your migrated databases no longer use functions in the ssma_oracle schema of the sysdb database.

    Monday, December 2, 2019 9:20 PM

All replies

  • 

    Hi sk_786,

     

    >>Is there any doc which goes through the steps of cleaning up and common issues faced during clean up?

     

    Please refer tohttps://www.sqlshack.com/microsoft-sql-server-migration-assistant-ssma-v7-1-for-oracle/

     

    /*

    Removing SSMA for Oracle Components */

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, December 3, 2019 7:06 AM

  • Thanks Dedmon,

    I am still not sure why the ssma_oracle schema has been created inside the target sql server database and not the sysdb? My version of SSMA for oracle is v8.3.0. All the objects and  functions are inside the ssma_oracle schema in the target database and not sysdb. Is there a change from older versions of SSMA? I do not see any tables or stored procedures or function related to ssma inside the sysdb database that is created as part of the extension pack installation?

    The link you suggested to refer also has the same note which says the ssma_oracle schema is expected to be in the sysdb and not in the target database? Also  how we can validate that the migrated database no longer uses functions or any objects in the sssma_oracle database schema is not clear?

    Note from the link.

    The SSMA v7.1 for Oracle Extension Pack should only be removed if any of the migrated databases does not use or does no longer use functions in the sysdb .ssma_oracle database schema. I would also like to add that we are migrating from oracle on prem to Azure SQL database (PAAS). The SSMA document says " You must install the extension pack files on the computer where the migrated databases will be hosted. That computer must be running SQL Server" In our case this is database as a service or Platform as a service is that why we see the sysdb objects not created? Just guessing..




    Appreciate feedback to clarify these questions.






    • Edited by sk_786 Tuesday, December 3, 2019 4:27 PM
    Tuesday, December 3, 2019 3:45 PM
  • Hi sk_786,

     

    Did you install SSMA for Oracle extension pack correctly? According to the document, The SSMA extension pack adds the sysdb and ssmatesterdb databases to the specified instance of SQL Server. The database sysdb contains the tables and stored procedures required to migrate data and the user-defined functions that emulate Oracle system functions. The ssmatesterdb database contains the tables and procedures required by the Tester component.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 4, 2019 7:42 AM
  • Thanks Dedmon,

     I followed below instructions from microsoft. I thought the tester database is optional so I did not install it.

    The target SQL server in question is a logical as this Azure SQL database (Platform as a service) the sysdb got created but without the objects.

    After the download, you must install SSMA for Oracle.


    Installing the SSMA for Oracle Client
    1. Double-click SSMAforOracle_8.5.0.msi.
    2. On the 'Welcome' page, click Next.
    3. If you do not have the prerequisites installed, a message will appear that indicates that you must first install required components. Make sure that you have installed all prerequisites, and then run the installation program again.
    4. Read the End User License Agreement. If you agree to the terms, select "I accept the agreement" option and click Next.
    5. On the 'Choose Setup Type' page, click Typical.
    6. Click Install.

    In addition to the SSMA program files, you must also install the SSMA for Oracle Extension Pack on the SQL Server machine.

    Installing the SSMA for Oracle Extension Pack
    Installing the extension pack creates new database, sysdb, on the instance of SQL Server. The extension pack adds tables, stored procedures, and user-defined functions.
    1. Double-click SSMA for SSMAforOracleExtensionPack_8.5.0.msi file.
    2. On the 'Welcome' page, click Next.
    3. Read the End User License Agreement. If you agree to the terms, select "I accept the agreement" option and click Next.
    4. On the 'Choose Setup Type' page, click Typical.
    5. On the 'Ready to Install' page, click Install.
    6. On the 'Completed the First Step of Installation' page, click Next. A new dialog box will appear, in which you select the target SQL Server platform for the extension pack installation.
    7. Select the target SQL Server platform and click Next. If installing Extension Pack for SQL Server running on Windows, then new dialog will appear asking you to select a local SQL Server instance. For SQL Server on Linux next step is skipped.
    8. Select the instance of SQL Server where you will be migrating Oracle schemas, and then click Next. The default instance has the same name as the computer. Named instances will be followed by a backslash and the instance name.
    9. On the connection page, select the authentication method and then click Next. Windows Authentication will use your Windows credentials to try to log on to the instance of SQL Server. If you select SQL Server Authentication, you must enter a SQL Server login name and password.
    10. On the next page, select Install Utilities Database n, where n is the version number, and then click Next. The sysdb database is created and the user-defined functions and stored procedures are created in that database. If Install Tester Database option is checked the tester ssmatesterdb database will be created.
    11. To install the utilities to another instance of SQL Server, select Yes, and then click Next. Or, to exit the wizard, click No.
    NOTE: Extension pack functionality is limited when running SQL Server on Linux or Azure SQL Database Managed Instance: server-side data migration and tester features are not supported. Azure SQL Database and Azure SQL Data Warehouse do not support extension pack.

    We installed the SSMA client for oracle but when we install the SSMA extension pack in Azure SQL instance it creates an empty sysdb. According to the below installation instructions for SSMA extention pack sysdb is created with ssma_oracle schema that contains user defined functions and stored procedures?


    On the next page, select Install Utilities Database n, where n is the version number, and then click Next. The sysdb database is created and the user-defined functions and stored procedures are created in that database. If Install Tester Database option is checked the tester ssmatesterdb database will be created.

    After you install the extension pack, an ssma_oracle.bcp_migration_packages table appears in the sysdb database. I dont see this table as well?

    Then when we run the SSMA tool to convert and synchronize the Oracle schema to the target SQL server it creates a schema by name "oracle_schema" in the target SQL server with a lot of  functions,stored procedures and some tables inside the target sql server database. Should these be created in the sysdb database and not the target SQL server DB? Or are these two different things?

    Also when looking to cleanup should we uninstall the extention pack from the SQL server instance (Azure SQL Database)?
    Will that also remove the ssma_oracle schema and all its objects inside the target sql server database?If not how should the ssma_oracle schema from the target sql database be handled? Should it not removed at all? If so will it be carried to the Test and Prod environment? And only the SSMA extention pack be uninstalled which removes the sysdb.

    Wednesday, December 4, 2019 5:50 PM
  • Hi sk_786,

     

    As you mentioned, Extension pack functionality is limited when running SQL Server on Linux or Azure SQL Database Managed Instance. I think it's because of the difference in platform, it behaves differently in sql server on premise and azure sql database.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 5, 2019 2:42 AM
  • Thanks Dedmon,

    But what about the ssma_oracle schema that got created inside the new target SQL server database. It has objects like functions, stored procedures and tables. Is this ssma_oracle schema something that gets created in all target SQL server databases when one is migrating from oracle to SQL server? Can it be removed without impacting the actual application tables incase there is a dependency. If we are not supposed to remove it will this schema be carried to all environments like Test and Prod?

    Thursday, December 5, 2019 4:31 PM
  • Hi sk_786,

     

    For the behavior of the ssma extension package in azure sql db, my recommendation is to be safe, do not delete these ssma_oracle schema created in the instance itself.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 6, 2019 3:08 AM
  • Hello,

    What is your target? Original post mentions "(SQL Server 2017 Azure Database)", which I would assume is SQL Server 2017 running on a VM. Then later you mentioned you moved to PaaS (Azure SQL Database). The answer to your question will be different depending on your actual target. If you used SSMA to migrate to SQL Server 2017 and now trying to host this database on Azure SQL Database, then it may not work properly (depending on the complexity of your schema) as there are some features that are not supported in Azure SQL Database. If you final destination is Azure SQL DB, then you should migrate your original Oracle schema to Azure SQL DB straight using SSMA, in which case SSMA will not use unsupported features.

    In regards to ssma_oracle schema, the purpose of it is to host functions and procedures that emulate those of Oracle, which are not natively supported in SQL Server. Regular expressions (ssma_oracle.regexp_like) would be one example. SSMA today is not smart enough to filter these to just the set that you actually need, so it always deploys all of them. There are some of them that you probably can remove, but some might be still needed, I don't think there is an easy way to find out, so it's better to leave it alone.

    Documentation is a little bit outdated on this matter. The ssma_oracle schema indeed used to be in the sysdb, thus making it one per server, as all of the functions are the same and can be easily shared across multiple migrated databases on the server. Once we introduced Azure SQL Database support, we had to pull the schema in each and every database, as Azure SQL DB does not support cross-database queries. In Azure SQL Database you are not going to get any sysdb at all, because it won't be accessible from anything but the sysdb itself, which makes it pointless. Supporting two different approaches for on-premises SQL Server and Azure SQL Database would be too much maintenance, so we decided to just create schema in the converted database for all supported SQL targets.

    Sysdb is created by extension pack when you install it on the on-premises SQL Server and hosts components necessary for server-side data migration and tester.

    Extension Pack itself is not supported on Azure SQL DB or Azure SQL Data Warehouse. That's it - you cannot install it there. We have limited support for Azure SQL Database Managed Instance. "Limited support" means you cannot use server-side data migration or tester features, but there is one more key component in the extension pack that drastically improves conversion capabilities, which we can use on Managed Instance. To emulate Oracle's autonomous transactions, package initialization (default values for packaged variables) and more - we have a set of extended stored procedures, which are being installed as part of Extension Pack.

    This essentially gives us following components that are installed with Extension Pack for Oracle:

    1. sysdb with components necessary for server-side data migration
    2. ssmatesterdb to support tester features of the SSMA client tool
    3. Extended stored procedures (XPs) to support conversion of autonomous transactions, packaged functions, etc.

    After you done with your migration and move to production you can remove/uninstall (1) and (2), but not (3), so extension pack cannot be removed completely. But if your target is Azure SQL Database or Data Warehouse, then it's not even a question, as you can't install Extension Pack there.

    I hope this answers your question.

    Regards,
    Alex.

    Monday, December 23, 2019 8:54 PM