0:06 Welcome to this IT Explainer video, part of our maintenance topic range.
0:10 In this video we will cover the subject of database maintenance for SLM.
0:15 In this section, we will discuss SQL Server database maintenance plans and explore various options available to the database administrator or IT Administrator.
0:25 A SQL Server database maintenance plan is a comprehensive strategy designed to ensure the optimal performance, reliability, and security of SQL Server databases.
0:36 This plan encompasses a variety of tasks and procedures aimed at maintaining the health and efficiency of the database environment.
0:43 Regular backups ensure that the database is regularly backed up to prevent loss in case of hardware failure, software issues, or other disasters.
0:53 Index maintenance involves rebuilding and reorganising the indexes to improve query performance and reduce fragmentation.
1:00 Integrity Checks involves running consistency checks to identify and repair any corruption or inconsistencies in the database.
1:08 Updating statistics to ensure the query optimizer has accurate information for generating efficient execution plans and database cleanup.
1:16 Removing unnecessary data such as old backup files and transaction logs to free up space and improve performance.
1:27 So for this task, we need to open Microsoft SQL Server Management Studio.
1:32 Once you’ve opened it, expand the Management folder.
1:35 Right click on Maintenance Plans and Maintenance Plan Wizard.
1:39 You may get this error message.
1:41 Not to worry.
1:43 If we come down to the SQL Server Agent, right click and click Start message pops up.
1:50 Click OK, Right click on the SQL Server Agent and click Start.
1:57 Then back to the Maintenance plan.
1:58 Right click and Maintenance Plan Wizard.
2:01 Click Next.
2:04 We’re going to create 2 maintenance plans.
2:06 One will be the daily maintenance plan and 1:00 will be the weekly plan.
2:12 So we’ll name this daily maintenance plan.
2:14 Under schedule, click on change.
2:17 We’re going to select daily and for the time that it’s going to occur, you need to select a time when that people won’t be using the system.
2:26 It could be 10:00 PM or it could be 12:00 AM.
2:30 You have to go whatever’s best for your organization.
2:32 Just click OK, click Next.
2:36 So on the daily plan, we’re going to select Backup database full, Check database integrity, cleanup History, and Maintenance cleanup Task, and then click on Next.
2:54 In this window, we can change the order.
2:56 So we’re going to go with backup database first.
2:59 So we need to click on there and move it up to the top.
3:02 Then we’ll have Check database, integrity, cleanup History, and maintenance cleanup task.
3:08 That’s fine.
3:08 Click Next for the backup.
3:10 Under the General tab, we’re going to select all user databases, and we’re going to tick this box for Ignore databases where the state is not online.
3:19 Now if you have a lot of databases, this may take a while.
3:22 There is a way to see how long this will take to run, and depending on how long it’s taking, you could come back and actually select individual databases rather than all of the databases.
3:36 Let’s say for example, you have 20 databases.
3:39 You can select 10 databases for the first plan and then the other 10 databases for a second plan.
3:45 But in this case, we don’t have very many, so we’re just going to select all databases and click OK, The backup to you have a URL, you can select that here.
3:56 We’re going to go with disk and click on destination.
4:01 If you’re backing up to URL, you can enter the URL in here, but as we’re going with disk, we’re going to go with the default backup folder extension is not back under the Options tab for the compression, we’re going with a compressed backup.
4:14 We do want a performance checksum and continue on error because if there is an error when running a backup, you want it to continue with the process.
4:22 Let’s click next, and this is the integrity task.
4:27 So let’s select all user databases and you can check this box again and click OK.
4:34 The default options are fine in here.
4:36 So click next.
4:37 So this is the clean up task.
4:39 Now 4 weeks is the default, so we’re going to go with that.
4:42 But depending on your company policy, you can obviously edit this and it could be hours, days, weeks, months or years.
4:48 But we’re going to go with the default and click next.
4:51 So now we’re on the clean up task.
4:53 So this will delete the old backups after a certain period of time.
4:57 If you have Azure storage or URL based storage, you can put in the URL here.
5:02 So for us, we’re going to find the SQL backup folder under Program Files SQL Server.
5:12 Go into the SQL folder and select Backup folder.
5:17 Click OK under the extension.
5:21 It’s going to be BAK file age is something again that will depend on your company policy and click on next.
5:28 We can leave this as the default to write a report to a text file.
5:32 Here is an overview of all the actions that we have set up.
5:36 So we’re going to click on finish.
5:38 The maintenance plan is now successfully set up.
5:42 Let’s right click on the maintenance plans and click refresh.
5:48 And here we can see the daily maintenance plan.
5:50 Now if you double click on that, we can see all the steps that are in the plan.
5:58 Here if you want to change something, you can directly click on it to make to edit the plan.
6:04 So for example, if we double click on the database backup, we’re back into this view again where we can make modifications.
6:13 When you make any modifications, make sure you click save.
6:17 If you right click on the maintenance plan, you can go to view history.
6:23 Here we can see the date that it runs the plan.
6:25 Name the sub plan.
6:29 The important thing here really is the duration.
6:31 We want to see how long the plan is taking to run.
6:34 If it takes a long time and you’ve selected all the databases, you might want to create multiple maintenance plans, splitting up the databases into different tasks.
6:43 But here it’s obviously currently empty because we haven’t run the maintenance plan yet.
6:46 Let’s click close.
6:49 So now let’s go back and create the second maintenance plan.
6:53 So let’s right click and go to the maintenance plan wizard again.
6:57 Click next.
6:59 So we’re going to name this the weekly maintenance plan.
7:05 Click on change.
7:07 It’s going to run weekly.
7:10 You want to select a day where it’s going to run when no users will be using the database.
7:15 So let’s go with Saturday.
7:16 So we have the daily maintenance plan running at 12.
7:19 So depending on how long it takes, you may want to change this time so as they don’t run at the same time.
7:23 So let’s just change this to 1:30 AM and that’s it.
7:29 And click on OK, select the default options for the plan properties.
7:35 Now in this one, we’re going to do some reindexing.
7:37 Now based on a data space server that you have.
7:40 If you have standard edition or anything but the Enterprise Edition, then you can select Reorganize and update statistics.
7:48 But if you have Enterprise Edition, you don’t want to select these two options.
7:51 You just want to select rebuild the index.
7:54 The reason is because Enterprise Edition, when it rebuilds the index, it doesn’t take the database offline, which is the aim, because we don’t want the databases to be taken offline.
8:04 In the standard edition, it doesn’t do that.
8:06 For the purpose of this video, I’ll select Reorganise Index, update Statistics, and Rebuild Index.
8:13 But again, if you have Enterprise Edition, you just want to select Rebuild Index.
8:18 It’s not necessary.
8:19 But since we’re producing this video for the standard and enterprise editions, let’s just move the enterprise task to the top here and click on Next.
8:30 And now we have the options for rebuilding the index for the databases.
8:35 Again, we want to select all and ignore databases where the state is not online.
8:41 On the advanced options, we want to keep index.
8:45 This is one that keeps the index online.
8:47 Everything else is default.
8:50 So now click on Next.
8:52 So this is for the standard edition when we reorganize the index, so we’re going to select all databases and ignore the databases where it’s offline.
9:02 Everything else is default.
9:03 Click on next and this is again for standard edition.
9:09 Select all databases and ignore databases that are not online.
9:14 Click on Next, write the report is fine.
9:16 Click on Next.
9:18 That’s an overview of the plan.
9:19 Now click finish.
9:23 If we right click maintenance plans and refresh we can see we now have a daily maintenance plan which involves daily backups and daily checks and cleanups and we have the weekly maintenance plan also.
