none
setup log shipping or replication from 2014 to 2016 RRS feed

  • Question

  • Hi all,

    I have an on premise VM on which SQL server 2014 standard edition running which has databases in 3 TB approx, its a prod environment which is used to run mostly analysis and report kinda query. it gets input data from third party replication tool continuously.

    There  is one more VM on Azure we have taken on which SQL server 2016 developer edition running. we have taken it to use it like a test environment, so that any queries we run for analysis and reporting can be executed here and tested before deploying it to prod environment.

    Now point here is I want data of prod to be synced in test environment from on premise 2014 standard edition to 2016 developer edition Azure VM, what are the possible ways ?

    what challenges with log shipping ?

    what challenges for replication?

    any other recommendations welcome

    please suggest ? point here to keep in mind is we have table size up to 1 TB.


    SQL Server DBA


    • Edited by Zeal DBA Monday, June 10, 2019 10:00 AM
    Monday, June 10, 2019 9:55 AM

All replies

  • >>>what challenges with log shipping ?

    It is not automatic fail over , and it is possible some data loss

    But all of those points are not for you, so LS is perfectly suits you. 

    >>>what challenges for replication?

    If you do not expect real time updated data, I would go with snapshot replication


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 10, 2019 11:36 AM
  • log shipping is doable? from sql 2014 on premise VM to sql 2016 Azure VM?

    and also storage is a big factor , since DB size is 3 TB so log shipping setup will require additional space to hold backups ? if yes how much is recommended? and whether 

    similarly Replication setup will also require additional space?



    SQL Server DBA

    Monday, June 10, 2019 1:33 PM
  • Hi SQLRocker,

    >> log shipping is doable? from sql 2014 on premise VM to sql 2016 Azure VM?

    You can try to configure SQL Server Log Shipping with Distributed File System Replication (DFSR) from on premises SQL server to Azure SQL server. Please refer to this official document to get more information.

    >> and also storage is a big factor , since DB size is 3 TB so log shipping setup will require additional space to hold backups ? if yes how much is recommended? and whether 

    Yes, log shipping require additional space to hold backups. The additional space size depends on the size of backup file.

    >> similarly Replication setup will also require additional space?

    Yes.

    Best regards,
    Cathy Ji

    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

    Tuesday, June 11, 2019 8:27 AM
  • > log shipping is doable? from sql 2014 on premise VM to sql 2016 Azure VM?

    What is your goal? Sync databases? Have a warm copy? Take a look at SqlPackage.exe tool 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 11, 2019 8:36 AM
  • Log shipping is not usable for your purposes. Since the target version is higher than the source version, the restore will have to be done using NORECOVERY, meaning you cannot get to the data at the target (the database isn't accessible). To access the target database, the restore has to be done using STANDBY, and that isn't possible when you restore onto a newer version of SQL Server).

    Since your purposes are to access the data on the target and not HA/DR, I suggest you look into replication. It adds some overhead from a maintenance viewpoint, so make sure you play with it a but first and read up on it. As for which type (transactional, snapshot or mere) you most probably don't want merge. So look into transactional and snapshot and decide which one is best suited for you. If you want semi-real-time data, then probably transactional.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, June 11, 2019 8:57 AM
    Moderator