Solved

Convert Sybase tables to MSSQL Server tables

Posted on 2008-10-07
4
1,402 Views
Last Modified: 2012-05-05
Hi EE -
My Boss, has tasked me with  finding out procedures for converting 10 or so tables from an SyBase ASE v.11.9.26
Database (linux box) to a MSSQL Server 2005 friendly set of tables?

I have been reading SSMA for SyBase :
http://www.microsoft.com/sql/solutions/migration/sybase/default.mspx

and DTS:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

The SSMA above sounds simple: a) I install the program SSMA and b) then launch it. c) Login to my SyBase DB and d) then tell it (using built in SSMA GUI tools) to convert (extract) these 10 tables. e) I then open my MSSQL tool (whatever the customer is using) and f) grab this object that I extracted and g) import it in to  the MSSQL Server 2005 DB.

The DTS above sounds mor like I have to be moving data around within MSSQL system. But I read another post here :

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20868418.html

this sounds like I could use the DTS to convert the tables, mentioned above.

All assistance is appreciated.

Thanks -
Wotan1953

0
Comment
Question by:wotan1953
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 22661136
You really have two issues here:
 - Creating the equivalent Domains/Tables/Keys/Indexes in MSSQL
 - Moving the data

There are a couple of questions here as well:
 - Is this a one time thing or is this going to be done on a regular basis?
 - How much data are you actually going to move, e.g. how many rows in the tables?

SSMA is designed to help you do a one-time migration from Sybase to SQL Server.  DTS is designed to help you move data on a regular basis from external sources (in this case Sybase) into SQL Server.

If you have to do this on a regular basis, then you should probably start with SSMA so you can get the tables and associated pariphinalia moved over the first time.  After that, you can use DTS to move the data on whatever schedule is required.

Without knowing more about your environment and requirements, it is a little difficult to give you more specific advice.

Regards,
Bill

BTW, Sybase is a proper name with the "b" in lower case.
0
 

Author Comment

by:wotan1953
ID: 22661600
Hi ... and thanks.

This is a one time thing. Also, we are hoping to have a create script and table descriptions, with any indexes and unique constraints etc., that we send the client. They would then open this in their MSSQL environment and add to the MSSQL db.

Regarding the number of rows, several of the tables have 10s of thousands of rows. Most others are more manageable .

So it looks like SSMA is the answer. I am assuming I need to download the SSMA package. First I need to add the .NET Framwork plus it looks like a J# package too. I will start that.

What other environment/requirements, would you need? Or if I download the SSMA package, will it's running be fairly straight forward?

Thanks again
0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 22661948
Actually, there are other ways of going about this, the viability of which depends on how complex the source database is.  If your tables are defined with a bunch of domains (user defined data types) or there are incompatible data types (there should not be many) things are a bit trickier.

You can extract the DDL for a Sybase database using the "ddlgen" utility for 11.9.2 or "defncopy" for newer releases.  It will extract the definitions of the database objects to a flat file SQL script.  You can then simply edit out any tables and their associated components you are not interested in.

The second step is to use BCP to get the data out of the tables.  You will have to do this in character mode and it will generate a complete copy of the table data.  You then use BCP on the MS end to stuff the data back in.

All of this is pretty straight forward and well documented.

Another alternative is to use a multi-database development tool like Embarcadero RapidSQL or Aquafold Aqua Data Studio.  You can extract the ddl for the objects you are interested in as well as creating INSERT scripts for all the data.  It is not as effecient as BCP for moving data but if you only have a few 10s of thousands of rows, performance is not really an issue.

Regards,

Bill
0
 

Author Closing Comment

by:wotan1953
ID: 31503900
Hi Bill -
Thanks. I went with WinSQL 30 day triial. It seems similar to the other to packages you mentioned. But if you decide to buy, it is only about $250 ... which is much less then Embarcadero RapidSQL or Aquafold Aqua Data Studio. I will comment more externally, about DDL and BCP, but just wanted you to know I appreciated the quick and helpful responses.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now