Install SQL Server and Related Services

Question 1

What is an instance of SQL Server?

Answer 1

It is a separate installation on the same server. You can only have a single default instance at a time.

Question 2

What are the instance features that are strictly per instance installations?

Answer 2

SQL Server Replication
Machine Learning Services (In-Database)
     R
     Python
Full-Text and Semantic Extractions for Search
Data Quality Services
PolyBase Query Service for External Data
Analysis Services (OLAP - Online Analytical Processing Data that is read- CUBEs, Data Mining - SSAS)

Question 3

What are the instance features that are installed for all instances?

Answer 3

Machine Learning SErver (Standalone)
     R
     Python
Data Quality Client
Client Tools Connectivity
Integration Services (SSIS)
     Scale Out Master
     Scale Out Worker
Client Tools Backwards Compatibility
Client Tools SDK
Distributed Replay Controller
Distributed Replay Client
SQL Client Connectivity SDK
Master Data Services

Question 4

What is ETL?

Answer 4

Extract Transfer Load - For integrating other data sources to the database

Question 5

What is SSIS for?

Answer 5

SQL Server Integration Services - Graphical tool for importing and transforming data between data sources

Question 6

What are the different versions of SSAS?

Answer 6

Multidimensional and Data Mining Mode
Tabular Mode
PowerPivot Mode

Question 7

Multidimensional and Data Mining Mode

Answer 7

Pre-Aggregated Data or Atomic Data from disk (CUBE)

Question 8

Tabular Mode

Answer 8

Reads data directly from the cache and takes advantage of the query acceleration resulting from the column store indexes

Question 9

PowerPivot Mode

Answer 9

Provides server hosting of power pivot data in a sharepoint farm.

Question 10

What is SSDT?

Answer 10

SQL Server Data Tools. Provides Visual Studio integration

Question 11

What’s the file extension for testing connectivity for a SQL Server?

Answer 11

UDL

Question 12

Once you have a UDL file, how to you test connection?

Answer 12

Double click the file which launches the Data Link Properties app. Select the server, the security and database then click Test Connection.

Question 13

How should you partition disk volumes hosting SQL databases (Data and Log)?

Answer 13

64 KB Allocation Unit Size (Done BEFORE any data goes on the disk)

Question 14

What should the OS Power Saving setting be?

Answer 14

High Performance