sql server cloud (data transfer)

Question

sql server cloud (data transfer)

nononame2021 261

i am new to use the MS SQL server in cloud.
now i am performing the transformation from window server to cloud.
but some data still need to extract from the window sql server and import to cloud DB.
i am now study which way is the better to connect to window sql server from cloud.
linked DB to connect to window/classic sql server from cloud is the way to do?
or copy the whole all the DB to cloud and then import to cloud DB?

Shashank Singh 6,251 Reputation points

2022-03-02T09:59:01.36+00:00

Are you using Azure sql DB in cloud or SQL Server on Azure VM ? See tutorial
https://learn.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql

1 answer

Your answer

Shashank Singh 6,251 Reputation points

2022-03-02T09:59:01.36+00:00

Are you using Azure sql DB in cloud or SQL Server on Azure VM ? See tutorial
https://learn.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql

Answer 1

Anurag Sharma 17,631

Hi @nononame2021 , welcome to Microsoft Q&A forum.

It seems you want to migrate your on-premise SQL Server(SQL server on your windows machine) to Azure SQL Database.

There are many ways to do this. Below are few simple approaches you can choose to migrate it:

Data Migration Assistant: The tool provides the simple User interface to assess and migrate your database to cloud. Please go through the article for step by step details.
Using BACPAC: Creating bacpac file from on-premise and importing to Azure SQL Server.
Migrate SQL Server to Azure SQL Database using DMS: This is another azure service that helps in migrating data to azure SQL Database

There are other ways also to do it but as you are new to cloud, these are simple approaches you can take to migrate.

All these articles are step by step process but in case you are facing any challenges/issues please let us know and we can look further into it.

----------

Please click on button if the answer helped you, as this could also help others having similar queries.

nononame2021 261 Reputation points

2022-03-02T14:58:55.453+00:00

I am not migrating, I would like to collect the data in on-prem sql server and then import to azure sql regularly
I am studying if I can use linked server to connect on-prem to select data and then import to azure sql.

is it good to use this method.
Anurag Sharma 17,631 Reputation points

2022-03-02T15:27:38.287+00:00

Thanks for replying back with more details. Linked servers are not enabled in Azure SQL Databases, that means we cannot create linked servers on Azure SQL and query on-premise SQL Server. This limitation is highlighted in the document below as well:

Linked Servers (Database Engine)

However, the other way round is possible where we can create linked server on on-premise and query azure SQL Databases.

If you want import data to Azure SQL from on-premise regularly, I would suggest you to use Azure Data Sync and this can be scheduled as well:

Set up SQL Data Sync between databases in Azure SQL Database and SQL Server

Please let us know if this helps or else we can discuss further.
nononame2021 261 Reputation points

2022-03-02T15:41:19.64+00:00

is the performance of data sync good?
other then data sync,

any other way to select data from on-prem sql server and then insert to azure sql cloud.?
nononame2021 261 Reputation points

2022-03-02T15:47:46.457+00:00

is it okay to create linked srrver by using sql azure managed instance as mentioned in the link you provided in previous post.

if yes, is the azure MI good to create linked server to access on-prem? is the performance good in using this way
Anurag Sharma 17,631 Reputation points

2022-03-02T16:13:07.077+00:00

Data sync provides best performance when the target tables are empty. Otherwise, Data Sync does a row-by-row comparison and insertion to check for conflicts. If performance is not a concern, however, you can set up sync between tables that already contain data.

Another way to achieve this is using Transaction replication as mentioned in below article:

Use Transactional Replication

Coming to Azure SQL managed instance, yes we can create a linked server on it. Performance of any of these methods discussed depends on various factors.

Linked server generally could be slow but we can improve the performance of them like using parameterized queries instead of selecting the entire dataset.

Techniques to enhance Linked Server performance
nononame2021 261 Reputation points

2022-03-02T16:44:45.35+00:00

if I enhanced linked server by adding index like the link reference provided from you. is the performance not too bad?

may I know the variance between 2 method ? my sql is many logic selecting data from on-premise to cloud

so replicate row by row sync is the good way, right?
Anurag Sharma 17,631 Reputation points

2022-03-03T04:55:51.86+00:00

Thanks for your reply. Yes adding indexes help in improving performance. When we are dealing with hug data these factors influence the performance.

As you also mentioned that your on-premise to Cloud data transfer will have logic involved, in that case SQL Data sync or transaction replication is not the right way to go about it as they are used to transfer the data as it it from source to target. In these specific cases using linked server is better as we have control of queries that we can write.

We can also choose Azure Data Factory if data needs any transformation or filtering before loading to cloud.
nononame2021 261 Reputation points

2022-03-03T09:22:24.867+00:00

hi, i am using azure sql MI to perform the data sync to cloud from on-prem sql server.
is it a good way to sync the data by replicate the DB or table to cloud ? is the performance good? or any other issue of using this method?
Anurag Sharma 17,631 Reputation points

2022-03-03T13:27:27.25+00:00

Yes transactional replication is one of the preferred way to replicate the data between on-prem to cloud. As this is continuous replication, it could be slow for initial load but then after for subsequent loads should be good enough performance wise.

Also I would suggest you to once check new feature 'Link' for same purpose:

Link feature for Azure SQL Managed Instance

Having said that, these methods are still row-to-row replication. But if you want to apply any logic before replicating data you should use any ETL tool like Azure Data Factory etc. as mentioned in my last comment.

----------

If answer is helpful please please click on as it could help other members of the Microsoft Q&A community who have similar questions and are looking for solutions. Thank you for helping to improve Microsoft Q&A!
nononame2021 261 Reputation points

2022-03-03T14:12:18.31+00:00

can I add logic to extract the required data t
from on-prem to cloud by replication sync ?
Anurag Sharma 17,631 Reputation points

2022-03-03T18:31:15.247+00:00

We cannot select specific data based on logic in Replication as it maintains the consistency across databases. If we need to insert selected records we might need to change to other ways like ADF etc.
nononame2021 261 Reputation points

2022-03-04T08:11:03.587+00:00

what is ADF? is it good performance ? any reference ?
Anurag Sharma 17,631 Reputation points

2022-03-04T08:36:38.43+00:00

Azure Data Factory is Azure's cloud ETL service for scale-out serverless data integration and data transformation. It is used to extract, transform and loading of data from source to target. There are many sources supported by ADF as of now but below is one article that describes loading data from on-premise to Azure SQL database. Good part is we can select data based on Select query as well by providing the logic. Article talks about that as well:

Copy Data From On-Premise SQL Server To Azure Database Using Azure Data Factory

Coming to performance, below is the metrics available on doc but again this is based on optimization and performance tuning updates provided in the same doc:

Copy activity performance and scalability guide
nononame2021 261 Reputation points

2022-03-04T08:54:29.867+00:00

thanks for info
it means ADF need to optimize / enchaned to have better performance ? if not doing that, what is performance and when there is error, how to handle?
Anurag Sharma 17,631 Reputation points

2022-03-04T10:58:10.853+00:00

If we don't optimize it as per the article, the baseline metrics shared above would differ in copy activity and could take more time in transferring the data. Again the data size mentioned above would also matter and for less data it would take less time.

Equivalent to ADF , we can also go with SSIS packages that will be configured on your local machine.

Load data into SQL Server or Azure SQL Database with SQL Server Integration Services (SSIS)

Share via

sql server cloud (data transfer)

1 answer

Your answer