June 24, 2010 at 2:39 pm
We are in the process of designing a plan to migrate our applications from Oracle to SQL Server. Each application is grouped in an Oracle schema and our critical applications share data between the schemas (e.g. the Claims System shows Membership contact information in a Claims Application screen). All applications are stored in one Oracle database. We are a small company so the total size of the data when exported is 5GB.
Should we migrate each Oracle schema into its own SQL Server database? Or should we create a separate database for each Schema/Application?
June 28, 2010 at 1:34 pm
If you are confortable with current structure on Oracle side I would keep the same structure in SQL Server.
In the SQL Server world does not exist the "schema" concept but nothing prevents you to have objects owned by different accounts - after all an Oracle schema is nothing but the collection of objects owned by an specific account.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 29, 2010 at 6:54 pm
Not exactly true. There is a little translation going from Oracle to SQL Server. It does depend on wether you are comfortable with the current setup. So if you want to set things up most like what you have within Oracle...keeping in mind file seperation and such....
Schemas = databases
This would allow you to create seperate data and log files for each of the corresponding Oracle schemas which would give you the easiest setup with the maximum amount of control over the file setup.
You could also create a singular database with Schemas representing each of the Oracle Schemas. However, to get the same type of file seperation you would have to create some secondary data files and then move objects into those seperated filegroups....which could just be a pain in the rear.
If you are used to the Oracle environment and new to SQL Server then you can find one of my presentations on SQL Server for the Oracle DBA on my blog site....if you can't then just shoot me a line and I will dig it up for you. It explains the translations pretty clearly.
We are migrating a large Oracle database right now for a client and there is a Oracle to SQL Server migration tool that you can download from Microsoft that may make the transition a little easier for you.
Hope this helps....
Cheers,
AJ
Arie Jones
http://www.programmersedge.com
http://www.sqlsherpa.com
June 30, 2010 at 7:13 am
Thanks Arie. I found your presentation and will review it. We are based out of Chicago and I will be attending the PASS Summit in November. So, I will keep an eye out for your presentations.
Christina Zanoni
Senior DBA
ALAS, INC
June 30, 2010 at 7:16 am
Coolio. Keep an eye out for the next SQL Server Saturday....I think that they said they may have another one in the fall in Chicago since the first one was so popular.
Cheers,
AJ
Arie Jones
http://www.programmersedge.com
http://www.sqlsherpa.com
July 1, 2010 at 8:26 am
This may or may not be relevant.
A while ago I had to set up a sql version of an oracle database(s).
I went with one sql database and multiple schemas that matched the oracle schemas.
I did this because it allowed the same code to work on both with minimal effort.
We were dealing with a vendor supporting an application with an oracle back end, but all our reporting had to come from sql. The vendor would give us code which we could then use on the sql database with minimal effort. Its a long story about why we needed to start with vendor code each time, but this worked pretty well.
July 1, 2010 at 8:45 am
Arie Jones-435881 (6/29/2010)
Not exactly true. There is a little translation going from Oracle to SQL Server. It does depend on wether you are comfortable with the current setup. So if you want to set things up most like what you have within Oracle...keeping in mind file seperation and such....Schemas = databases
This would allow you to create seperate data and log files for each of the corresponding Oracle schemas which would give you the easiest setup with the maximum amount of control over the file setup.
The Oracle-Schema = SQLServer-Database concept is a very common misunderstanding.
An Oracle schema is nothing but the collection of objects owned by a specific account. Exactly the same happens on SQL Server side, you can have collections of objects owned by a specific account.
Moreover, all Oracle-Schemas in a particular database - called "instance" on the Oracle world - are backup at the same time and are restored/recovered as a whole ensuring integrity.
What would happen if an Oracle-Schema based environment is deployed as different databases in an SQL Server environment? how do you ensure data integrity in between all those databases if the time comes where a restore/recovery is needed?
Bottom line is... Oracle-Schema != SQLServer-Database
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2010 at 9:52 am
Sorry but that is simply not the case. Shemas are 'normally' allocated in blocks to group the logical data elements together.. that part is true. Much like you do in grouping tables and assorted objects together in a database. Tables and various other objects are alloacted into a tablespaces which are the actual data files.
"Tablespaces are used to limit or extend disk space allocation for the data of a particular database or user, perform partial backups or recovery, and even allocate data storage among devices (such as tape drives, CD ROM drives, USB hard drives and so on) that in some cases improves performance. "
You do not have to backup and restores all schemas within an instance. You can use RMAN to backup/restore individual tablespaces. So if you are building applications within Oracle normally you would create a tablespace or decide to join with another tablespace ...create your schema...and then start creating objects within the schema to support the application.
I fail to see why 1 of the ways in which you can think of the conversion process would be schema = database.
Cheers,
AJ
Arie Jones
http://www.programmersedge.com
http://www.sqlsherpa.com
July 10, 2010 at 7:00 pm
Should we migrate each Oracle schema into its own SQL Server database? Or should we create a separate database for each Schema/Application?
This really depends on your application and requirements. You can follow one of two typical approaches to schema mapping:
1. Every Oracle schema becomes a separate SQL Server database. Use this method when there are few references between Oracle schemas.
2. Another approach is to map all Oracle schemas to one SQL Server database. In this case, an Oracle schema can become a SQL Server schema with the same name. Use this method when different source schemas are deeply linked with each other.
Not all Oracle database objects have direct equivalents in SQL Server.
July 11, 2010 at 2:18 am
Just a little side note :
Keep in mind ms published a "sqlserver migration assistant for oracle"
This may get you started with your plan of attack 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 11, 2010 at 8:14 am
Arie Jones-435881 (7/1/2010)
Sorry but that is simply not the case. Shemas are 'normally' allocated in blocks to group the logical data elements together.. that part is true. Much like you do in grouping tables and assorted objects together in a database. Tables and various other objects are alloacted into a tablespaces which are the actual data files.
:pinch: This is uterly wrong, so wrong I'm not sure if you are going to grasp how wrong is it.
There is not such a thing as schemas allocated in blocks. Schemas are nothing by the collection of objects owned by a specific account.
Tablespaces are not the actual data files, tablespaces are collections of data files.
Arie Jones-435881 (7/1/2010)
"Tablespaces are used to limit or extend disk space allocation for the data of a particular database or user, perform partial backups or recovery, and even allocate data storage among devices (such as tape drives, CD ROM drives, USB hard drives and so on) that in some cases improves performance. "
There is no such a relationship in between tablespaces and schemas as you are suggesting.
You can allocate an object in whatever tablespace you want no matter to what schema .e.g. owner it belongs.
Arie Jones-435881 (7/1/2010)
You do not have to backup and restores all schemas within an instance. You can use RMAN to backup/restore individual tablespaces. So if you are building applications within Oracle normally you would create a tablespace or decide to join with another tablespace ...create your schema...and then start creating objects within the schema to support the application.
There is nothing more dangerous that a little knowledge, isn't it?
Yes... YES! Do you even know what an SCN is? Oracle databases have to be recovered as a whole. Please note I've used the word "recovered" which is not a synonym for "restored".
In some particular cases like when you have a tablespace marked as read-only you are allowed to restore/recover such specific tablespace individually - this is not the general rule.
Arie Jones-435881 (7/1/2010)
I fail to see why 1 of the ways in which you can think of the conversion process would be schema = database.
This is because you are not looking harder.
An Oracle schema is not a database but a logical collection of objects - owned by the same account - that belong to a database.
Just for starters If you go with the common misunderstanding of schema=database your backup/recovery strategy will be screwed-up and problem is you are going to notice it the day you need to recover your database. Have your resignation letter at hand when the time comes.
Hope this sheds some light
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply