In this blog, we will cover how to use Use external table on Azure SQL Managed Instance to read data from Azure SQL Database.
Topics we’ll cover:
- Networking Requirement
- Create authentication credentials on Managed Instance
- Create an external table on Managed Instance
- Create external data source on Managed Instance
- Test querying data from SQL Database on Managed Instance
You can create external tables that access the data on an Azure storage account to allow access to the azure active directory. Generally, it is the same way to create the external table like you create an external table in SQL Server. The External table is created on manage instance, Using create EXTERNAL DATA SOURCE(Transact-SQL). This is the way to consume the data from Azure SQL Database on Managed Instance.
This sort of arrangement could be helpful to applications that need to devour both Managed Instance and SQL Database information, or maybe for an online relocation of information from SQL Database to Managed Instance. This article discloses how to set this up.
Networking Requirement In External Table
As the initial step, there needs to exist availability between Azure SQL Database and Managed Instance. The least demanding choice is to design a public endpoint on SQL Database and permit inbound traffic on port 1433 from Managed Instance. This will permit Managed Instance to sign in and inquiry information from SQL Database. Then again, if you might want to utilize the private endpoint for SQL Database, the least demanding choice is to convey its private endpoint in a similar VNet as Managed Instance. For this situation, too, kindly remember to permit inbound traffic to SQL Database on port 1433 from Managed Instance subnet. If it’s not too much trouble, note that definite guidelines on setting up the systems administration are past the extent of this article.
Options To Set Up Networking Requirement In External Table
1.) Public Endpoint Connection
- If necessary, open the NSG rule on the SQL Database side to permit 1433 inbound traffic.
- Ensure that public endpoint access is working on SQL Database using a database client (such as SSMS).
2.) Private Endpoint Connection
- Create an additional subnet in Managed Instance VNet.
- Deploy SQL Database private endpoint to the new subnet created inside Managed Instance VNet.
If that you maybe have a VM inside a similar VNet as Managed Instance, you can utilize it to test the network to SQL Database. For instance, use SSMS from a VM inside the Managed Instance VNet to associate with SQL Database. Assuming this works, Managed Instance will effectively associate with SQL Database to understand information or read data.
Also, Check Our blog post on Database Migration Service Azure.
Steps To Make Table To Use Azure SQL Database
As the initial step, choose which table on SQL Database you want to expose to Managed Instance. This could be a current table, or you could maybe make another table.
The example code underneath makes another table, AzureDBtable, on a current SQL Database and supplements the current timestamp. Ideally, we’d have the option to peruse off this timestamp from Managed Instance.
-- Execute on Azure SQL Database -- Create table and insert some sample data CREATE TABLE AzureDBtable (TimeTable datetime2) INSERT INTO AzureDBtable values (getutcdate()) SELECT @@servername AS [Server name], * FROM AzureDBtable
Using SSMS, the below shows the table created on SQL Database.
For this blog, we have inserted the following timestamp to the table on SQL Database.
Create Authentication Credentials On Managed Instance
Make verification accreditations for SQL Database on Managed Instance. This will permit Managed Instance to sign onto SQL Database and inquiry information. Utilize a current data set on Managed Instance, for which you need to make an expert key first. Then, at that point, make login qualifications for SQL Database with regards to this data set.
Underneath is the sample script, which you’ll need to change according to your database name, credential to log in to SQL Database, and new master key password.
-- Execute on Managed Instance -- Create auth credentials to SQL DB for a database on Managed Instance -- Use an existing database on Managed Instance USE [databasename-on-mi]; -- Create master key for database on Managed Instance CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'; -- enter credetials to login to Azure SQL Database CREATE DATABASE SCOPED CREDENTIAL AppCredential WITH IDENTITY = 'username', SECRET = 'password';
Create An External Table On Managed Instance
The last step is to make an external table on the information base on Managed Instance, connected with the table on SQL Database. The underneath script is a model interfacing with the “AzureDBtable” made as to the initial step of this blog as a showcase. In this progression, the data type(s) you must characterize for the external table match similar data types characterized for the source table on SQL Database. If information types may not be coordinated, there will be a mistake in query data. This likewise implies that if the external table design on SQL Database transforms, you should re-make the external table on Managed Instance, coordinating with this new construction.
-- Execute on Managed Instance, in the context of the database used -- Connect external table on Managed Instance with Azure SQL Database CREATE EXTERNAL TABLE AzureDBtable (TimeTable datetime2) -- must ensure data structure matches the one on SQL Database WITH ( DATA_SOURCE = RemoteReferenceData );
The screenshot below shows that through SSMS, we can see that an external table connected to SQL Database has been successfully created on Managed Instance.
Create External Data Source On Managed Instance
The subsequent stage is to make an external information source on Managed Instance. This will permit Managed Instance to know to which SQL Database to associate with. Alter the example script underneath to remember the name of the data set for Managed Instance, the association URL (FQDN) to interface with SQL Database worker, and incorporate SQL Database name. Consider it is giving association boundaries to Managed Instance on the best way to interface with SQL Database. You would utilize comparable boundaries if you were associating with SQL Database from another customer, such as, for instance, SSMS.
-- Execute on Managed Instance, in the context of the database used -- Create external table on Managed Instance, link it with Azure SQL Database CREATE EXTERNAL DATA SOURCE RemoteReferenceData WITH ( TYPE=RDBMS, LOCATION='tcp:server-name.database.windows.net', -- insert SQL Database server FQDN DATABASE_NAME='sqldb-databasename', -- insert SQL Database name CREDENTIAL= AppCredential );
Test Querying Data From SQL Database On Managed Instance
As in the step, we have inserted a time stamp on Azure SQL Database; after creating an external table on Managed Instance, execute your query.
-- Execute on Managed Instance, in the context of the database used -- Read data from the external table on Managed Instance. This should pull the table data from Azure SQL Database SELECT @@servername AS [Server name], * FROM AzureDBtable
This result shows the data where the query was executed on the managed instance. This will show the real-time query data from SQL Database on the Managed instance.
Related/References
- Exam DP-300: Microsoft Azure Database Administrator Associate
- Azure SQL Deployment Options | SQL Managed Instance | SQL Database| SQL On VM
- Migrate SQL Server To Azure SQL Database
- Implement A High Availability And Disaster Recovery Environment
Next Task For You
We will cover all the exam objectives related to performing migrations, Hands-On Labs, and practice tests in our Azure Database Administrator training program if you want to begin your journey towards becoming a Microsoft Certified: Azure Database Administrator Associate by checking our FREE CLASS.
Leave a Reply