Best way to model SQL Server

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.

No Data
  • 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.