Creating and connecting to MSSQL Database on Azure with Delphi / C++Builder

by Jan 20, 2020

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

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.

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.

Happy coding!

The post Creating and connecting to MSSQL Database on Azure with Delphi / C++Builder appeared first on Stephen Ball's Technical Blog.