It was never a surprise for me meeting such people. Now this DBA asked me the fundamental difference with how instances are done in Oracle Vs SQL. And he was looking for my advice to when to use what. This was an interesting conversation and I wanted to pen down the same for your future references.
What are the Differences?
One area a difference between Oracle and SQL Server is the relationship between instances and databases. In Oracle, one instance = one database (each of which can have several schemas). The database contains lots of schema objects. In SQL Server, one instance = several databases (each of which can have several schemas). This includes standard built-in databases, some of which store much of the schema information. These built-in databases are master, model, msdb, & tempdb.
When migrating from Oracle to SQL Server, you need to consider whether you want to take Oracle schemas and map them to SQL Server schemas or move them to separate SQL Server DBs. There is no general rule of thumb but:
- If they do not interact much, you may want to use separate DBs.
- If they interact a lot, then you may be best to migrate them to different schemas in a single SQL Server database.
I use the above mechanism to take the simple decision tree.
And finally, as part of conversation, one thing to avoid in SQL Server is the use of the default/dbo schema; it is better to create new schema(s) relevant to your job. I wrote about them at: Importance of Database Schemas in SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)