Microsoft® SQL Server® 2012 Bible

66

Part I: Laying the Foundations

The minimum server memory setting does not need to be changed unless the operating sys-

tem constantly requests memory resources for other applications sharing the same memory

space. You want to avoid having to release too much memory to the operating system and

potentially starve a SQL Server instance from memory.

On the other hand, the maximum server memory setting limits the maximum amount of

memory a SQL Server instance can allocate. A value set too high can potentially starve an

operating system from memory resources. The maximum server memory value should not

equal or exceed the total amount of server memory available. This value should be at least

1GB less than the total server memory.

Additional SQL Server settings such as processor affi nity, parallelism thresholds, login

auditing, and network packet size may need to be confi gured depending on your environ-

ment requirements. You can confi gure the most commonly used SQL Server settings and

properties using SQL Server Management Studio (SSMS). You can access the complete set of

confi guration options using the sp_confi gure System Stored Procedure.

Visit http://msdn.microsoft.com/en-us/library/ms189631(v=SQL.110).aspx

for a complete list and description of all available SQL Server confi guration options available through

the sp_confi gure System Stored Procedure, a and a complete list and description of SQL Server

properties available through SQL Server Management Studio (SSMS).

Chapter 4, “Client Connectivity,” covers SQL Server confi gurations in more detail.

TCP/IP Ports

As discussed earlier in this chapter in the “Firewalls” section, default instances of SQL

Server use default TCP/IP port 1433 to communicate with clients. Named SQL Server

instances on the other hand are dynamically assigned TCP/IP ports upon service startup.

For hacking prevention and fi rewall confi guration purposes, you may need to change

default ports and control the port numbers over which named SQL Server instances

communicate.

SQL Server 2012 includes a tool called SQL Server Confi guration Manager to manage SQL

Server services and their related network confi gurations. The SQL Server Confi guration

Manager can be found under Microsoft SQL Server 2012 ➪ Confi guration Tools folder in the

Start Menu. Figure 3-17 shows the TCP/IP Properties dialog box in SQL Server Confi guration

Manager where the default 1433 port can be changed.

Best Practices Analyzer (BPA)

The Microsoft SQL Server 2012 Best Practices Analyzer (BPA) is a free diagnostic

tool that gathers information about installed SQL Server 2012 instances, determines

c03.indd 66c03.indd 66 7/30/2012 4:10:09 PM7/30/2012 4:10:09 PM

http://www.it-ebooks.info