How to Create a SQL Database in Azure and Connect to SSMS: Step-by-Step Guide

 Q. How to Create a SQL Database in Azure and Connect to SSMS?

You can go through the steps of how to create a SQL database in Microsoft

Azure and how to connect to the database in SQL Server Management Studio in the below video:

Azure SQL Database is a fully managed relational database service provided by Microsoft as part of the Azure cloud platform. It allows you to create, manage, and scale SQL Server databases in the cloud without having to worry about underlying infrastructure and management tasks. 

Azure SQL Database can be integrated with Microsoft Dynamics 365 to provide a reliable and scalable database backend for Dynamics 365 applications. Dynamics 365 is a cloud-based business application platform that includes a variety of applications for managing different business functions, such as sales, marketing, finance, and operations. Azure SQL Database can be used as a backend database for Dynamics 365 to store and manage data for these applications.

The whole process is divided into 3 parts/videos
1. Virtual Entity Mapping PART 1
2. Creation of Azure Database and connecting in SQL Server Mgmt Studio PART 2

3. Integration into MS Dynamics 365 (Upcoming)

Features: 

•  Fully Managed: Azure SQL Database is a fully managed service, which means that Microsoft takes care of all the maintenance tasks, such as software updates, backups, and high availability, freeing you from the burden of managing and maintaining the database infrastructure.

• Scalability: Azure SQL Database allows you to scale up or down your database resources based on your workload needs, so you only pay for what you use. You can easily increase or decrease the number of vCores or storage capacity of your database without downtime.
  
  

• High Availability: Azure SQL Database provides built-in high availability features such as automatic failover and geo-replication, which helps you achieve a high level of availability and disaster recovery.
  
  

• Security: Azure SQL Database provides multiple layers of security to protect your data, such as network security, encryption at rest, and dynamic data masking.
  
  

• Compatibility: Azure SQL Database is fully compatible with SQL Server, which means you can use the same tools and applications you use with SQL Server, such as SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), and SQL Server Integration Services (SSIS).
  
  

• Integration with Azure Services: Azure SQL Database integrates with other Azure services, such as Azure Active Directory, Azure Monitor, and Azure Logic Apps, which allows you to build complex solutions with ease. 

Limitations: 

• Cost: Azure SQL Database can be more expensive than running SQL Server on-premises, especially if you have a large database and high traffic.
  
  

• Limited Control: Since Azure SQL Database is a fully managed service, you have limited control over the underlying infrastructure, which can be a problem for some customers who require full control over their databases.
  
  

• Limited Customization: Azure SQL Database has some limitations in terms of customization, such as limited access to system databases and limited ability to configure some database settings.
  
  

• Query Performance: Azure SQL Database may not always provide the same level of query performance as SQL Server on-premises, especially for complex workloads.
  
  

• Features and Compatibility: Azure SQL Database may not support all the features and compatibility options available in SQL Server on-premises, which may limit the functionality of some applications.
  
  Overall, Azure SQL Database is a powerful and flexible service that provides many benefits, but it's important to carefully consider its limitations before deciding to use it.

Steps for creating Azure SQL Database and connecting to the database in SQL Server Management Studio

Prerequisite - You should have an Azure account and with a license to create SQL Database. If you don't have a license then you can see the above video for creating a free trial instance for the same.

Steps:

1.  Navigate to the Azure homepage by going to portal.azure.com. 

2. Search for SQL databases and click on Create. 

3. Select the subscription and resource group that the SQL database belongs to. 

4. Create a new resource group and name it. 

5. Name the database. 

6. Create a new server and name it, ensuring that the location is the same as the resource group selected. 

7. Choose SQL authentication as the authentication method and create a set of user ID and passwords as the admin access. 

8. Choose the desired tier for the database. 

9. Select the network connectivity as a public endpoint and set firewall rules accordingly. 

10. Set security to default. 

11. Upload the sample database to the data source. 

12. Review and create the server and database. 

13. Wait for deployment to finish. 

14. Copy the Server name and keep note of your username and password which you created for SQL authentication.

15. Click on SQL databases to find the sample database. 

16. Launch SQL Server Management Studio and select SQL Server Authentication. 

17. Use the server name and admin credentials to connect to the database. 

18. Expand the databases folder to view the sample database and its tables. 

19. Create a new query to run SQL statements on the database.

Virtual entity Integration with Azure Database in MS Dynamics 365| Power Platform

Q. How to create Virtual Entity and integrate with External Data Sources like MS SQL/SharePoint?

You can see how the full video from below YT linkas well.

The whole process is divided into 3 parts/videos
1. Virtual Entity Mapping 
2. Creation of Azure Database and connecting in SQL Server Mgmt Studio PART 2

3. Integration into MS Dynamics 365 (Upcoming)

In D365 we have a  concept called a table, earlier it was called an entity.

An entity/table is a collection of fields that define a set of data and their relationships to each other. It’s the same as an SQL table. 

Both Dynamics 365 entities and SQL tables consist of a collection of fields/columns that define the data structure and a field/column has a data type like int, float, string, optionset, etc which specifies the type of data that can be stored in it. These Entities are used for data storage and management. 

