Skip to content

Latest commit

 

History

History
226 lines (149 loc) · 19.7 KB

configure-sql-server-on-a-server-core-installation.md

File metadata and controls

226 lines (149 loc) · 19.7 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Configure Server Core Installation
This article covers details about configuring SQL Server on a Server Core installation, including troubleshooting tools.
rwestMSFT
randolphwest
09/15/2021
sql
install
conceptual
IsHadrEnabled server property
Server Core Installation [SQL Server]
>=sql-server-2016

Configure SQL Server on a Server Core Installation

[!INCLUDE SQL Server -Windows Only]

This article covers details about configuring [!INCLUDEssNoVersion] on a Server Core installation.

Configure and Manage Server Core on Windows Server

The section provides references to the articles that help configure and manage a Server Core installation.

Not all features of [!INCLUDEssNoVersion] are supported in Server Core mode. Some of these features can be installed on a client computer or a different server that is not running Server Core, and connected to the Database Engine services installed on Server Core.

For more information about configuring and managing a Server Core installation remotely, see the following articles:

Install [!INCLUDEssNoVersion] Updates

This section provides information about installing updates for [!INCLUDEssNoVersion] on a Windows Server Core machine. We recommend that customers evaluate and install latest [!INCLUDEssNoVersion] updates in a timely manner to make sure that systems are up to date with the most recent security updates. For more information about installing [!INCLUDEssNoVersion] on a Windows Server Core machine, see Install SQL Server on Server Core.

The following are the two scenarios for installing product updates:

Installing Updates for [!INCLUDEssNoVersion] During a New Installation

[!INCLUDEssNoVersion] Setup supports only command prompt installations on Server Core operating system. For more information, see Install SQL Server from the Command Prompt.

[!INCLUDEssNoVersion] setup integrates the latest product updates with the main product installation so that the main product and its applicable updates are installed at the same time.

After Setup finds the latest versions of the applicable updates, it downloads and integrates them with the current [!INCLUDEssNoVersion] setup process. Product Update can pull in a cumulative update, service pack, or service pack plus cumulative update.

Specify the UpdateEnabled, and UpdateSource parameters to include the latest product updates with the main product installation. Refer the following example to enable product updates during the [!INCLUDEssNoVersion] Setup:

Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /UpdateEnabled=True /UpdateSource="<SourcePath>" /IACCEPTSQLSERVERLICENSETERMS

[!INCLUDE sql-eula-link]

Installing Updates for [!INCLUDEssNoVersion] After It Has Been Installed

On an installed instance of [!INCLUDEssNoVersion], we recommend that you apply the latest security updates and critical updates including General Distribution Releases (GDRs), and Service Packs (SPs). Individual Cumulative updates and security updates should be adopted on a case-by-case, "as-needed" basis. Evaluate the update; if it's needed, then apply it.

Apply an update at a command prompt, replacing <package_name> with the name of your update package:

  • Update a single instance of [!INCLUDEssNoVersion] and all shared components. You can specify the instance either by using the InstanceName parameter or the InstanceID parameter.

    <package_name>.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /InstanceName=MyInstance

  • Update [!INCLUDEssNoVersion] shared components only:

    <package_name>.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch

  • Update all instances of [!INCLUDEssNoVersion] on the computer and all shared components:

    <package_name>.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances

Start/Stop [!INCLUDEssNoVersion] Service

The sqlservr Application application starts, stops, pauses, and continues an instance of [!INCLUDEssNoVersion] from a command prompt.

You can also use Net services to start and stop the [!INCLUDEssNoVersion] services.

Enable Always On availability groups

Being enabled for Always On Availability Groups is a prerequisite for a server instance to use availability groups as a high availability and disaster recovery solution. For more information about managing the Always On availability groups, see Enable and Disable Always On Availability Groups (SQL Server).

Using [!INCLUDEssNoVersion] Configuration Manager Remotely

