98-364 MTA Microsoft Database Fundamentals – Database Administration Fundamentals

1. Securing a SQL Server Database

Database administration is the function of managing and maintaining a database management system, with the primary goal of ensuring maximum uptime for the databases so they can be made available as needed. Some of the most important activities performed as part of database administration are installing, configuring, and upgrading the DBMS along with related application tools. Securing the database is done by managing authentic communication and authorization, database tuning, and performance. Monitoring, the primary goal of which is to assess how a server is performing and optimize it for maximum performance, and last but not least, plan the database recovery, which basically means creating a backup and recovery.

Strategy for the database so that in case of any accidental or deliberate loss of data, there is a way to restore the lost data up to the point the data was removed. When securing a database, there are a few security components we need to be familiar with. First, there are secureables. These are all the objects for which authorization is required, which basically means any object that can be secured. Some of these securable objects can be contained within others. For example, tables reside within a schema, schemas reside within a database, and the database itself resides within the server, therefore creating nested hierarchies called “scopes” that can themselves be secured. We have three secure scopes. The server scope securables come first. Here we have logins, server roles, and databases. Secondly, we have the database scope secureables, which include database users, database roles, and schemas.

And last but not least, we have the schema scope-secureables, which include tables, views, functions, and so on. Aside from securables, we also need to be familiar with principles. Principles are entities that can request access to SQL Server secure variables. For example, server logins, database users, roles, and processes Depending on the scope of influence of a principle, we can categorise them into SQL Server-level principles and database-level principles. Next, we have permissions. Permissions are rights or privileges that a principal can be granted for each SQL Server secure. In order for a principal to access a securable object, the principal needs to have the necessary permissions to do so. Just like principles, we also have permissions at the server level, which can be assigned to logins and server roles, and permissions at the database level, which can be assigned to database users and database roles. To manage the permissions of a principal, we use Data Control Language commands. First, we have the famous grant command.

This command is used to grant permission on a secure object to a principal, for example, authorizing a database user to select data from a specific table. Secondly, we have the revoke command, which is used to withdraw any permission that was initially assigned using the grant command from the principal. And finally, we have the deny command, which is used to deny permission to a principal. This command is helpful when you don’t want an individual member of a role or a group to inherit some of the permissions assigned to the entire role or group. Now we’re going to look at roles. Roles in database terms refer to a collection of any number of permissions or privileges that can be assigned to a principal. Just like permissions, we also have roles at the server level and roles at the database level.

These can further be categorized into fixed roles, which are roles that cannot be altered, and user-defined roles, which are all the roles that we create ourselves. We’re only going to COVID some of the most important server- and database-level fixed roles. If we look at the server level, we have the following fixed server roles that you should be familiar with: First, we have the Yasmin Fixed Server role. This role authorises you to perform any activity on the server. This is why this role is the most powerful fixed server role in SQL Server.

Next, we have the Server Admin Fixed Server Role. This role authorises you to change server-wide configuration options and even shut down the server. Then, we have the Security Admin Fixed Server Role; this role authorises you to manage server-level and database-level authorization. And finally, we have the public fixed server role. This is the server role to which any SQL Server login belongs. Noteworthy about fixed server roles is that permissions cannot be granted, denied, or revoked from them except for the public fixed server role, which is implemented differently than the others. At the database level, we have the following fixed roles that you should be familiar with: First, we have the DB owner role, which is sometimes designated as DBO. Anyone who has been granted this role is authorised to perform all configuration and maintenance activities on the database, as well as dropping the database if need be. This is why this role is the most powerful database-level role.

Secondly, we have the DBDataWriter fixed database role. This role authorises you to add, delete, or change data in all user tables. Thirdly, we have the DBDataReader fixed database role. Once you’ve been granted this role, you’re authorised to read data from all user tables. And finally, we have the fixed public database role. This is the default database role to which any database user or role belongs. This is the only fixed database role for which the permissions can be changed. These are some of the most common fixed server-level and fixed database-level roles you’ll come across when administering a database. In the next slide, we have an illustration that shows the securable principle permission model when it comes to securing a SQL Server database. There are three layers of security. The first layer is the Windows level of security. To authenticate at the server level, we use Windows authentication based on a Windows group, a domain user account, or a local Windows user account. In SQL Server, we have two modes of authentication.

We have Windows authentication mode, where the SQL server relies on Windows to authenticate the login. And we have mixed mode authentication, which is a combination of Windows authentication and SQL Server authentication. To connect to a SQL Server instance, we need a login. We’ve already covered what a login is in one of the previous videos. But just to recap, a login is essentially a security principle by which a user account can be authenticated.

