Video : ER/Studio Data Architect
Design Azure SQL Databases
Microsoft Azure SQL Database is a managed cloud database service provided by Microsoft Azure. It is a fully managed relational database service that is built for the cloud and offers the simplicity and flexibility of a multi-model database that scales to meet demand. Azure SQL Database is a database as a service (DBaaS) that falls into the category of Platform-as-a-Service (PaaS). It is a cloud-based database that runs on a cloud computing platform and provides access as a service. Azure SQL Database is best suited for modern cloud applications that need to use the latest stable SQL Server features.
Microsoft Azure SQL Database offers several benefits, such as scalability, flexibility, and security features. Azure SQL provides complete transparency on its inner workings, allowing for high-level monitoring and analysis via tools like dynamic management views and extended events. Azure SQL offers built-in security controls, including T-SQL, authentication, networking, and key management, as well as intelligent advanced threat detection and proactive vulnerability assessment alerts.
Are you planning to use Azure SQL to manage your data in the cloud?
But are you concerned about challenges and risks, including security and regulatory implications?
Data professionals who design and manage Azure SQL data structures can use ER/Studio to capture organizational requirements, translate requirements into design, and deploy the design to Azure SQL.
Watch this video to learn about the various Azure SQL offerings and how ER/Studio can work with them to provide better visibility and easier updates to data structures.
The session covers:
- Reverse engineer and document source databases.
- Transform database structures to new Azure SQL models.
- Generate and deploy designs to Azure SQL.
- Propagate changes by comparing and merging.
- Automate tasks with macros.
- Generate HTML reports.
And more!”.
Transcript
00:08
Er/Studio Data Architect
Hello everyone, and welcome to this webinar on Azure SQL databases with ER/Studio. I’m super excited to be showing you how we can go ahead and reverse engineer a database from Azure SQL.
01:20
And we will also be exploring some of the features that are specific to Azure SQL within Data Architect, creating a few models, modifying them, and then generating the DDL for them. So, without further ado, let’s get started.
Azure SQL DB
The first thing you’ll do is you’ll go to file, and here you’re presented with the screen, which you can see as a relational data model. And then you have the option to reverse engineer into an existing database. What I would like to demonstrate first is to actually go ahead and show how you can go ahead and create an Azure SQL DB the manual way.
02:08
Reverse Engineer Database
We have the option to show you how you can go ahead and reverse engineer an existing database. Finally, we’ll go ahead and generate the DDL from that reverse-engineered database and show you how it works. And we’ll generate a report.
02:30
Now I’ll go ahead and drag and drop two entities here and then here. One of the cool things about Data Architect is that it’s very highly standardized. You can actually go ahead and add a project name here. If I just double click on this, I’ll just call it that’s. Why, sure, SQL webinar here, I can just give my name. There you go.
03:01
So now that we have this, now. I can go ahead and double-click on entity one. Here I can choose the department table. Department number, enter primary. There you go. Now I’ll just add an employee table.
04:05
So now what I can do is I can actually call this as Employee. And here I’ll go ahead and add. the employee number, primary key name, and location.
04:25
Entity Relationship Diagram
Now what we can do is I’ll go ahead and establish a relationship between these two. Then we’ll go into the ribbon bar and we’ll choose Identifying Relationship. I’ll highlight the department number on the left, and then I’ll left-click on the employee entity.
04:47
Physical Data Model
And there you go I have my relationship established. Next, what I’ll do is I’ll go ahead and save this.
And now what I can do is I can right-click on here and choose Generate Physical Model. Here we’ll be generating Azure SQL databases. So here under the platforms, and this comes as part of a multiplatform license or a single platform Azure SQL DB license.
05:21
So here I have the multiplatform. So here I can choose. Microsoft, SQL, Azure, SQL, DB. And here, I can choose next. Here I’m asked how I would like my keys to be, and since we don’t have any other Objects, you can enforce a naming standard template.
05:43
Here we can go ahead and hit Finish. And there you go. Here’s how you would go ahead and look at Azure SQL DB once it’s turned into a Physical Data Model. And here’s the DDL.
06:04
Reverse Engineer Existing Azure SQL Database
Next, let’s take a look at how we can reverse engineer from an existing Azure SQL database
I Want to stress that in Azure Portal, as soon as you spin up an Azure SQL database, you are required to grant the client firewall to be able to communicate with the Azure SQL Databases service. So with that in mind, I’ve gone ahead and done that in advance and I’m just going to go ahead and log in.
06:40
Next, I’ll go ahead and choose the database in question and I have the option to choose the Schema. So here I’ll pick the sales Schema. And I’ll choose next. Here you can see I have all of the objects listed.
06:56
Next, I’ll go ahead and choose if I need to infer primary keys and foreign keys. I know this has already been designed. But if it’s not, you can go ahead and choose that option.
07:07
Logical Data Model
So here we are commencing the reverse engineering process. Once that is done, you should be able to go ahead and look at the results set here. In just a minute it will go ahead and generate a logical data model and a physical data model here as well.
07:32
Hierarchical Data Model
So then I like to keep my diagrams nice and organized. So I’ll choose a hierarchical model. So here, you can always zoom in and zoom out like this with your mouse, with the control mouse forward, et cetera. The overview also helps you navigate what you need to be. So far so good. This is at the logical level.
In the physical model, we can do the same. I’ll choose a different one and go ahead and double-click on the products table and here, you can see there is the detail that’s been customized.
08:17
Now that we’ve done this, you can do one of two things.
08:21
One is you can either use the Compare and Merge utility to make changes to how you work with it.
08:27
The other option is to actually go ahead and generate a database within Azure SQL.
08:35
Create Azure SQL Database
In my case, I just want to generate a SQL file. I’ll go ahead and generate a SQL file here and put it on the desktop, and then from there I can actually go ahead and choose if I want to create a database.
08:49
I can choose my SQL preview, and send this DDL code off to my developers. And I’m off to the races. So that was how easy it was.
09:01
To do a simple regular generation of the data model and then also how to showcase a reverse engineer of the data model. Now let’s take this to the next level. I’m going to go ahead and generate a report here. I can choose either an HTML report or an RTF report.
09:31
Then I’ll choose HTML here. I can go ahead and choose any of these, choose Next, and select these for the images. Let’s go ahead and create it. So here we go. It’s going to go ahead and show us all of the images. Here we go. Then I’ll choose my Entities. You can see all of them here putting the keys, the relationships. This is the physical data model. And click on this. There you go.
10:14
Now let’s take this to the next level in terms of utilizing our macros, where we can actually go ahead and generate an actual script, basically all the metadata. So now I’ll go ahead and export this model to Excel.
Topics : Data Governance,Data Modeling,Enterprise Architecture,Metadata,
Products : ER/Studio Data Architect,
