You can invoke SQL Server’s Installation Center by running SQL Server’s setup.exe application. The Installation Center provides many utilities that will help you install an instance; these include links and tools to assist you with planning your deployment, stand-alone and clustered installation capability, and advanced tools, which will allow you to build instances using configuration files or based upon preprepared images.

This chapter will provide an overview of the options available to you in the Installation Center before guiding you through the process of installing SQL Server using the graphical user interface (GUI). It will also offer real-world advice on decisions that are critical to the ongoing supportability of your instance.

Installation Center

The SQL Server Installation Center is a one-stop shop for all activity that relates to planning, installing, and upgrading a SQL Server instance. It is the application that you are greeted with when you run the SQL Server installation media. Installation Center consists of seven tabs, and the following sections will describe the content of those tabs.

The Planning Tab

The Planning tab is illustrated in Figure 2-1 and consists of numerous links to MSDN (Microsoft Developer Network) pages, which provide you with important documentation on SQL Server, such as a complete set of hardware and software requirements and documentation for SQL Server’s security model.

In addition to accessing documentation with the links provided, you can also access two tools. The first of these is the System Configuration Checker. This tool runs during the installation process to determine if there are any conditions that will prevent SQL Server from being installed. These checks include ensuring that the server is not already configured as a domain controller and checking that the WMI (Windows Management Instrumentation) Service is running. When you run this tool before you begin installing SQL Server, it can prewarn you of any issues that may cause the installation to fail so that you can fix them before you begin installation. The System Configuration Checker is also available on the Tools tab on the Installation Center.

The second tool is the Install Upgrade Advisor. You can use this tool to analyze any instances of SQL Server 2005 or higher that already exist on the server to ensure that you will have no issues upgrading those instances to SQL Server 2014.

The Installation Tab

As illustrated in Figure 2-2, the Installation tab of the Installation Center contains the tools that you will use for installing a new instance of SQL Server, adding new features to an existing instance, or upgrading an instance from SQL Server 2005, 2008, or 2012. In order to install a stand-alone instance of SQL Server, you would select the New SQL Server Stand-Alone Instance Or Add New Features To An Existing Instance option.

In addition to installing a stand-alone instance, adding new features to an instance, and upgrading an existing instance to the latest version, there are also options on this screen for installing a SQL Server failover clustered instance and for adding a new node to an existing failover cluster. A failover cluster is a system where between 2 and 64 servers work together to provide redundancy and protect against a failure that stops one or more of the servers from functioning. Each server that participates in the cluster is known as a node.

The SQL Server Database Engine and the SQL Server Analysis Services are both “cluster-aware” applications, meaning that they can be installed on a Windows cluster and can make use of its failover capabilities. When installed on a failover cluster, databases and transaction logs are located on shared storage, which any node in the cluster can use, but the binaries are installed locally on each of the nodes. Failover clustering will be discussed in detail in Chapter 12.

The Maintenance Tab

The Maintenance tab contains tools for performing an edition upgrade, repairing a corrupt instance, and removing a node from a cluster; it also contains a link to run Windows Update, as illustrated in Figure 2-3.

You can use the Edition Upgrade option to upgrade an existing SQL Server 2014 instance from one edition to another; so for example, you may wish to upgrade an instance installed as Developer edition to Enterprise edition. You may remember that in Chapter 1, I spoke about a situation in which you may have mistakenly installed Enterprise edition as opposed to Enterprise Core edition. You can use the Edition Upgrade option to fix that issue.

You can use the Repair option to attempt to resolve issues with a corrupt installation of SQL Server. For example, you can use this tool if the Registry entries are corrupt or if the data files of the Master database have become corrupted, preventing the instance from starting.

Use the Remove Node From A SQL Server Failover Cluster option to remove SQL Server from a node within a failover cluster. You can use this option as part of the process for evicting a node. Unfortunately, the Installation Center has no functionality for uninstalling an instance. You must do this through the Control Panel.