A virtual table is a custom table in Microsoft Dataverse that has columns containing data from an external data source. Virtual tables appear in your app to users as regular table rows but contain data that is sourced from an external database, such as an Azure SQL Database. Virtual entities can be used to connect directly with an external data source like SharePoint or ms SQL without storing the data in CRM. They allow access and manipulation of data from these external sources within Dynamics 365 without replicating that data in the database.

In General, there are two types of entities on Dynamics 365- 

1. System Entities: These are the default entities provided by Dynamics 365, such as Accounts, Contacts, Leads, Opportunities, and Cases. These entities are designed to meet common business requirements and come with pre-defined fields and relationships.

2. Custom Entities: Custom entities are created by users to store data specific to their organization or business process. These entities are created based on the specific data requirements and can be used to model complex business processes.

Custom entities are categorized into two types i.e. Standard entity and Virtual Entity.

Whenever you create a table/entity, by default it will be considered a standard entity. So in order to create a virtual entity you need to change the entity type in the power app portal or mark the checkbox in classic Dynamics 365 interfaces.

1. Standard Entity- They are created and managed inside Dynamics 365 database. Data is stored in the Dynamics 365 database and fields can be created, updated, and retrieved in Dynamics 365 itself

2. Virtual Entity- Virtual entities can be used to create custom integrations with external systems or to enhance the functionality of Dynamics 365 by incorporating external data.

The unique feature of virtual entities is the ability to access and manipulate data from external sources like Sharepoint or MS SQL without replicating that data in the Dynamics 365 database. 

Virtual entities do not support all the features and functionalities that regular entities in Dynamics 365 support, such as workflows or business rules.

Standard Entity	Virtual Entity
Data is stored in the Dynamics 365 database	Data is not stored in the Dynamics 365 database; it is accessed through an external data source using OData endpoints
Data can be created, updated, and retrieved in Dynamics 365	Data is retrieved from the external data source when accessed in Dynamics 365
Schema is defined by the OData endpoint and includes the data source and schema	Schema is defined in Dynamics 365 and includes fields, relationships, and other properties of the entity
Supports all features and functionalities available in Dynamics 365, such as workflows, business rules, and plugins	Has some limitations in terms of the features and functionalities available
Used for storing and managing data within Dynamics 365	Used for integrating external data sources with Dynamics 365, or for enhancing the functionality of Dynamics 365 by incorporating external data

Prerequisite for virtual entities in Dynamics 365:-

1. OData endpoint: A valid OData endpoint is required to connect to the external data source.

2. Data source: The external data source should be accessible and provide the required data for the virtual entity.

3. Data mapping: The external data should be mapped to the virtual entity schema in Dynamics 365. This involves defining the fields, relationships, and other properties of the virtual entity based on the external data source.

4. Security: Appropriate security permissions should be configured to access the external data source

5. Dynamics 365 version: Virtual entities are supported in Dynamics 365 version 9.0 and later versions.

Advantages

1. Reduced data storage requirements: Virtual entities allow you to avoid replicating data in the Dynamics 365 database, which can help reduce storage requirements and simplify data management.

2. Integration with external systems: virtual entities make it easier to integrate with external systems and incorporate external data within Dynamics 365.

3. Improved performance: By avoiding the need to store large amounts of data in the Dynamics 365 database

4. Real-time data access: Since virtual entities access data in real time, you can be sure that you are working with the most up-to-date data available.

Steps are different for creating the virtual table in the classic dynamics interface and in makepower app page. The steps for both are described below.

Steps for creating Virtual Table/entity in the makePowerApp portal

1. Log in to the CRM instance and go to tables, Click on Create on “New Table”

2. Click on Advance Options

3. Select Virtual from the Type field, 2 other fields will become mandatory now i.e. External name and External Plural name

4. Fill in the details for the External name and External Plural name fields. This name should match with the table which is created in an external data sources like SharePoint or MS SQL

5. Scroll down and choose your external data source. For this, the data source should be Added to use for virtual tables. The creation and addition of data sources are shared in another blog.

(1. Sign in to Power Apps, and then select Settings > Advanced settings. 2. Go to Settings > Administration > Virtual Entity Data Sources. 3. On the actions toolbar, select New. 4. On the Select Data Provider dialog box, select from the following data sources, and then select OK.)

6.  Click on the Save button

7. Go to the and click on a column to create a column

8. Click on create new Solumn button and fill the details. Click on Advance Options

9. Give the external name, it should match with the name in external data like SQL or Sharepoint. Click on save.

You’ve successfully created a virtual table. 

Steps for creating Virtual Table/entity in the makePowerApp portal

1. In solution explorer, create a new table. To do this, select Entities in the left navigation pane, and then select New.

2. On the General tab of the Entity Definition, select Virtual Entity, and then in the Data Source drop down list, select the data source that you want.

3. On the Entity Definition, complete the following required columns.

External Name- Enter the name of the table in the external data source this table maps to. 

External Collection Name -Enter the plural name of the table in the external data source this table maps to.

4. Here's an example of a virtual table named Movie that uses an Azure Cosmos DB data provider to access document files.