From the course: Microsoft SQL Server 2016 Essential Training
Advanced installation options - SQL Server Tutorial
From the course: Microsoft SQL Server 2016 Essential Training
Advanced installation options
- [Presenter] In this section we'll look at some of the advanced options of installing SQL Server 2016. I've pulled up the SQL Server Installation Center, just like from the previous video, and again I'll click on new SQL Server Standalone Installation. In the first few parts of this wizard I'm going to make the same selections I did last time. Perform a new installation of SQL Server 2016. Next. Enter a product key. Next. Accept the license. Next. I'm going to install the same features. Database Engine, and my Client Tool Connectivity is already checked for me. Next. I'll have to give a different name this time, so I'll go with SQL 2016 ADV, standing for advanced. Then I'll click next. We talked about the Service Accounts previously, but we didn't look at the Coalition tab. Coalition has to do with how we put data into alphabetical order and initially it might be easy to think that there's only one way to put things into alphabetical order, but actually if we dive deeper into that, there are some choices. One of the choices is how do we handle upper-case and lower-case. So Apple spelled with an upper-case a, versus apple spelled with a lower-case a. Which of those should come first alphabetically? We also need to tell the machine whether or not it should consider accent marks. So if we have a word that starts with a without an accent mark and a word that starts with a with an accent mark, should the computer consider the accent marks when sorting things into alphabetical order? The default coalition is called Latin general CI_AS. CI stands for case insensitive, meaning it will ignore case. So apple spelled with an upper-case or apple spelled with a lower-case a would be handled exactly the same. The AS stands for accent sensitive. So accent marks are considered when sorting words alphabetically. If you'd like to change this, you can click on Customize, and we have lots of different options. Most of them will have either CI or CS as part of it. Standing for either case sensitive or case insensitive, and most of them will end with either AS or AI, standing for accent sensitive or accent insensitive. The other part of the coalition usually involves a default language. So we have various default languages, but I'm going to stick with Latin. And I'll hit OK there, and Next. So last time we talked about the Server Configuration tab, where I need to add the current user to make sure I can log into the SQL server. But we did not get into the Data Directories tab. The Data Directories tab allows us to set up a default directory for different file types. By default everything is written to the C drive. And for a development or testing server, nothing wrong with putting everything onto the C drive. But for a server you're going to put into production, you might get better performance by moving some of these files off of the C drive. In particular, the database directory and database log directory typically should be on different drives. When you make a change to data in SQL Server, some data has to be written to the database, and other data is written to the log. If both of those are on the C drive, one will happen first, the other task will have to wait on the first one. However if we move either the database directory or the log directory to another drive, then they could both happen at the same time. So now I have most of my data on C, but the logs are going to be on F, therefore, when I write data to the database, it could at the same time, write to the log. Hopefully giving me a performance advantage. This could also give me an advantage of recovery. So if the C drive or the F drive completely fails, at least I'll have the other one. So I may lose the database, but still have the log, or vice versa, and that will provide us some options to partially restore the database. You also can change the backup directory. So we probably don't want to be writing all of our backups to our C drive, we would probably want to chose the biggest drive we have, and write backups to wherever we have the most disk space possible. Again these are going to be defaults, and all of it can be changed when you create an individual database. By default it will put data and logs and backups in the directories, but that can be changed. The next tab is TempDB. TempDB is a special database that is used when SQL Server can't process everything it needs to process in memory. So imagine it needs to sort a collection of a million records. If those one million records will not all fit into memory at one time, it will have to move some of them to TempDB and records will be moved back and forth between memory and TempDB. Therefore the performance of TempDB can have a big impact on the overall performance of our SQL Server. New in 2016, Microsoft allows us to tweak some of the things about TempDB, when we're doing the installation. Prior to SQL Server 2016 people often reconfigured their TempDB after they installed SQL Server, now we can do it in the installation wizard. Most of the things I wouldn't recommend changing. It typically defaults to a number of files either four, eight or 16 based on your number of processors. It also has an initial size and an auto grow factor. If I was going to change any of these the one I'm most likely to change is initial size, 8 megabytes is pretty small, although it will auto grow automatically rather than waiting for that auto grow event to happen. I could increase the size, give it a little more room to work with initially. Then the last tab on the database engine configuration panel is called FILESTREAM, and again this is something that can be turned on or turned off after you install SQL Server, but we can also do it here in the installation wizard. Just a simple checkbox to enable FILESTREAM and a second checkbox to enable FILESTREAM to access the underlying file system. So once you've configured all of those we'll click Next and we'll get to our Ready to Install page just like we did before when we did the basic installation. So again the things we've looked at in this video are things you may not want to change on a development or a test machine, but some things you may want to change on a production machine, particularly moving the logs to a different drive will almost always result in an increase in performance.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.