Not surprisingly, you can use the Launch Windows Update To Search For Product Updates option to launch Windows Update. You can then choose to install the updates and fixes that are available for SQL Server.

The Tools Tab

The Tools tab contains a selection of tools that will assist you in installing SQL Server, as illustrated in Figure 2-4. This includes the System Configuration Checker, which I introduced earlier in this chapter; a discovery tool for SQL Server components already installed on the local server; the Microsoft Assessment and Planning (MAP) tool; a link for downloading SQL Server Data Tools for Business Intelligence; and the PowerPivot Configuration Tool.

Choose the Installed SQL Server Features Discovery Report option to analyze the local server and return a list of all SQL Server features and components that are installed. This will include features from all versions, from SQL Server 2005 on.

The Microsoft Assessment And Planning (MAP) Toolkit For SQL Server option will provide you with a link from which you can download the MAP for SQL Server tool. When you run this tool, it will perform a network-wide search for SQL Server, Oracle, and MySQL installations. It will produce a detailed report, which, for SQL Server, will include the name, version, and edition of the component. For Oracle, it will include the size and usage of each schema, including complexity estimates for migration. You can also use this tool to plan migration and consolidation strategies and to audit license requirements across the enterprise.

The How To Get SQL Server Data Tools - Business Intelligence option provides you with a link for downloading SQL Server Data Tools. Data Tools provides an IDE (integrated development environment) and project templates that allow developers to build components across the SQL Server BI stack, including SSIS (SQL Server Integration Services), SSRS (SQL Server Reporting Services), and SSAS (SQL Server Analysis Services), within a Visual Studio shell. T-SQL project templates are also available for Data Tools, taking away developers’ dependency on SQL Server Management Studio.

You can use the PowerPivot Configuration Tool to remove, upgrade, repair, or configure PowerPivot within a SharePoint 2010 or 2013 farm. The configuration options included allow you to specify the account that will run PowerPivot’s Internet Information Services (IIS) application pool, select a port number for the Central Administration web app, and specify the local instance of SQL Server that will run PowerPivot. This option is required if you are using a named instance.

The Resources Tab

As illustrated in Figure 2-5, the Resources tab contains links to useful information regarding SQL Server. This includes a link to SQL Server Books Online, the Developer Center, and the SQL Server product evaluation site. Additionally, on this tab, you will also find links to Microsoft’s privacy statement and the full SQL Server license agreement. Another very useful link is one that directs you to the CodePlex samples site. From this site, you can download the AdventureWorks databases, which will aid you in testing features of SQL Server with a precreated database.

The Advanced Tab

On the Advanced tab, illustrated in Figure 2-6, you will find tools for performing advanced installations of SQL Server, both as a stand-alone instance and also as a cluster. These tools include Install Based On Configuration File, Advanced Cluster Preparation, Advanced Cluster Completion, Image Preparation Of A Stand-Alone Instance Of SQL Server, and Image Completion Of A Stand-Alone Instance Of SQL Server.

When you are installing SQL Server, a configuration file will automatically be created. It is also possible to create this configuration file manually. You can then use this configuration file to install other instances of SQL Server with an identical configuration. This can be useful for promoting consistency across the enterprise. Once this configuration file has been created, you can use the Install Based On Configuration File option to install further instances based on the precreated configuration. Configuration files can also be useful for command-line installs, which will be discussed in Chapter 3. Additionally, you can also use a configuration file for cluster preparation.

If you wish to use a configuration file for cluster preparation, then instead of choosing to install the cluster via the New SQL Server Failover Cluster Installation and Add Node To A SQL Server Failover Cluster wizards, which are available on the Installation tab, you should choose the Advanced Cluster Preparation option on the Advanced tab. You will initially run this on one of the cluster nodes that can be a possible owner of the SQL Server instance, and a configuration file will be generated. Subsequently running the Advanced Cluster Preparation wizard on all other nodes of the cluster that can be possible owners will result in the configuration file being used to ensure consistency of installation across the cluster. This approach will even work for multi-subnet clusters (also known as geoclusters), since SQL Server will automatically detect the relationship between the subnets and you will be prompted to select an IP address for each subnet. The installation will then add each of the IP addresses as dependencies to the cluster role, using the OR constraint, where each node cannot be the possible owner of every IP address. Alternatively, it will use the AND constraint, where each node can be the possible owner of every IP address.

