I am taking over a Database set up where we have 2 databases. One is a SQL SERVER 2000 db and the other is an Oracle 9i db.
THe previous DBA had/has a problem with transferring CLOB and Blob datatypes From the ORACLE db to SQL SERVER. He says that the OLEDB driver SQL SERVER provides has limitations particularly on the CLOB data because it truncates data over 4000 characters in length.
For the Blob issue:
We basically want to store/move attachments (word documents etc)
into image datatypes (SS) from the blob datatype (9i).
I figured I could do this using DTS or even using the Linked server method. I figured worst case scenario I would just store the documents on the server and then use SQL SERVER's textcopy utility to upload the documents into the image datatype column of the SQL SERVER table.
For the Clob Issue I have no idea what to do, because I cant find anything online that mentions SQL SERVER having issues with importing CLOB data over 4000 charcters in length, afterall the text datatype accomdates more than 4000 characters.
Does anyone have any suggestions for moving the Blob and most importantly the Clob datatypes from 9i to SQL SERVER without any truncation in the data??Did you try the DTS approach?|||...and you realize this will retart the firestorm argument about whether it is better to store BLOBs in the database, or in the File System with a simple link in the database...|||I don't think there is a limitation. I usually do a BCP to load them in. Check out this site for some good stuff:
TEXT, NTEXT, and IMAGE datatypes - Tutorial (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)|||Originally posted by bpdWork
...and you realize this will retart the firestorm argument about whether it is better to store BLOBs in the database, or in the File System with a simple link in the database...
I dont think DTS has an issue with migrating Blobs. It does have issues with migrating Clobs with data over 4000 characters in Length
p.s. LOL about starting a firestorm|||Originally posted by rdjabarov
I don't think there is a limitation. I usually do a BCP to load them in. Check out this site for some good stuff:
TEXT, NTEXT, and IMAGE datatypes - Tutorial (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)
Thanks. Ill go through it
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
migrate Oracle to sqlserver
Hi
I am a Oracle DBA who is going to embark upon a
oppurtunity to migrate Oracle to sqlserver.
Can somebody give me tips for
Learning sqlserver2k5 for a Oracle DBA Books
Or Beginner DBA/Development Books for sql2k5
How good is the migration assistant from m$$ for
migrating from oracle to sql2k5
Does it migrate data as well as procedural code ?
Do you anything for me to watch out for best parctices
migration guides
You opinion would be higly appreciated
regards
db2hrishyThere are a number of things to be aware of, the transition can be challenging, and once you've done one migration it will forever change how you view both Oracle and MS-SQL.
First and foremost, Oracle requires a row-oriented view of data in order to acheive good performance, and often to do anything at all. MS-SQL is exactly the opposite in that you need to abandon the row-oriented view of data (especially using cursors) in order to achieve good performance. This is a huge mind shift, and it is usually the hardest thing for a long time Oracle DBA to master.
Oracle provides PL/SQL, which spans several layers of the OSI model. MS-SQL provides many tools that together offer more functinality than PL/SQL, but they are many tools instead of just one tool. This can be a blessing or a curse depending on how you look at things.
The migration assistant provided by Microsoft is good, but like any generic migration tool it has both costs and benefits. Since I'm familiar with both Oracle and MS-SQL and have only had to migrate from Oracle to MS-SQL, I generally don't bother using any software assistant at all. As the auto makers are fond of saying: your milage may vary!
SQL-2005 provides two tools that make the migration of the data almost trivial. One tool that I couldn't live without for coversions is a Linked Server (http://support.microsoft.com/kb/280106). Another tool that is very powerful and you need to at least understand before you start is SSIS (http://msdn2.microsoft.com/en-us/library/ms141026.aspx) because it can offer simple solutions to otherwise noxious problems. There are more soltuions than just these two, but these are more than enough for 99.95% of the migrations that I've ever seen.
Schema migration can be a challenge, depending on how you/your business handles schema objects. If you have no existing migration controls (no SCCS, just brew the schema directly in production), then migration is relatively easy. If you have strict migration controls, then you need to determine the political issues, but technically it isn't going to be a big challenge. Unfortunately, most folks sit between the two extremes so the conversion can be charitably described as challenging. We might need to start a whole new thread for that discussion!
I don't know of anything that will migrate procedural code in a way that will suit me. I've seen many code migrators, a few of them produce working solutions, but I can't recommend any of them. I think that code needs to be manually migrated.
Anywho, I'll let you chew on this diatribe for the moment, but I've got lots more opinions if you are interested in them... Keep in mind that an opinion and a buck will get you a cup of coffee though (in other words, everybody has an opinion, but you can't trade an opinion for much of anything)!
-PatP|||Hi Phat
Thank you very much for taking time out and responding :-)
I have migrated (attempted) or tried migrating Db2 from mainframes to oracle and failed miserably :-) as i grossly underestimated the effort and here i am again trying to be brave :-)
The insights and experience some of you might provide is much valued by me.
I had a look SSMA(sqlserver migration assistant) and it claims to migrate Data as well as code anybody has working experience with this ?
For code migration i.e pl/sql
http://www.swisssql.com/
but it all depends on what the management thinks and if i can convince them for the $$ etc
For data migration i am thinking of SSIS or some open source ETL tool.
I have few questions though
1)Does anybody have a Migration template ?approach
for doing this sort of a activity which can be shared?
2)How good is SSMA for migrating data and PL/SQL to T-SQL
3)is SSIS free with SQL2k5 ?
4) When i migrate say a million row table from oracle to sql2k5 how do i ensure that all rows have been migrated and all the migrated rows are correct?
regards
Hrishy
I am a Oracle DBA who is going to embark upon a
oppurtunity to migrate Oracle to sqlserver.
Can somebody give me tips for
Learning sqlserver2k5 for a Oracle DBA Books
Or Beginner DBA/Development Books for sql2k5
How good is the migration assistant from m$$ for
migrating from oracle to sql2k5
Does it migrate data as well as procedural code ?
Do you anything for me to watch out for best parctices
migration guides
You opinion would be higly appreciated
regards
db2hrishyThere are a number of things to be aware of, the transition can be challenging, and once you've done one migration it will forever change how you view both Oracle and MS-SQL.
First and foremost, Oracle requires a row-oriented view of data in order to acheive good performance, and often to do anything at all. MS-SQL is exactly the opposite in that you need to abandon the row-oriented view of data (especially using cursors) in order to achieve good performance. This is a huge mind shift, and it is usually the hardest thing for a long time Oracle DBA to master.
Oracle provides PL/SQL, which spans several layers of the OSI model. MS-SQL provides many tools that together offer more functinality than PL/SQL, but they are many tools instead of just one tool. This can be a blessing or a curse depending on how you look at things.
The migration assistant provided by Microsoft is good, but like any generic migration tool it has both costs and benefits. Since I'm familiar with both Oracle and MS-SQL and have only had to migrate from Oracle to MS-SQL, I generally don't bother using any software assistant at all. As the auto makers are fond of saying: your milage may vary!
SQL-2005 provides two tools that make the migration of the data almost trivial. One tool that I couldn't live without for coversions is a Linked Server (http://support.microsoft.com/kb/280106). Another tool that is very powerful and you need to at least understand before you start is SSIS (http://msdn2.microsoft.com/en-us/library/ms141026.aspx) because it can offer simple solutions to otherwise noxious problems. There are more soltuions than just these two, but these are more than enough for 99.95% of the migrations that I've ever seen.
Schema migration can be a challenge, depending on how you/your business handles schema objects. If you have no existing migration controls (no SCCS, just brew the schema directly in production), then migration is relatively easy. If you have strict migration controls, then you need to determine the political issues, but technically it isn't going to be a big challenge. Unfortunately, most folks sit between the two extremes so the conversion can be charitably described as challenging. We might need to start a whole new thread for that discussion!
I don't know of anything that will migrate procedural code in a way that will suit me. I've seen many code migrators, a few of them produce working solutions, but I can't recommend any of them. I think that code needs to be manually migrated.
Anywho, I'll let you chew on this diatribe for the moment, but I've got lots more opinions if you are interested in them... Keep in mind that an opinion and a buck will get you a cup of coffee though (in other words, everybody has an opinion, but you can't trade an opinion for much of anything)!
-PatP|||Hi Phat
Thank you very much for taking time out and responding :-)
I have migrated (attempted) or tried migrating Db2 from mainframes to oracle and failed miserably :-) as i grossly underestimated the effort and here i am again trying to be brave :-)
The insights and experience some of you might provide is much valued by me.
I had a look SSMA(sqlserver migration assistant) and it claims to migrate Data as well as code anybody has working experience with this ?
For code migration i.e pl/sql
http://www.swisssql.com/
but it all depends on what the management thinks and if i can convince them for the $$ etc
For data migration i am thinking of SSIS or some open source ETL tool.
I have few questions though
1)Does anybody have a Migration template ?approach
for doing this sort of a activity which can be shared?
2)How good is SSMA for migrating data and PL/SQL to T-SQL
3)is SSIS free with SQL2k5 ?
4) When i migrate say a million row table from oracle to sql2k5 how do i ensure that all rows have been migrated and all the migrated rows are correct?
regards
Hrishy
Monday, March 19, 2012
Migrate from DB2 to MSSQL
Dear All,
I am exploring the feasibility of migrating from DB2 (v7) to MSSQL server 2005. My current applications are using UDB, but the DBA wants to change to MSSQL. I believe the impact is huge. For example, how to create schema in MSSQL, how to migrate the data...
Anyone had the experience before? Could kindly suggest some reference for such a migration? Thanks!
Regards,From the database perspective, this is almost trivial.
Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.
A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.
-PatP|||String Manipulation?
Dates, OK, you are going to have to trim the DB2 dates as SQL Server only supports 3 ms...and only cpu clock speeds...I believe every .333 ms
But strings/chars?
Huh?
And what, besides sheer whim or only knowledge of sql sever does the dba want to move off DB2|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?
From the database perspective, this is almost trivial.
Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.
A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.
-PatP|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?Yes, there is. Your DBA can tell you for sure, but my first guess is "dbo".
You are treading very near one of the confusing points in the switch from DB2 to MS-SQL... In MS-SQL 2000 and all earlier versions, there was a logical concept called a "user" that actually spanned across many of the concepts such as schema, permissions, etc in the purely relational world. Each user could logically own objects within the database, giving them something quite close to their own schema. There was a kind of "uber user" named dbo (an acronym for DataBase Owner) that was the default user under the name resolution rules.
In MS-SQL 2005, true schemas were introduced into the product. The schemas don't behave quite the way that you are accustomed to in DB2, but they're pretty close. For the moment, I wouldn't worry too much about them unless you have to make adjustments to cope with them.
-PatP
I am exploring the feasibility of migrating from DB2 (v7) to MSSQL server 2005. My current applications are using UDB, but the DBA wants to change to MSSQL. I believe the impact is huge. For example, how to create schema in MSSQL, how to migrate the data...
Anyone had the experience before? Could kindly suggest some reference for such a migration? Thanks!
Regards,From the database perspective, this is almost trivial.
Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.
A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.
-PatP|||String Manipulation?
Dates, OK, you are going to have to trim the DB2 dates as SQL Server only supports 3 ms...and only cpu clock speeds...I believe every .333 ms
But strings/chars?
Huh?
And what, besides sheer whim or only knowledge of sql sever does the dba want to move off DB2|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?
From the database perspective, this is almost trivial.
Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.
A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.
-PatP|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?Yes, there is. Your DBA can tell you for sure, but my first guess is "dbo".
You are treading very near one of the confusing points in the switch from DB2 to MS-SQL... In MS-SQL 2000 and all earlier versions, there was a logical concept called a "user" that actually spanned across many of the concepts such as schema, permissions, etc in the purely relational world. Each user could logically own objects within the database, giving them something quite close to their own schema. There was a kind of "uber user" named dbo (an acronym for DataBase Owner) that was the default user under the name resolution rules.
In MS-SQL 2005, true schemas were introduced into the product. The schemas don't behave quite the way that you are accustomed to in DB2, but they're pretty close. For the moment, I wouldn't worry too much about them unless you have to make adjustments to cope with them.
-PatP
Subscribe to:
Posts (Atom)