Manage Relational Databases

3. SQL Database Security

So in this video, we're going to talk about how Azure SQL Database handles security. Now, again, this is a DP 900 course. And so we're going to be talking about this in a pretty high-level way. But the Azure SQL database has what's called a firewall metaphor. So if I go into the server itself, the AzureSQL Server, then and you can see here, there's even a show firewall settings right on the Overview screen. But I can go down to the security settings under firewalls. Now, this is pretty much how we set this up. We set this up to allow public network access. And we also whitelisted my own IP address in the settings. And this is how we were able to get access to it using the Sequel Management Studio. If I was to deny public network access,then that endpoint would no longer be valid. Furthermore, I am unable to access the SQL Server from outside of Azure. Only servers on the Azure network would be able to access it. Okay, I'm not a big fan of this deny setting where you have to say no in order to enable access anyway. Now, we can also set a minimum TLS version if you follow security at all. TLS 1.2 is the secure version of that standard. A one point oh, and 1.1 are there for backwards compatibility, but I don't think it's considered secure anymore. A lot of your banks and other secure institutions have a one-two minimum. Okay, so I set this myself at one two.Do we want to allow other Azure Services to access the server so that you can set up Azure Databricks or Azure Analysis Services? This must be set to yes. In order for that service to be able to access our database for non-azureservices, you need to have a firewall. You can do it in a range, so you can say from this IP start to this IP end, you can have multiple of them at a minimum. While you're doing development, you need your own IP in there so that you can access it from wherever you're doing development. The other aspect of security is that we can have this SQL Server running on a virtual network. And so in this way, it sort of acts a bit more like a virtual machine, and you can do security through firewall devices and networksecurity groups and have other benefits of having security by having this machine being on a virtual network as opposed to being on the public Azure network. So this is basically how the Azure SQL Database handles security. Now, I should point out that I'm granting myself access to all of the databases on the server. When I whitelist myself at the server level, I'm basically giving myself full access to the server. Now, remember, we did say that you could have multiple databases on the same server. And so there is a way where you can whitelist yourself only for certain databases as opposed to all of them. And that would happen at the database level. And that is not in the portal. That is something you have to do within the database itself. So you will not see that if I wanted to change the firewall, it's going to take me back to the server level at this point. Okay, so that is basically how this handles security.

4. Relational Query Tools

So let's say you have a big database in Azure that contains lots of great data. The thing that makes it valuable to you is the abilityto, which we say is the ability to run queries against the data. So you're able to ask the database questions and have them come back with useful answers. And these are called queries. And you're going to need to use one or more different tools in order to ask the database questions. Now, there is a built-in query editor in the Azure Portal. So as you're going through the database section of the Azure Portal, one of those items is going to be the query editor. You log in using the credentials that you created and then you can run queries and hopefully get useful answers. So here's a look at what the query editor looks like. So I'm in the database that we created. I entered the query editor and then just ran a select statement on the customer table, and you can see it's a browser. It's less useful than a query that's run in a query editor that is downloaded on your machine. But you can save the query, you can download the data. There are ways that this is useful if you need to run a quick query. More useful is a tool called Azure Data Studio. Now this is a free open source tool. My tip for you is that you really should download this and become familiar with Azure Data Studio. What it can do, how it works It's cross-platform because it's open source. It runs on Windows, Mac, and Linux and can connect to your SQL Relational database, whether it's a localSQL Server or an Azure SQL database. And it's kind of like a modern query tool. It's got IntelliSense, you can store your snippets,you can log your queries into Source Control. It's got a terminal if you prefer working in a terminal type format. And query results can come back and you can even create charts from them. So get to know Azure DataStudio before taking this exam. This is a big part of the exam. The other thing is that we already saw that you can use SQL Server Management Studio. I used it a couple of videos ago. I'm calling this the OG, right? SQL Server's original gangster I mean, I can remember using SQL ServerManagement Studio more than 20 years ago, so it's been around for a while. And it does support database administration tasks. So, in conclusion, Azure Data Studio is for running queries but is not for managing your database. You can't create schemas and grant permissions and things like that, but SQL ServerManagement Studio has all that stuff. Some of that is point and click and some of that is run through the query. It's got security features; it's got performance tuning. You can import and export SQL Server backup files. As a result, SQL Server Management Studio is similar to enterprise-grade SQL Server Management, whereas AzureData Studio is more useful and practical. You need an answer? Finally, you do need to know the command line utility as well. There's an SQL command. It is again a command-line utility. You can use it to run SQL statements and stored procs. If you have a script file that you need to passthrough, you can use the command utility to execute the file. It runs on Windows and Linux, so it's crossplatform. There's a Mac version in preview mode, and this can even be run in a Docker container. So, if you have an app that needs to perform some SQL tasks, you can have it issue a sequel command. in a command line in order to get that work done. So those are the basic sequela management tools that you can use. Now, you can do some stuff in like Visual Studio. There are other tools, but for this exam and for what you need to know, you need to know these things. And I would recommend spending some time investigating these tools, reading the website, downloading them, and experimenting with them. That would certainly be worth your time preparing for this test.

