SQL SERVER – Instance Definition Working with Oracle and SQL Server

During various conferences and user group Meetups I meet tons of new people who keep asking me on a number of topics that can sometimes be challenging for me. In a recent SQL Server Bangalore User Group chapter that I just bumped into an individual who had worked extensively with Oracle and was in transition to moving to SQL Server.

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)