SQL Server 2000 Administration in 15 Minutes a Week: Basic Installation - Page 3

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.