Zeal DBA on Mon, 10 Jun 2019 09:55:44
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
Uri Dimant on Mon, 10 Jun 2019 11:36:54
>>>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
Zeal DBA on Mon, 10 Jun 2019 13:33:35
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?
Cathy Ji on Tue, 11 Jun 2019 08:27:00
>> 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?
Uri Dimant on Tue, 11 Jun 2019 08:36:23
> 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
TiborK on Tue, 11 Jun 2019 08:57:50
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.