SQL Server Configuration Manager is a tool in which many settings of SQL Server are made.
In this article, I’ll refer to many of the settings that can be done through SQL Server Configuration Manager.
For example;
What is the SQL Server Browser Service?
What is Named Pipe?
How to change the user or user password of SQL Server Service
How to activate Always ON or FileStream?
What is Alias and how is it defined?
Right-click SQL Server 2014 Configuration Manager by typing Configuration Manager in the search form below and open it as Run As Administrator.
If you use SQL Server Instance with SQL Server 2014 and you are using SQL Server 2012 Configuration Manager, you cannot see your services on Configuration Manager.
Therefore, you need to use the highest version.
When Configuration Manager opens, a screen as follows will appear in SQL Server Services.
You can see the services of SQL Server Instance that we use on this screen.
What are these services in the picture below?
| SQL Server Integration Services | It is used for transferring and converting data in ETL operations.
Usually we use when transferring data from the database to the data warehouse. |
| SQL Server (TESTINSTACE2)
SQL Server (TESTINSTANCE) SQL Server (MSSQLSERVER) |
Beginning with “SQL Server” are SQL Server Database Engine Service.(Instance)
So, on my computer, there is two named instance(TESTINSTANCE and TESTINSTACE2) And the default instance is MSSQLSERVER. There can be only one default instance in a server |
| SQL Server Agent (TESTINSTACE2)
SQL Server Agent (TESTINSTANCE) SQL Server Agent (MSSQLSERVER) |
Beginning with “SQL Server Agent” are SQL Server Agent services.
(the service required to run our jobs). Each instance has an agent service attached to it. |
| SQL Full-text Filter Daemon Launcher(TESTINSTANCE) | This service is the service required for Full-text search operation. You can see how to install this service in the article “How To Install SQL Server“.
For details of Full-text search, you may want to read my article “Fulltext Search On SQL Server“. This service is installed on my computer in TESTINSTANCE. |
| SQL Server Reporting Services(TESTINSTANCE) | It is a service used to generate reports in SQL Server. |
| SQL Server Browser | It is the service that directs clients when connecting to SQL Server.
The SQL Server Browser reads from the Registery on which port all the SQL server instances on the server are running. or, if activated, reads which named pipe is used. Named Pipes is a protocol. If TCP/IP or Domain Name Service (DNS) is not available, you can enable it by right clicking. I’ve never used SQL Server Browser until now. Because TCP/IP is a worldwide accepted protocol and SQL Server uses the TCP/IP protocol by default. |
Right-click one of the Database Engine Services in the SQL Server Services tab and click on the properties.
On the Log on tab, you can select Local System from the Built-in account.
But if you are going to install a cluster system, you need to set the domain user to your sql server service.
By clicking Browse from the “This account:” section, you can find the relevant user in the active directory, check the names and “ok”, then enter the password of this user in the Password and Confirm Password.
Also, if you changed the user’s password from the active directory, you can still update the password in the Password and Confirm Password section.
Make sure that the password of the user you authorize as SQL Server Service will not expire.
Because the password of users defined from Active Directory is usually expired after a certain period of time.
In order to avoid expire, it is necessary to set this user to not expire from Active Directory.
If you are going to use Always On in your instance, you need to come to the AlwaysOn High Availability tab and activate it as follows.
If you are going to use filestream in databases on instance, you need to activate file stream by clicking “Enable FILESTREAM for Transact-SQL access” by going to FILESTREAM tab.
If you select “Enable FILESTREAM for file I / O access”, files can also be accessed via windows. I wouldn’t advise you to do that in terms of security.
If you select “Allow remote clients access to FILESTREAM data”, you can also access FILESTREAM data from remote computers. I wouldn’t advise you to do that in terms of security.
By simply selecting the following option, we ensure that FILESTREAM data can only be accessed from the database via TSQL.
What is SQL Server Alias?
For example, you have a server named server1 and you have an instance named instance1 on that server. The IP of your server is “10.6.45.43”.
This instance uses TCP / IP 1434 port.
When connecting to this instance, you can connect with one of the following methods.
- server1\instance1
- server1,1434
- 10.6.45.43\instance1
- 10.6.45.43,1434
By identifying the alias, we can connect with a name that we have determined instead of connecting using one of the above statements.
Let’s clarify the subject with an example.
Right click on Aliases in SQL Native Client Configuration and click New Alias.
In Alias Name we write the name we want to use to connect to the server.
In Port no, we are writing the instance’s port.
We choose TCP/IP as the protocol.
In the Server section, we write one of the methods mentioned above to connect to the instance and click ok.
Then, when connecting to sql server with SSMS, we enter alias name in Server name.
And when we connect in this way, the server name appears as testalias as below.
You can set the port that an instance uses with SQL Server Configuration Manager.
I explained this in detail in the article “How To Configure SQL Server After Installation“.
To open the sql server service automatically when the server is turned on, you need to set it as automatic in the services tab as below.
Default is automatic.
If your settings are not this way, you should correct them.
You can change the Dump Directory from the Advanced tab.
Dumps are stored in the dump directory when a sql server crash.
From the Startup parameters section, you can set some parameters for SQL Server.
Let’s examine the trace flags in the screenshot above.
For more information about Startup Parameters, please read the article “SQL Server Startup Parameters“.
| Trace Flag 1117 | You can set this trace flag so that the files within a filegroup grow evenly. |
| Trace Flag 1118 | This trace flag allows the Uniform Extent to be used instead of the Mixed Extent.
For more information about the Extents, please read the article “SQL Server Storage Concepts (GAM, SGAM, PFS, IAM, BCM, DCM)“. |
| Trace Flag 8048 | If you use a version that is earlier than SQL Server 2014 SP2, this trace flag will gain importance for you.
There is no effect after SQL Server 2014 SP2. Converts numa-based split memory objects into CPU-based split. |
| Trace Flag 1236 | If you are using a version that is earlier than SQL Server 2012 SP3 and SQL Server 2014 SP1, you must set this trace flag.
Solves the performance problems with lock causes in the database. |
| Trace Flag 9024 | If you are using earlier versions of SQL Server 2014 SP1, you must set this trace flag.
It prevents log write waits to increase. |
