To Schema or Not To Schema

  • Article

One of the first decisions you need to make when migrating to SQL Server is to decide whether to migrate to a schema or to a database. There may be different concept of schema between your source database product and SQL Server. Unlike Oracle, for example, schema in SQL Server is not necessarily linked to a specific user or a login. SQL Server can also contain multiple databases. For more information about user schema concept in SQL Server, visit https://msdn.microsoft.com/en-us/library/ms190387.aspx

Using SSMA, you have the option to migrate a schema to a SQL Server database or migrate to a schema in a specified SQL server database.

  • Migrate schema to a separate SQL Server Database

This is the default conversion approach in SSMA and is the preferred approach when there are few references between source database schemas. The converted schema will be assigned to "dbo" schema in SQL Server - the default database owner in SQL Server.

  • Migrate schema to a SQL Server Database.

You can modify the project setting to map the source schema to a schema with the same name in an existing SQL Server database. You should consider to use this approach if your source schemas are deeply linked with each other (such as the case where you have many foreign key relationship to another table in a different schema). SQL Server does not allow constraint relationship to another table in a different database. You may implement a workaround using database triggers for one-off scenario.

 

To specify the conversion option, go to SSMA project setting > conversion > general:

 

 

Under default schema mapping, you can select whether to map "Schema to Database" or "Schema to Schema".