Introduction

Scenario: You create a Web + Database

Unfortunately, this process does not give you the option to create the web app in an existing database, nor does it give you the option to choose which VNets or Private DNS Zones you want to connect to. This process will create a full app stack with its own web services, VNet, Private DNS Zone, and database.

Issue: You create different web services for multiple websites to be hosted via Azure App Services. However, each service is being billed separately with their own services, and pricing tiers. This method caused a huge increase in costs. Additionally, all web service are created behind a private network (VNet).

Proposed Solution: Consolidate all web apps into a single flexible database server to reduce costs.

Problem: How do I access the database servers with private access to export the database tables?

All web app services created using the Web + Database method was created with private access that required VNet integration. In this article (https://learn.microsoft.com/en-us/azure/mysql/flexible-server-quickstart-create-connect-server-vnet), Microsoft proposes creating a Linux virtual machine within the same VNet, then install MySQL client tools via SSH to connect to the database server using mysql CLI. Or… I’m certain you could install MySQL Workbench in the Linux VM then access the database server that way as well.

Actual Solution: It was easier to convert the flexible database server from private to public using this method – (https://techcommunity.microsoft.com/azure-database-for-mysql-blog/changing-network-connectivity-options-for-azure-database-for/ba-p/3509233).

Once you have successfully converted the databases from private to public, make note of the server name. Username is the same, and password is the password used by the web app.  Proceed with the following steps:

1. 1. Add your public IP address (the IP address that you will be connecting from to the database server using MySQL Workbench) to the Firewall rules of the converted database server with public access. Settings –> Networking

• • Check “Allow public access from any Azure service within Azure to this server”

• • Install MySQL Workbench locally

• • Go to Settings –> Connect –>MySQL Workbench –> then add the following information in MySQL Workbench to create the connection. Test connection. If successful, connect to the database server.

• • When connected to the newly created database with public access, proceed to export the database tables. NOTE: Include Create Schema.

• • If there are more than one private access databases, follow steps 1 – 5 for each database to export the database tables.

• • After completing the export of all database tables, save them to a safe location, then proceed to create a new flexible database server. Azure Database for MySQL flexible servers à Create à Flexible server. Make note of the server name, username and password. You can reset password to the database if needed.

• • Connect MySQL Workbench to the newly created flexible server from step 7 using the configuration from step 4.

• • Import database tables

• • Go to the web app in Azure App Services –>Settings –> Configuration and update DATABASE_HOST, DATABASE_USERNAME, and DATABASE_PASSWORD –> Save to restart the app service.

    

• • Test access to the web apps. If any issues, make sure the database host, username, and password are correct. If access is successful, Congratulations! You have now successfully consolidated all app services to a single flexible database server.

Cleanup – Delete private databases initially created, VNet’s, and Private DNS Zones. Delete all public databases that were converted prior to step one above.