Merging the sysdb database (a dependency due to conversion from Oracle) into my SQL Server 2014 Express Database

Question

I am looking to convert my SQL Server 2014 Express Database to Azure SQL Database.

My SQL Server database was automatically converted from Oracle a number of years ago, and needs the sysdb database (provided by Microsoft) as a dependency to provide user defined functions that emulate Oracle functions.

I ran the Data Migration Assistant to check for compatibility, and the main problem seems to be that "Cross-database references not supported in Azure SQL Database". This relates to the dependency on the sysdb database.

The two recommendations of the Data Migration Assistant are either:

1. Move the dependent datasets from other databases into the database that is being migrated. OR
2. Migrate the dependent database(s) to Azure and use 'Elastic Database Query' functionality to query across Azure SQL databases.

I would eventually like to get rid of all Oracle related references, although I do not have the resources to do that right now. I feel that the best thing to do is to merge the sysdb database with my database (so that it would then work with Azure SQL Database) and then as a long-term project slowly rid myself of Oracle dependencies.

Any ideas about how to achieve this? (Or a better idea?)

Answer 1

In SQL Server, you can take advantage of the SCHEMA and SYNONYM, the former which are similar to Oracle's User (hence the sysdb database) and the latter lets you hide the actual object name from your application. See the documentation for SCHEMA and SYNONYM.

1) On your target DB, create the sysdb schema:

USE [TargetDB]
GO
CREATE SCHEMA [sysdb] AUTHORIZATION [dbo]

2) In your nonproduction environment, merge all the relevant procedures, tables, and any required object into this schema.

• You can either use the SQL Server Import and Export Data utility or script it out yourself.

3) If you are unable to specify a schema in your application, create SYNONYMS for each object you require:

USE [TargetDB]
GO
CREATE SYNONYM dbo.usp_myproc FOR [TargetDB].sysdb.usp_myproc
GO
GRANT EXECUTE ON dbo.usp_myproc TO [USER]
GO

Qualification: you can only specify the current Database or tempdb (if the object is a temp table) and There might be some issues if there are duplicate objects in each schema, but otherwise this will work just fine. :)