At the SQL Server instance, when you, for example, instal SQL Server and leave all the settings to their defaults, a login will be created based on the local Windows user account or a trusted domain account. SQL Server then relies on Windows to authenticate the Windows user account using the login. Since we are logged into Windows when we run SQL Server, there is no need to separately log into SQL Server. This is the reason we were able to log in without supplying a password. At the server level, we have SQL Server logins and server roles. SQL Server logins are logins that are based on SQL Server authentication. So instead of relying on Windows, SQL Server relies on itself to authenticate the logins.

By using internal authentication methods, we’ve already covered what server roles are and how they can enable users with a specific login to perform server-level activities. This is where authorization occurs for server-level and some database-level secureables, as can be seen in this image. As the last line of defense, we have database-level security. Here we have, among other things, database users, fixed end-user-defined database roles, application roles, and groups. In the middle, we have the various permissions that can be granted to a principal on a secure. We also have the three DCL commands used to manage permissions. Let’s head over to SQL Server Management Studio to get a more practical approach to what we’ve covered so far. To get started, we need to connect to the database engine. Here you can see that I’m using an account that is based on Windows authentication. If we go to the Object Explorer and expand the security node as well as the Logins node, we’ll see a list of all the logins for this SQL Server instance. This is the login we used a few seconds ago to connect to the server. And this is the login we created earlier in this course, which is based on SQL Server authentication. Let’s take a look at the properties of this login. The first thing you see is the authentication mode on which this login is based on.

Since we’re using SQL Server authentication, both password fields are enabled. The credentials for this login are stored in the master database, which is a system database managed by the server. The next thing we’re going to look at are the server-level roles. At the moment, this login is only a member of the public server role. Any server-level principle we create automatically becomes a member of this role and therefore inherits all server-level permissions assigned to this role. For now, we’ll leave this unchanged. Then we have the user mapping section. In this section, we can map database users to this login by simply checking the Map column of the database. Once we have checked the Map column of the desired database, you’ll see a name appear in the User column. This will be the name of the database user that will be created as part of this login. In the next column, we can specify the default database schema for this user. This can be done by clicking on the Browse button over here.

Once we have clicked this button, the following window appears: In this window, we can search for objects of a specific type within the server. Right now, we are searching for objects of the type schema. To see all the available schemas, we can click on the Browse button. Here we have an overview of all the schemas within the fundamental database. We can now simply select the desired schema and click on OK. If you already know the name of the schema, you can simply write it in this text area and click on Check Names. This will validate the text entered and return all schemas with the corresponding names. Once we are finished, we can click OK. Our default schema is now configured. The next thing we can do in this section is manage the database-level roles for this user. At the moment, this user is only a member of the public database role. Just like the public server role, the public database role is automatically assigned to any database level principle.

For demonstrating purposes, we’re going to make this user also a member of the DB Data Reader fixed database role. This will authorize the user to read all data from all user tables. The server scope secureables are the next thing we’ll look at. In this section, we can manage the permissions for the various server-scope securable. In the upper grid, we have the name and type of the secureables for which permissions can be set. And in the bottom grid, we have the explicit permissions that can be granted or denied for the selected secure. We currently have only one secure object, which is the server itself. If we look at the explicit permissions, you’ll notice that we’ve been granted the Connect SQL permission. In the Grand Tour column, we have the name of the principal who granted the permission. This permission has been granted to us by the SA principal, which is the system administrator login. The Connect SQL permission authorises this login to connect to this instance of SQL Server. Except for the With Grant column, the following columns are all very familiar. When a permission is granted to a principal using the With Grant option, that principal is authorised to grant other principals the exact same permission. This is, however, not the case.

If the permission was only granted with the grant command, then it’s very important to be aware of this difference. On the right side, we have the Effective tab. This tab displays all the combined explicit permissions and the permissions that are received from group or role memberships. If you can remember, this login is a member of the public server role and therefore inherits all server-level permissions from the public fixed server role. This is the reason why we have the “View any database” permission. If we want to add another secure item, we can do that by clicking on the search button, after which the following window will appear: Here are three different ways we can search for an object. First, we can select objects by searching for a specific type of object. If we click on OK, we get this familiar window. Here we can select the desired object types. Once we have selected the type of object we want to search for, we can click on Browse to see the available objects of the selected types.

