Can I migrate multiple SQL DBs into a single Babelfish instance and keep them segregated for different users?

Question

I am migrating multiple SQL DBs (say A and B) to a single Babelfish instance.

I have SQL instance A with tables x, y and z and instance B with tables x, y and z.

I want Babelfish to have some way to have them coexist as A.x, A.y, A.z, B.x, B.y and B.z.

The original idea was to prefix the table names (so A_x, A_y, etc.) but this is untenable because of the app changes required, so I need some way to make it transparent to the application as long as the connection points to A or does a "USE A" at the start.

Answer 1

Babelfish lets you migrate databases A and B while retaining the SQL server database structure as well as the references A.dbo.x, B.dbo.x , etc. (note that cross-DB queries with 3-part object names are not yet currently supported outside the current database, but these are in the works). When you run "USE A" and then do "SELECT * FROM x", you'll touch A.dbo.x. When you do "USE B" first, you'll touch B.dbo.x. So when you run the DDL for creating databases A and B against Babelfish, you'll get basically the same as what you had in SQL Server and there is no need to change any syntax (again, assuming cross-DB queries are not currently needed).

Now, if your question is how to merge databases A and B into a single database, then that is not different from how you would do that in SQL Server itself. In this case I would recommend actually performing that merge in SQL Server, then reverse-engineering the resulting SQL Server database, and running that script against Babelfish to recreate that database.