SQL Server 2019 | Windows Server 2019 | Practonet

SQL Server 2019

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

Now, for the first time ever, SQL Server 2019 creates a unified data platform with Apache SparkTM and Hadoop Distributed File System (HDFS) packaged together with SQL Server as a single, integrated solution. Through the ability to create big data clusters, SQL Server 2019 delivers an incredible expansion of database management capabilities, further redefining SQL Server beyond a traditional relational database. And as with every release, SQL Server 2019 continues to push the boundaries of security, availability, and performance for every workload with Intelligent Query Processing, data compliance tools and support for persistent memory. With SQL Server 2019, you can take on any data project, from traditional SQL Server workloads like OLTP, Data Warehousing and BI, to AI and advanced analytics over big data.

SQL Server provides a true hybrid platform, with a consistent SQL Server surface area from your data center to public cloud—making it easy to run in the location of your choice. Because SQL Server 2019 big data clusters are deployed as containers on Kubernetes with a built-in management service, customers can get a consistent management and deployment experience on a variety of supported platforms on-premises and in the cloud: OpenShift or Kubernetes on premises, Azure Kubernetes Service (AKS), Azure Stack (on AKS) and OpenShift on Azure. With Azure Hybrid Benefit license portability, you can choose to run SQL Server workloads on-premises or in Azure, at a fraction of the cost of any other cloud provider.

[SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, manage, administer, and develop all components of SQL Server, Azure SQL Database, and Azure Synapse Analytics.]

Install SQL 2019
Connect to Database Engine
Connect from Remote Hosts
SQL Server Services
SQL Server System Databases
Firewall Settings for SQL Server
AlwaysOn Availability Groups #1
AlwaysOn Availability Groups #2

Install SQL 2019

Install SQL Server 2019 with stand-alone installation.
Download SQL Server 2019 from here.

Step 1: Navigate to the directory/folder where you downloaded the application then double-click it to start the installation process.

Step 2: After you have chosen the custom installation, click on "Install". Allow the application to download and install packages.
You have the “Basic“, “Custom” and “Download Medium” installation types. I recommend you choose “Custom” to tweak your installation of SQL Server 2019 Developer Edition on Windows Server 2019.



Step 3: Specify a directory where SQL Server will store files downloaded for installation and click on Install to move further. It downloads the media and starts the installation.



Step 4: Be patient as the download begins, it may take some time. Once downloading is completed it will start installing automatically.
You may asked to install SSMS which you can install and may need ro restart PC.



Step 5: After all of the packages have downloaded, a new page as illustrated below will follow. Click on “Installation" and and click New SQL Server stand-alone installation *** on the top of right pane.



Step 6: Select an Edition of SQL Server you'd like to install and click Next.



Step 7: Read License terms well and check a box I accept the license terms to agree with it and click Next.



Step 8: Select to check important update or not and click Next.



Step 9: Setup rules indetify potential ploblems. Check the result and improve if ploblems are detected. For the warning of Windows firewall, it's possbile to configure later for SQL Server, so it's OK to proceed.



Step 10: This is the section of selecting features you'd like to install.

On this example, it selects mostly all features except Machine Learning and PolyBase Query Service for External Data. For Machine Learning and PolyBase Query Service for External Data, those are installed with Python, Java, Microsoft R for dependency.



Step 11: This is the section of Instance Configuration. Select Default Instance or Named Instance and click Next. (Keep default on this example)



Step 12: This is the section of Service Accounts Configuration. Change them for your requirements and click Next. (Keep default on this example)



Step 13: This is the section of Database Configuration. Select Authentication Mode and specify SQL Server administration account. If that's OK, move to Data Directories tab.



Step 14: This is the section of Data Directories Settings. Change them for your requirements. (Keep default on this example) If that's OK all, move to TempDB tab.



Step 15: This is the section of TempDB Settings. Change them for your requirements. (Keep default on this example) If that's OK all, move to MaxDOP tab.



Step 16: This is the section of MaxDOP Settings. Change them for your requirements. (Keep default on this example) If that's OK all, move to Memory tab.



Step 17: This is the section of Memory Settings. Change them for your requirements. (Keep default on this example) If that's OK all, move to FILESTREAM tab.



Step 18: This is the section of FILESTREAM Settings. Change them for your requirements. (Keep default on this example) If that's OK all, Proceed to next.



Step 19: This is the section of Analysys Services Configurations. Specify Administration Account of Analysys Services.



Step 20: This is the section of Integration Services Scale out (Master Node) Configurations. Change them for your requirements. (Keep default on this example) If that's OK all, Proceed to next.



Step 21: This is the section of Integration Services Scale out (Worker Node) Configurations. Change them for your requirements. (Keep default on this example) If that's OK all, Proceed to next.



Step 22: This is the section of Distributed Relay Controller Configurations. Change them for your requirements. (Keep default on this example) If that's OK all, Proceed to next.



Step 23: This is the section of Distributed Relay Client Configurations. Change them for your requirements. (Keep default on this example) If that's OK all, Proceed to next.