With the second method, we can select all the objects of a specific type. And with the third method, we can select the server itself, which we already have here. For now, this will be sufficient. Let’s go ahead and click OK to finalize the configurations we have just made. We’re now going to disconnect the server, and this time log in using the Mitch login. This login is based on SQL Server authentication, so we have to specify the login credentials. Let’s head over to the fundamentals database. If we expand the security node and the user’s node as well, you’ll see our newly created user along with four other user accounts. The DBO user is the default database user and is always a member of the DBO owner database role. Then we have the guest user account. If we have a user who has access to the database but doesn’t have a user account in the database, the user will inherit all the permissions granted to the guest user account. You can only imagine the security vulnerability this can present. This is why it’s a well-known best practice to disable this user account in every database except the system databases.

Then we have the information schema and the SYS users. These principles are required for internal use by the database engine and cannot be modified or dropped. Let’s take a look at the properties of the Mitch user account. The first thing we see is that this database user is mapped to the login we just used to authenticate. The next thing is the default database schema we configured when we created the user. If we move over to the Membership section, we can see all the database roles that have been assigned to this user. Noteworthy about this is that I cannot change these roles or assign any new database role to this user. And that’s because I am not authorised to do so. Let’s close this for now and look at the activities I am allowed to perform in this database. If we expand the Tables node, you’ll see all the tables we have in this database.

If you can remember, this user has been assigned the DB data reader role, which means that I am authorised to read all data from all user tables. So I can basically go ahead and query each of these tables. One of the things I cannot do is manipulate the data. Let’s go ahead and write an insert statement. If I execute this statement, I get the following error, and that’s because I am not authorised to perform this action. An important thing to know about securables is that each type of securable has its own set of permissions. With the current permissions I have been granted, I can, for example, query a view. But if I want to execute a stored procedure, I need to be explicitly granted the execute command on that object or be a member of a role that has those permissions. Another thing I would like to point out is that everything we’ve done in this video can also be done with Transact-SQL. So I hope you now have a basic understanding of the SQL Server security components, such as secure variables and permissions, and how they all fit together. In the next video, we’re going to cover the basics of backing up and restoring a SQL Server database. Thank you for watching. see you then.

2. Backing up and Restoring a SQL Server Database

and stored data in the database software with the primary goal of reducing data loss in case of any accidental or deliberate loss of data. These processes occur within the context of the recovery model of the database. Before we take a deep dive into recovery models, we’re first going to try and understand what the transaction log is for. It plays an essential role in data recovery. The transaction log is a sequential record that records all transactions and the database modifications made by each transaction. When we perform operations against the database, SQL Server will first check if the data pages of the data to be changed are already in the buffer cache. If that’s not the case, SQL Server will then load the clean data pages from disc into the buffer cache.

These data pages are then altered, resulting in dirty pages. If the buffer cache or log buffer becomes full, the data changes will be written to the transaction log disc at a later transaction checkpoint. The log buffer then flushes the transaction log and writes the dirty data pages permanently to the data file on disk. At this point, the transactions will be marked as completed. If the system crashes for some reason while there are persisted changes, the database will be left in an inconsistent state.

This is where the transaction log comes in. If the system is operational again, SQL Server will check the transaction log for transactions that haven’t been completed prior to the system failure. The data changes that haven’t been persisted to the database on disc will then be persisted, bringing the database back to a consistent state. This is just a high-level overview of the transaction log to point out the important role it plays in terms of data recovery. Let’s now look at the recovery models. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires and allows backing up, and what kinds of restore operations are available. In SQL Server, we have three recovery models available. First, we have the simple recovery model. With this model, the transaction log is truncated on a regular basis and therefore saves log space, but it comes with great risks.

When we use the Simple Recovery model, the transaction log is truncated every time the database reaches a transaction checkpoint. If for some reason the system crashes, you won’t be able to restore the data to a point in time right before the system crashed because there won’t be log records in the transaction log to use. The only thing you’d be able to do is restore the database using a full or differential backup, which we’ll cover in a second.

This means that any data changes that were made between the time of a full or differential backup and the time of the system crash will be lost. That’s why this model should only be used if data loss is acceptable. Secondly, we have the full recovery model. This model allows you to restore to a point in time because all the transactions are fully recorded and preserved in the transaction log until a backup is made. This is the default recovery model when we create a new database in SQL Server. If a system failure were to occur, we’d be able to restore the data to the point in time right before the failure occurred, therefore undoing the effects of the error.

So basically, with the full recovery model, no work is lost due to a lost or damaged data file. The problem with this model is that the transaction log keeps growing and might eventually become full, which requires some maintenance on our end. Last but not least, we have the bulk logging recovery model. This model is a compromise between the two. It provides excellent performance while consuming the least amount of log space. It is also the least used recovery model.