5. Structured Query Language (SQL)

So, in the previous video, we discussed the various tools that can be used to query Azure SQL Database. And in this video, we're going to talk about the language that is used to query Azure SQL Database. That language is called "sequel." Now, there are two types of sequel in the SQL Server world. One is called DDoS DL, and one is called DML, and we'll discuss that here. Now, DDL is an abbreviation for DataDefinition Language, and these are the SQL commands that are management commands. So you can create objects and alter them, drop things, and truncate tables. Any kind of manipulation of the structures of a database is a DDL language. The counterpart to that is the DML, which is the more traditional data manipulation language that the select statements insert and updates deletes.Anytime you're operating on data, it's DML. When you're operating on the schema and the server itself, it's DDL. Finally, there is a permission system grantand revoker the two commands, and that's called DCL Data Control Language. So are DDL, DML, and DCL. While we're talking about SQL, let's talk a little bit about the history of that. So, believe it or not, I sometimes refer to it as SQL and sometimes as it, but it is actually a sequel because it was originally titled Sequel S-E-Q-U-E-L in the 1970s and was forced to change the name to SQL due to a trademark dispute. The standard, the international standard for SQL, was formalised in 1986, and they are still working on that standard. So the latest that I could find is Sequel2019 in terms of the industry standard for SQL. But the trick, of course, is that every database has its own version of SQL. So in SQL Server, we use what's called T SQL. Oracle uses PL and SQL. MySQL uses SQL and PSM, and PostgreSQL has its own plpg.SQL. Now, you don't have to memorise these terms, but just understand that there's a standard for SQL and each database vendor has its own slight modifications. Of course, you have to select and insert an update. There's a core set of commands that are standard, but there are, of course, extensions to it that only work in one database or another based on the features of that database. So you might have an Oracle PL SQL command, and like I said, a very simplistic select statement might work. But as you get more complicated, or if you're using the DDL portion of that, then that's certainly not going to be compatible. So you can't just easily switch database vendors without changing your code.

Non-Relational Database Concepts

1. Introduction to Non-Relational DBs

So in this section of the course, we're going to be talking about the next section of the exam, which is to talk about non-relational data, which is worth 25 to the overall score. Now you might see this term and ask, what is non-relational data? We just finished talking about relational data in the last couple of sections. So, a nonrelational database is just an acatchall term that means any other type of database other than a relational database. So those relational databases are based on tables, rows, and columns, as we saw, and any database that stores its data differently is going to be called a nonrelational database. Now the reason these databases exist is because they are going to be useful for storing data that comes from different sources. It's almost an optimised database, and we'll talk about how these things came about in a second. You might also see the term NoSQL, and I may have mentioned this term before in this course. It actually stands for not only SQL. NoSQL is a bit of a misnomer because there are ways of using SQL queries with nonrelational data. And when we go into the document database section, we're going to see that it's calledcore SQL even in the Azure interface. So it's not that there is absolutely no sequel in a non-relational database, it is just that it is not only SQL. Now if we go back into the history of this, we're going to see that it's basically the internet that came along and has forced us to change the way that we deal with data and send data around. We can look at some of the most popular websites online today and things like Facebook were started in the mid nineties, Google became popular in the late 90s, and Twitter in the mid 2000s. These applications and companies started to have to deal with not only Gigabytes or Terabytes, but Petabytes,even exabytes of data and millions of simultaneous users at the same time. And that's when they really started to find the limits of the traditional relational database. Companies like Twitter started on the MySQL database, and we've talked about that. Azure does have a hosted MySQL,so Twitter started on a MySQL database. But if you were around on Twitter in the late 2000s, very famously, there's a thing called the fail whale. Whenever Twitter was down, it would show you a picture of a whale, basically saying "oops,we're having a bad day" kind of thing. Twitter had to basically push the limits of relational database technology, as well as the code on which they built their applications. To handle this at this scale, Twitter had to be rewritten two or three times since its inception, including scrapping its MySQL database and almost inventing its own database technology. At the same time that these companies were there,we got standards that were starting to come. So HTML, the OG in 1991, is now a web page is data, it is not structured at all. By definition, a web page is quite flexible. The browser has to accept tags that are not closed properly, BR and p,and things that don't have closing tags. And so HTML is a data storage format for text, but it's not very rigid. And so some of those people, including Tim Berners-Lee, had to create a new standard for data, namely XML, in the late ninety s.And that became more standard. You can also define a schema and have your application enforce it on XML. Some say XML is too rigid. It's a very difficult file format to work with sometimes. The JavaScript object notation or JSON format became popular in the mid-2000s because people had browsers and JavaScript is the dominant scripting programming language for browsers, and they needed a way for servers to return data into the browser without the need for Java outlets or Flash plugins. You know, you needed a native waybuilt into a browser to handle data. So JSON was born in the mid-2000s, and now XML and JSON are the two dominant data standards for passing free text over the internet between servers. Now think about what we're just talking about, Twitter. I just looked at the stats at 200 billion tweets per year as the current run rate on that. And I've worked with databases all my life. I've worked with what I, at the time, considered to be rather large databases, where copying that database took forever. There were queries that we ran that took 24 hours to complete, things like that. Assume you're trying to run a search query on a table with a trillion rows. So if Twitter had a tweet a table, and every year 200 billion were added to it, and you wanted to find tweets with the word Azure in them, I mean, that's just going to take forever. And just to get the first result back could take hours just to run a traditional search for that. So very quickly, these companies found that relational databases were not good for their usage. So back to non-relational databases, because they're optimised for different usages. In the next video, we're going to talk about the different uses of that.