Once you have run the Advanced Cluster Preparation wizard on every node that is a possible owner of the clustered instance, you can run the Advanced Cluster Completion wizard. You only have to run this wizard once and you can run it on any of the nodes that are possible owners. After this wizard has completed successfully, the clustered instance will be fully functioning.

The Image Preparation Of A Stand-Alone Instance Of SQL Server option will use Sysprep for SQL Server to install a vanilla instance of SQL Server, which is not configured with account-, computer-, or network-specific information. It can be used in conjunction with Windows Sysprep to build a complete template of Windows with prepared SQL Server instances, which can then be used for deployments across the enterprise. This helps enforce consistency. In SQL Server 2014, all features of a stand-alone instance are supported by Sysprep; however, repairing an installation is not supported. This means that if an installation fails during either the prepare phase or the complete phase of the process, the instance must be uninstalled.

To finish the installation of a prepared image, you can use the Image Completion Of A Prepared Stand-Alone Instance Of SQL Server option. This option will allow you to complete the configuration of the instance by inputting the account-, computer-, and network-specific information.

The Options Tab

As illustrated in Figure 2-7, the Options tab of the SQL Server Installation Center displays the processor architecture that you can use to install SQL Server, based on the processor type in your server. It also allows you to specify a path to the installation media. This can be useful if you have a copy of the media stored locally on the server.

Installing a Stand-Alone Database Engine Instance

As discussed in the preceding section, an instance of SQL Server can be installed in various ways, including via the command line, by using Sysprep with an advanced installation using a configuration file, or by using the New SQL Server Stand-Alone Installation Or Add Features To An Existing Installation option on the Installation tab. It is the last of these options that we will use to install SQL Server in the following demonstration. In the following sections, we will install a Database Engine instance with features that will be examined in further detail throughout this book, including FILESTREAM and Distributed Replay. We will also take an in-depth look at choosing the correct collation and service account for the instance.

Preparation Steps

When you choose to install a new instance of SQL Server, the first screen of the wizard that you are presented with will prompt you to enter the product key for SQL Server, as illustrated in Figure 2-8.

If you do not enter a product key on this screen, you will only be able to install either the Express edition of SQL Server or the Evaluation edition. The Evaluation edition has the same level of functionality as the Enterprise edition, but it expires after 180 days.

The next screen of the wizard will ask you to read and accept the license terms of SQL Server, as illustrated in Figure 2-9. Additionally, you will need to specify if you wish to participate in Microsoft’s Customer Experience Improvement Program. If you select this option, then error reporting will be captured and sent to Microsoft. A link provided on this screen will give you further details of Microsoft’s privacy policy in relation to this error reporting. You should always read the statement carefully, to ensure acceptance aligns with you company policy, but in general, it is a good thing to participate in the program because it helps Microsoft improve the platform and generally, no personal data is collected.

After you accept the license terms, SQL Server setup will run a rules check to ensure that it can continue with the installation, as illustrated in Figure 2-10. This is the same configuration check that you can run independently from the Planning tab of SQL Server Installation Center, as discussed earlier in this chapter.

Assuming that all checks pass successfully, the screen of the wizard illustrated in Figure 2-11 will prompt you to choose if you want Windows Update to check for SQL Server patches and hotfixes. The choice here will depend on your organization’s patching policy. Some organizations implement a ridged patching regime for the testing and acceptance of patches, followed by a patching cycle, which is often supported with software such as WSUS (Windows Server Update Services). If such a regime exists in your organization, then you should not select this option.

