Database configuration options - SQL Server Tutorial

- [Instructor] I'm back in Management Studio logged in under my Windows account and we can start to explore the database that stored on our SQL Server instance by expanding the Databases folder here in Object Explorer. Here we see the Kinetico database that we created in the last movie. This is an example of a user database. User databases are the ones dedicated to storing our own data. We also have a folder that contains the System Databases, and we can expand that, here. These four databases are the ones that SQL Server uses to manage itself. The Master database keeps track of System Settings, the Model database is used as a template for all databases that you're going to create, MSDB is used for scheduling tasks, and the tempDB is used as temporary storage locations that SQL Server uses when performing large numbers of calculations or when moving records around in other databases. Now even though there's nothing from preventing Assistant Aministrator account from digging into the System databases and making changes, at this point in your SQL Server journey, it's probably best to leave them alone. Let's go ahead and just collapse that folder again. Now, creating new databases through the Management Studio interface gives us an opportunity to explore some of the other configuration options that are available without having to know all the intricacies of the Create Database Transact SQL command. To do this, I'm going to right-click on the Databases folder and choose the first option at the top of the popup menu to create a new database. That'll bring up this New Database window and we have several different options to move through and they're organized into these three pages. Management Studio starts us here on the General page. First up, we're going to give our new database a name. I'm going to create a new database for another company that I want to work with called the Landon Hotel. Notice that when I fill in the name at the top, the two files that are going to be created down here at the bottom get filled in. At the top, we can also specify the owner of the database. This is the login account that'll be responsible for managing the database, assigning permissions to other users, and so on. Let's go ahead and assign an owner to this database by clicking on the ellipsis button here on the far right. Management Studio will look through the logins information and I can click on the Browse button, here to browse through that login detail. I'm going to scroll down and I'll place a checkmark next to Octavia's account, here. I'll say OK. That'll add Octavia to the list, and I'll say OK to make Octavia the owner of the LandonHotel database. If we review the database files that are being created, we'll see the logical file names that are being generated based on the name of the database. We can go ahead and expand this column by dragging on this divider line, here. So I'll create two different files. One called LandonHotel and one called LandonHotel_log. The File Type column displays the information that's going to be stored. We have the ROWS data and the LOG data. The Filegroup setting allows us to create multiple data files for our database. In large databases, it's often advantageous to split data across files so that they can be placed on separate harddrives to increase the speed at which they can be searched. Or you can place data files in different geographic regions so that they're physically closer to the end users. For instance, if you want your east coast operational data to be stored in a Datacenter in New York, and your west coast data to be stored in San Francisco, collectively, the data will all be part of the same database, but just stored in separate physical locations. For our purposes, we'll just stick with a single Datafile in the PRIMARY Filegroup. Next up is the initial size of the database which is set to eight megabytes. SQL Server will reserve this space on the harddrive, even though we're not filling it up right away. When we do fill up the initial space reservation, the database will automatically grow based on the settings on the next column. The default is to grow the space being reserved by an additional 64 megabytes when it hits the reservation allotment and it'll continue to do so until the entire harddrive is filled up. You can click on the ellipses button, here, to make changes to the Autogrowth and Maximum File Size properties. For instance, we can choose the file growth in percent or megabytes. We specify those values over here. And we can specify a Maximum File Size. We can either leave it Unlimited or cap it at a specific size. I'll just leave the defaults, here, and press OK. Scrolling right, we'll see the actual file path for both our Datafile and the LOG files. Let me make this just a little bit wider, here. One database administration best practice is to store the data in a LOG file on different physical drives so that they can be read from and written to, simultaneously. For our purposes, we're not going to notice any performance hit by having these files on the same location so let's just keep it simple and store them in the default locations. And finally, if you have a reason to give the files a different name, you could do so over here on the far right. There's probably very little reason to do this, but the option's available. Underneath the Options page are a number of different settings that we can apply to the database. The first one here, is called Collation. This is the way that SQL Server will sort information when you ask for it to be returned in ascending or descending orders. So, for instance, will capital letters sort before lowercase, or will a vowel with an umlaut or another accent mark will that sort before letters without, and where in the ordering will letters from non-Latin charactersets get placed? All of this is important for the SQL Server instance to know and the default options are most likely going to be the right choice for you, but this will differ depending on the region settings of your computer or your business. The Recovery model defines how backups are made. The default option, here, a Full, means that everything will be backed up. For large databases, it might make sense to only backup the changes made since the data was last backed up. There's several different strategies, here, and we'll cover those later on in the course. Compatibility level is fairly straightforward. If you need the database you're creating to be compatible with prior versions of SQL Server, you'll specify that, there. And finally, we have Containment. When it's set to None, that means that the database will fully require a SQL Server for maintenance. Setting this to Partial will move some of the management tasks into the database container. For instance, you could have the database manager verifying login credentials rather than having the SQL Server engine do it. Again, unless you know differently, the default option of None is probably your best bet at this point. Below that is a long, scrolling list of other option that we can change. At this stage, it's probably no reason to adjust any of these settings and they can always be modified later on, if you need to. Finally, we have the Filegroups page. This is where we can set the options for setting up or selecting file groups if we're creating multiple data files. Once everything's configured to your liking, go ahead and come down to the very bottom of the screen and press the OK button. Management Studio will then send the appropriate commands to the SQL Server engine and the database appears over here in the Object Explorer. Now if you want to see exactly what Management Studio instructed the Server to do, you can right-click on the database, point to Script Database as, then CREATE To, and created it to a New Query Editor Window. This will reveal the exact Transact SQL Commands that were sent. You can see it starts with the same CREATE DATABASE statement that we used, previously. Then below that, it contains all the configuration options that we specified. For instance, Containment was set to NONE on the PRIMARY Filegroup and here are the filenames that we'll be creating. When you're done reviewing the code, you can go ahead and close out of the script by clicking on this X, here on the tab. And now we have two different user databases created on our SQL Server instance, and these are created through two drastically different methods.