Contents

Configure and Manage Server Core on Windows Server. 1

Install SQL Server Updates. 2       

Start/Stop SQL Server Service. 3

Enable AlwaysOn Availability Groups. 3       

Configuring Remote Access of SQL Server Running on Server Core. 5

SQL Server Profiler. 6

SQL Server Auditing. 6

Command Prompt Utilities. 6

Use troubleshooting tools. 8

 

Configure and Manage Server Core on Windows Server

The section provides references to the topics that help configure and manage a Server Core installation.
Not all features of SQL Server 2012 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 topics:

·         Windows Server 2008 R2: Best Practices for Server Core Deployments

·         Configuring a Server Core installation: Overview

·         Configuring a Server Core installation of Windows Server 2008 R2 with Sconfig.cmd

·         Installing a server role on a server running a Server Core installation of Windows Server 2008 R2: Overview

·         Installing Windows Features on a server running a Server Core installation of Windows Server 2008 R2: Overview

·         Managing a Server Core installation: Overview

·         Administering a Server Core installation

Install SQL Server Updates

This section provides information about installing updates for SQL Server 2012 on a Windows Server Core machine. We recommend that customers evaluate and install latest SQL Server 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 SQL Server 2012 on a Windows Server Core machine, see Install SQL Server 2012 on Server Core.
The following are the two scenarios for installing product updates:            

Installing Updates for SQL Server 2012 during a new installation

SQL Server Setup supports only command prompt installations on Server Core operating system. For more information, see Install SQL Server 2012 from the Command Prompt.
SQL Server 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. For more information on Product Updates, see Product Updates in SQL Server 2012 Installation.
After Setup finds the latest versions of the applicable updates, it downloads and integrates them with the current SQL Server 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 SQL Server Setup:
Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine,Replication /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /UpdateEnabled=True /UpdateSource=”<SourcePath>” /IACCEPTSQLSERVERLICENSETERMS

Installing Updates for SQL Server 2012 after it has already been installed

On an installed instance of SQL Server 2012, 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 SQL Server 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 SQL Server shared components only:

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

 

·         Update all instances of SQL Server on the computer and all shared components: <package_name>.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances

Start/Stop SQL Server Service

The sqlservr application starts, stops, pauses, and continues an instance of Microsoft SQL Server from a command prompt.
You can also use Net services to start and stop the SQL Server services. For more information see, Manage SQL Server Services from the Command Line.

Enable AlwaysOn Availability Groups

Being enabled for AlwaysOn 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 AlwaysOn Availability Groups, see Enable and Disable AlwaysOn Availability Groups (SQL Server).

Using SQL Server Configuration Manager remotely

These steps are meant to be performed on a PC running the client edition of Windows 7 or later, or another server that has the Server Graphical Shell installed (i.e. a full installation of Windows Server 2008 R2 or a Windows Server 8 installation with the Server Graphical Shell feature enabled).

1.       Open Computer Management. To open Computer Management do one of the following:

a.       On Windows 7, Windows Server 2008, or Windows Server 2008 R2:

                                                               i.      Click Start, click All Programs, click Administrative Tools, and then click Computer Management.

                                                             ii.      Click Start, click Run, type COMPMGMT.MSC, and then click OK.

b.      On Windows 8 with Server Graphical Shell enabled:

                                                               i.      Move your mouse to the bottom-left corner of the screen and right-click when you see the Start overlay.

                                                             ii.      Select Computer Management  from the context menu.

2.        

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

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

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

6.       You will see SQL Server Configuration Manager, double-click on this. The opens  the SQL Server Configuration Manager.

7.       In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (<instance name>), where <instance name> is the name of a local server instance for which you want to enable AlwaysOn Availability Groups, and click Properties.

8.       Select the AlwaysOn High Availability tab.

9.       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 AlwaysOn Availability Groups. Either the local computer is not a cluster node, the WSFC cluster has been shut down, or this edition of SQL Server 2012 that does not support AlwaysOn Availability Groups.

10.   Select the Enable AlwaysOn Availability Groups check box, and click OK.

11.   SQL Server Configuration Manager saves your change. Then, you must manually restart the SQL Server service. This enables you to choose a restart time that is best for your business requirements. When the SQL Server service restarts, AlwaysOn will be enabled, and the IsHadrEnabled server property will be set to 1.

Notes

·         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 AlwaysOn Availability Groups

The PowerShell Cmdlet, Enable-SqlAlwaysOn, is used to enable  AlwaysOn Availability Group on an instance of SQL Server.  If AlwaysOn Availability Groups is enable while the SQL Server 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 cancelled, no operation occurs.
You must have Administrator permissions to execute this cmdlet.
You can use one of the following syntaxes to enable AlwaysOn Availability Groups for an instance of SQL Server:

·         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 AlwaysOn Availability Groups on an instance of SQL Server (Machine\Instance):
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\Machine\Instance
 

Configuring Remote Access of SQL Server Running on Server Core

Perform the actions described below to configure remote access of a SQL Server 2012 instance that is running on Server Core.
Enable remote connections on the instance of SQL Server
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 SQL Server Browser service
By default, the Browser service is disabled. If it is disabled on an instance of SQL Server 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 SQL Server 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 SQL Server
The TCP/IP protocol can be enabled through Windows PowerShell for an instance of SQL Server on Server Core. Follow these steps:
  1. On the computer that is running Windows Server 2008 R2 Server Core SP1, launch Task Manager.
  2. On the Applications tab, click New Task.
  3. In the Create New Task dialog box, type sqlps.exe in the Open field and then click OK. This opens the Microsoft SQL Server Powershell window.
  4. In the Microsoft SQL Server 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
 

SQL Server Profiler

On a remote machine, start SQL Server Profiler and select New Trace from the File menu, the application displays a Connect to Server dialog box where you can specify the SQL Server 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 SQL Server Profiler, see Permissions Required to Run SQL Server Profiler.
For additional details about SQL Server Profiler, see SQL Server Profiler.

SQL Server Auditing

You can use SQL Server Management Studio or Transact-SQL remotely to define an audit. After the audit is created and enabled, the target will receive entries. For more information about creating and managing SQL Server audits, see:

·         Creating and Managing Audits with SQL Server Management Studio

·         Creating and Managing Audits with Transact-SQL

The SQL Server Audit How-to Topics topic provides SQL Server Management Studio and Transact-SQL examples for using the auditing feature.

Command Prompt Utilities

You can use the following command prompt utilities that enable you to script SQL Server operations on a Server Core machine. The following table contains a list of command prompt utilities that ship with SQL Server for Server Core:
Utility
Description
Installed in
Used to copy data between an instance of Microsoft SQL Server and a data file in a user-specified format.
<drive>:\Program Files\Microsoft SQL Server\110\Tools\Binn
Used to configure and execute an Integration Services package. A user interface version of this command prompt utility is called DTExecUI, which brings up the Execute Package Utility.
<drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn
Used to manage SSIS packages.
<drive>:\Program Files\Microsoft SQL Server\110\DTS\Binn
Allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt.
<drive>:\Program Files\Microsoft SQL Server\110\Tools\Binn
Used to start SQL Server Agent from a command prompt.
<drive>:\Program Files\Microsoft SQL Server\<instance_name>\MSSQL\Binn
Allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt.
<drive>:\Program Files\Microsoft SQL Server\110\Tools\Binn
Used to collect diagnostic information for Microsoft Customer Service and Support.
<drive>:\Program Files\Microsoft SQL Server\110\Tools\Binn
Used to execute database maintenance plans created in previous versions of SQL Server.
<drive>:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
Used to run PowerShell commands and scripts. Loads and registers the SQL Server PowerShell provider and cmdlets.
<drive>:\Program Files\Microsoft SQL Server\110\Tools\Binn
Used to start and stop an instance of Database Engine from the command prompt for troubleshooting.
<drive>:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
 

Use troubleshooting tools

You can use SQLdiag to collect logs and data files from SQL Server 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 topic: SQLdiag Utility.