4. Enabling Address Windows Extensions
Address Windows
Extensions (AWE) is one of the configuration options that you should be
aware of if you are running on 32-bit operating systems. Enabling AWE
allows a 32-bit SQL Server to use more than 2 or 3 GB of RAM. You can
enable AWE from the Server Properties dialog, as previously shown in Figure 4.
AWE is not applicable in
64-bit operating systems because 64-bit architecture does not have the
same memory restrictions as 32-bit systems. There are additional
configurations that you may want to consider prior to enabling AWE. The
following list describes some configuration options:
How much memory do you have? If your server only has 4 GB of memory, do you need to enable AWE?
Do you need to enable /3gb? The /3gb switch in the boot.ini file allows processes to use more than 3 GB of memory.
Is the /pae switch enabled on your server? To take advantage of 4 GB of memory, you must enable the /pae switch.
Can
you enable the Lock Pages in Memory option? Lock Pages in Memory
identifies accounts that use a process to keep data pages in memory.
That minimizes the amount of paging to virtual memory on disk. You have
to enable Lock Pages in Memory on 32-bit systems in order to turn on
AWE.
Unfortunately, those
configuration options are system dependent and are difficult to cover in
such a small section. So I encourage you to look for AWE in SQL Server
Books Online or see the topic "Awe-Enabled Option" at http://msdn.microsoft.com/en-us/library/ms190731.aspx for more details.
5. Specifying the Backup Compression Default
The Backup Compression Default option enables you to compress backups without specifying the WITH COMPRESSION or WITH NO_COMPRESSION
options to your backup statements. By default, SQL Server 2008
Enterprise Edition disables backup compression during the installation
process. If you have determined that backup compression is your
preferred choice for performing backups, then enable compression by
default to remove the requirement of specifying that you want your
databases compressed each and every time you initiate a backup.
You can specify backup compression by default from the Database Settings section of the Server Properties dialog, shown in Figure 5.
And you can specify
compression by default through a stored procedure call. For example, the
following will enable compression by default:
EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
Specifying compression by
default can save you time by eliminating one possible avenue for
mistake. Imagine yourself on the night when you have to back up the
database before applying a major change. You execute the backup command
that you have been running for years, and it runs for hours. Too late,
you realize that you forgot to include compression as part of that
command. What makes matters worse is the fact that your server doesn't
have enough space to keep that uncompressed backup on the server. So you
have to back up the database again, after having lost time that you
dearly need for the major change that your boss wants in place by 8:00
AM. Don't put yourself in the position of having to request compression
each and every time you make a backup. Enable compression by default if
you prefer to compress your backups.
6. Enabling Login Failure Auditing
We like to track
successful and failed logins on the SQL Server instances that we
support. Monitoring the failed login attempts to SQL Server enables you
to identify and track down unwanted login activity against it. We also
track successful logins to help identify who was logged into the system
when unexpected actions occur. You can enable login auditing from the
Security section of the Server Properties dialog, as shown in Figure 6.
You may also invoke auditing via a call to the xp_instance_regwrite stored procedure. (That extended stored procedure allows you to write directly to your system registry.) For example:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',_
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', _ REG_DWORD, 3
GO
Unfortunately, many of us
(myself included) work in environments where we do not get 100% control
over who has access to SQL Server. Someone in management has forced you
to grant Insert, Update, and Delete permissions to non-DBA personal, and
you are terrified about the mistakes that they can make. Logging
successful logins to SQL Server allows you to identify when such
individuals are in your system and can help you narrow down which
individuals to pursue when all the data is deleted from a table and no
one owns up to it.
