Sql Endpoint Setup with ownbackup

Amar Agnihotri 891

Hi Guys!
I have to create a SQL Endpoint setup with own backup but i am struggling to find all required pieces from my SQL. Can anybody help me regarding that by providing some useful links which could show the step by step process of setting up .

Thanks in advance

Anurag Sharma 17,566 Reputation points

2021-09-17T12:14:11.967+00:00
Hi @Amar Agnihotri , welcome to Microsoft Q&A forum.

From the problem statement we understand that you want to migrate on-premise MySQL backup to Azure Database for MySQL. Below are couple of links that will guide you step by step on this process:

Migrate your MySQL database to Azure Database for MySQL using dump and restore

Tutorial: Migrate MySQL to Azure Database for MySQL offline using DMS

Migrate MySQL on-premises to Azure Database for MySQL: Migration Methods

Please let us know if this is what you are looking for or else we can discuss further on the same.
Amar Agnihotri 891 Reputation points

2021-09-18T02:28:40.88+00:00

Hi @AnuragSharma-MSFT ! Thanks for the reply. Actually we are using own backup tool to create backup of Salesforce data everyday when there is any change in the objects. I want to import that backup data into azure by creating my sql endpoint. Hope you understand my problem. Please suggest regarding this.

Thanks
Anurag Sharma 17,566 Reputation points

2021-09-20T05:48:47.28+00:00

Hi @Amar Agnihotri , thanks for responding back.

If we go through the first article that was mentioned above it talks about how can we create an empty Azure MySQL DB and load the data from backup/dump created.

When you say you want to create MySQL endpoints we assume you want to create a Azure MySQL Database, please let us know if this is not correct and we can discuss further on the same.
Amar Agnihotri 891 Reputation points

2021-09-22T10:47:02.253+00:00

Hi @AnuragSharma-MSFT !
I am facing this error while connecting from MySQl Workbench. Can you please look into this.
Anurag Sharma 17,566 Reputation points

2021-09-22T12:12:32.623+00:00

Hi @@Amar Agnihotri ,

We need to add the firewall rule to Azure Database for MySQL. Please check the screenshot below. The IP address that you see in the error message, you need to add that to start ip and end ip field here and then save the details. Please try it once and let me know if you face any other issue.
Amar Agnihotri 891 Reputation points

2021-09-22T14:53:07.777+00:00

Hi @AnuragSharma-MSFT !
That issue is resolved but now i am facing another issue. I am trying to connect ssms to azure but everytime it is throwing error that network path not found. Even after turning off my firewall
the issue remains same . I don't know what to do now. Because i am used through to ssms . Will create database in sql server and will export it to azure but i am stuck at this initial stage of connecting ssms to azure.
Anurag Sharma 17,566 Reputation points

2021-09-22T15:21:53.71+00:00

Hi @Amar Agnihotri , Could you please let me know if you are using Azure MySQL or Azure SQL? If possible, please provide the screenshot or error as well to understand the nature of error clearly.

For Azure MySQL we can use MySQL Workbench as mentioned in your previous reply. For Azure SQL, we use SSMS to connect.

Also as mentioned you want to create a new database and export it. if you could explain your use case or requirement we would try to come out with right solution.
Amar Agnihotri 891 Reputation points

2021-09-23T04:41:32.347+00:00

@AnuragSharma-MSFT
First of all Thanks a lot for your support and reply. You seems to be a very nice and helpful person otherwise you don't know me in personal and only few people are there who always ready to help others. First of all let me introduce you myself. I am working as a BI developer but now my company is planning to implement Azure and i am very new to Azure. I never worked on Azure so i am trying to learn it by tutorials and support blogs. Primary task that i need to do is to import backup of the salesforce data to the Azure database. That's why i created a test database in Azure. I am familiar with SSMS so i want to create test db in sql server and and then will export that db to Azure and vice versa. When i will get succeed in that i will go further to import salesforce db . I started learning Mysql workbench also because i never worked on that. Even couple of days ago i was not able to know exactly what Mysql workbench is because in case of ssms we need to have a sql server database installed on the machine to connect through it but what i saw in Mysql workbench is that we can create db and table directly in Mysql workbench.We don't need to have any database in case of Mysql workbench. May be i am wrong because its my start in Mysql workbench.
Amar Agnihotri 891 Reputation points

2021-09-23T04:48:42.203+00:00

I have successfully connected Mysql workbench to Azure but don't know how to import Azure databse to Mysql workbench and where will it get stored on machine. There are many more things that i want to learn . I have no one in my friend circle who works on Azure. I am attaching all snaps so that it could easy for you to understand the problem .
Amar Agnihotri 891 Reputation points

2021-09-23T04:52:08.113+00:00

