Creating and connecting to MSSQL Database on Azure with Delphi / C++Builder
I have been asked multiple times recently about connecting to a Azure databases with Delphi and C++Builder. So….I decided to make a video! Which even shows how to use the data directly at design time in the RAD Studio IDE.
The video follows the the 3 phases.
- Creating an account
- Creating a database
- Connecting to the database from the RAD Studio IDE
Creating an Azure Account
The first thing you need to do is to create an account on Azure. To create an account for free, (and you can even use a free trial from Microsoft with most things included for 30 days) visit https://portal.azure.com/
Creating the a database on Azure
Once you have registered, you need to Create a resource. I used a SQL database (as that’s what I have had requests for).
At this point a wizard displays, and it’s pretty straight forward to follow.
Create SQL Database – Basics
Azure Resource Group
On Azure, a resource group brings all the resources you are using in a deployment together. This enables identification of costs and easy removal of all aspects of a deployment later on.
Resource groups can also be used to generate a JSON deployment file that enables re-deploying a configuration again.
Database details include the name of your database. This needs to be unique on your server. The first time you run the wizard, you will need to create a server. (use “Create new” hyper link).
When you create a server, it will ask for a unique server name, plus a user name and password, along with a location to locate the database.
The location for the database can be important depending on the type of data to be stored and your local geographical data storage regulations. – Full list of Azure data locations
The nice thing here, is that this configures everything around the virtual machine that the database server is running on. You don’t get direct access here, as it runs as a platform service, which is highly efficient.
Regarding the Server, you can configure its spec here as well, but for now, just go with the Standard version.
Create SQL database – Networking
Azure offers a great option for limiting access to the database from specific IP only (or from within your resource group on the cloud only. I’ll leave that up to the reader to explore further, but its very simple to define the database to work on public, and add in your IP Address as an allowed route for access only. (Great if you have a fixed IP Address)
Create SQL database – Additional Settings
Additional settings enable the configuration of parameters including Collation and data source.
The data source can either be a
- None – a blank database
- Backup – a backup of a database (ideal if you are migrating to the cloud)
- Sample – which creates a copy of the AdventureWorksLT (which is great if testing the setup and configuration for the first time)
Create Database – Tags
Tags are used to categorise and consolidate azure resource usage to help with billing information. Kind of out of scope for this post, but at least you know what they are!
Create SQL Database – Review and Create
If you miss anything in the wizard, you will get validation failed message at the end. If this happens, just skip back through the wizard using the links on the page (rather that the back button) Once the wizard is complete, confirm with “Create” and watch while the database creates.
Using the Azure Cloud Database in RAD Studio
Once the database is live, you can then connect to it using RAD Studio IDE. From here you can drag and drop into your applications to get started.
In the video I used a VCL application and a DataModule. If you follow the same approach, you can always see if you can then use the same DataModule in a FMX application and Target Linux (as I believe there are MSSQL drivers for Linux). For now, however, I only needed to cover Windows… so thats out of scope for now.
Adding MSSQL connection to the Data Explorer.
I’ve previously blogged about Using the Data Explorer with FireDAC, so I don’t plan to cover that in depth here. But in short, Right click on Microsoft SQL Server, and choose “Add New Connection” and enter the details required.
For the latest on the connection see FireDAC DocWiki entry for MSSQL.
In Summary – Note the “@<server>” suffix in User_Name and the “tcp:” prefix in the Server parameters.
DriverID=MSSQL Server=tcp:nasdfert6.database.windows.net Database=Northwind User_Name=addemo@nasdfert6 Password=asd123zxc Encrypt=Yes MetaDefSchema=dbo MetaDefCatalog=Northwind
For the sample used, replace NorthWind with the name of your sample databases. The username is <username>.<servername> (but just the first part of the server name that you added in). I found it worked for me with both tcp: and without, but you may want to add this in to be safe.
Once added, test the connection before saving.
You are now ready to use the datasource in the IDE, explore the tables and data and to drag and drop onto your data module for use in your application.