These steps are meant to be performed on a PC running the client edition of Windows, or Windows Server that has the Server Graphical Shell installed.

  1. Open Computer Management. To open Computer Management, select Start, type compmgmt.msc, and then select OK.

  2. In the console tree, right-click Computer Management, and then select Connect to another computer....

  3. In the Select Computer dialog box, type the name of the Server Core machine that you want to manage, or select Browse to find it, and then select OK.

  4. In the console tree, under Computer Management of the Server Core machine, select Services and Applications.

  5. Double-click [!INCLUDEssNoVersion] Configuration Manager.

  6. In [!INCLUDEssNoVersion] Configuration Manager, select [!INCLUDEssNoVersion] Services, right-click [!INCLUDEssNoVersion] (<instance name>), where <instance name> is the name of a local server instance for which you want to enable Always On Availability Groups, and select Properties.

  7. Select the Always On High Availability tab.

  8. Verify that Windows failover cluster name field contains the name of the local failover cluster node. If this field is blank, this server instance currently does not support Always On Availability Groups. Either the local computer is not a cluster node, the WSFC cluster has been shut down, or this edition of [!INCLUDEssNoVersion] does not support Always On Availability Groups.

  9. Select the Enable Always On Availability Groups check box, and select OK.

  10. [!INCLUDEssNoVersion] Configuration Manager saves your change. Then, you must manually restart the [!INCLUDEssNoVersion] service. This enables you to choose a restart time that is best for your business requirements. When the [!INCLUDEssNoVersion] service restarts, availability groups will be enabled, and the IsHadrEnabled server property will be set to 1.

Note

  • You must have the appropriate user rights or you must have been delegated the appropriate authority on the target computer to connect to that computer.
  • The name of the computer that you are managing appears in parentheses next to Computer Management in the console tree.

Using PowerShell Cmdlets to Enable Always On Availability Groups

The PowerShell Cmdlet Enable-SqlAlwaysOn is used to enable Always On Availability Group on an instance of [!INCLUDEssNoVersion]. If the Always On Availability Groups feature is enabled while the [!INCLUDEssNoVersion] service is running, the Database Engine service must be restarted for the change to complete. Unless you specify the -Force parameter, the cmdlet prompts you to ask whether you wish to restart the service; if canceled, no operation occurs.

You must have Administrator permissions to execute this cmdlet.

You can use one of the following syntaxes to enable Always On Availability Groups for an instance of [!INCLUDEssNoVersion]:

Enable-SqlAlwaysOn [-Path <string>] [-Credential <PSCredential>] [-Force] [-NoServiceRestart] [-Confirm] [-WhatIf] [<Commom Parameters>]  
Enable-SqlAlwaysOn -InputObject <Server> [-Credential <PSCredential>] [-Force] [-NoServiceRestart] [-Confirm] [-WhatIf] [<Commom Parameters>]  
Enable-SqlAlwaysOn [-ServerInstance <string>] [-Credential <PSCredential>] [-Force] [-NoServiceRestart] [-Confirm] [-WhatIf] [<Commom Parameters>]

The following PowerShell command enables Always On Availability Groups on an instance of [!INCLUDEssNoVersion] (Machine\Instance):

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\Machine\Instance

Configuring Remote Access of [!INCLUDEssNoVersion] Running on Server Core

Perform the actions described below to configure remote access of a [!INCLUDEssNoVersion] instance that is running on Windows Server Core.

Enable remote connections on the instance of [!INCLUDEssNoVersion]

To enable remote connections, use SQLCMD.exe locally and execute the following statements against the Server Core instance:

  • EXEC sys.sp_configure N'remote access', N'1'

    GO

  • RECONFIGURE WITH OVERRIDE

    GO

Enable and start the [!INCLUDEssNoVersion] Browser service

By default, the Browser service is disabled. If it is disabled on an instance of [!INCLUDEssNoVersion] running on Server Core, run the following command from the command prompt to enable it:

sc config SQLBROWSER start= auto

After it is enabled, run the following command from the command prompt to start the service:

net start SQLBROWSER

Create exceptions in Windows Firewall

To create exceptions for [!INCLUDEssNoVersion] access in Windows Firewall, follow the steps specified in Configure the Windows Firewall to Allow SQL Server Access.

Enable TCP/IP on the Instance of [!INCLUDEssNoVersion]

