More complex databases can be created with the aid of the graphical interface provided by Management Studio. In this video, discover how options for the size and location of the main data and log files are exposed in easy to understand tools, and users can be assigned as the database owner, all without having to know the full syntax of the CREATE DATABASE Transact SQL command.
logged in under my Windows account, and we can start to explore the databases stored in 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 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. Even though there's nothing preventing a system administrator account from digging into these 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. 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 pop-up menu to create a new database. That'll bring up this New Database window, and we have several different options to move through. 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 get 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 login's 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 check mark next to the 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 Landon Hotel database. If we review the database files that are being created, we'll see the logical file names that are being generated based off of the name of the database. We can go ahead and expand this column by dragging on this divider line here. I'll create two different files; one called Landon Hotel and one called LandonHotel_log. The File Type column displays the information that's going to be stored, so 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 they can be placed on separate hard drives 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 data center 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 data file in the PRIMARY file group. Next up is the initial size of the database, which is set to 8 MB. SQL Server will reserve this space on the hard drive even though we're not filling it up right away. When we do fill up the initial space reservation, and we specify those values over here. 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 data file and the log files. Let me make this just a little bit wider here. One database administration best practice is to store the data and the log files 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. 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 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 character sets get placed? All of this is important for the SQL Server instance to know, and the default option is most likely going to be the right choice for you. But this'll differ depending on the region settings of your computer or your business. The recovery model defines how backups are made. The default option here of Full means that everything will be backed up. For large databases, it might make sense to only back up 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. Finally, we have containment. When it's set to none, that means that the database will fully require 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 manage 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 options that we can change. At this stage, there'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. 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 create it to a new Query Editor window. This'll 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 file group, and here are the file names 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. Now we have two different user databases created on our SQL Server instance, and these are created through two drastically different methods.
- Installing Management Studio
- Enabling features with SQL Server Configuration Monitor
- Creating a database
- Creating tables with Transact-SQL (T-SQL)
- Creating relationships between tables
- Creating views
- Creating user-defined scalar functions
- Backing up and restoring SQL Server
- Monitoring and management