Installing SQL Server
SQL Server Editions
They are various versions of SQL Server available. Before you proceed to the installation of the system, you need to develop an installation plan.
Express Edition The lightweight version of SQL Server, designed for use by application developers. For this reason, the product supports Common Language Runtime (CLR) integration and native XML. Also, you can download SQL Server Management Studio Express (SSMSE), to easily manage a database. SQL Server Express is available as a free download.
Standard Edition Designed for small and medium-sized businesses. It supports up to four processors as well as 2TB of RAM and includes the full range of BI functionality, including Analysis Services, Reporting Services, and Integration Services. This edition does not include many enterprise-based features from Enterprise Edition.
Enterprise Edition The special form of the SQL Server system that is intended for time-critical applications with a huge number of users. In contrast to Standard Edition, this edition contains additional features that can be useful for high-end installations with symmetrical multiprocessors or clusters. The most important additional features of Enterprise Edition are data partitioning, database snapshots, and online database maintenance.
Developer Edition Allows developers to build and test any type of application with SQL Server. It includes all the functionality of Enterprise Edition, but is licensed only for use in development, testing, and demonstration. Each license of Developer Edition entitles one developer to use the software on as many systems as necessary; additional developers can use the software by purchasing additional licenses. For rapid deployment into production, the database system of Developer Edition can easily be upgraded to Enterprise Edition.
Web Edition A low-cost option for the Web, which provides scalability and manageability capabilities for small to large web applications.
More information between is found the Microsoft Website
Management Components
The following management components, among others, belong to SQL Server 2016:
SQL Server Management Studio (SSMS) The primary tool for database administrators (DBAs) to interact with the system. Both administrators and end users can use this tool. Chapter 3 describes this component in detail.
SQL Server Configuration Manager The primary tool for managing the services associated with SQL Server, configuring the network protocols used by SQL Server, and managing the network connectivity configuration from SQL Server client computers. It is a Microsoft Management Console (MMC) snap-in that is available from the Start menu. This tool will be used and explained in Chapter 5.
SQL Server Profiler A graphical tool that enables system administrators to monitor and record database and server activities. The tool is described in detail in Chapter 20.
Database Engine Tuning Advisor (DTA) A graphical tool that enables DBAs to automate the physical design of their databases. The tool is described together with SQL Server Profiler in Chapter 20.
Data Quality Client An application that enables DBAs to create knowledge bases, create and run data quality projects, and perform administrative tasks. The detailed description of the tool is beyond the scope of this guide.
SQL Server Data Tools (SSDT) A management tool that provides one development platform for different business intelligence (BI) applications. Built on Visual Studio, SSDT is an integrated platform for system developers in the BI area.
Planning Phase
The planning phase is divided into two parts, general recommendations and the other part will go through the actual installation process.
General Recommendations
During the installation process, you have to make many choices. As a general guideline, it is best to familiarize yourself with their effects before installing your system. At the beginning, you should answer the following questions:
Which SQL Server components should be installed?
Where will the root directory be stored?
Should multiple instances of SQL Server be used?
Which authentication mode for SQL Server should be used?
Which SQL Server Components Should Be Installed?
Before you start the installation process, you should know exactly which SQL Server components you want to install.
There are two groups of features on the Feature Selection page: Instance Features and Shared Features.
- Instance features are the components that are installed once for each instance so that you have multiple copies of them (one for each instance)- Shared features are features that are common across all instances on a given machine. Each of these shared features is designed to be backward compatible with supported SQL Server versions that can be installed side by side.
Instance Features - Database Engine Service
The Database Engine is the relational database system of SQL Server.
SQL Server Replication, allows you to replicate data from one system to another.
R Services, this is an optional feature of the Database Engine that lets you execute scripts written in the R language. This feature lets you deploy R packages to SQL Server, execute R solutions in the context of SQL Server, and easily work with SQL Server data in R.
Full-Text and Semantic Extractions for Search, Full-Text Search is a component of SQL Server that allows you to store and query unstructured data
Data Quality Services, this feature have already been discussed previously.
PolyBase Query Service for External Data, PolyBase is a new component of SQL Server that builds a gateway from SQL to Hadoop.
Other Instance Features
Reporting Services, and Integration Services, which are components related to business intelligence (BI).
Analysis Services is a group of services that is used to manage and query data that is stored in a data warehouse.
Reporting Services allows you to create and manage reports.
Where Will the Root Directory Be Stored?
The root directory is where the Setup program stores all program files and those files that do not change as you use the SQL Server system. By default, the installation process stores all program files in the subdirectory Microsoft SQL Server, although you can change this setting during the installation process. Using the default name is recommended because it uniquely determines the version of the system.
Should Multiple Instances of SQL Server Be Used?
An instance is a database server that does not share its system and user databases with other instances (servers) running on the same computer.
There are two instance types:
Default
Named
The default instance operates the same way as the database server versions of SQL Server, where support for only one database server exists. The computer name on which the instance is running specifies solely the name of the default instance. Any instance of the database server other than the default instance is called a named instance.
You should consider using multiple instances if both of the following are true:
You have different types of databases on your computer.
Your computer is powerful enough to manage multiple instances.
A single-processor machine will not be the right hardware platform to run multiple instances of the Database Engine, because of limited resources. For this reason, you should consider the use of multiple instances only with multiprocessor computers.
Which Authentication Mode for SQL Server Should Be Used?
In relation to the Database Engine, there are two different authentication modes:
Windows mode Specifies security exclusively at the operating system level—that is, it specifies the way in which users connect to the Windows operating system using their user accounts and group memberships.
Mixed mode Allows users to connect to the Database Engine using Windows authentication or SQL Server authentication. This means that some user accounts can be set up to use the Windows security subsystem, while others can use the SQL Server security subsystem in addition to the Windows security subsystem.
Which is the recommended authentication mechanisms
Microsoft recommends the use of Windows mode.
Installing SQL Server - Step by Step
To start the SQL Server installation, mount the image of the SQL Server 2016 software. After that, click the “Setup” execution file to open SQL Server Installation Center.
From the Planning page, you can execute the following tasks, among others:
Hardware and Software Requirements
Security Documentation
Online Release Notes
System Configuration Checker
Download Upgrade Advisor
Hardware Requirements
Windows operating systems are supported on the Intel and AMD hardware platforms.
Processor speed should be 1.4 GHz at a minimum, but 2 GHz or faster is recommended.
Officially, the minimum requirement for main memory is 1MB. (Express Edition needs only 512KB.) However, almost everybody recognizes that such a minimal configuration will not perform very well, and as a general guideline, main memory of your computer should be at least 4GB.
The minimum hard disk space is 6GB.
Security Documentation
When you click Security Documentation, the system takes you to the Microsoft page that discusses general considerations concerning security. One of the most important security measures is to isolate services from each other. To isolate services, run separate SQL Server services under separate Windows accounts
Online Release Notes
There are two main sources to get information concerning all the features of the SQL Server system: Books Online and Online Release Notes.
System Configuration Checker
One of the most important planning tasks is to check whether all conditions are fulfilled for a successful installation of the database system. When you click System Configuration Checker, the component called Global Rules is automatically started. Setup Global Rules identifies problems that might occur when you install SQL Server support files. After finishing this task, the system shows you how many operations were checked and how many of them failed. All failures have to be corrected before the installation can continue.
Installing SQL Server
After starting the installation, the installation rules runs and verifies if SQL Server can be installed.
Choose Installation Type
Feature Selection
Instance Configuration
On the Instance Configuration page, you can choose between the installation of a default or named instance.
To install a new named instance, click Named Instance and type a new name in the given text box. In the lower part of the page, you can see the list of instances already installed on your system. (MSSQLSERVER is the name of the default instance for SQL Server.) Click Next to continue.
Server Configuration
The next page, Server Configuration, contains two tabs. The first one, Service Accounts, allows you to specify usernames and corresponding passwords for services of all components that will be installed during the installation process.
To choose the collation of your instance, click the second tab of the same page. You can either choose the default collations for the components that will be installed, or click Customize to select some other collations that are supported by the system. Click Next to continue.
Database Engine Configuration
The Database Engine Configuration page has several different tabs. The first tab, Server Configuration , allows you to choose the authentication mode for your Database Engine system.
The second tab of the Database Engine Configuration page, Data Directories, allows you to specify the locations for the directories in which Database Engine–related files are stored. In other words, the installation process lists default directories where the installation process will store different SQL Server components and which you can modify.
The third tab, TempDB, is a new one in SQL Server 2016 and relates to the tempdb system database. (This database provides storage space for temporary objects that are needed.) On this tab you can configure the number of data files assigned to tempdb. By default, the installation process sets the default to the number of cores on your machine. If your machine has more than eight cores, this initial number of tempdb data files will be set to 8.
The last tab, FILESTREAM, allows you to enable FILESTREAM. By default FILESTREAM is disabled. To enable it, check the Enable FILESTREAM for Transact-SQL Access box.
What appears for the next step depends on whether or not you chose to install Analysis Services and/or Reporting Services.
Completing the Installation