Step 24: Verify selection of features and it it's OK, Click Install button.



Step 25: Installation Starts. After finishing Installation, Click Close button to complete.

Install SQL 2019
Connect to Database Engine
Connect from Remote Hosts
SQL Server Services
SQL Server System Databases
Firewall Settings for SQL Server
AlwaysOn Availability Groups #1
AlwaysOn Availability Groups #2

Connect to Database Engine

Step 1: If you'd like to connect on localhost with GUI operation, it needs to install SQL Server Management Studio (SSMS).

Download and Install SSMS from the official site below.
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?redirectedfrom=MSDN&view=sql-server-ver15

After installing SSMS, run it.



Step 2: Click Connect button. It's OK to keep default for all selection if localhost connection.



Step 3: Just connected. It's possible to manage and configure SQL Server on here.

Install SQL 2019
Connect to Database Engine
Connect from Remote Hosts
SQL Server Services
SQL Server System Databases
Firewall Settings for SQL Server
AlwaysOn Availability Groups #1
AlwaysOn Availability Groups #2

Connect from Remote Hosts

Step 1: On GUI change operation, first Install and use SSMS on SQL Server Host. and if Windows Firewall is running, allow [1433/TCP].

Change authentication mode. Run SSMS and connect to database, then right-click the instance name and open Properties.



Step 2: Select Security on the left pane and check a box SQL Server and Windows Authentication mode on the right pane.



Step 3: Right-click instance name and run Restart.



Step 4: Next, enable SQL Server admin user.
Open Security -> Logins on the left pane and right-click sa to open Properties.



Step 5: Select General on the left pane and set SQL Server admin password.



Step 6: Select Status on the left pane and enable Login.



Step 7: For remote Host on Windows 10 setting, Download and Install SQL Server Management Studio (SSMS).
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?redirectedfrom=MSDN&view=sql-server-ver15
After installing SSMS, run it and select SQL Server Authentication and also specify SA for login user to login with remote connection.


Step 8: You are just loged in from remote host.

Install SQL 2019
Connect to Database Engine
Connect from Remote Hosts
SQL Server Services
SQL Server System Databases
Firewall Settings for SQL Server
AlwaysOn Availability Groups #1
AlwaysOn Availability Groups #2

SQL Server Services

SQL Server related Services are like follows. (Instance name or Account name are all default one)



Service Name Service Account (Default) Description
SQL Full-text Filter Daemon Launcher FTPNT Service\MSSQLFDLauncher Provides Full-text search
SQL Server NT Service\MSSQLSERVER SQL Server Relational Database Engine Service
SQL Server Analysis Services NT Service\MSSQLServerOLAPService Supplies online analytical processing (OLAP) for business intelligence applications
SQL Server Analysis Services CEIP NT Service\SSASTELEMETRY CEIP (Customer Experience Improvement Program) for Analysis Services
SQL Server Browser NT AUTHORITY\LocalService Provides SQL Server connection information to client computers
SQL Server CEIP service NT Service\SQLTELEMETRY CEIP for Database Engine Service
SQL Server Distributed Replay Client NT Service\SQL Server Distributed Replay Client Provides Distributed Replay Client feature
SQL Server Distributed Replay Controller NT Service\SQL Server Distributed Replay Controller Provides Distributed Replay Controller feature
SQL Server Integration Services 15.0 NT Service\MsDtsServer150 Provides management support Integration Services package storage and execution
SQL Server Integration Services CEIP NT Service\SSISTELEMETRY150 CEIP for Integration Services
SQL Server Integration Services Scale Out Master 15.0 NT Service\SSISScaleOutMaster150 Manage Scale Out System by SSIS DB catalog
SQL Server Integration Services Scale Out Worker 15.0 NT Service\SSISScaleOutWorker150 Execute local package to pull tasks from Scale Out Master
SQL Server VSS Writer NT AUTHORITY\System Provides the interface to backup and restore SQL Server through the Windows VSS
SQL Server Agent NT Service\SQLSERVERAGENT Execute jobs, monitoring SQL Server and so on

Install SQL 2019
Connect to Database Engine
Connect from Remote Hosts
SQL Server Services
SQL Server System Databases
Firewall Settings for SQL Server
AlwaysOn Availability Groups #1
AlwaysOn Availability Groups #2

SQL Server System Databases

There are two type of Databases on SQL Server, one is System Database, other is User Database.
User Databases are the common one users can use.
System Databases are created automatically by System and there are 5 System Databases like follows. (one is Hidden database)



System Database Description
master Records all the system-level information for a SQL Server system
msdb Used by SQL Server Agent for scheduling alerts and jobs and by other features
model Used as the template for all databases created on an instance of SQL Server
tempdb Global resource that is available to all users connected to the instance of SQL Server or connected to SQL Database
resource Read-only database that contains all the system objects that are included with SQL Server

Install SQL 2019
Connect to Database Engine
Connect from Remote Hosts
SQL Server Services
SQL Server System Databases
Firewall Settings for SQL Server
AlwaysOn Availability Groups #1
AlwaysOn Availability Groups #2

