none
SSMA tool Limitation

    Question

  • Hi There,

     

    I am trying to migrate Access (.mdb) database to SQL server 2008. I am able to successfully achieve this for one server at an instance.

     

    My ultimate aim to migrate .mdb files to “n” number of SQL server server 2008 where as the script is failing with the error in the attached document.

    From the console error what I assume is, the server connection to “Server1” has passed but it fails while trying for “server2”.

     

     

    Code snippets:

     

    In ConversionandDatamigration.Xml

    -----------------------------------------------

    <connect-target-database server="Server1" />

                    <connect-target-database server="Server2" />

     

     

    In ServerConnectionFile.xml

    ---------------------------------------

    <sql-server name="Server1">

        <windows-authentication>

          <database value ="$SQLServerDb$"/>

          <server value ="XXXXX\SQLSERVER2008,1433"/>

          <encrypt value="true"/>

          <trust-server-certificate value="true"/>

        </windows-authentication>

      </sql-server>

     

      <sql-server name="Server2">

        <windows-authentication>

          <database value ="$SQLServerDb$"/>

          <server value ="YYYY\MSSQLSERVER2008,1433"/>

          <encrypt value="true"/>

          <trust-server-certificate value="true"/>

        </windows-authentication>

      </sql-server>

     

     

    Note: SSMA works fine if I try to migrate for any one server at a time.

     

    Is SSMA a tool for multiple server migration? Please help us.


    K.G.Manivannan

    Monday, November 18, 2013 4:39 PM

Answers

  • Hi Kcgmani,

    According to your description, the server connection file contains server connection information for each server. Each server definition is identified by a unique Server ID. I recommend you specified Server information in a separate server connection file,  then execute the SSMA console (Access) based on the different project needs.

    For more information, you can review the following article.
    http://technet.microsoft.com/en-us/library/hh313057.aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by kcgmani Tuesday, November 26, 2013 7:49 AM
    Wednesday, November 20, 2013 9:01 AM
    Moderator
  • Sofiya,

    I split the scripts into two and made it as batch file and it worked. Hope this will be helpful to you .

    Regards


    K.G.Manivannan

    • Marked as answer by kcgmani Tuesday, November 26, 2013 7:50 AM
    Tuesday, November 26, 2013 7:50 AM

All replies

  • Hi There,

     

    I am trying to migrate Access (.mdb) database to SQL server 2008. I am able to successfully achieve this for one server at an instance.

     

    My ultimate aim to migrate .mdb files to “n” number of SQL server server 2008 where as the script is failing with the error in the attached document.

    From the console error what I assume is, the server connection to “Server1” has passed but it fails while trying for “server2”.

     

     

    Code snippets:

     

    In ConversionandDatamigration.Xml

    -----------------------------------------------

    <connect-target-database server="Server1" />

                    <connect-target-database server="Server2" />

     

     

    In ServerConnectionFile.xml

    ---------------------------------------

    <sql-server name="Server1">

        <windows-authentication>

          <database value ="$SQLServerDb$"/>

          <server value ="XXXXX\SQLSERVER2008,1433"/>

          <encrypt value="true"/>

          <trust-server-certificate value="true"/>

        </windows-authentication>

      </sql-server>

     

      <sql-server name="Server2">

        <windows-authentication>

          <database value ="$SQLServerDb$"/>

          <server value ="YYYY\MSSQLSERVER2008,1433"/>

          <encrypt value="true"/>

          <trust-server-certificate value="true"/>

        </windows-authentication>

      </sql-server>

     

     

    Note: SSMA works fine if I try to migrate for any one server at a time.

     

    Is SSMA a tool for multiple server migration? Please help us with necessary support.

     

    Regards,


    K.G.Manivannan

    Tuesday, November 19, 2013 3:42 PM
  • Hi Kcgmani,

    According to your description, the server connection file contains server connection information for each server. Each server definition is identified by a unique Server ID. I recommend you specified Server information in a separate server connection file,  then execute the SSMA console (Access) based on the different project needs.

    For more information, you can review the following article.
    http://technet.microsoft.com/en-us/library/hh313057.aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by kcgmani Tuesday, November 26, 2013 7:49 AM
    Wednesday, November 20, 2013 9:01 AM
    Moderator
  • Hi Sofiya,

    As recommended by you, I have used multiple server connection(without changing the ConversionandDatamigration.Xml). It failed.

    Hence i created multiple conversionanddatamigration.xml and serverconnectionfile.xml. The scripts passed but the migration is happening only to one server.

    Here is my script,

    SSMAforAccessConsole.exe -s "C:\Program Files\Microsoft SQL Server Migration Assistant for Access\Sample Console Scripts\Test scripts\ConversionAndDataMigration.xml" -v "C:\Program Files\Microsoft SQL Server Migration Assistant for Access\Sample Console Scripts\Test scripts\VariableValueFile.xml" -c "C:\Program Files\Microsoft SQL Server Migration Assistant for Access\Sample Console Scripts\Test Scripts\ServersConnectionFile.xml" -s "C:\Program Files\Microsoft SQL Server Migration Assistant for Access\Sample Console Scripts\Test scripts\ConversionAndDataMigration1.xml" -c "C:\Program Files\Microsoft SQL Server Migration Assistant for Access\Sample Console Scripts\Test Scripts\ServersConnectionFile1.xml"

    Strnagely the conversion is successful to the servers(latter) available in ServersConnectionFile1.xml which is in second order as per the script.

    There is no error neither migration to other server. Please help(All your earlier commnets really helped and worked for me :) )

    Regards,


    K.G.Manivannan

    Thursday, November 21, 2013 6:49 PM
  • Sofiya,

    I split the scripts into two and made it as batch file and it worked. Hope this will be helpful to you .

    Regards


    K.G.Manivannan

    • Marked as answer by kcgmani Tuesday, November 26, 2013 7:50 AM
    Tuesday, November 26, 2013 7:50 AM