Video Tutorial

Installation of SQL Servers

further Links:

Installation

Download SQL Server 2017. Select an Installation Type
Download install Package
SQL Server Installation Center
SQL Server Product Key
Global Rules Identify problems that might occur
Microsoft Update

Features:

  • Data Engine Services: This is the core database engine or database itself. This must be selected
  • SQL Server Replication: The SQL Server Replication includes a set of technologies for creating an exact replica of the database from one database to another. It also synchronizes the database for consistency.
  • Machine Learning Services (In-Database) ( R & Python): Includes extensions that enable integration with R and Python languages using standard T-SQL statements.
  • Full Text and semantic Extractions for search: Includes a Search engine that supports Full-Text Extraction for fast text search as well as Semantic Extraction for keyphrases (likely tags) and similarity search on content stored in SQL Server.
  • Data Quality Services: Includes Data quality database objects.
  • Polybase Query Service for external data: Includes PolyBase technology that enables truly integrated querying across Hadoop non-relational data and SQL Server relational data using standard T-SQL statements. (if you select to install PolyBase, the setup will ask for the Oracle JRE. You can obtain the JRE installer from https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html)
  • Analysis Services: Includes Analysis Services and tools used to support online analytical processing (OLAP) and data mining. Only Database Engine Services and Analysis Services can be clustered.

Shared Features

  • Machine Learning Server (Standalone) R & python: Includes Microsoft Machine Learning Server for Windows that provides parallelized big data analysis, transformation, modeling and operationalization capabilities.
  • Data Quality Client: Includes Data quality client objects.
  • Client Tools Connectivity: Includes components for communication between clients and servers.
  • Integration Services: Includes the designer, runtime, and utilities that enable Integration Services to move, integrate, and transform data between data stores.
  • Client Tools backward compatibility: Client Tools for Backwards Compatibility for older versions
  • Client Tools SDK: Includes the software development kit containing resources for programmers.
  • Distributed replay controller: Includes the Distributed Replay Controller which orchestrates the actions of the distributed replay clients.
  • Distributed replay client: Includes the Distributed Replay Client. Multiple Distributed Replay Clients work together to simulate a workload against an instance of SQL Server.
  • SQL Client Connectivity SDK: SQL Client Connectivity SDK will install SQL Server OLEDB and ODBC connectors that can be used to connect to SQL Server using.NET, Java, PHP or other programming languages. Includes SQL Server Native Client (ODBC / OLE DB) SDK for database application development.
  • Master Data Services: Includes Master Data Services, the platform for integrating data from disparate systems across an organization into a single source of master data for accuracy and auditing purposes. Installs the Master Data Services Configuration Manager, assemblies, PowerShell snap-in, and folders and files for Web applications and services.

Recommended Features

Feature selection
  • when you want to run the server as da database Server (no analytics)
    • Database Engine Services
    • Client Tools Connectivity
    • Client Tools for backward compatibility
    • SQL Client Connectivity SDK
    • Client Tools SDK
  • when you also want to run Analytics (SSAS) in addition
    • Analysis Service
    • Integration Services

Instances

You can install multiple Instances of the SQL Server running on a sever. Each of the Instances must have its own intance name.

  • only one instance:
SQL Server Instance Configuration
  • multiple instances:
SQL Server Instance Configuration Named Instance

Server Configuration

The Next window list the various services, which you can configure. This list of services displayed here depends on the Features selected. Here, you need to specify the Account Name, Password and startup type for each service.

On a Development System you can leave the Settings as predfined. On a Productive Enviroment I would recommend to run the Services with AD-Service-Users. eg SQL Server Agent – svcSQLAgent …

Server Configuration

Database Engine Configruation

Server Configuration

Database Engine Configuration Server

Here you are asked to configure the database by providing the authentication mode & password for the SA user.
Under Authentication Mode, you have two options:

  • Windows Authentication: In this mode, the windows user is automatically recognized by SQL Server. The Windows security identifier (SID) is passed from Windows to SQL Server. The SQL Server does not store any user information and trusts the Windows user. The SA user gets disabled when you use this
  • Mixed Mode (Windows Authentication and SQL Server authentication Mode): In this mode, along with the Windows Users, the SQL Server also stores the user name & password in the database. In this scenario, you can connect to SQL Server either using the Windows account and using the user name/password stored in SQL Server itself

I would recommend to run the Server in MIXED MODE because you will have systems that want to access the SQL Server with SQL-User instead of AD User. But I recommend to use AD-User where possible.

Data Directory

Database Engine Configuration Data Directories

On a Development System you can leave the Settings as predfined, when you ensured that you have enough available Space on your harddisk. On a Productive Enviroment I would recommend to split the Direcotries over different pyhsical Harddisks (not partitions).

Installation of SSMS

Download-Link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

I would recommend to install SSMS in English (United States) because the localized versions are not always translated perfectlly.