SQL Server Performance Forum – Threads Archive
Is DTS the right tool for this job?…
I am transferring the entire contents of an Oracle Rdb database (Oracle Rdb is different than the regular Oracle) to a SQL Server 7.0 database. The Rdb database is on a DEC Alpha machine and an Oracle Rdb ODBC driver is needed for the data transfers. It is a large undertaking since this database is over 60GB (and that does not include indices) and has over 400 tables. I have been using only Data Transformation Services (DTS) in Enterprise Manager (using the import Wizard) for about a week and have only managed to transfer 1/3 of all of the data. Is there a better way to do this? Are there ways to obtain performance improvements or to prevent transaction logging with DTS? Is there a way to run DTS with the SQL Server database in a "single-user mode"? (I tried to do "single-user mode" but couldn’t get it to work). We have used Bulk Insert for other projects but I’m thinking it would mean extracting all of the Oracle Rdb data first onto flat files and FTP’ing them over from our DEC Alpha machine and this sounds like a very time-consuming process. One other note is that I will only to have to transfer this database one time now for a pilot project and then one more time when it goes live. After these two times, I never have to do it again. Thank you,David Josephs
I’ve never seen direct resource or method to import from Oracle to SQL Server.
As performed table structures and data can be moved using DTS and I believe SQL Books online has information in this regard. I suggest to get hold of "MS SQL Server 2000 Resource Kit" book, which has a chapter on converting from Oracle to SQL Server. Oracle and SQL Server have significantly different approaches and philosophies. There is just about always a way to accomplish the same thing in SQL Server as there is in Oracle. Concentrate on handling the business requirements, rather than just trying to port code.# Take help of this linkhttp://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=71 for more information.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thank you for your reply. I just wanted to follow up on the possibility of using "single-user mode". Here is my question… Is it possible to run a DTS package with SQL Server 7 in a single-user mode? If so, would it be a correct assumption that this would make the import go faster since SQL Server would not have to check for record locks? Thank you,
David Josephs
You can keep the database in single-user mode by using SP_DBOPTION and make sure only DTS job runs during that instance. If not you can keep the database in dbo-use only also and execute the DTS package with your login (presumably with Sysadmin privileges). _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I have been getting the following error when executing the DTS package in single-user mode: Could not acquire a FastLoad session.Unspecified error
Timeout expired. When I switch back to the regular mode (not single-user), then it works fine (but slowly). Please let me know if there are any ideas about why this is happening. It appears that the timeout is due to the single-user mode so I’m wondering if the fact that I am in Enterprise Manager (DTS Import Wizard) and also asking to have the package execute immediately is a problem due to more than one user? Just a wild guess. Thank you,
David Josephs
Open a query analyzer on the side and run sp_who or sp_who2 in the master database. Then you can see if there is an existing connection in your database already. /Argyle
single user mode won’t stop locking though… not having any indexes or triggers on the tables and putting the database into bulk_logged mode will speed up bulk inserts and bcp, etc. which is what dts will use for a simple copy DTS packages often use bcp or other tools to do some of the steps, these will require another connection to the database Cheers
Twan
Use FASTLOAD option for DTS packages to increase the speed and reduce the logging time for the job. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
]]>