{"id":3151,"date":"2018-09-23T14:20:39","date_gmt":"2018-09-23T14:20:39","guid":{"rendered":"http:\/\/dbtut.com\/?p=3151"},"modified":"2018-11-24T20:34:34","modified_gmt":"2018-11-24T20:34:34","slug":"sql-server-configuration-manager-settings","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/09\/23\/sql-server-configuration-manager-settings\/","title":{"rendered":"SQL Server Configuration Manager Settings"},"content":{"rendered":"

 <\/p>\n

SQL Server Configuration Manager is a tool in which many settings of SQL Server are made.<\/p>\n

In this article, I’ll refer to many of the settings that can be done through SQL Server Configuration Manager.<\/p>\n

 <\/p>\n

For example;<\/strong><\/p>\n

What is the SQL Server Browser Service?<\/p>\n

What is Named Pipe?<\/p>\n

How to change the user or user password of SQL Server Service<\/p>\n

How to activate Always ON or FileStream?<\/p>\n

What is Alias and how is it defined?<\/p>\n

 <\/p>\n

Right-click SQL Server 2014 Configuration Manager by typing Configuration Manager in the search form below and open it as Run As Administrator.<\/p>\n

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.<\/p>\n

Therefore, you need to use the highest version.<\/p>\n

<\/p>\n

 <\/p>\n

When Configuration Manager opens, a screen as follows will appear in SQL Server Services.<\/p>\n

You can see the services of SQL Server Instance that we use on this screen.<\/p>\n

What are these services in the picture below?<\/p>\n

<\/p>\n

 <\/p>\n

\n\n\n\n\n\n\n\n\n
SQL Server Integration Services<\/td>\nIt is used for transferring and converting data in ETL operations.<\/p>\n

Usually we use when transferring data from the database to the data warehouse.<\/td>\n<\/tr>\n

SQL Server (TESTINSTACE2)<\/p>\n

SQL Server (TESTINSTANCE)<\/p>\n

SQL Server (MSSQLSERVER)<\/td>\n

Beginning with “SQL Server” are SQL Server Database Engine Service.(Instance)<\/p>\n

So, on my computer, there is two named instance(TESTINSTANCE and TESTINSTACE2)<\/p>\n

And the default instance is MSSQLSERVER.<\/p>\n

There can be only one default instance in a server<\/td>\n<\/tr>\n

SQL Server Agent (TESTINSTACE2)<\/p>\n

SQL Server Agent (TESTINSTANCE)<\/p>\n

SQL Server Agent (MSSQLSERVER)<\/td>\n

Beginning with “SQL Server Agent” are SQL Server Agent services.<\/p>\n

(the service required to run our jobs).<\/p>\n

Each instance has an agent service attached to it.<\/td>\n<\/tr>\n

SQL Full-text Filter Daemon Launcher(TESTINSTANCE)<\/td>\nThis 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<\/a>“.<\/p>\n

For details of Full-text search, you may want to read my article “Fulltext Search On SQL Server<\/a>“.<\/p>\n

This service is installed on my computer in TESTINSTANCE.<\/td>\n<\/tr>\n

SQL Server Reporting Services(TESTINSTANCE)<\/td>\nIt is a service used to generate reports in SQL Server.<\/td>\n<\/tr>\n
SQL Server Browser<\/td>\nIt is the service that directs clients when connecting to SQL Server.<\/p>\n

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.<\/p>\n

Named Pipes is a protocol.<\/p>\n

If TCP\/IP or Domain Name Service (DNS) is not available, you can enable it by right clicking.<\/p>\n

I’ve never used SQL Server Browser until now.<\/p>\n

Because TCP\/IP is a worldwide accepted protocol and SQL Server uses the TCP\/IP protocol by default.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

Right-click one of the Database Engine Services in the SQL Server Services tab and click on the properties.<\/p>\n

On the Log on tab, you can select Local System from the Built-in account.<\/p>\n

But if you are going to install a cluster system, you need to set the domain user to your sql server service.<\/p>\n

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.<\/p>\n

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.<\/p>\n

Make sure that the password of the user you authorize as SQL Server Service will not expire.<\/p>\n

Because the password of users defined from Active Directory is usually expired after a certain period of time.<\/p>\n

In order to avoid expire, it is necessary to set this user to not expire from Active Directory.<\/p>\n

<\/p>\n

 <\/p>\n

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.<\/p>\n

<\/p>\n

 <\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

By simply selecting the following option, we ensure that FILESTREAM data can only be accessed from the database via TSQL.<\/p>\n

<\/p>\n

 <\/p>\n

What is SQL Server Alias?<\/h3>\n

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”.<\/p>\n

This instance uses TCP \/ IP 1434 port.<\/p>\n

When connecting to this instance, you can connect with one of the following methods.<\/p>\n