Structured Query Language abbreviated as SQL is a standard programming language used for managing and manipulating relational databases. It provides a standardized way to interact with databases, perform various operations such as querying data, inserting, updating, and deleting records, creating and modifying database schemas, and managing access permissions.
The SQL Server, is a relational database management system (RDBMS) developed by Microsoft. This software implements the SQL language and provides a robust platform for storing, managing, and retrieving data in a relational database format. SQL Server offers many features and capabilities, including high performance, scalability, security, business intelligence, and integration with other Microsoft products.
SQL Server 2022 is the latest version of Microsoft’s relational database management system, released on November 8, 2021. It introduces several new features and enhancements that aim to improve performance, scalability, security, and developer productivity. Some of the key features and benefits of SQL Server 2022 include:
- Improved Performance and Scale: It offers enhanced performance and scalability, leveraging features such as Intelligent Query Processing, Accelerated Database Recovery, and In-Memory OLTP. These optimizations enable faster query execution, improved transaction throughput, and efficient use of system resources.
- Big Data Clusters: SQL Server 2022 introduces enhancements to Big Data Clusters, allowing you to manage and analyze large volumes of data from various sources, including structured, unstructured, and streaming data. It enables integration with Apache Spark and Hadoop Distributed File System (HDFS) for advanced analytics and data processing capabilities.
- Enhanced Security: This latest SQL server version brings new security features, including Always Encrypted with Secure Enclaves, which protects sensitive data even during computation, and Static Data Masking for data obfuscation in non-production environments. Additionally, SQL Server 2022 includes improvements in security assessments, threat detection, and data classification.
- Hybrid and Multi-Cloud Capabilities: It also offers enhanced support for hybrid and multi-cloud environments. It provides features like Distributed Availability Groups for disaster recovery across multiple regions and Azure Arc-enabled Data Services for deploying SQL Server instances in various cloud platforms.
- Developer Productivity: The SQL Server 2022 focuses on improving developer productivity by introducing features such as Intelligent Query Completion, Schema Comparison, and improved integration with Visual Studio and Azure Data Studio. It provides a more seamless development experience, making writing, testing, and debugging queries and database applications easier.
- Advanced Analytics and Machine Learning: It integrates with powerful machine learning and analytics tools, including integration with Azure Machine Learning and support for Python and R language extensions. This enables data scientists and analysts to perform advanced analytics and build machine learning models directly within the database.
- Intelligent Database and Query Tuning: It includes features like Automatic Index Management, Automatic Plan Correction, and Query Store enhancements that simplify database performance tuning and optimization. These capabilities help to identify and resolve performance issues automatically or with minimal manual intervention.
This guide will focus on how to install and configure SQL Server 2022 on Windows Server 2022.
Install SQL Server 2022 on Windows Server 2022
To install the SQL Server 2022 on Windows Server 2022, we need to download the required Edition from the SQL server downloads page. For this guide, we will use the Developer version.
This can also be downloaded from the PowerShell using the command:
wget https://download.microsoft.com/download/c/c/9/cc9c6797-383c-4b24-8920-dc057c1de9d3/SQL2022-SSEI-Dev.exeOnce downloaded, click on the EXE file and proceed as shown:
Here, there are 3 options:
- Basic Installation: This option installs SQL Server with the default settings and components. It provides a quick and straightforward installation experience, ideal for users who prefer the standard configuration.
- Custom Installation: With this option, you have the flexibility to choose specific components and settings during the installation. It allows you to customize the installation based on your requirements, enabling you to select the specific features and options you want to install.
- Download Media: This option enables you to download the SQL Server installer and save it for later use. You can initiate the installation at a more convenient time, ensuring you have the necessary files readily available without the need for an active internet connection during the installation process.
In this guide, we will proceed with the custom Installation option. Once selected, proceed and specify where you want the installation media to be stored.
The download will proceed as shown.
Once complete, the installation centre will be launched
Here, select the “New SQL Server stand-alone installation or add features to an existing installation” option. Proceed to the activation page. Here, select the edition you want.
Accept the License Terms
Accept the check update feature. This allows updates to b pulled automatically when available.
Go through the pre-requisite check before we install the SQL server 2022.
If you need Azure Cloud integration, you can install and configure this extension.
We configure the Windows firewall later. Proceed and select the features you’d like to install. Here, we will select everything apart from Machine Learning and PolyBase Query Service for External Data. Those features are installed with Python, Java, Microsoft R for dependency.
Under the instance name configuration, we will keep the defaults.
This is the Service Accounts configuration. You can change them to suit your requirements or keep defaults as in this example.
Set eh desired authentication mode and specify the SQL Server administration account. For this guide, we will use Mixed mode with both SQL server authentication and Windows Authentication.
If that’s OK, you can also move and modify the Data Directories, TempDB, MaxDOP, Memory and FILESTREAM. For this guide, we will keep the adults, so proceed to the next tab.
Here, set the Administration Account for the Analysis Service
In this section, you’ll find the configuration settings for Integration Services Scale-out (Master Node). You can modify these settings according to your specific requirements. However, for the purpose of this example, we will keep the default configurations unchanged.
Provide the endpoint of the master. You can keep the defaults:
You will then be granted the installation summary.
Now install the SQL server 2022. Once complete, you will see this:
Access SQL Server 2022 Locally
After the installation, you can access the SQL server 2022 locally without no additional packages required. To connect to the server, launch PowerShell and run the command:
sqlcmd -S localhost Verify the connection:
select @@version
goSample Output:
To access the SQL server with GUI, you need to install additional software, the SQL Server Management Studio (SSMS). Once downloaded, install it, then launch it as shown.
Connect to the database.
On a successful connection, you will see this:
Configure SQL Server 2022 for Remote Access
For the service to be accessed remotely, you need to make several configurations. These configurations can be made from both the GUI and PowerShell.
On GUI, enable remote connection with Windows Auth by launching SQL Server 2022 Configuration Manager from the start menu. Once launched, select Server Network Configuration then enable TCP/IP
Next, select SQL Server Services, then right-clicking the menu of SQL Server (instance name) and select Properties.
Move to the Log on tab and change the logon account. If the user is a local account you need to change to a domain account. Remember, the domain account needs to have admin privilege to the local Windows system.
For the changes to apply, restart the SQL server.
One last thing to be done is to allow the service through the firewall. Launch Windows Defender and allow the default port 1433 through it as shown.
Create a new rule:
Provide the port number and protocol
Allow connections on the port.
Proceed with the creation then set a name for the Rule. Once complete, proceed and test connections to the SQL server as shown below.
Access SQL Server 2022 Remotely
Once the above configs have been performed, you can access the SQL server remotely using both Windows and SQL server authentication.
Once the SQL Server Management Studio (SSMS) has been installed on the remote machine. Connect to the SQL server with Windows Auth or SQL server Auth as shown.
Once connected, you can now perform any activities on the database as desired.
Recommended books for Learning SQL database:
Verdict
We have learned how to install and configure SQL Server 2022 on Windows Server 2022. We have also learned how we can configure the SQL server for remote access. I hope this was of great importance to you.
Related posts:
