Hi Ian,
MSSQL has an isolated "master database" which contains system wide
information like file locations, user permissions, backups etc. It also
has a "template" database MSDB. When the new database is created, the
content of MSDB is copied into the new database. There is also a special
database which is used for sorting and hashing. Yes, the name is TempDB,
or "temporary database" which performs the role of the TEMP tablespace.
By the way, Oracle 18c now allows creating private temporary tables.
Oracle, on the other hand, has a container database, which contains the
central data dictionary and all the tenant databases. Logical separation
is much cleaner with the Microsoft. As for the claim of Microsoft way
being inferior, I cannot say anything until I hear arguments for such a
claim. Saying that MS approach is inferior to Oracle approach is like
calling CNN "fake news" without any arguments. Who would do something
like that?
Oracle enterprise manager is far inferior to SQL Server data studio, at
least in my opinion. DB2 has another approach, where databases are
completely separate and there is no common data dictionary. From the
practical point of view, all 3 allow me to create a new database. Only
Oracle charges me for that privilege. Oracle justification of the
multi-tenant architecture is that it saves resources. Well, the
resources saved by the multi-tenant architecture are much, much cheaper
than the license for the multi-tenant option.
On 08/30/2018 01:32 PM, MacGregor, Ian A. wrote:
I remember a panel discussion when multi tenancy was first introduced which responded to the question of why MSSQL had "the same thing for free". with you really cannot compare the two, imitating the MSSQL approach to multitenancy was inferior.
How do they differ?