0:06 Welcome to this IT Explainer video, part of our configuration topic range.
0:10 In this video we’ll cover the subjects of databases within SLM.
0:15 SQL Database or Structured Query Language Database is a type of database management system that stores data in tables and allows users to interact with the data using SQL queries.
0:28 It provides a structured and organised way to store, retrieve, and manipulate data.
0:33 SQL databases are widely used in various applications and industries due to the flexibility, scalability, and ability to handle large volumes of data efficiently.
0:44 The following information will be covered in this training chapter.
0:47 One will be restoring databases, Chapter 2 will be setting up users, chapter 3 will be views and stored procedure permissions, and chapter 4 will be database backups.
1:01 In chapter one we will review the procedure for restoring databases utilised by SLMSLM uses 3 databases, Cluster master and Revision.
1:12 Let’s go over the restore process.
1:14 Once connected to the database server, let’s right click on the databases and click on Restore database.
1:21 Since we’re restoring from a backup file or a BAK file, we need to select the device and let’s select the backup media.
1:29 Let’s start with the cluster database.
1:33 Let’s click OK and then click OK again, and we can see that we’ve restored the cluster database.
1:46 Let’s follow the same process to restore the master and the revision databases.
1:52 Restore database, select device.
1:57 Let’s add the device master.
2:08 We’ve now restored the master database.
2:12 Restore database again.
2:14 We’ll select our device and select the revision database.
2:26 We’ve now restored the revision database.
2:30 Now we can see all three databases have been restored.
2:32 The SLM uses the cluster, the master, and the revision.
2:39 In Chapter 2, we’ll cover how to set up the users the SLM uses to access the databases.
2:46 These include admin users, application users, and SSRS users.
2:53 Once SLM database has been restored, we need to create the three logins that are going to be used by SLM to access the databases.
3:01 These three logins are going to be the application login, the administration login, and the SSRS login which will be used by the SSRS reports to retrieve data from the database.
3:14 The SQL statement that we’re going to be using to create those logins is going to be CREATE Login, the login name with the password for each associated login.
3:24 Let’s copy these SQL statements.
3:28 Let’s click on a new query.
3:33 We have to make sure that we have the master database selected, and let’s click Execute.
3:38 The logins have now been created.
3:40 If we go to the security and refresh the logins folder, we can see the three logins have been created here.
3:49 Now that the logins have been created, we need to create a user for each login for each of these databases.
3:56 At the same time, for each of these users, depending on the access, we need to assign specific roles.
4:02 Let’s take a look at the roles assigned to each one of these users.
4:06 So we’re going to create an application user using the application login and to this user we will assign the role of data reader and data writer.
4:16 Then for the admin user which is going to be using the admin login, we will assign the role DB owner.
4:24 And then for the SSRS user, we’re going to assign data reader only since this user’s only going to access data requested by the SSRS reports.
4:34 So if we copy this SQL statement, we need to run this script on each of the SLM databases.
4:41 So let’s start with the cluster.
4:44 Click execute.
4:45 Now if we open up the cluster database and we go to security, go to users, and click refresh, we’re going to be able to see that the free users were created.
4:57 Let’s click on the admin user.
4:59 If we go to memberships, the DB owner role has been assigned.
5:05 The application user has Data Reader and Data Writer roles assigned and the SSRS go to membership.
5:19 It has the Data Reader role assigned.
5:23 Now let’s go ahead and run the same script on the master database for SLM Gold.
5:31 Here under the Users folder.
5:33 If we click Refresh again, we can see the three users have been created.
5:38 And lastly, we’ll run the script on the revision database.
5:50 Now we’ve created the free users for the free databases that SLM will use to access data.
5:59 In Chapter 3, we will cover how to apply permissions for views, stored procedures, and functions in SLM databases.
6:08 Once the users have been created, we’ll need to grant execution and select permissions on stored procedures, scalar functions, and tabular functions that will be used inside SLM to retrieve data or perform processes in the database.
6:23 Let’s go over the SQL script that will grant those permissions.
6:26 Let’s start with the first user, which is going to be the application user.
6:31 So what we do here is to declare an iterator that will go through all of the stored procedures and scalar functions and start granting execution permissions for each one of those assigned and assign the permissions to the user, which in this example is the application user.
6:49 After that’s done, we’ll need to also perform the same process but assigning the grant select permission for tabular functions.
6:57 So we have the declaration of the iterator that will go through each one of the tabular functions and it will grant the select permission for each one of those for the specified user.
7:07 Now if we copy this script, if we go to the cluster database, right click and select New Query.
7:15 So we’re going to start running this for the SLM app user.
7:18 Click execute.
7:20 Now let’s run the same script for admin, and now let’s run it for the SSRS user.
7:30 We need to run the same process, the same script for the master and revision databases.
7:36 So let’s go to the master database, start with the application user, the admin, and then the SSRS user.
7:54 Now let’s do that for the revision database appuser, the admin and the SSRS user.
8:07 And this is how we grant, execute and select permissions for the users to the stored procedures, tabular functions, and scalar functions in the database.
8:17 And the last permission that we need to assign is the grant view to any definition assigned to the SLM app.
8:24 This permission will allow the API to take a look at all the default values for each one of the SLM databases.
8:31 When we talk about default values, we’re talking about primary keys, foreign keys, default values for columns for each one of the tables in SLM.
8:39 This information is required by the API to perform validations at the model level when we are, for example, updating records or deleting records.
8:48 Let’s grab this SQL script.
8:50 Let’s start a new query.
8:52 Let’s make sure that we’re on this master SQL Server database.
8:55 Not the SLM master but the actual SQL Server and let’s execute this permission.
9:07 In chapter four, we will cover how to generate a database backup.
9:11 In order to create a backup for SLM databases, we need to follow the following process.
9:17 Let’s right click on the database, hover over tasks and click on backup.
9:22 Destination is going to be backup to disk.
9:26 We need to remove any default destination and let’s add our own one.
9:36 Let’s look for the desired folder which is the SQL DB backups.
9:42 Now let’s use the name prod_slmgold_cluster dot bak for the name.
9:49 Click OK OK again, make sure the backup type is full.
9:58 Go to the backup options and make sure under compression we’re going to use compressed backup.
10:08 Let’s click OK.
10:11 As we can see, we’ve created a backup for the cluster.
10:15 We’re going to repeat the same process for master and revision.
10:20 So let’s right click, go to Tasks and Backup.
10:26 Find our desired folder and give the backup a name.
10:39 In this instance is going to be the master.
10:42 Click OK, set the compression that’s been completed successfully.
10:52 Now let’s backup the revision database.
10:59 Add the destination folder, add the name, make sure the backup type is full, set the compression, click OK and that’s been completed successfully.
11:27 And this is how we backup SLM databases.
