×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Should I create a schema for each database (convert MSSQL to ORACLE)

Should I create a schema for each database (convert MSSQL to ORACLE)

Should I create a schema for each database (convert MSSQL to ORACLE)

(OP)
Hi, I am new to ORACLE.  We currently have a lot of databases running on a Microsoft SQL server(MSSQL).  I am working on converting them to ORACLE, which is running on Solaris.  I know how to load a database from MSSQL to ORACLE, but I am not quiet understanding some ORACLE design issues.

To convert these MSSQL databases to ORACLE, should I create one schemas for each database on different tablespace? Am I on the right track? What is the best approach for the conversion?  Adding more instance does not matter here, right?

Any advice would be very appreciated.  Thanks!

Mary


RE: Should I create a schema for each database (convert MSSQL to ORACLE)

Hi Mary,

The answer, sorry, is: "It Depends."

Use Separate Instances If:
==========================
You need to be able to take down individual databases once they're on Oracle (and by databases I mean the groups of tables that used to be an MSSQL db)

The databases are large and/or busy.

Use Schemas Within One Instance If:
===================================
You don't mind that when one database is unavailable everything is.

The databases are not too large or too busy to need individual optimization approaches.

You should also bear in mind:
=============================

That processes working in one instance can read/update tables in another instance easily.

There's no programming advantage to having all your tables in the same instance -- a remote table can look just like a local table to a programmer.

If your applications get real busy it's much easier to move just the one database to another server -- so that you can spread the load about.

My bias is (obviously) to create separate instances for each existing MSSQL database. There's less work involved in having everything in the same instance -- but not *that* much less.

Mike
michael.j.lacey@ntlworld.com
Cargill's Corporate Web Site

RE: Should I create a schema for each database (convert MSSQL to ORACLE)

(OP)
Hi Mike,

Thank you very much for your valuable information.  It is very helpful to me. I've been stuggling over these two ways.  Now I can see the advantage of each method.  

I prefer creating separate instance for each MSSQL databases too. But I still have some questions on multiple instances.  On MSSQL,  there are over 15 databases.  Some are big and some are small, and they all can be fit into our new server.  So is creating over 10 instances on one server practical?  will so many instances on one server effect performance badly or just slightly?  Should they be spread over multiple servers?

Any advice or suggestion on these?  Thanks very much!

Mary

RE: Should I create a schema for each database (convert MSSQL to ORACLE)

   Each instance requires its own SGA and background processes.
   Do the MS SQL databases support more than one application?
One option would be to have one Oracle instance per application.
There can be multiple schemas within one application.

Jim Carlson
nx56@inetarena.com

oracle, vb, some javascript

RE: Should I create a schema for each database (convert MSSQL to ORACLE)

(OP)
Hi Jim,

Yes, The MSSQL databases support multiple applications.  I got the idea.  

Thanks very much for your help!

Mary


RE: Should I create a schema for each database (convert MSSQL to ORACLE)

Hi, Mary.

I would expect that the biggest problem in creating so many instances is that you may run short of memory.  You are right that each instance needs its own SGA which must be large enough to efficiently run the application.  You're in danger of duplicating memory structures which could be shared.

Of course this is not an all or nothing decision.  Instead of creating an instance for every single database perhaps you could create only a few.  Combine all the small applications into a single instance and put all the large applications into separate instances.

RE: Should I create a schema for each database (convert MSSQL to ORACLE)

Karl's quite right, the balance is between:

one instance per application:
    simplicity in managing the different applications
    uses more memory

one instance containing lots of application:
    can become very unwieldy
    uses less memory

"Combine all the small applications into a single instance and put all the large applications into separate instances." -- Yes -- I wish *I'd* said that.

Mike
michael.j.lacey@ntlworld.com
Cargill's Corporate Web Site

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close