12 Essential Steps After Installing SQL Server

Step 5: Configure tempdb Database

The temporary database is used heavily in SQL 2005 and latter versions. Best practice suggests that for optimal performance, SQL Server’s tempdb database files should be placed in a separate dedicated disk drive. This should be followed wherever possible, but even if your server’s current disk configuration does not allow this, you can still make some changes to ensure tempdb is optimally tuned. Part of this tuning means you should allocate a healthy amount of space to this database.

The number of data files you should allocate in the tempdb database will depend on the number of CPUs (logical or physical) present in the machine. When SQL Server accesses a database, it starts a scheduler thread for each data file present. So if you have eight CPUs in your physical server, the tempdb database should have eight data files to “load balance” the threads.

The easiest way you can check the number of CPUs present in the machine is by looking at the Windows Task Manager’s Performance tab.

Also, data files in a database are used in a “proportional fill” algorithm. This means that if you have two data files in a database – one 4 GB and another 8 GB, SQL will try to stripe the data across the two files in a 1:2 ratio. For each stripe of data written to the 4 GB file, SQL will write two stripes in the 8GB file – hence the term “proportional fill”.

Now if you configure tempdb to have the same number of data files as the number of CPUs present and specify each data file to have the same size and growth, you are effectively ensuring the CPU load is evenly distributed across tempdb.

The following script shows one such configuration done from the query window.

Configuring_tempdb_Data_Files.sql

As you can see from the code, we are adding three new data files to tempdb (since there are four CPUs present and there is already one data file) and ensuring the four files to have the same size and growth characteristics.

Once you have completed the tempdb configuration, you will need to restart the SQL instance for the changes to take effect.

Step 6: Configure Server Properties and Features

Of all the SQL Server configurations, this one is probably the most important and has a direct effect on how the server behaves. What properties you configure and what values you set here will depend on your organisation’s business needs, SOEs etc. There are a number of properties you can set for the database server engine and there is more than one way you can set some of them. I like to divide these properties into three categories:

Configuring Features:

If it is SQL 2005, use the Surface Area Configuration tool for enabling some of the SQL Server features. For SQL 2008, use the Surface Area Configuration Facet from the Server properties. Both these versions list the same features and by default all of these features are disabled.

What you enable here will depend on what you want your server to do. You may want to enable Database Mail (discussed later). If your stored procedures are going to use the xp_cmdshell utility, you will need to switch it on here. If you think there will be managed code in your database application, you need to enable CLR.

Configuring Parameter Values:

a)    Memory: Set the minimum and maximum server memory parameters. You can do so from the server properties dialogue box or using the sp_configure system stored procedure. If AWE is required, enable that too.

b)    Fill factor: Set a default value for index fill factor. This can save page splitting in indexes in future.

c)    Processor: Configure max. degree of parallelism, cost threshold for parallelism as necessary.

d)    Network connections: Configure remote query timeouts if you want to override the default behaviour of 600 seconds.

e)    Security: Although you can change the authentication mode (Trusted or Mixed) at this stage – this is a choice you probably have made during the installation. You can also enable C2 audit mode as part of security configuration.

f)     Trace Flags: If you are installing SQL Server 2005, you may wish to enable trace flags as start-up parameters for detecting and reporting deadlocks. The trace flag 1204 can be used for such purposes.

Configuring SQL Server Network:

This is where you define how SQL will use the network to talk to other computers. The first thing you need to do here is enabling or disabling the network protocols SQL will use to listen for incoming traffic. You set this from the Configuration Manager. The picture below shows how I have disabled Named Pipes.

Next, you need to see if your applications require server aliases. Some of the older apps may require you to define a server alias for a named instance.

Finally, you need to decide if you will assign non-default TCP port number for your instance. The default instance uses a default port number of 1433 and the named instances are usually assigned dynamic port numbers at start up. If you want to assign a non-default port number to SQL Server for TCP connections, you can set it here.

Most of the server property changes would require restarting the service.

Step 7: Configure Security

When it comes down to SQL Server security, companies usually take one of two approaches – some follow a rigorous data security policy, others just leave it all to defaults (and to potential attacks). If you are working for a company like the former, you are in luck – you will know what to do. If your company does not have any specific procedures for implementing SQL Server security (written or unwritten), it is up to you how you define it. Once you have completed this part though, you will have something to refer back to in future.

First and foremost, ensure there are as few members in the sysadmin server role as possible. The Local Administrators Windows group is a member of the sysadmin role by default. Unless you want to have your Windows Administrators to have access to the SQL Server, you should get rid of this group from the sysadmin fixed server role. The next step would be to revoke the login for this group.

If you have a pre-defined account that you use for database administration, add that to the sysadmin server role.

You can also create Credentials and Proxy accounts at this stage if you want your jobs to run SSIS packages or operating system commands under those accounts.

Step 8: Configure Error Logs

SQL Server error log is the first port of call for DBAs when they troubleshoot server related issues. By default, the server keeps six logs as archive and one log as current. Every time SQL service starts, the system begins a new log file. The currently active log file name is ERRORLOG. The log file before restart (previous ERRORLOG) becomes archived as ERRORLOG.1, the log file before that (named ERRORLOG.2) becomes ERRORLOG.3 and so on.

One of the annoying things with default error log behaviour is that any one file can contain a number of days of entries. When you try to view a file, it can take some time to load. If you are interested in a particular day’s events, you will need to sift through the entries or use a filter.

Also, log files are overwritten in a rolling fashion after six files are created. So that means you won’t have any history before the oldest log file (ERRORLOG.6).

What you can do here is to configure SQL Server to keep a predefined number of log files. You can also create a scheduled job to “recycle” the error logs regularly. Recycling means that SQL Server will start a new log file without restarting the service.

To keep a pre-defined number of archived logs, you will need to configure the SQL error log properties.

As you can see, I would recommend keeping at least the last thirty log files.

Once the number of error log files is configured, you can create a scheduled job to run every day to reinitialise the log. The job will have only one step that will call the sp_cycle_errorlog system stored procedure. This procedure starts a new error log file without restarting the SQL service. I usually schedule it to run at 12:00 AM every morning.

The combined effect of these two changes is that you will have one new log file created every day at midnight. If your server does not restart in between, you will have the last one month’s log entries in disk with each day’s log in one separate file.

The same kind of job can be defined for SQL Agent error log files. The latest Agent error log is named SQLAGENT.OUT. Executing the msdb database’s sp_cycle_agent_errorlog procedure from a job will create a new Agent log file and archive the previous one. Unlike SQL Server error logs though, you cannot use a dialogue box to specify the number of archived Agent error logs to keep.