I have never heard anyone dream of becoming a DBA when they grow up as a child. Life simply brings us to this profession, although many people really like it. Dive into administration SQL Server rarely happens with a product manual, more often than not we have to learn the secrets of the craft on our own. This is how I started myself, and in the course of my studies I made a lot of mistakes. Therefore, I have prepared a series of articles that have already covered the topics. Reserve copy and recovery. Now it's time to talk about the main tool for tweaking SQL Server settings - SQL Server Settings Manager.
SQL Server Settings Manager is an online application for managing all SQL Server-based services, network protocols, listener ports, and server aliases. The SQL Server Settings Manager (SSCM) is available in the Start \ menu item hierarchy Microsoft SQL Server 20xx \ Configuration Tools \ SQL Server Configuration Manager in versions Microsoft Windows prior to the release of Windows 8 and Windows Server 2012. In the latest specified versions operating system just search for SQL Server and select "SQL Server Settings Manager" from the list of applications. The first time you access SSCM, your program looks something like Figure 1 (using SQL Server 2014 as an example in this article).
| Figure 1: SQL Server Settings Manager |
Customization
Let's take a closer look at the possible actions allowed in the SSCM. Each item in the left pane represents one or more tasks that can be performed in SSCM. In some cases, there are both 64-bit and 32-bit options. In this article, we will focus on the 32-bit version. Today Microsoft SQL Server is hosted on 32-bit servers only if:
a) you are the owner old version SQL Server;
b) most likely, you are depriving the instance of SQL Server of valuable resources of RAM.
Let's list the actions available in the SSCM (see Figure 2).
.jpg) |
| Figure 2: Actions Available in SSCM |
- SQL Server Services. This action allows you to start, stop and restart all services related to Microsoft SQL Server. In addition, you can change service accounts, startup behavior, and additional functions and startup options depending on the service.
- SQL Server network settings. This action allows you to enable or disable specific network protocols: Shared Memory, Named Pipes and TCP / IP, and configure additional parameters for each of them.
- SQL Server Native Client Settings (currently version 11.0). This action allows you to set the order in which clients will use the specially enabled protocols to connect to the customized instance of SQL Server. It can be used to create aliases for an instance of SQL Server so that various applications end users could connect to servers with names other than the actual server name. This is done in case you cannot change connection strings when migrating application databases, but still want to maintain consistency or hide the real server name from end users. Let's take a closer look at each of these steps.
SQL Server Services
SQL Server bundled services can (and should) be managed and configured from the SSCM, not the services.msc API. As noted above, we can control not only the startup behavior and service account, but also additional parameters for each service (see Figure 3).
- SQL Server Integration Services. There are no additional configurable options.
- SQL Server Analysis Services. There are no additional configurable options.
- SQL Server Service:
1. FILESTREAM. This setting allows you to enable or disable T-SQL access, file I / O access, remote client access, and set the share name FILESTREAM.
2. High level AlwaysOn availability. This setting gives you the ability to enable or disable AlwaysOn Availability Groups, and configure the Windows Failover Cluster (WFCS) on which the availability group is built.
3. Launch parameters. SSCM allows you to assign custom startup options that take effect on an instance. You will always have at least three startup options for any instance of SQL Server to ensure that Microsoft SQL Server starts correctly:
- -d. Specifies the location of the data file (.mdf) for the master database.
- -l. Specifies the location of the transaction log file (.ldf) for the master database.
- -e. Specifies the location of the instance error log file.
Why exactly these three parameters? Because master functions as the outward-facing “brain” of the SQL instance, working in conjunction with the hidden Resource database to provide all the metadata required to start the SQL Server instance. It is required to use an error log to record each step of the startup process.
Extra options can be configured, in particular, to start the server in single-user mode for troubleshooting purposes (-m); launching an instance with a minimal set of settings (-f) in cases where it is necessary to bypass an unsuccessful parameter that can lead to a deterioration in the response of the instance; setting trace flags that change the basic behavior of Microsoft SQL Server (-T). I had to use -f to work around bad settings when I tried to demonstrate memory limitation issues and set the maximum server memory so small that I could not start SQL Server. Of course, most DBAs set several trace flags during their professional activity on each of the SQL Servers via the -T parameter, but I'll talk more about this in another article.
A complete list of startup options can be found in the official Microsoft documentation (https: // msdn.
microsoft.com/en-us/library/ms190
4. Advanced. The Advanced tab for the SQL Server service, which Figure 4 shows, provides the ability to change the dump directory for an instance, as well as configure a mechanism to send feedback to Microsoft for further product development. In addition, you get the ability to read (but not change) the additional parameters listed below.
- SQL Server Reporting Services. We can manage some basic service settings for SQL Server Reporting Services (SSRS), but there is a separate interface for this service. I do not recommend using SSCM for all aspects of configuring SSRS, although some are available.
- SQL Server Browser. In addition to the startup behavior and service account, there are several additional settings for this service that you can control besides the dump directory and error log. We recommend that you disable this service unless you have multiple instances of SQL Server on the same node.
- SQL Server Agent Service. Like many other services, you can only configure the dump directory, error logging, and feedback, along with startup behavior and service account.
SQL Server Network Settings
Actions on network configuration SQL Server allows you to enable any or all of the three network protocols available in Microsoft SQL Server: Shared Memory, Named Pipes, and TCP / IP.
Shared Memory and Named Pipes provide access network computer to SQL Server, and TCP / IP defines how network devices communicate with an instance of SQL Server. Yes, Named Pipes can be used in a Windows environment, but you lose all the benefits of traversing the network stack when using Named Pipes between remote servers... Shared Memory settings begin and end with an enable status. Named Pipes, in addition to being enabled / disabled, allows you to specify a channel name for SQL Server. Finally, TCP / IP allows you to enable or disable this protocol, along with changing the port on which SQL Server listens for requests.
Configuring SQL Server Native Client
This set of actions allows you to prioritize the processing of queries in SQL Server. In addition, you can disable named protocols for the client, and assign a channel name and port number.
The SQL Server Configuration Manager interface is an essential component for configuring how instances work and how SQL Server interacts with computers and clients that query the databases hosted on the instance. It defines how an instance is started and secured at its most basic level through managing the service accounts that own the various components of Microsoft SQL Server. This is SQL Server's first line of defense, and both a novice and experienced DBA need to properly use SSCM and be familiar with its customization options.
SQL Server is a tool for managing the services associated with SQL Server, for configuring the network protocols that are used by SQL Server, and for managing the configuration of connections from SQL Server client computers. SQL Server Microsoft Configuration Manager is a Microsoft Management Console (MMC) snap-in that can be opened from the Start menu or added to any Microsoft Management Console screen. Microsoft Management Console ( mmc.exe) uses the file SQLServerManager<версия>.msc(for example, SQLServerManager13.msc for SQL Server 2016) to open the Configuration Manager. Below are the locations of the last four versions of this dispatcher when Windows installation to drive C.
| SQL Server 2016 | C: \ Windows \ SysWOW64 \ SQLServerManager13.msc |
| SQL Server 2014 | C: \ Windows \ SysWOW64 \ SQLServerManager12.msc |
| SQL Server 2012 | C: \ Windows \ SysWOW64 \ SQLServerManager11.msc |
| SQL Server 2008 | C: \ Windows \ SysWOW64 \ SQLServerManager10.msc |
| Note |
|---|
Because SQL Server Configuration Manager is a Microsoft Management Console snap-in and not a sandbox, when working in newer Windows versions SQL Server Configuration Manager does not appear as an application.
|
SQL Server Configuration Manager and SQL Server Management Studio use WMI to view and change some of the server settings. WMI provides a consistent interface to the API calls that handle registry operations that query SQL Server tools, as well as improved control and management of selected SQL services in the SQL Server Configuration Manager snap-in. For information about setting WMI-related permissions, see.
For information about starting, stopping, pausing, resuming, and configuring services on another computer using SQL Server Configuration Manager, see.
The SQL Server Configuration Manager is used to start, pause, resume, and stop services, and to view or change the properties of services.
Use SQL Server Configuration Manager to start the Database Engine using startup options. For more information, see.
You can use SQL Server Configuration Manager to manage SQL Server services.
| Important |
|---|
Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by SQL Server Services or SQL Server Agent, or to change the password for an account. SQL Server Configuration Manager not only changes the account name, but also performs additional configuration, such as setting permissions in Windows registry so that the new account can read the SQL Server settings. Other tools, such as Windows Service Control Manager, can change the account name but do not change the corresponding settings. If the service cannot access the SQL Server registry key, it may not start correctly. |
An additional benefit of SQL Server Configuration Manager, SMO, and WMI is that the new settings take effect immediately without restarting the service.
SQL Server allows you to configure server and client network protocols and connection parameters. Once the correct protocols have been enabled, there is usually no need to change network connections server. At the same time, SQL Server Configuration Manager can be used to reconfigure connections so that SQL Server listens on a specific network protocol, port, or channel. For more information about enabling protocols, see. For information about allowing access to protocols in the firewall, see.
SQL Server Configuration Manager lets you manage server and client network protocols, including applying protocol encryption, viewing alias properties, and enabling or disabling the protocol.
SQL Server allows you to create or drop aliases, change the order in which protocols are used, and view the properties of a server alias, including:
server aliases - server aliases used for the computer to which the client connects;
protocol - the network protocol used for this configuration;
connection parameters - parameters associated with the connection address for configuring the network protocol.
SQL Server Configuration Manager also allows you to view information about failover cluster instances, although Cluster Administrator must be used for some actions, such as starting and stopping services.
Available network protocols
SQL Server supports shared memory, TCP / IP, and named pipes. For information on choosing network protocols, see. SQL Server does not support VIA network protocols, Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, and NWLink IPX / SPX. Clients previously connected using these protocols must select a different protocol to connect to SQL Server. SQL Server Configuration Manager cannot be used to configure WinSock Proxy. To configure WinSock Proxy, see the ISA Server documentation.
SQL Server Services
SQL Server bundled services can (and should) be managed and configured from the SSCM, not the services.msc API. As noted above, we can control not only the startup behavior and service account, but also additional parameters for each service (see screen below).
SQL Server Integration Services. There are no additional configurable options.
SQL Server Analysis Services. There are no additional configurable options.
SQL Server Service:
SQL Server Linked Services
1. FILESTREAM. This setting allows you to enable or disable T-SQL access, file I / O access, remote client access, and set the share name FILESTREAM.
2. High level of AlwaysOn availability. This setting gives you the ability to enable or disable AlwaysOn Availability Groups. Plus, it allows you to configure the Windows Failover Cluster (WFCS) on which the availability group is built.
3. Launch parameters. SSCM allows you to designate custom startup options that take effect on an instance. You will always have at least three startup options for any instance of SQL Server to ensure that Microsoft SQL Server starts correctly:
-d. Specifies the location of the data file (.mdf) for the master database.
-l. Specifies the location of the transaction log file (.Idf) for the master database.
-e. Specifies the location of the instance error log file.
Why exactly these three parameters? Because master functions as the outward-facing “brain” of the SQL instance, working in conjunction with the hidden Resource database to provide all the metadata required to start the SQL Server instance. It is required to use an error log to record each step of the startup process.
Additional parameters can be configured, in particular, to start the server in single user mode for troubleshooting purposes (-m); launching an instance with a minimal set of settings (-f) in cases where it is necessary to bypass an unsuccessful parameter that can lead to a deterioration in the response of the instance; setting trace flags that change the basic behavior of Microsoft SQL Server (-T). I had to use -f to work around bad settings when I tried to demonstrate memory limitation issues and set the maximum server memory so small that I could not start SQL Server. Of course, most DBAs set several trace flags during their professional career on each of the SQL Servers via the -T parameter, but I'll cover that in more detail in another article.
Student of the IS-369 group Gulin Nikita
SQL Server Administration Tools
Beginning with SQL Server 2005, several SQL Server 2000 administration tools have been incorporated into SQL Server Management Studio, several have been built into SQL Server Configuration Manager, and the Index Tuning Wizard has been renamed.
The following table shows how SQL Server 2000 tools match their SQL Server 2005 equivalents:
Using Current SQL Server Management Tools previous versions SQL Server
Starting in SQL Server 2005, the tools use SMOs and fully support the new features of SQL Server. SQL Server 7.0 and SQL Server 2000 tools use DMOs and do not support new SQL Server features.
· SQL Server Tools lets you manage SQL Server and all the features of SQL Server 2000.
· SQL Server 2000 Tools cannot manage later versions.
· Both the current SQL Server tools and SQL Server 2000 tools can be installed on the same computer, and both packages will work fine.
SQL Server Configuration Manager
SQL Server Configuration Manager is a tool for managing SQL Server-related services, configuring the network protocols that SQL Server uses, and managing connection configuration from SQL Server client computers. SQL Server Configuration Manager is a Microsoft Management Console (MMC) snap-in that can be opened from the Start menu or added to any screen in Microsoft Management Console. Microsoft Management Console (mmc.exe) uses the SQLServerManager10.msc file in Windows folder System32 to open SQL Server Configuration Manager.
SQL Server Configuration Manager and SQL Server Management Studio use WMI to view and change some of the server settings. WMI provides a consistent interface to the API calls that handle registry operations that query SQL Server tools, as well as improved control and management of selected SQL services in the SQL Server Configuration Manager snap-in.
Service management
The SQL Server Configuration Manager is used to start, pause, resume, and stop services, and to view or change the properties of services.
Changing accounts used by services
You can use SQL Server Configuration Manager to manage SQL Server services.
Server and client network protocol management
SQL Server Configuration Manager allows you to configure server and client network protocols and connection settings. Once the correct protocols have been enabled, there is usually no need to change the server's network connections. At the same time, SQL Server Configuration Manager can be used to reconfigure connections so that SQL Server listens on a specific network protocol, port, or channel. For more information about enabling protocols, see How to Enable or Disable SNP (SQL Server Configuration Manager). For information about allowing access to protocols in the firewall, see Configuring Windows Firewall to allow access to SQL Server.
SQL Server Configuration Manager lets you manage server and client network protocols, including applying protocol encryption, viewing alias properties, and enabling or disabling the protocol.
SQL Server Configuration Manager lets you create or drop aliases, change the order in which protocols are used, and view the properties of a server alias, including:
Server alias — the server alias used for the computer to which the client connects.
· Protocol — network protocol used for this configuration;
· Connection parameters — parameters associated with the connection address for configuring the network protocol.
SQL Server Configuration Manager also allows you to view information about failover cluster instances, although the Cluster Administrator must be used for some actions, such as starting and stopping services.
Available network protocols
SQL Server supports Shared Memory, TCP / IP, Named Pipes, and VIA protocols. SQL Server does not support Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, and NWLink IPX / SPX networking protocols. Clients previously connected using these protocols must select a different protocol to connect to SQL Server. SQL Server Configuration Manager cannot be used to configure WinSock Proxy.