The TCP/IP protocol can be enabled through Windows PowerShell for an instance of [!INCLUDEssNoVersion] on Server Core. Follow these steps:

  1. On the computer that is running Windows Server Core, launch Task Manager.

  2. On the Applications tab, select New Task.

  3. In the Create New Task dialog box, type sqlps.exe in the Open field and then select OK. This opens the Microsoft [!INCLUDEssNoVersion] Powershell window.

  4. In the Microsoft [!INCLUDEssNoVersion] Powershell window, run the following script to enable the TCP/IP protocol:

$smo = 'Microsoft.SqlServer.Management.Smo.'  
$wmi = new-object ($smo + 'Wmi.ManagedComputer')  
# Enable the TCP protocol on the default instance.  If the instance is named, replace MSSQLSERVER with the instance name in the following line.  
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"  
$Tcp = $wmi.GetSmoObject($uri)  
$Tcp.IsEnabled = $true  
$Tcp.Alter()  
$Tcp

[!INCLUDEssNoVersion] Profiler

On a remote machine, start [!INCLUDEssSqlProfiler] and select New Trace from the File menu, the application displays a Connect to Server dialog box where you can specify the [!INCLUDEssNoVersion] instance, residing on the Server Core machine, to which you want to connect. For more information, see Start SQL Server Profiler.

For more information on the permissions required to run [!INCLUDEssSqlProfiler], see Permissions Required to Run SQL Server Profiler.

For additional details about [!INCLUDEssSqlProfiler], see SQL Server Profiler.

[!INCLUDEssNoVersion] Auditing

You can use [!INCLUDEssManStudioFull] or [!INCLUDEtsql] remotely to define an audit. After the audit is created and enabled, the target will receive entries. For more information about creating and managing [!INCLUDEssNoVersion] audits, see SQL Server Audit (Database Engine).

Command Prompt Utilities

You can use the following command prompt utilities that enable you to script [!INCLUDEssNoVersion] operations on a Server Core machine. The following table contains a list of command prompt utilities that ship with [!INCLUDEssNoVersion] for Server Core:

Utility Description Installed in
bcp Utility Used to copy data between an instance of [!INCLUDEmsCoName] [!INCLUDEssNoVersion] and a data file in a user-specified format. [!INCLUDEssInstallPathVar]Tools\Binn
dtexec Utility Used to configure and execute an [!INCLUDEssISnoversion] package. [!INCLUDEssInstallPathVar]DTS\Binn
dtutil Utility Used to manage SSIS packages. [!INCLUDEssInstallPathVar]DTS\Binn
osql Utility Allows you to enter [!INCLUDEtsql] statements, system procedures, and script files at the command prompt. [!INCLUDEssInstallPathVar]Tools\Binn
sqlagent90 Application Used to start [!INCLUDEssNoVersion] Agent from a command prompt. <drive>:\Program Files\[!INCLUDEmsCoName] [!INCLUDEssNoVersion]\<instance_name>\MSSQL\Binn
sqlcmd Utility Allows you to enter [!INCLUDEtsql] statements, system procedures, and script files at the command prompt. [!INCLUDEssInstallPathVar]Tools\Binn
SQLdiag Utility Used to collect diagnostic information for [!INCLUDEmsCoName] Customer Service and Support. [!INCLUDEssInstallPathVar]Tools\Binn
sqlmaint Utility Used to execute database maintenance plans created in previous versions of [!INCLUDEssNoVersion]. <drive>:\Program Files\[!INCLUDEmsCoName] [!INCLUDEssNoVersion]\MSSQL14.MSSQLSERVER\MSSQL\Binn
sqlps Utility Used to run PowerShell commands and scripts. Loads and registers the [!INCLUDEssNoVersion] PowerShell provider and cmdlets. [!INCLUDEssInstallPathVar]Tools\Binn
sqlservr Application Used to start and stop an instance of [!INCLUDEssDE] from the command prompt for troubleshooting. <drive>:\Program Files\[!INCLUDEmsCoName] [!INCLUDEssNoVersion]\MSSQL14.MSSQLSERVER\MSSQL\Binn

Use troubleshooting tools

You can use SQLdiag Utility to collect logs and data files from [!INCLUDEssNoVersion] and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers. SQLdiag is intended to expedite and simplify diagnostic information gathering for Microsoft Customer Support Services.

You can launch the utility on the administrator command prompt on the Server Core, using the syntax specified in the article: SQLdiag Utility.

See Also

Install SQL Server on Server Core
Installation How-to articles