Post reply

MySQL To SQL Server

  • November 23, 2013 at 6:39 pm

    #285525

    Guys,

    Actually I am doing a migration from MySQL to SQL Server. At the moment the database MySQL have a 80GB. I done all scripts to migrate.

    Informations:

    1. Windows Server 2008 R2 Enterprise Edition 64bits (8 Processors, 18GB Memory)

    2. SQL Server 2008 R2 Enterprise Edition 64bits (15GB Max Memory)

    3. I am using openrowset...

    4. All processor to be enabled...

    5. Simple (Recovery Model)

    6. Disks distinct (D: data, L: Log, T: TempDB)

    I want know if exist best practices(parameters, tips or anymore) to SQL Server to import big data volume...

  • November 25, 2013 at 5:20 am

    #1668756

    Anyone?

  • November 25, 2013 at 7:59 am

    #1668812

    Hi,

    I did database migration from MySql to MS Sql 2005, but it was small db, so I simply

    used MySQL ODBC Connector and set it as linked server in SSMS.

    Br.

    Mike

  • November 25, 2013 at 8:14 am

    #1668822

    offhand, there's four ways, and i'll list them in order of what i consider ease of use, from easiest to hardest:

    1. a pre-built utility to migrate from mySQL to SQL server:

    Microsoft SQL Server Migration Assistant for MySQL v5.2

    2. the Built In Import Export Wizard in SSMS:

    3. Creating a Linked Server, and migrating via linked servers(Slow, because it does not use BCP/Bulk methods, but easy to do)

    http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

    4. Export data to text files, and BCP or BULK insert into SQL server.

    --MYSQL:

    SELECT * FROM MyTable INTO OUTFILE c:\Data\'Export_o.txt'

    --SQLSERVER

    CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))

    BULK INSERT BULKACT FROM 'c:\DataExport_o.txt'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '\n',

    FIRSTROW = 1

    )

    Now, I'm not including SSIS here, because I don't think of it as easy to use, unless you've used it before; The Import Export Wizard is technically SSIS, but your abstracted out of the building of connectors and all the things that are scary at first glance.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 25, 2013 at 10:25 am

    #1668891

    1. a pre-built utility to migrate from mySQL to SQL server

    is not good for me. my datavase have a 80GB and exists many erros..

    2. the Built In Import Export Wizard in SSMS:

    is not for me, because i done all scripts do migrate...

    3. is not for me, because i done all scripts do migrate...

    and whatever...

    I am using openrowset... and i want to know if exist some best practice in sql server to boost data performance

  • November 25, 2013 at 10:37 am

    #1668898

    Import/Export wizard should perform better than inserts from openrowset.

    I understand that you already have the scripts to migrate but the wizard will create the SSIS package automatically and will preform much better.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 25, 2013 at 10:48 am

    #1668901

    yep linked servers, which include openquery , openrowset, and selecting from four part names, are horribly slow;

    my rule of thumb is never to use it when fiddling with more than say, 10K rows of data; otherwise it's the teaspoon to empty the ocean problem...you can do it, but it takes longer than it's worth.

    As Luis mentioned, the Import Export Wizard will use SQL's Bulk insert commands to move the data, so you can see millions of rows, and gigs of data, move in under a minute.

    bcp/sqlcmd/bulk insert are basically command line or TSQL ways to access that same high speed functionality.

    It might take you a little bit of time to change it, but it would be much faster to switch to the import export wizard, as well as being able to have repeatable, tested packages to do the work for you.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply