DP-300 Microsoft Azure Database – Upgrade strategy, configure database authentication, and filegroups Part 3

6. 25, 112, 114. configure security principals (MIs and VMs)

Now in addition to database roles on the Mi and VM we’ve also got logins and server roles. So let’s just have a look at the fixed server roles that are there. So we have Sysadmin so that’s any activity so be very careful giving people sysadmin Server admin you can change server wide configuration options including shutting down the server security admin. This is another one you need to be aware of just like the DB security admin appear in the database roles you can grant, deny and revoke server level permissions and any database level permissions if they have access to the database.

So this basically allows them to assign most server permissions so be careful when using that process admin allows you to end processes setup admin allows you to add or remove linked servers. So maybe I’ve got an SQL Server elsewhere. I can link it to this one and I can then not just have cross database select statements, but cross server select statements.

Bulk admin so you can run the Bulk insert statement disk admin you can manage disk files DB creator you can create or alter or drop or restore any database and then public. So this includes all users, groups and roles. So it’s when you want the same basic permissions for everybody and again we can use a graphical interface so I can go new Server role and I can use this. Now I’ve created a server role called New Role which I have created here so I’m just going to drop it and again I can just right and click and go delete and have a graphical user interface.

Now before I create it, I just want to just talk through the server principles so I can get a list of all of the server principles by going to Sys server principles. Now notice there’s an awful lot of them including ones that are set up by the server. So I have here type underscore desk so I can say I don’t want the server roles, I just want say external logins or SQL Logins. Now another way of getting SQL logins is using Sys SQL Logins so that’s the system administrator SA and if I want other type of logins that I’ve set up I can use Syslogin token.

So you can see here I’ve got external users and server roles as well as window groups. Now to add an individual to a particular group I can say alter Server role here is a server role and then add member and put the member in. So this will put Susan into the disk admin server all. Now we had previously seen the exec SP help role that lists the database roles. Make sure you’re in the right database before doing it. However, there’s two additional ones we can use here in Mi and VM we’ve got SP help protect or Help Rostec and that returns user permissions for an object or all objects in a current database. So you can see here, these are the permissions. And then we’ve got SP help role member.

So this lists direct members of a role. So what I’m going to do is I’m going to create a new role called New Role. I need to say who is giving this authorization? And I’m going to say Jane, who is the admin on Azure is giving this authorization. So you can see, there it is. Now I can alter it, and I probably would alter it by going to right and click and go down to properties. And then I can add individual role members here. I can do that here as well by Alter Server role, name of role, add a particular member. So if I go back to this download box, cancel it and go back in, we can see that Susan has been added as a role member. I can also change what that role does because it’s a custom role. I can go to General, for instance, and I can put on any endpoints or logins or servers or availability groups.

We’ll talk about that in future videos or server roles and allow me to do things such as Alter, Control, Impersonate, view definition, take ownership. Here’s how I can do it in TSQL. So grant whatever you’re wanting. So grant altar on a particular login to a particular server role, custom one. So I’ll execute that and there you can see we have a problem. And the problem is permissions at the server scope can only be granted when the current database is master. So we can’t do that in a custom database, a user database. So what I’m going to do is change that to Master and I could say use Master go for this, or I can change the database here, so use Master goal, so that would also work. So notice what happens when I run that. The current database then changes.

So, these are your security principles for the Mi and VM. So we’ve got logins and we have got server wide login permissions. So do be careful about giving anyone sysadmin that’s any activity and security admin, because they can give themselves whatever permission they need. We’ve got server admin to chain server wide configuration options, including shutting down the server process. Admin ends, processes, setup admits adobe removes linked servers, bulk admin can run the bulk insert statement disk, admin manages disk files.

And probably the most useful of these fixed server roles, dbcurator to create, alter, drop or restore any database. But make sure you don’t get Dbcreator, which is a server role mixed up with the fixed database role in the master database of an Azure SQL database of DB Manager. So they are similar but different. DB Manager is solely for Azure SQL database, whereas DB Curator is for Mi and VM.

7. 70, 104. recommend table and index storage including filegroups (MI and VMs)

In this video we’re going to talk about table and index storage with regards to file groups. Now, this is for Mi and VM as Azure SQL database only supports one database file, except in the case of the hyperscale. Now, there are three different types of database files. Now, if I write and click on my existing database in the Mi and go to files, you can see that we have got some in a primary file group. So this is a primary file with startup information. There is only one primary file per database. Now, it happens to be in a primary file group as well, but the primary file group can have multiple files. Now, how can I tell it’s a primary file? It’s the extension MDF so M.

Remember that secondary files, you can have additional but optional user defined data files from zero to as many as you want. This can’t be used in Azure SQL database as I say, because it only supports one database file, it can be on separate disks, so you can install a different disk that may increase the input output throughput. So because you’ve got several disks, you can read on one and write on another at the same time. Now, the recommended file extension for secondary files is NDF, and then we’ve got a transaction log. So this is information needed to recover the database and you can have one to multiple transaction files. The recommended file name extension LDF l for log, so m for main, n because it’s the next letter for secondary and L for transaction.

One file can only be used in one database. So simple databases like this have a single data file and a single transaction log file. Now, there are two different file names that are used on VMs especially, and that is the logical name. So this is used in TSQL statements and then you have the operating system name, its location, including its directory path. Now, this is outside of your control if it’s an Mi, so you create the file, but you have to let us just say where it’s going to be. If you’re on a VM, you can say it’s at this specific location. Now we have a size, so this is the current size and it can grow automatically by a percentage or by a fixed file size. You can disable autograph as well and you can have a maximum size or unlimited. Now, it can be a bit confusing if you’ve got lots of different files where you can group them together in file groups to make things easier.

So this contains multiple files, potentially could just contain one file as you can see here for admin, data collection and storage purposes. And again, this is not used in Azure SQL database. So what I’m going to do for this video is create a new database called DP 300 Mia. Now you have a primary file group, so that is the first one that is the main one. With startup information, however, you can change what is the default file group. So what’s the default file group? Well, it contains any objects where you have not specified a file group. So this is separate to the primary file group which contains the primary file which includes system tables. So at the beginning, the primary file group is the default file group. So if I go to properties and go to file groups, you can see here that the primary file group is the default, but it doesn’t necessarily need to be. So other file groups are called user defined file groups and you may also find other terminology like memory optimized, data, file groups, file stream, file groups, those are other different types of file groups. Now a file can only be contained in one file group. So each of these files has a very specific file group, doesn’t have two, doesn’t have zero.

A file group can only be used by one database. Transaction logs are not part of a file group. So you can see it says not applicable. Now if you use multiple data files, microsoft recommends that you create a second file group for the other files and make that file group the default file group. So it separates it out from the primary file and the system tables. So let’s have a look at some TSQL code regarding file groups. So we can do quite a lot of this in the GUI as well, in fact probably all of it, but let’s see how we can do it in TSQL. First of all, I want to alter the database and add a new file group so that’s done. So let’s have a look and see what happens. So we now have two file groups. We have this new file group which contains zero files and is not the default.

Next we can add a file and put it in a file group. So alter database, add file to a particular file group. Now we have to specify the name. This is the logical name of the file. If you’re on a VM, you could also need to specify the file name because this is where it is stored. Then we specify a size and file graph. So that could be a number of kilobytes or it could be a percentage like 10%. So if I execute that, it will create that new file and add it into that file group. I’m also going to add this as well. Auto database modify file group auto grow all files. So what does that do? Well, let’s say you’ve got five files in a file group. We’ve got these certain file restrictions where we say, okay, allocate this amount of memory and then if it happens to go into that memory and wants to go beyond it, auto grows by this amount. Well, what autograph all files means is that if any one file in the file group hits that threshold and has to grow, then all of the files in that file group have to grow.

So that means that presumably you might have five files which are roughly the same size. And when one has to grow it says, okay, let’s put space in all of them. Now it doesn’t affect any files which are not in that file group. So you can see now we have got this new file, it’s in the new file group. And if I go to new file groups you can see it contains one file which is also set for autograph all files. Now, what I’m going to do is create a second file group and I’m going to use basically the same code. I’m going to say file growth equals 10% and this time I’m going to add a second file and I’m going to add that in as well. So this particular file group will have two files.

So now if I look at the properties we will see we’ve got all of these different files and you can see the file group that they’re in. And here we’ve got the new file group with two files. Now, if I want to see where a particular table or other object is, I can look for SP underscore help. So if I create this table by going to create table and then look at it, you’ll see lots of information about it. But it’s here that I primarily want to talk about. Data located on file group primary. So what I can do now is scroll down and create another table. So this table I’ll say exactly the same syntax, create this table but then on a particular file group. So if I do that, then you can see that this is located on that file group. Now, when I create indexes, I can also say which file group I want them to be on. So new file group, file group and that’s where it lives. Now, if there’s multiple files in a file group, you don’t get to say which particular file it goes on, you just get to say it goes on this file group and let the computer sort out all of the files.

Now, what if you wanted a particular file group to be the default? Then we would say alter database, modify file group default. So now if I create another table but not specify where it is because I have said that new file group two is going to be the default, when I create a new table and say where it is, it is now on the new default file group, new file group two. So anything that is unspecified goes on the default file group which is initially the primary file group.

But you can change it by just saying I want a different file group to be the default. And if I write and click on our database and go down to the file groups, you’ll see now that file group two is the default. So this is how you create additional files and file groups so they can be used to split data up between lots of different files in a particular file group which made aid I or access because you can be reading from multiple files at the same time and then you can combine them together. In the query optimizer you have a primary file which contains a startup information and system files.

You have secondary files, as many as you want which are user defined data files and then you have a transaction log, one or multiple transaction logs which aren’t part of file groups and you define a logical name. And if you’re on a VM, an operating system file name, so it’s a location including the directory path and the acronym or the default extension at the end is MDF for the primary file, not primary file group but primary file NDF for secondary and LDF for transaction logs.