Before I start I want to level set my terminology.
Database Instance - a SQL Server executable environment which hosts 1 or more databases. The database instances is also used to host the SQL Server job scheduling agent.
Database - a Database is a collection of one or more database schemas.
Database Schema - is used as the owner of one or more database objects. (Tables, Procedures, etc.)
To the Question: What is the best way to reverse engineer 6 database instances, which contain more than 300 databases with each database having 1 or more schemas which have the same name but with different database objects?
Be mindful that in a single database instance there may be views, functions and procedures which have database objects which reside in different databases.
What I've noticed with some of my tests is that the tool does not recognize objects which reside in another physical database which reside in the same model file.
What I've also noticed is that I am unable to find anything that resembles a SQL Server Database.
I should be able to group database objects, in a physical model, under a database.
Your opinions are greatly appreciated.
300 Databases sounds daunting to me. Are there really a few shared logical models, or are they truly independent designs? If independent, I would go ahead and make separate models for each. With a task this large, I'd enlist help and strongly consider the ER/Studio repo to help keep everything straight. If you have something shared, I'd model the 'superset' then build out another document or web page to track the variances. I've done the web page thing where I was distributing databases for an operational data store.
Yes there is a lot of overlap from database to database on the same database instance. I will probably model each database instance in a separate project file with each file having one physical database for each database in an individual instance. Unfortunately, the views, functions and procedures with have to be invalid because the tool does not have the ability to look for database object in another physical database in the same project.
Thanks for the advice.
Powered by IDERA