Hey @AnuragSharma-MSFT ,

If you don't mind then i want to make request for something. Can we be Azure friends ? Can we connect on Linkedin because i am not comfortable with this portal. Everytime i have to open my laptop to post or message anything.

IIf you deny for that then i will not compel you.

Accepted answer

Anurag Sharma 17,566 Reputation points

2021-09-23T07:15:03.123+00:00

<Writing this as answer as it was crossing 1600 characters limit>

Firstly I would like to thank you for all the positive words and glad that you are choosing Azure in your company. We actually try to provide solution to all your queries on Microsoft Q&A forum. Now Azure offers a lot of services and I would know few but not all. Posting your queries here would make appropriate group of people to look and respond to them. Also more and more users can see and would be benefitted with the solution. It does not matter if query is minor or major, please feel free to post it here anytime.

Coming to the issue, SSMS and MySQL Workbench are 2 tools for 2 different kind of databases i.e. SQL Server and MySQL respectively. In both of these, we have 2 ways to create databases. One is on-premise which is local to our system/server and other is on Cloud (Azure). To use on-premise we need to have a server installed on our local system. But in case of Azure we just need to connect to Azure Database and we can start working directly by creating tables and all.

We can install SQL Server from below link and then you can start creating database
https://www.microsoft.com/en-in/sql-server/sql-server-downloads

For salesforce again we need to choose where we need to load the backup into, in Azure SQL or Azure MySQL. Below is one article to start with where we use Azure Data Factory to read data from Salesforce.

Copy data from and to Salesforce using Azure Data Factory or Azure Synapse Analytics

However, I would first recommend you to get comfortable with Azure Databases by connecting them through specific tools. We can keep on discussing here until you have a proper solution to all your queries. We can go step by step here. Please try it once and let me know your next query and I would be delighted to provide solution of it.
Please sign in to rate this answer.
Amar Agnihotri 891 Reputation points

2021-09-23T10:02:26.277+00:00

Thanks @AnuragSharma-MSFT
Sign in to comment

1 additional answer

Amar Agnihotri 891 Reputation points

2021-09-23T10:41:55.947+00:00

Hey @AnuragSharma-MSFT ,
I read the article you provided. What i understand is that if want to salesforce data to azure for Analytics purpose then we don't need to have Azure SQl or MYsql database rather we can copy data from salesforce directly to Azure data factory. Am i right? Correct me if i am wrong.
Please sign in to rate this answer.
Anurag Sharma 17,566 Reputation points

2021-09-24T06:41:38.637+00:00

Thanks for replying back. SO through ADF we can read and write data from sources to destinations. We can read from one source and write to another destination. As an example if we have our source as salesforce from where we read the data and then destination is azure sql database, then we can utilize ADF to do that. It is basically kind of ETL tool like SSIS.

Amar Agnihotri 891 Reputation points

2021-09-26T05:53:22.157+00:00

Hi @AnuragSharma-MSFT

Thanks for the reply and support. We take backup of Salesforce data using OwnBackup tool everyday. This tool export a backup. The options that it shows in the backup export is shown in the snap attached . I exported it as Mysql. Now i want to import that .sql file to Azure database. My concern is that i want it to store it to SQl server database for azure because i believe that sql server is better than MySQL. So please suggest me which database would be best to import the backup and please suggest me a link which could show the step by step process to import this backup file to azure.

Hope you understand my concern .

Anurag Sharma 17,566 Reputation points

2021-09-27T05:59:48.18+00:00

Thanks for the detailed reply, really appreciate. So what I understand from your query is you have MySQL backup that has to be migrated to Azure SQL database. You can refer to below article that has step by step migration using SQL Server Migration Assistant for MySQL

Migration guide: MySQL to Azure SQL Database

Could you please once check it and see if this meets your requirements and then we can discuss further if we have any query related to implementation?

Amar Agnihotri 891 Reputation points

2021-09-28T14:28:56.347+00:00

Hi @AnuragSharma-MSFT ,
Thanks for the reply. Actually i am using OwnBackup tool of salesforce which creates a backup everyday. There is an option to create a Mysql or AWS S3 endpoint to link it to the Azure . Here are the details -

I I tried Creating MySQL endpoint because i have created an Azure MySQLDB as shown in the images -
Azure My SQL DB -

OwnBackup MySQl Endpoint -

I download the SSL ca certificate from this link and copy all the content into the field using notepad

But i am getting this error. Don't know how to resolve this

Amar Agnihotri 891 Reputation points

2021-09-29T05:08:07.46+00:00

Hey @AnuragSharma-MSFT ,
I got a right SSL ca certificate for own backup. I configured Mysql Endpoint in ownbackup tool but now it was showing this error -