The next screen of the wizard, illustrated in Figure 2-12, will attempt to scan for SQL Server updates to ensure that you install the latest CUs (cumulative updates) and SPs (service packs) with your installation. It will check the Microsoft Update service on the local server for these updates and list any that are available. This is an extension of slipstream installation functionality, which allows you to install updates at the same time as the installation of the base binaries by specifying their location for setup, but it has now been deprecated. The Product Updates page can also be configured to look for updates in local folders or network locations. This functionality will be discussed in further detail in Chapter 3.

As setup moves to the next page of the wizard, which is illustrated in Figure 2-13, the extraction and installation of the files required for SQL Server setup begins, and the progress displays. This screen also displays the progress of the download and extraction of any update packages that were found by Product Updates.

As illustrated in Figure 2-14, the next screen of the wizard runs an installation rule check and displays and errors or warnings that you may need to address before installation begins.

In Figure 2-14, notice the warning being displayed for Windows Firewall. This will not stop the installation from proceeding, but it does warn you that the server has Windows Firewall switched on. By default, Windows Firewall is not configured to allow SQL Server traffic, so rules must be created in order for client applications to be able to communicate with the instance that you are installing. We will discuss SQL Server ports and Firewall configuration in detail in Chapter 4.

Assuming no errors are discovered that need to be addressed before you continue, the next page of the wizard will ask you to decide if you want to perform a SQL Server Feature Installation, a SQL Server PowerPoint for SharePoint installation, or All Features With Default Values for service accounts. This screen is illustrated in Figure 2-15.

The Feature Selection Page

If you select the SQL Server Feature Installation option, when you move to the feature selection page of the wizard, none of the feature options will be selected, and you can select each option you require. If you choose the All Features With Defaults option, and then all features on the feature selection page, the reverse will be true. Every available option will be checked and you will need to uncheck any options that you do not require. Also, as you move through the setup wizard, you will find that all options have been prepopulated with default values. However, this includes options such as adding the current user as an instance administrator, which you may not want to do in all scenarios, so be extremely careful when you use this approach.

The SQL Server PowerPivot For SharePoint option can be used to install PowerPivot within a SharePoint farm. A full discussion of PowerPivot administration is beyond the scope of this book.

Choosing the SQL Server Feature Installation option will cause the Feature Installation page of the setup wizard to display. This will allow you to select the options that you wish to install. An overview of each of the available options can be found in Chapter 1. The Feature Selection page is illustrated in Figure 2-16.

We will select the following features, since they will be used for demonstrations and discussions throughout this book.

• Database Engine Services
  • SQL Server Replication
• Client Tools Connectivity
• Management Tools - Basic
  • Management Tools - Complete
• Distributed Replay Controller
• Distributed Replay Client

Additionally, this page of the wizard requires you to specify folder locations for the instance root directory and the shared features directory. You may want to move these to a different drive in order to leave the C: drive for the operating system. You may want to do this for space reasons, or just to isolate the SQL Server binaries from other applications. The instance root directory will typically contain a folder for each instance that you create on the server, and there will be separate folders for the Database Engine, SSAS, and SSRS installations. A folder associated with the Database Engine will be called MSSQL12.[InstanceName], where instance name is either the name of your instance, or MSSQLSERVER for a default instance. The number 12 in this folder name relates to the version of SQL Server, which is 12 for SQL Server 2014. This folder will contain a subfolder called MSSQL, which in turn will contain folders that will be home to the files associated with your instance, including a folder called Binn, which will contain the application files, application extensions, and XML configurations associated with your instance; a folder called Backup, which will be the default location for backups of databases; and a folder called Data, which will be the default location of the system databases. The default folders for TempDB, user databases, and backups can be modified later in the installation process, and splitting these databases onto separate volumes is almost always good practice, as discussed in Chapter 1. Other folders will also be created here, including a folder called LOGS, which will be the default location for the files for both the Error Logs and the default Extended Event health trace.

If you are installing SQL Server in a 64-bit environment, you will be asked to enter folders for both 32- and 64-bit versions of the shared features directory. This is because some SQL Server components are always installed as 32-bit processes. The 32- and 64-bit components cannot share a directory, so for installation to continue, you must specify different folders for each of these options. The Shared Features directory becomes a root level directory for features that are shared by all instances of SQL Server, such as SDKs and management tools.