This model uses a technique known as “minimal logging.” This means that only the information required to recover a transaction is logged, reducing the possibility of bulk operations filling up the transaction log. However, due to minimal logging, this model does not support point-in-time recovery. Knowing the recovery models, we’re now going to look at the three most common types of database backups we can make in SQL Server. First, we have the full database backup. As the name suggests, this is a complete copy of the entire database, including all data files and the active part of the transaction log. This type of backup allows for the simplest form of database restoration and can be easily used to restore a database in exactly the same form as it was at the time of the backup. A full backup must be done at least once before any of the other types of backups can be run, and therefore forms the basis for every other kind of backup. Then we have the differential backup. This is a backup of all the changes that have been made since the last full backup.

As you can see in the image, if we create a full backup on Sunday and a differential backup on Monday, that backup would contain only the changes that have been made since Sunday. If I now create another differential backup on Tuesday, that backup would also contain only the changes that have been made since Sunday but would be relatively larger than the one we made on Monday because it now spans over two days. This is also the case if we create another differential backup on Wednesday. Differential backups are generally quicker than full backups because so much less data is being backed up. Noteworthy about differential backups is that the amount of data being backed up grows with each differential backup until the next full backup, as can be seen in this image. Finally, we have the incremental backup, also known as the transaction log backup. This type of backup contains all the changes that have been made since the last backup operation.

So if we, for example, create a full backup on Sunday and an incremental backup on Monday, that backup will only contain the changes made since Sunday. If we now create another incremental backup on Tuesday, that backup will contain only the changes that have been made since Monday. So unlike the differential backup, the incremental backup creates a backup of all the changes since the last backup, regardless of whether it was a full backup or not. Since it only backs up the transaction log, it runs a lot quicker than a differential backup and allows for a point-in-time restoration. Let’s head over to SQL Server Management Studio to create our first backup. First, we’re going to look at how we can configure the recovery model of a database. To do this, we’re going to head over to the Object Explorer and look at the properties of the database. Then we can proceed to the Options section. Here you’ll see a number of database configurations we can change, including the recovery model. Here we could, for example, change the recovery model to simple or bulk logged, but we’re going to leave it at the default. Another way to do this is by using Transact-SQL.

We would then alter the database and change the recovery model property of the database to what we deem appropriate. Let’s close this for now. To create a backup of a database, we can right-click on the database and select Tasks. We can then choose to create a backup, after which the following window will appear: Here you’ll again be able to see the recovery model of the database. The next thing we can do is configure the backup type. For now, we’re going to leave it as a full backup. We still have one thing left to do. We need to select the destination for the backup. Since I already had a backup created for this database, I’m going to remove it so we can start from scratch. To add a destination for the backup, we can click on the Add button. Here we can browse the destination directory. We can either choose to leave it in the default directory SQL Server uses to store backups or choose our own by using the treeless navigation. To keep it simple, I’m going to use the default directory and name the backup file fundamentals; don’t forget the back extension.

Once we’ve finished configuring the destination, we can go ahead and create the backup. If the process is complete, we get a message stating that the backup of this database has been completed successfully. Let’s say that during some database routine or activity, one of my colleagues accidentally deletes one of the tables, specifically the track table. We would now have three tables instead of four. This is a database that stores music information, but if it were a database related to a core banking system, you can understand the ramifications this would have. Luckily for us, we created a database backup, which we can easily use to restore the database to the exact point in time we created the backup, which was before the table got removed. To restore the database, we can right-click on the database and select Tasks. Here you’ll see a restore option. If we select this, you’ll see an array of things we can restore. For now, we will only focus on a database. The first thing you’ll see is the full backup we recently created, and that’s because we use the default directory. If we hadn’t used the default directory, we would need to select Device and then click on the browse button.

Then we get this familiar window. We can now click on the Add button to open the browser window to search for the backup file. Mine is right here, so I can simply select it. Since I’ve used the default location for backups, I’m going to switch the source back to Database. Now let’s go ahead and restore this database. Once the restoration process is done, we get the following message stating that the database has been successfully restored. If we now go to the Object Explorer and refresh the Tables node, you’ll be able to see the track table unharmed, exactly as it was at the time we created the backup. We’ve now covered the fundamentals of backing up and restoring a SQL Server database. This video marks the end of this course. Thank you for being a part of it. In the next video, I’m going to share some resources you can use to further your knowledge on the exam objectives and the practice questions you can use. see you then.