Firewall Settings for SQL Server

It needs to configure Firewall if it is running and also if you'd like to connect to SQL Server from remote Hosts.

Service Protocol Port Description
Default Instance TCP 1433 Default Port for Default Instance
Default Instance (only for Admin) TCP 1434 Admin Port for Default Instance
SQL Server Browser Service UDP 1434 Used by SQL Server Browser if Named Instance is used
HTTP Connection TCP 80 HTTP Connection Port (Database Engine, Analysis Services, Reporting Services)
HTTPS Connection TCP 443 HTTPS Connection Port (Database Engine, Analysis Services, Reporting Services)
Service Broker TCP 4022 Not the default port but used in Books Online examples
Database Mirroring TCP 7022 Not the default port but used for Database Mirroring by Default Instance
Transact-SQL Debugger TCP 135 Transact-SQL Debug Port
Analysis Services TCP 2383 Default Port used by Default Instance
SQL Server Browser Service TCP 2382 Used by Named Instance for Analysis Services
Integration Services TCP 135 Integration Services use DCOM on Port 135

Install SQL 2019
Connect to Database Engine
Connect from Remote Hosts
SQL Server Services
SQL Server System Databases
Firewall Settings for SQL Server
AlwaysOn Availability Groups #1
AlwaysOn Availability Groups #2

AlwaysOn Availability Groups #1

Configure AlwaysOn Availability Groups.
This example is based on the environment with 2 SQL Servers like follows. (possible to configure with more than 3 SQL Servers)



Step 1: Configure Windows Server Failover Clustering (WSFC) on all Nodes you plan to configure AlwaysOn Availability Groups, refer to here. .

Step 2: Create a domain user account on AD DS for SQL Server, refer to here.

Step 3: Install SQL Server with standalone mode on all Nodes you plan to configure AlwaysOn Availability Groups.

Step 4: Change settings like following all sections on all Nodes you plan to configure AlwaysOn Availability Groups.
Run SQL Server 2019 Configuration manager.



Step 5: Select SQL Server Services on the left pane and right click SQL Server (***) on the right pane to open Properties. .



Step 6: Change logon user to the AD user you created on step 2. Next, move to the Alwayson Availability Groups tab.



Step 7: Check a box Alwayson Availability Groups and apply settings. After changing, restart SQL Server service.



Step 8: Add the AD user account you created on step 2 to the SQL Server login. Run SQL Server Management Studio (SSMS) and add the user on Security -> Logins like follows.



Step 9: Create a folder for the database of AlwaysOn Availability Groups on the same Path on all Nodes and also add full control permission to the SQL Server logon user.
It creates C:\AlwaysonDB on this example like follows.



Step 10: Create a folder for the backup of database of AlwaysOn Availability Groups and also add full control permission to the SQL Server logon user.
It creates C:\AlwaysonDB_BK on this example like follows.



Step 11: Create a database of AlwaysOn Availability Groups with specifying the database folder you created on step 9.
It's OK to create it only on primary Node.
It creates AlwaysonDB database on this example like follows.



Step 12: Take a backup of the database for AlwaysOn Availability Groups.
Run SQL Server Management Studio (SSMS) and right click the target database to open the menu, then click Tasks -> Back Up.



Step 13: Click Remove button on lower-right tp remove default backup folder, next, click Add button to specify backup folder path you created on Step 10.



Step 14: Input any backup file name and Click OK.



Step 15: It's OK if taking backup has been finished successfully.
Next, Proceed to the settings of AlwaysOn Availability Groups #2.

Install SQL 2019
Connect to Database Engine
Connect from Remote Hosts
SQL Server Services
SQL Server System Databases
Firewall Settings for SQL Server
AlwaysOn Availability Groups #1
AlwaysOn Availability Groups #2

AlwaysOn Availability Groups #2

Configure AlwaysOn Availability Groups.
This example is based on the environment with 2 SQL Servers like follows. (possible to configure with more than 3 SQL Servers)



Step 1: Configure AlwaysOn Availability Groups settings #1 first.

Step 2: Logon with the SQL Server logon account and run SQL Server Management Studio (SSMS) to connect to the primary node. Next, right click Always On High Availability -> Availability Groups to open the menu, then click New Availability Group Wizard.



Step 3: Click Next button.



Step 4: Input any name you like on Availability group name and proceed to next.



Step 5: Select databases for Alwayson availability group and proceed next.



Step 6: Specify replicas. Click Add Replica button and specify replica Hosts. Proceed next.



Step 7: After adding all replicas, move to the Listener tab.



Step 8: Input any listener name you like on Listener DNS Name and also input a listening port on Port field.
Furthermore, set an IP address for the listener.



Step 9: Select data synchronization preference. It selects Automatic seeding on this example.



Step 10: The validation actions run before applying settings. That's OK if all are Success.



Step 11: Confirm summary of settings and if that's OK, click Finish button to apply settings.



Step 12: After finishing successfully, click Close button.



Step 13: It's possbile to see the status of Availability group on SSMS.



Step 14: Access to the listener to verify to connect to SQL Server.