On the next page of the wizard, illustrated in Figure 2-17, an additional rules check will be carried out to ensure that the features that you have selected can be installed.

The rules that are checked will vary depending on the features that you have selected, but a common gotcha is that, if the .NET Framework 3.5 is missing, it may only be discovered at this point. It is sound advice to install the .NET Framework 3.5 before beginning the installation of SQL Server, since the Database Engine relies on its availability.

The Instance Configuration Page

After successful completion of the rules check, the following screen of the wizard will allow you to specify if you would like to install a default instance or a named instance, as illustrated in Figure 2-18. The box in the lower half of the screen will give you details of any other instances or shared features that are already installed on the server.

The difference between a default instance and a named instance is that a default instance takes the name of the server that it is installed on, whereas a named instance is given an extended name. This has the obvious side effect that it is only possible to have a single default instance of SQL Server on a server, but you can have multiple named instances. With SQL Server 2014, up to 50 stand-alone instances can be hosted on a single server. For failover clusters, this number stays the same if your data is hosted on an SMB file share, but it reduces to 25 if you use a shared cluster disk for storage.

You are not required to install a default instance before installing a named instance. It is a perfectly valid configuration to have only named instances on a server with no default instance. Many DBA teams choose to only support named instances in their environments so that they can enforce naming conventions that are meaningful at the SQL Server layer, as opposed to relying on the naming conventions imposed by the infrastructure teams who build the servers or VMs. The maximum length of an instance name is 16 characters. By default, the InstanceID will be set to the instance name, or MSSQLSERVER for a default instance. Although it is possible to change this ID, it is bad practice to do so, because this ID is used to identify Registry keys and installation directories.

Selecting Service Accounts

The next screen of the wizard is separated into two tabs. The first tab will allow you to specify service accounts for each of the SQL Server services, as illustrated in Figure 2-19, and the second tab will allow you to specify the collation of your instance.

SQL Server 2014 supports the use of local and domain accounts, built-in accounts, virtual accounts, and MSAs (managed service accounts) as the security context used to run a service. The service account model that you choose is key to both the security and manageability of your environment.

Different organizations have different requirements for service account models, and you may be constrained by compliance requirements and many other factors. Essentially, the choice that you make is a trade-off between the security and operational supportability of your environment. For example, the Microsoft best practice is to use a separate service account for every service and to ensure that every server in your environment uses a discrete set of service accounts, since this fully enforces the principle of least privilege. The principle of least privilege states that each security context will be granted the minimum set of permissions required for it to carry out its day-to-day activities.

In reality, however, you will find that this approach introduces significant complexity into your SQL Server estate, and it can increase the cost of operational support, while also risking increasing outage windows in disaster scenarios. On the flip side, I have worked in organizations where the service account model is very coarse, to the point where there is only a single set of SQL Server service accounts for each region. This approach can also cause significant issues. Imagine that you have a large estate and the whole estate uses the same service account. Now imagine that you have a compliance requirement to change service account passwords on a 90-day basis. This means that you would cause an outage to your entire SQL Server estate at the same time. This simply is not practical.

There is no right or wrong answer to this problem, and the solution will depend on the requirements and constraints of individual organizations. For organizations that use domain accounts as service accounts, however, I tend to recommend a distinct set of service accounts for each data-tier application. So if you imagine an environment, as shown in Figure 2-20, where your data-tier application consists of a two-node cluster and an ETL server in a primary site, and two DR servers in a secondary site, this design would involve a common set of service accounts used by all of these instances, but other data-tier applications would not be allowed to use these accounts and would require their own set.

Of course, this model poses its own challenges. For example, you would need to review and amend this policy if you were to start a process of consolidation. Because of the challenges surrounding service account management, Microsoft introduced virtual accounts and MSAs. Virtual accounts are local accounts that have no password management requirements. They can access the domain by using the computer identity of the server on which they have been created. Managed service accounts, on the other hand, are domain-level accounts. They provide automatic password management within AD (Active Directory) and also automatically maintain their Kerberos SPNs (service principal names), as long as your domain is running at the functional level of Windows Server 2008 R2 or higher.

