Solved

Convert Sybase tables to MSSQL Server tables

Posted on 2008-10-07
4
1,397 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

11 Experts available now in Live!

Get 1:1 Help Now