Post-Install Configuration

Now that you have your SQL Server components installed, you will probably need to do some additional configuration. Beginning with SQL Server 2005, Microsoft and the world have taken a more serious approach to security. In the past, almost everything was enabled by default. We have learned that it is better to leave most things turned off, so you must make a decision to implement the features and capabilities you need. Microsoft is calling this "Secure by Default." You should run the Surface Area Configuration tool to turn on any features you will need.

Surface Area Configuration

Run the SQL Server Surface Area Configuration program. There are two pieces to the application: Surface Area Configuration for Services and Connections and Surface Area Configuration for Features. Click the Surface Area Configuration for Services and Connections, as shown in Figure 2-10.

Figure 2-10

If you are installing SQL Express and Evaluation Edition or Developer Edition, only local connections can be made. You can test this by going to another computer and trying to connect to this server. You will get an error indicating that remote connections are not allowed.

To enable remote connections, select the Database Engine; then select Remote Connections. Enable Local and Remote Connections using the protocols you need, as shown in Figure 2-10. In addition, go through each of the items available and ensure you have the proper capabilities enabled. This is also one of the places you can specify which SQL Services you want started automatically and manually. For a production server, you will wish to start SQL Server, SQL Agent, and the Browser. If this is an Analysis Server, it should autostart also.

Also open the Surface Area Configuration for Features, shown in Figure 2-11. You will find that features you need may be turned off. Items such as Remote Dedicated Administrator Connections, OLE Automation, CLR Integration, and Ad Hoc Remote Queries are turned off by default. Turn on only those items you know you need and leave the others off. This will give you a safer install.

Figure 2-11

Note

You will have to restart SQL Server for changes to take effect.

SQL Server Configuration Manager

The SQL Server Configuration Manager tool allows you to specify SQL Server Services options and whether the service starts automatically or manually. You can also stop and start services here, since the Service Manager in the toolbar has gone away in this release. This program also includes a couple of very helpful features. Although I have not mentioned it before, I am sure you already know that SQL 2005 installs place items in strangely named directories: MSSQL.1, MSSQL.2, MSSQL.3, and so on. Each one might be an instance of Analysis Services, Reporting Services, or the SQL Engine. If you have many side-by-side installs of SQL Server, you could have MSSQL.1 through MSSQL.x. How can you find out which of these directories belongs to a particular instance? You can find this in the registry, of course, but this is one of the things you can use the SQL Server Configuration Manager for. It will tell you many of the properties of the service. Double-click on one of the services and go to the Advanced tab, as shown in Figure 2-12. There you will find lots of good information, including version information.

Figure 2-12

As with the Surface Area Configuration tool, you may set up the services, change service accounts, and auto start services. You can choose which network protocols SQL Server will listen on. This can be found in the SQL Server 2005 Network Configuration node. This node also allows you to import certificates for Secure Sockets Layer (SSL) encrypted communication.

The replacement for the Client Connectivity setup in SQL 2000 is located here as well, in the SQL Native Client Configuration node. You may choose which protocols the client uses and the order in which they will be used. You may also set up server aliases here. A server may listen on named pipes and TCP/IP. If you wish to force your client to use a specific protocol when communicating with a specific server, you may set up an alias for this.

SQL Server Management Studio

Once your instance is installed, you should visit the SQL Server Management Studio and complete any configuration or tuning changes you wish to make. Most of the items you change will be based on performance considerations and are covered in a later chapter. Among the items you may wish to consider changing are:

• Processor section: Number of processors

• Memory Section: Minimum and maximum memory

• Database Section: You may wish to set the default location for new data and log files. This is used when someone issues a create-database command without specifying file locations.

