question

salilsingh-9961 avatar image
0 Votes"
salilsingh-9961 asked Simon-2248 commented

Sync on premises SQL server db to Azure SQL db

Hi Team,

I have a requirement to sync on-premises SQL Server db to Azure SQL db in every few days.
What options could be used for this requirement?
Can Transactional Replication from On-Premise SQL Server to Azure SQL Database (using SSMS) be used for this task?
It will be really helpful if a link is provided me which contains the implementation details.

Thanks,
Salil

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KalyanChanumolu-MSFT avatar image
0 Votes"
KalyanChanumolu-MSFT answered Simon-2248 commented

@salilsingh-9961 Welcome to Microsoft Q&A Forums and thank you for your interest in Azure SQL Database.

If you are already familiar with setting up transactional replication, you can choose to do so.
But since you have mentioned that you do not require replication in real-time, you can use Azure SQL Data Sync which is easier to maintain.

Please refer to this tutorial for step-by-step instructions.


If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KalyanChanumolu-MSFT,

I am not sure Azure SQL data sync could achieve my requirement as I need sync to always happen in single direction from on premises SQL Server DB to Azure SQL DB.
In above mentioned link, SQL database is selected as hub database from which sync is happening to other Azure db and on-premises db. As mentioned earlier, I need sync to always happen in single direction from on premises SQL Server DB to Azure SQL DB.

Please correct me if my understanding is incorrect.

Can Transactional replication from on-premises SQL db to Azure SQL db be automated (scheduled to run in every few days)?
If you have could you please provide me with a link to achieve above.
I think transactional replication is applied through SSMS (or through SQL scripts in SSMS), it can't be implemented through Azure portal.

Thanks,
Salil

0 Votes 0 ·

You can configure the on-premises member with To Hub direction. You should also pick Member Wins as conflict resolution policy when creating the sync group for this scenario.

0 Votes 0 ·

Hi Vitor,
I'm trying to reach someone who is familiar with DataSync and can't find a direct contact point, so I am leaving a message hoping you will see this.
We have been using DataSync with To Hub and Member Wins to do one way replication to Azure SQL from on-prem SQL. It works however, there is a performance issue when it checks for changes.

Essentially the tracking table query that the dss stored procedure executes contains a FULL OUTER JOIN on the base table. This results in the entire base table being table scanned and is very slow. The reality is, that this seems unnecessary, because the changes are in the tracking table so what it should really be doing is a RIGHT JOIN. This speeds it up by orders of magnitude and it returns immediately. This is the difference between the change checking process taking up to 10 minutes for us, or being instant. We wanted to use DataSync to do 1 minute interval change change checks but without this being fixed we can't.

Can you let me know how to reach you or anyone on the DataSync team to see what options we have? Email me on simon@ams.co.nz.

Our company has an Azure subscription but the Azure support is not familiar with DataSync enough to provide useful support.

Thanks,
Simon

0 Votes 0 ·
NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered

Hey @salilsingh-9961 ,
You can leverage Data sync and control the directionality of the Sync from On prem to Azure SQL.
There is another similar thread :
https://docs.microsoft.com/en-us/answers/questions/330125/migrating-onpremises-sql-server-database-to-azure.html

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.