none
SQL Server Data Migration RRS feed

  • Question

  • Hi,

    In My project, i have requirement that migrate the old data into SQL database. Old Data from Access DB or from SQL DB. Have to migrate the data based on the current application structure.

    In my project, we have more than one attachments for each primary record. On old system attachments are captured and save in physical folder in server. But in current system, attachments are saved in sql server database column with datatype as varchar(max)

    Now my requirement is i have migrate those attachments to database. I have a .Net application which can convert the physical file into base64 string to save into the database.

    From client we received the attachments which is 100+ GB in size. now i have to migrate these attachments into database. 

    i have many doubts here

    1. how much size sql need to for this 100+ GB files, We have Separate SQL server

    2. Will it affect the application performance, how to optimize 

    currently our application in running in production. Have to look for minimal changes which can be done at sql server side not in application. Need to consider this point

    Thanks


    Wednesday, July 10, 2019 4:54 AM

All replies

  • Hello,

    Please try the migration assistant to have a good starting point for your assessments:

    for SQL to SQL:

    https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-2017


    for Access to SQL 

    https://docs.microsoft.com/en-us/sql/ssma/access/sql-server-migration-assistant-for-access-accesstosql?view=sql-server-2017


    ManyThanks, NARI

    Thursday, August 1, 2019 6:46 AM
  • Hi,

    You can use VARBINARY to store your attachments, instead of base64-encoded string and VARCHAR.

    Usually it is recommended to store files outside of the database and only keep the reference to where the file is in the database.

    Regards,
    Alex.

    Monday, August 5, 2019 4:30 PM
  • You might want to look at the FileStream feature. This will store database data in a separate folder in the file system. The data can be streamed to the client outside of the database engine which offers better performance.

    It is unpredictable as to how much storage 100 Gigs of files would take stored inside the database.

    Monday, August 5, 2019 5:39 PM