It was asking to client IP 52.4.24.63.

II tried adding this IP in Azure MySql db Connection security but it is not allowing me to add this IP showing this when i click save

can you please help me regarding this . How can i get this error resolved ?

Thanks

Anurag Sharma 17,566 Reputation points

2021-09-29T05:42:45.003+00:00

Hi @Amar Agnihotri , this looks intermittent issue as I was able to add the same ip in firewall rules. Please check the screenshot below. Can you please try it after sometime and see if you still observe the same issue?

Amar Agnihotri 891 Reputation points

2021-09-29T05:50:34.343+00:00

Hi @AnuragSharma-MSFT
That issue is resolved . I have added that IP but now it is showing this

I don't know why is it not getting Database it is existing , though
see this -

Amar Agnihotri 891 Reputation points

2021-10-01T06:01:11.187+00:00

Hey @AnuragSharma-MSFT

Have you find the solution for this issue "Unknown Database"?

Anurag Sharma 17,566 Reputation points

2021-10-01T06:39:08.277+00:00

Hi @Amar Agnihotri , apologies for the delay. It looks like the dump file that you have does not contain the name of database which is shown in error. Can you please try opening the dump file and check the name of database there once?

Amar Agnihotri 891 Reputation points

2021-10-01T06:51:45.213+00:00

@AnuragSharma-MSFT
Thanks for reply . Please tell me how to open dump file because i am using the database name which is created in azure . I have sent you the screenshot. Is there any other way to check the database name then please tell me . I have a database created in Azure and i am using these details in ownbackup. I have successfully
connected that database to Mysql Workbench then why it is not connecting in OwnBackup

Amar Agnihotri 891 Reputation points

2021-10-01T06:55:20.247+00:00

See the details --

it is clearly showing that ids-bi is the database name but error is showing as -

Anurag Sharma 17,566 Reputation points

2021-10-01T07:00:57.513+00:00

If we are able to connect through MySQL Workbench to the same database, it seems the problem lies with OwnBackUp. Can you please tell me how so we add the endpoints here and provide a screenshot of the same? Also the user name should be in format xxxx@<servername>.

Can you also provide the screenshot of workbench after connecting please?

Amar Agnihotri 891 Reputation points

2021-10-01T07:16:36.493+00:00

Give me 10 minutes I am sending you the snaps

Anurag Sharma 17,566 Reputation points

2021-10-01T08:46:22.517+00:00

Please let us know when you have the snaps ready. Also please provide the snap of MySQL Workbench as well.

Anurag Sharma 17,566 Reputation points

2021-10-01T08:55:14.643+00:00

I think I understand the issue now. Mostly you are using Azure MySQL Database instance name as database which is different than database. One Azure MySQL Database can contain multiple databases. As an example your instance name is 'ids-bi.mysql.database.azure.com' but in this instance we need to create databases where we can restore the backup from the dump.

Please find the workbench snapshot below. Here I connected to azure MySQL instance and then created a testdb database. Now this database name you need to specify in database field when configuring an endpoint:

Amar Agnihotri 891 Reputation points

2021-10-01T09:20:37.31+00:00

@AnuragSharma-MSFT OMG let me check this

Amar Agnihotri 891 Reputation points

2021-10-01T09:23:35.527+00:00

@AnuragSharma-MSFT How can i see this Database in Azure Mysql instance . One more thing . Can i run Query in AzureMysql because i didn't find any query editor there

Amar Agnihotri 891 Reputation points

2021-10-01T09:37:55.61+00:00

Hey @AnuragSharma-MSFT

Bravo man Bravo . You did it .
Now please tell me where can i see this database in azure and can i run queries in azure Mysql because i didn't find a query editor in Azure Mysql

Anurag Sharma 17,566 Reputation points

2021-10-01T09:55:55.093+00:00

Great it worked out for you. Through Azure portal we don't have query editor to run queries for Azure MySQL. We need to connect through some tools like MySQL workbench, mysql.exe etc. Below is the screenshot of Workbench:

We can also connect to databases using Azure Cloud Shell as mentioned in below article step by step:

Connect to the server

Amar Agnihotri 891 Reputation points

2021-10-01T10:08:59.05+00:00

Hey @AnuragSharma-MSFT ,
It worked . I exported all the backup to Azure Mysql and now i am able to see all the data in Mysql workbench. Thanks ..

Can you please tell me if i want to perform some transformation on this data in ADF and then save that transformation as a data set. Is it possible to connect ADF to this data or i need to use any other tool.

Anurag Sharma 17,566 Reputation points

2021-10-04T09:12:49.507+00:00

Glad it worked out for you :)

You do not need to perform any transformation on ADF. as ADF is altogether different way to do the same process.
Sign in to comment