locked
SSMA for Sybase: Server to Server without installing extension packs RRS feed

  • Question

  • Can I do server to server data migration without installing the extension packs? Actually let me rephrase, can I create the necessary sql scripts to load any necessary databases and objects? I'm trying to workaround my company. There is a 3 month process (pretty insane, I know) that is required just to get SSMA extension packs installed. There's a whole sizing, pricing, etc. phases. SSMA with extension packs is NOT in the company software catalog, they'll have to contact a vendor and find pricing (even though it is free), etc, etc. ... see the pain. We've already spent a month trying to get the DBAs to find the Sybase SDK to provider (sizing/cost phase), that the instructions say is needed. 

    I've installed the extension packs locally and know there are 2 databases that get created (I can easily script this out). So I tried that on another computer. Turned on Server to Server Migration under Project Settings and tried a data migration. I now get error: "Cannot find the Symmetric Key 'SsmaForSybaseKey', because it does not exist or you do not have permission." 

    Is there a way to create this key?

    I've also found under C:\Microsoft SQL Server Migration Assistant for Sybase Extension Pack\bin\SybaseServerDataMigrationEngine.dll ... by the name of this dll, does that have be installed for Server to Server data migration? 

    I am not concerned with any unit testing, just data migration. 

    I have been able to move data via Client, I assume Sybase Server -> Client -> SQL Server is the flow of data. This has been sufficient, still takes a while. How much performance increase would there be Server to Server? Is it worth all the hassle of trying to workaround my company policy? 

    Thanks ...

    Tuesday, March 14, 2017 7:45 PM

All replies

  • There is not a requirement to use SSMA for anything.  This is simply a tool to get your started without having to change much syntax and functions.

    You can and should rewrite your code to not require SSMA and use TSQL instead.

    Tuesday, March 14, 2017 8:29 PM
  • Tom, I'm trying to migrate data from Sybase to SQL Server. The schema, stored procs, etc. are being rewritten. 

    Thanks for responding, any advice on migrating data Server to Server without installing extension packs?

    Tuesday, March 14, 2017 8:48 PM
  • Okay found out some more information. I located "migration-with-bcp.sql" file that comes from the extension pack install (again I've run this local on my dev box, but trying to workaround issues having it installed on the official dev box, qa box, and prod box). 

    I see that the script is what creates the sysdb and this db has the SsmaForSybaseKey installed

    use sysdb
    go
    select * from sys.symmetric_keys

    On the other developer box that I'm trying to make work Server to Server I ran from that sql file this code to create the key

    if not exists (SELECT 1 FROM master.sys.databases WHERE name = DB_NAME() and is_master_key_encrypted_by_server = 1)
    create master key encryption by password = '%SSMA_MK_PASSWORD%'
    go

    create certificate [SsmaForSybaseCertificate]
        with subject = 'SSMA for Sybase Certificate'
    go

    create symmetric key [SsmaForSybaseKey]
        with algorithm = AES_256 encryption
    by certificate [SsmaForSybaseCertificate]
    go

    Back in SSMA the previous error is gone, but the table that I'm testing with 'tries' to migrate data, but it immediately shows the results report and just says 'N/A' on success rate.  I tried it again, same result.

    I'm assuming I still have some issue since I'm trying to hack around NOT really running the extension pack install. Note: on my local dev box, where I DID install the extension pack, I get the same 'N/A' error.

    Any suggestions, other than running the extension pack install?


    • Edited by CWinKY_2017 Tuesday, March 14, 2017 8:58 PM
    Tuesday, March 14, 2017 8:56 PM
  • Hi CWinKY_2017,

     

    In your another case, you said that the extension packs cannot be installed, does that related to this case? It looks like that you want to use SSMS skipping the extension packs.

     

    Extension packs are used to read data from other database, according to your description, you want to migrate data from Sybase to SQL Server, do you have a Sybase OLE DB provider? if it is, you can use linked server to migrate the database, you can first create linked server and then use Import/Export wizardto migrate data. Besides, using SSIS can be taken into consideration.

     

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 15, 2017 8:54 AM
  • Correct, I would like to see if there is a way to Server to Server data migration without installing the extension packs. Instead can the necessary components required for this be put in a SQL script that I can have the DBAs run? Going through our internal company policies will be, no joke, a 2 to 3 month process to have them run the install. The software is not in the company software catalog. Even though it is free, it would take couple weeks to contact a Vendor to determine the cost, go through a whole sizing phase, approval lots of meetings and arguing. It is easier just to submit a request for the DBAs to run a SQL script. 

    Thanks for the linked server idea, I'll look into that. 

    Wednesday, March 15, 2017 1:15 PM
  • All you need is a machine, any machine, with both the SQL Server driver and the Sybase driver installed and SSMS installed.  Then you can use the Import/Export wizard to connect to the Sybase server and send the data to the SQL Server.  If you need more than that, you can write an SSIS package to transfer the data.

     
    Wednesday, March 15, 2017 1:22 PM
  • That is true, but I've got 1000 tables to migrate, SSMA tool is very nice just select all tables. I'm trying to accomplish this task with SSMA not SSMS. I've been able to do SSMA in a Sybase Server -> local machine -> SQL Server data flow, but I'm trying to take out the local machine as an extra hop ... if possible. 

    I'll also try the Import/Export wizard from Sybase to SQL, my understanding is that creates SSIS packages automatically, so at least I wouldn't have to the Source/Destination components a 1000 times. 

    Thanks again for the suggestion. 

    Wednesday, March 15, 2017 4:27 PM
  • Okay I've created sysdb database manually by running create-db-2005.sql and migration-with-bcp.sql (updated to include a password for the certificate). 

    I can run SSMA, I get no warnings initially about missing SSMA components or anything. I select a table to migrate and after 'preparing table', it shows starting data migration and then immediately finishes with a status of N/A in the Success Result column.

    What does N/A mean? There isn't any additional error information. 

    Is there still something that the extension pack install does that is necessary? 

    Wednesday, March 15, 2017 4:31 PM