2. Non-Relational Data Types

So now let's go a level deeper and look at the types of data that are stored in a non-relational database so that we can contrast them with what we saw previously talking about relational data. Now the first thing we can talk about is what is called a document or JSON document. Remember, I said JavaScript script has an object notation which is called JSON. And what we're finding is that little applications might want to store smaller bits of data. And instead of having it as a JavaScript notation and then having to translate that back into a SQL statement to insert that into a relational database, we can just store the JSON data in the database. So you put that JSON data there,pull it out, put it there. This could be done with a relational database. You could have a primary key and a single column that stores the JSON document. But document databases can do this more efficiently. The time to write this is a lot faster. The time to read it is a lot faster. There's a product we're going to talk about in the next section called Cosmos DB, which is the Azure nonrelational database enterprisegrade system, and it used to be called Document DB in that. And now it's called Core, core sequel.And so anyway, if you need to store data and the original source is in JSON format and you're going to consume it in JSON format, this could be a good option for storing it. Maybe you don't need to do queries, adding up the costs or multiplying by the quantity. You don't have to do complicated stuff on the individual fields in there. That could be a good option. The next type of data is called column family data or column data. I have trouble saying the word "column," so I'm going to call it "column family." And you could say this is similar to a table structure. But really, the column family is sort of like an anested tables system where you have this customer ID and you've got groups of columns that are grouped together, like the Identity group or the Contact Info group. Now, we can see here that the data itself is not a fixed schema. So sometimes you have a title, sometimes you have a suffix, sometimes you don't have that. And so you have more of a flexible structure, but it is a denormalized data structure where groups of columns are stored against the same ID. This was made famous in the Cassandra database. I believe Cassandra was invented by Facebook. And so Facebook developed this for its own purposes. There's also a database in Azure called HD Insight,which is Hadoop, which is a big data solution. A lot of that is based on Apache products. And so they have a database as part of the big data called HBase, and that's another column family database. Next up, we have what is called a table API. It could be key-value data, where you have a key and then you have a value. This could also be called a hash table. And we see this for various things, like redis. A good example is where you're using it as a cache where some sort of GUI ID has been created. You get stored in the new cookie, and that data can be stored in the cache. When you need it, you simply look it up using its unique key. Again, the data, in this case, the TableAPI, is not meant to be searchable. You're storing data in some strange format, I'll call it, but at least it's opaque. And then you just retrieve it. It's a key-value data store. This is also how Azure Table Storage works and just said redis as well. So, this is another option for storing data. Now, we can also get outside of traditional tables, key values, and things like that. This is called a graph database. A graph database deals with nodes, the relationships between items. And so I took this graphic from Microsoft. We can see that there's a person, Thomas, in the middle. He's interested in football. He knows a person named Ben who is also interested in football. And so you have these nodes within the graph database, and they have relationships. So it's kind of like a relational database. But you'll see that Thomas has many relationships. He's interested in football. He uses a laptop computer. He knows Ben and Robin, and his computer runs Windows, etc. And so, these are relationships. Again, it's a very, you know, Facebook has a similar concept when you do a search where you can search for something, and then there's posts and people, and you can say, I want to know all the people near me who know this person. And Facebook, through Graphsearch, can interpret that information. Next up, we'll talk about something we don't talk about very often, which is the Time series. And so this data really is optimised around timestamps. You're going to store something. Let's say you've got an Internet of Things monitor,and every few milliseconds it pushes in a piece of data and it's all organised around the timestamp. So Time Series Data optimises data storage around that? There's a product in Azure called Time Series Insights that is a data store optimised around timestamps. There's also an HBase option called OpenTSDB. That is time series data within big data. Now, getting out of the traditional databases, we are getting into what we could call file storage, or in this case, blob storage. You could store CSV files, text files, or JSON files in a Blob container, and that is a data storage. And you can process and pull those out of the professional system. Sometimes those get imported into a SQL data warehouse or something else, and we'll show the adiagram in a coming video about that. So Blob storage counts as a non-relational database when it's used like this. Finally, this isn't often mentioned, but Azure has a search product. And you can point your Azure search, add a database, add a web server, and it's going to go and pick out all this information and store it. And, you know, it has built inoptimizations around searching, natural language recognition, queryscoring, and point system relevance. So it's totally different data storage,but it is indeed data storage.

ExamSnap's Microsoft DP-900 Practice Test Questions and Exam Dumps, study guide, and video training course are complicated in premium bundle. The Exam Updated are monitored by Industry Leading IT Trainers with over 15 years of experience, Microsoft DP-900 Exam Dumps and Practice Test Questions cover all the Exam Objectives to make sure you pass your exam easily.