• Permissions Section: Some sites require that only database admins have admin access, which means that operating system administrators should not have SQL admin capability. The default install adds a login for the administrators group on the local server. This login has SQL Server sysadmin permissions. Practically speaking, this means you cannot prevent any local administrator from having sysadmin permission for SQL. All they have to do is add themselves to the local administrator group, which is set by default. This is true for any login in SQL Server that has sysadmin privileges. Local server admins merely have to add themselves to the group, and they will have sysadmin privileges again. In some shops, usually smaller ones, this is not a problem. However, if you wish to exclude local server admins from having SQL privileges, delete the Builtin/Administrators login. Then add only specific logins for your SQL Administrators, adding them to the sysadmin group.

Tempdb

Tempdb has taken on more responsibility than it had in the past. Tempdb historically is used for internal processes such as some index builds and table variable storage, as well as temporary storage space by programmers. The following is a partial list of some of the uses for tempdb:

• Bulk load with triggers

• Common table expressions

• DBCC operations

• Event notifications

• Indexes, including (SORT_IN_TEMPDB, partitioned index sorts, and online index operations)

• Large object type variables and parameters

• Multiple active result set operations

• Query notifications

• Row versioning

• Service broker

The location and attributes of tempdb have always been important. This is even truer now. Therefore, you should always ensure it is using Simple Recovery Mode.

Ensure enough space has been preallocated to handle most operations. You do this by setting the file size to an appropriate size. Even though autogrowth should be enabled, autogrow operations are expensive and time consuming. Think of autogrow as an emergency growth operation. When you enable autogrow, choose a file-growth increment sufficiently large so that autogrow operations do not have to occur frequently. Microsoft recommends a file-growth increment of 10 percent of the tempdb data file size, with a maximum of 6GB. File-growth operations can be slow, causing latch timeouts. The length of filegrowth operations should be less than two minutes.

Use instant database file initialization if possible. Normally, SQL Server will write zeros into a datafile to overwrite any data left on the disk from a preexisting file. This operation occurs under the following conditions:

• Creating a database

• Adding log files or data files to an existing database

• Increasing the size of a file (including autogrowth)

• Restoring a database or filegroup

Instant file initialization claims disk space without overwriting with zeros. This allows initialization, and in this case tempdb autogrowth, to occur much faster. Log files must still be initialized with zeros, so they cannot be initialized instantly.

Instant file initialization applies globally on all instances that use the same service account. This setting applies to all databases on the instance, not just to tempdb. It occurs automatically on Windows XP Professional and Windows Server 2003 or later, when the SQL Server Service account has been granted the SE_MANAGE_VOLUME_NAME privilege. Windows administrators can set this feature by adding the SQL Server Service Account to the Perform Volume Maintenance Tasks local security policy, as shown in Figure 2-13. You can get there from Control Panel Administrative Tools Local Security Policy.

Figure 2-13

There is a security issue related to using instant initialization. If files are not initialized to zeros, old data will be visible on reused areas of disk, which may be a security risk. You could drop a filegroup for one database and add a filegroup and files on the same drive to another database. If these newly added files are not initialized, they will still contain the information from their previous use. Of course, an intruder would have to open the files directly and get the information, but it is doable. You can prevent this by ensuring that the physical files have the appropriate discretionary access control list (DACL) or are simply not using instant initialization.

Note

If you use instant file initialization and later turn it off, the existing files are not affected. They still contain the original data. Only newly created or expanded files will be initialized with zeros.

You should use multiple files in tempdb to ensure that tempdb operations can take advantage of all of the IO bandwidth available. Create about one file per processor but not more than two files per processor. Too many files simply increase the overhead and cannot be used simultaneously. (Dual-core CPUs count as two processors.) Ensure that each file is the same size. SQL Server 2005 continues to use the proportional fill algorithm from prior releases. This helps ensure the round-robin use of all files.

Finally, you should place tempdb on a fast IO subsystem, using striping if multiple disks are available, and place tempdb on a different drive from your user databases.

Back it up

After you have made all of your configuration changes, now would be a good time to do your initial backups, create your disaster-recovery plans, and get things going. Backups are discussed more thoroughly in Chapter 18.