Both of these types of account have a limitation, however. They can only be used on a single server. As discussed earlier, this can introduce complexity into your SQL Server estate, especially for highly available, multiserver applications. This issue has been resolved by the introduction of group MSAs, which give you the ability to associate an MSA with multiple servers within the domain. In order to use this functionality, however, your forest needs to be running at the functional level of Windows Server 2012 or higher.

Choosing the Collation

The second tab of the Server Configuration page will allow you to customize your collation, as illustrated in Figure 2-21.

Collations determine how SQL Server will sort data and also define SQL Server’s matching behavior, with regard to accents, kana, width, and case. You can also specify that sorting and matching should be carried out on the binary or binary code point representations.

If your collation is accent sensitive, then in comparisons, SQL Server does not regard è as the same character as e, whereas it will treat these characters as equal, if accent insensitivity is specified. Kana sensitivity defines if the Japanese Hiragana character set is equal to the Katakana character set. Width sensitivity defines if a single byte representation of a character is equal to its two-byte equivalent.

Case sensitivity defines if a capital letter is equal to its lowercase equivalent during comparison. For example, the code in Listing 2-1 will create and populate a temporary table and then run the same query, but using two different collations.

--Create a local temporary table

CREATE TABLE #CaseExample
(
        Name        VARCHAR(20)
)

--Populate values

INSERT INTO #CaseExample
        VALUES(’James’), (’james’), (’John’), (’john’)

--Count the number of entries for James, with case sensitive collation

SELECT COUNT(*) AS ’Case Sensitive’
FROM #CaseExample
WHERE Name = ’John’ COLLATE Latin1_General_CS_AI

--Count the number of entries for James, with case insensitive collation

SELECT COUNT(*) AS ’Case Insensitive’
FROM #CaseExample
WHERE Name = ’John’ COLLATE Latin1_General_CI_AI

--DROP temporary table

DROP TABLE #CaseExample

You can see from the results in Figure 2-22, that the first query only found one example of the word John, because it used a case-sensitive collation, but because the second query uses a case-insensitive collation, it matched two results.

Although the effects of the various collation sensitivities may be fairly straightforward, a slightly more confusing aspect is how collations can affect sort order. Surely there is only one correct way to order data? Well, the answer to this question is no. There are various ways that data can be correctly ordered. For example, while some collations order data alphabetically, other collations may use nonalphabetic writing systems, such as Chinese, which can be ordered using a method called radical and stroke sorting. This system will identify common character components and then order them by the number of strokes. An example of how collations can affect sort order is demonstrated in Listing 2-2.

--Create a temporary table

CREATE TABLE #SortOrderExample
(
        Food        VARCHAR(20)
)

--Populate the table

INSERT INTO #SortOrderExample
VALUES (’Coke’), (’Chips’), (’Crisps’), (’Cake’)

--Select food using Latin1_General collation

SELECT Food AS ’Latin1_General collation’
FROM #SortOrderExample
ORDER BY Food
COLLATE Latin1_General_CI_AI

--Select food using Traditional_Spanish collation

SELECT Food AS ’Traditional_Spanish colation’
FROM #SortOrderExample
ORDER BY Food
COLLATE Traditional_Spanish_CI_AI

The results in Figure 2-23 show that the value Chips has been sorted differently using the two collations. This is because in traditional Spanish, ch is regarded as a separate character, and is sorted after cz.

There are two types of binary collation to choose from. The older style binary collations are included for backward compatibility only and are identified with the BIN suffix. If you choose to choose this type of binary collation, then characters will be matched and sorted based on the bit patterns of each character. If you choose the modern binary collations, which can be identified with a BIN2 suffix, then data will be sorted and matched based on Unicode code points for Unicode data and the code point of the relevant ANSI code page, for non-Unicode data. The example in Listing 2-3 demonstrates the behavior of a binary (BIN2) collation, compared to case-sensitive and case-insensitive collations.

