Solved

Convert Sybase tables to MSSQL Server tables

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

810 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