An effective and secure installation of SQL Server is a result of proper planning. In this lesson, we'll discuss the important considerations you should understand first.
- [Instructor] Another option that you'll want to consider and plan out before you start the installation of your SQL Server is the collation settings. Collation determines how SQL Server will handle the sorting and the languages that it will support for that particular instance. Each instance of SQL Server can have its own collation settings, so what that means is that if you have multiple instances of SQL Server on a single computer, each can be configured to have its own collation settings. When you start the setup routine, SQL Server will detect your computer's locale based on what the Windows operating system has configured, and then it will select a default collation setting specific to what it finds in that locale setting.
That doesn't mean that you have to keep that. You have the option to change that for each instance that you are installing. Again, the primary thing to keep in mind about collation settings, is it will determine how a lot of your results get returned, in other words, the sorting order that you will see when you issue queries against the database. Other options for you to consider are the authentication mode when you install SQL Server. Now the default selection by Microsoft for the Wizard is a Windows authentication mode only. And this provides perhaps the most secure installation for SQL Server, and the reason being is that it will rely on Windows accounts created either locally on the machine, or through a Windows active directory domain.
If you have an active directory domain environment, it's always recommended to use the Windows authentication mode, and you can really control the security of that server through that active directory domain. However there may be instances where you want to set up a mixed mode authentication, so that is the only other option available. And what that means is that you can choose SQL Server authentication along with the Windows authentication requirements. SQL Server authentication allows you to set up local SQL Server only accounts and passwords.
Sometimes you may want to do that for the purposes of applications that are going to connect to the database, or for other specific purposes. You should really consider the security aspects of your server environment, and whether it's running in a domain environment or standalone, and make the appropriate selections here. Also you'll note that you can select current user, so if you have a specific account that you're logged in as, you can add that current user as an administrator on the SQL Server. It's very important to ensure that you have at least one administrative account that you can access, so that you can continue the configuration of SQL Server.
Also, you can choose the data directories. Now this is an important consideration for a little bit of security but more around terms of the performance of the system that SQL Server resides on. You'll note on this screenshot that the system database directory cannot be changed, but you can change the data route directory, in other words, where SQL Server will start to store your data files by default, and you can also choose to change your user database, user database log, and backup directories.
I highly recommend that you store all user databases, user database logs, and backups on a drive separate from the system drive. So in other words, if your SQL Server is installed on the C drive of a computer, you should be putting all of your data files on a separate drive, and the reason being is because your database files can actually end up being set to grow with no upper limit, and if a database file grows too large and takes up all of the remaining space on your system drive, then your system will actually halt, and Windows can no longer function because it has no more hard drive space left for the memory swap file.
You can also choose to enable FileStream, and you can do that for TransX SQL access, and for file I O access. FileStream allows SQL Server to integrate with the Windows and TFS file system, and it does so by storing varbinary max data types and the binary large object data types on the file system, rather than in the database itself. So these are just some of the planning considerations that you should take into account when you're getting ready to put SQL Server on a system. You'll see some of these screens as we go through the install of SQL Server a little bit later on in one of the install videos.
But for now, keep in mind, planning is of upmost importance. So take these options into consideration and plan your SQL Server install prior to actually starting the installation of the product.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options