CREATE TABLE #CaseExample
(
        Name        VARCHAR(20)
)

--Populate values

INSERT INTO #CaseExample
        VALUES(’James’), (’james’), (’John’), (’john’)

--Select all rows with a case sensitive collation

SELECT name as [Case Sensitive]
FROM #CaseExample
Order by Name COLLATE Latin1_General_CS_AI

--Select all rows, with a case insensitive collation

SELECT name as [Case Insensitive]
FROM #CaseExample
Order by Name COLLATE  Latin1_General_CI_AI

SELECT name as [binary]
FROM #CaseExample
Order by Name COLLATE  Latin1_General_BIN2

--DROP temporary table

DROP TABLE #CaseExample

The results in Figure 2-24 show that because the data is ordered by code point rather than alphabetically, the values beginning with capital letters are ordered before those beginning with lowercase letters, since this matches the code points of the characters.

Collations can be challenging, and ideally you will maintain consistent collations across the enterprise. This is not always possible in today’s global organizations, but you should aspire to it. You should also be careful to select the correct collation for the instance at the point of installation. Changing the collation afterward can be challenging, because databases and columns within tables have their own collations, and a collation cannot be changed if other objects depend on it. At a high level, a worst-case scenario will involve the following actions to change your collation at a later date:

1. Re-create all databases.
2. Export all data into the newly created copies of the databases.
3. Drop the original databases.
4. Rebuild the Master database with the desired collation.
5. Re-create the databases.
6. Import the data back into you database from the copies that you created.
7. Drop the copies of the databases.

Unless you have a specific backward compatibility requirement, you should avoid using SQL collations and only use Windows collations. It is best practice to use Windows collations because SQL collations are deprecated and are not all fully compatible with Windows collations. Additionally, you should be mindful when selecting newer collations, such as Norwegian or Bosnian_Latin. Although this new family of collations map to code pages in Windows Server 2008 or above, they do not map to code pages in older operating systems. So if you were to run a SELECT * query, against your instance from an older operating system, such as Windows XP, the code page would not match, and an exception would be thrown.

Provisioning Instance Security

The next page of the setup wizard allows you to configure the Database Engine. It consists of three tabs. In the first tab, you can specify the authentication mode of the instance and instance administrators, as illustrated in Figure 2-25. The second tab allows you to specify the folder that will be used as the default data directory, as well as specific locations for user databases and TempDB. The third tab will allow you to configure FILESTREAM.

Windows Authentication Mode means that the credentials that a user supplies when logging into Windows will be passed to SQL Server, and the user does not require any additional credentials to gain access to the instance. With Mixed Mode, although Windows credentials can still be used to access the instance, users can also be given second tier credentials. If this option is selected, then SQL Server will hold its own user names and passwords for users inside the instance, and users can supply these, in order to gain access, even if their Windows identity does not have permissions.

For security best practice, it is a good idea to only allow Windows authentication to your instance. This is for two reasons. First, with Windows authentication only, if an attacker were to gain access to your network, then they would still not be able to access SQL Server, since they would not have a valid Windows account with the correct permissions. With mixed-mode authentication however, once inside the network, attackers could use brute force attacks or other hacking methodologies to attempt to gain access via a second tier user account. Second, if you specify mixed-mode authentication, then you are required to create an SA account. The SA account is a SQL Server user account that has administrative privileges over the instance. If the password for this account became compromised, then an attacker could gain administrative control over SQL Server.

Mixed-mode authentication is a necessity in some cases, however. For example, you may have a legacy application that does not support Windows authentication, or a third party application that has a hard-coded connection that uses second tier authentication. These would be two valid reasons why mixed mode authentication may be required. Another valid reason would be if you have users that need to access the instance from a non-trusted domain.

Configuring the Instance

On the Server Configuration tab, you will also need to enter at least one instance administrator. You can use the Add Current User button to add your current Windows security context, or the Add button to search for Windows security principles, such as users or groups. Ideally, you should select a Windows group, which contains all DBAs that will require administrative access to the instance, since this simplifies security.

