1

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?)

|improve this question
  • how many UDFs are there in the sysdb database that you need to move? – Jack Douglas Nov 21 '18 at 11:53
  • There seem to be 196 Scalar Functions and about 100 stored procedures. (Also 3 user-defined types, 4 assemblies, 8 tables, 7 views) – gordon613 Nov 21 '18 at 12:15
  • 2
    Have you tried creating any of these in your database and removing them from sysdb to see if anything breaks? – Jack Douglas Nov 22 '18 at 22:16
  • @Jack Douglas. I have briefly looked over all my 761 references to sysdb and most of them seem to be calls to only a few functions. So this is definitely a way to go... – gordon613 Nov 25 '18 at 18:18
2

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. :)

|improve this answer
  • Thanks for your answer... I will hopefully be looking at this in the next few days... – gordon613 Nov 25 '18 at 18:11
  • Update... I have looked into this. I think your answer is great for merging a normal database, but I think the sysdb database has some special peculiarities that render this a unique challenge. One of the main stumbling blocks seems to be the dependency on dll's. shouldiremoveit.com/… I imagine that it would be impossible to install these dll's on an Azure database. – gordon613 Dec 11 '18 at 12:56
  • You may want Microsoft Support then to help with this. CLR can allow imports of DLLs (in truth, it becomes part of the Instance), but that would be something Microsoft could help you with removing dependencies like that. The whole point of Migrating from Oracle to SQL Server in the first place was to go away from Oracle Logic, I would think... – clifton_h Dec 11 '18 at 18:56
  • That's a good idea! I hope to update this post if I find out anything! – gordon613 Dec 13 '18 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.