Featured Database Articles

11. At this point the installation gives you the opportunity to select
optional components.

Click to Enlarge

Click Next to continue the installation.

12. Choosing the proper service accounts (the accounts that SQL Server will use
to authenticate to Windows when it needs to access resources such as NTFS hard
drives or other computers) is an important one. SQL Server 2000 uses two
accounts: one for SQL Server and one for the SQL Server Agent. The SQL Server
account is used by the SQL database engine which provides all the database
related services. The SQL Server Agent account is used by the SQL Server Agent
which provides alerts and job scheduling for your SQL Server. For now just
understand there are two different accounts; the differences will become
apparent when we discuss the SQL Server Agent in more detail.

The first option you must select is whether you want the two services to use the
same account and have the SQL Server agent start automatically or if you want to
customize the accounts for each service. If you select to use the same account,
you then have the option to use the Local System account or use a Domain User
account.

If this is your only SQL Server on the network and you don't plan on having this
server interact with any other SQL servers or remote storage servers, you can
use the Local System account. If you want to interact with other SQL servers or
pull information off of another server you are going to need to create a user
account in your domain. If you do use a domain account for SQL Server, it is
recommended that you create an account for just SQL server and not use an
existing user account. This is important because if your network's account
policy is to have the password expire every 30-90 days you would have to change
the account passwords on all your SQL Servers every 30-90 days. To overcome this
problem, set the "Password Never Expires" on the account you use for your SQL
Servers.

Customizing each service's account gives you the same options as using the same
account for each service, but allows you to set the account each service uses
independently. It also gives you the option to start the SQL Server Agent at
startup or not. We will look at services in more detail in the next article.

Select "Use the same account for both services. Autostart SQL Server Agent
Service," "Use the Local System account," and click Next.

13. Selecting the Authentication Mode is the next option we are presented with.
The process of authenticating (using your User Name and Password) to a SQL
Server for access to the databases can be accomplished by the SQL Server or by
the operating system.

If you select "Windows Authentication Mode" all authentication will be handled
by the Windows Security Users and Groups. In other words the accounts that you
use to login to a Windows computer are also used to access the SQL Server
databases. Having the ability to use Windows' Security provides benefits such as
streamlined authentication and much simpler user management. It is recommended
to use only Windows Authentication Mode unless you have a spasific reason not
to.

"Mixed Mode" authentication provides both Windows Authentication and SQL Server
Authentication. Unlike Windows Authentication, SQL Server Authentication is
handled by SQL Server and does not rely on the operating system. Because SQL
Server Authentication is independent, authentication can be provided for
platforms other than Windows (ex: Unix). When you choose Mixed Mode
authentication you are asked to provide a password for the system administrator
(sa) account. This account is available so you can authenticate as a "sysadmin"
via SQL Server Authentication. When you use only the Windows Authentication Mode
the sa account is disabled.

There are two final notes about Windows Authentication (for either of the two
modes): The Windows Security groups BUILTIN\Administrators on the local computer
(the computer SQL Server is installed on) and the group Domain Admins (if the
SQL Server computer is part of a domain) are automatically granted "sysadmin"
rights to the SQL server. Also remember you must use Windows NT or Windows 2000
to provide Windows Authentication. If you are using Windows 98, for example, you
can only use SQL Server Authentication.

Security is a big issue and will take one or two articles to examine in more
detail. For now it is only important to understand the two modes of
authentication.

Select "Windows Authentication Mode" and click Next.

14. Another important decision you have to make is the default collation. There
are three parts to the collation setting:

- Character Set
- Sort Order
- Case Sensitivity

The character set specifies what ASCII code page will be used. ASCII (pronounced
"ask-ee") code pages contain the codes (numbers 0 through 255) that correspond
to the appropriate characters. For example, the ASCII code for the letter A is
65. Characters 0 through 127 are called the "Standard ASCII Set" and are the
same from one code page to another. The character codes 128 through 255 are in
the "Extended ASCII Set" and will varied between different code pages. Take a
look at ASCII code pages 1252 and 437.

There are two major types of sort orders: Dictionary and Binary. You have seen a
Dictionary sort order before if you have ever used a dictionary. Just like in a
dictionary, Dictionary sort order arranges the data in alphabetical order. When
you use a dictionary sort it's simple to predict in what order your data will be
returned to you.

Binary sorts, on the other hand, are a little more complicated to understand and
predict. A binary sort is preformed on the numbers that make up the ASCII
letters. Binary sorts can return results that are not always expected because
the numbers that make up ASCII codes are not in dictionary order. However the
benefit of a binary sort order is its fast speed.

Case sensitivity comes into play when you start to make comparisons. For
example, if you had a statement that was looking for the word "trainers" and you
used a collation that was case-insensitive; "Trainers", "TRAINERS", and "TrAiNeRs"
would all be returned. If you used a case-sensitive collation only "trainers"
would be returned.

Now that you understand what makes up a collation lets look at the settings
available on the collation setup screen. You can choose to use Windows' Locale
settings to set the collation or use a SQL Server Collation. By using Windows'
Locales you use the Collation information that is provided by the operating
system. SQL Collations are provided for compatibility when you need to interact
with older SQL Servers or upgrade a database.

Just remember that you are only setting the default for the server. You can
select another collation for each database, or even each table, individually.

Select "SQL Server Collations", "Dictionary order, case-insensitive, for use
with 1252 Character Set.", and click Next.