The Data Directories tab of Database Engine Configuration page is illustrated in Figure 2-26.

The Data Directories tab allows you to alter the default location of the data root directory. On this screen, you can also change the default location for user databases and their log files, as well as specify where TempDB data and log files should be created. As you may recall from Chapter 1, this is particularly important, because you will probably wish to separate user data files from their logs and also from TempDB. Finally, this tab allows you to specify a default location for backups of databases that will be taken.

The FILESTREAM tab of the Database Engine Configuration page allows you to enable and configure the level of access for SQL Server FILESTREAM functionality, as illustrated in Figure 2-27. FILESTREAM must also be enabled if you wish to use the FileTable feature of SQL Server. FILESTREAM and FileTable provide the ability to store data in an unstructured manner within the Windows folder structure, while retaining the ability to manage and interrogate this data from SQL Server.

Selecting Enable FILESTREAM For Transact-SQL Access will enable FILESTREAM, but the data can only be accessed from inside SQL Server. Additionally, selecting Enable FILESTREAM For File I/O Access enables applications to access the data directly from the operating system, bypassing SQL Server. If this option is selected, then you will also need to provide the name of a preexisting file share, which will be used for direct application access. The Allow Remote Clients Access To FILESTREAM Data option makes the data available to remote applications. The three options build on top of each other, so it is not possible to select Enable FILESTREAM For File I/O Access without fist selecting Enable FILESTREAM For Transact-SQL Access, for example. FILESTREAM and FileTable will be discussed further in Chapter 5.

Configuring Distributed Replay

As illustrated in Figure 2-28, the next page of the wizard will prompt you to specify the users who will be given access to the Distributed Replay Controller service. In the same fashion that you grant administrative permissions to the instance, you can use the Add Current User button to add your current security context or you can use the Add button to browse for Windows users and groups.

On the next page of the wizard, you can configure the Distributed Replay client, as illustrated in Figure 2-29. The Working Directory is the folder on the client where the dispatch files are saved. The Results Directory is the folder on the client where the trace file will be saved. The files in both of these locations will be overwritten each time a trace is run. If you have an existing Distributed Replay Controller configured, then you should enter its name in the Controller Name field. However, if you are configuring a new controller, then this field should be left blank and then amended later in the DReplyClient.config configuration file. The configuration and use of Distributed Replay will be discussed in Chapter 20.

Completing the Installation

The Ready to Install page of the wizard is the final page before installation commences, and it is illustrated in Figure 2-30. This screen gives you a summary of the features that will be installed, but possibly the most interesting component of this page is the Configuration File Path section. This gives you the path to a configuration file that you are able to reuse to install further instances with an identical configuration. Configuration files will be discussed further, in Chapter 2.

The setup wizard will display a progress bar during the installation. When installation is complete, a summary screen will be displayed, as shown in Figure 2-31. You should check to ensure that each of the components being installed has a status of Succeeded. The SQL Server installation is then complete.

Summary

SQL Server’s Installation Center provides many useful tools and links for guiding and assisting you in the installation process. You can use the Installation Center to install failover clustered instances as well as stand-alone instances of SQL Server. There are also tools to assist in advanced installation requirements, such as preprepared images of SQL Server and installations based on configuration files.

In addition to using the SQL Server 2014 Setup wizard to install an instance of the Database Engine, you can also use the same tool to install the tools within the BI and ETL suite, such as Analysis Services, Integration Service, Reporting Service, Data Quality Services, and Master Data Services. If you use the wizard to install Analysis Services, then the tool can be configured with the multidimensional model, the tabular model, or installed to SharePoint in order to support PowerPivot and Power View.

Although you can install SQL Server successfully using default values, for the ongoing supportability of your instance, and indeed your estate, make sure you consider many aspects of the installation. This applies especially to collations, service accounts, and other security considerations, such as the most appropriate administrators group to add and the authentication model to implement.