Custom Installation
In the Setup Type dialog box, which Figure 3 shows, the Installation Wizard asks you to choose among three installation types: Typical, Minimum, and Custom. If you choose Typical or Minimum, SQL Server uses default options for components and subcomponents, collations, and Network Libraries. Because of the Typical installation's potential to cause tricky problems, I strongly recommend that you always choose Custom even if you think that the defaults meet your installation needs. Some of the aforementioned options—particularly collation settings—are very hard to change after installation if the defaults turn out not to meet your needs. Custom installation lets you double-check those options. Walking through a few more dialog boxes is a small price to pay to make sure that you select the right installation options.
Security
During the installation process, you specify security-related information in two dialog boxes: Services Accounts (which Figure 4 shows) and Authentication Mode (which Figure 5 shows). In the Services Accounts dialog box, you fill in the service account details for the SQL Server and SQL Server Agent services. Each service loads to the OS with the account specified for it in this dialog box and runs in the OS in the security context of that account. When you back up to a disk device, for example, SQL Server checks whether the login you used to log in to SQL Server has the appropriate BACKUP DATABASE permission. However, to create the backup file device and write to it, SQL Server has to create a file on disk or in a network share, and this operation uses the SQL Server service account's security context.
Similarly, the SQL Server Agent service runs processes in SQL Server and in the OS or network under the security context of the SQL Server Agent's service account. Although an account that doesn't have administrative privileges in the machine can start the SQL Server service, making the SQL Server service account a member of the local Administrators group is a good idea. Otherwise, you'd need to explicitly grant the account all required permissions (e.g., to create database files). You also need to grant the service account the appropriate network permissions (e.g., to create and write to files on network shares that SQL Server uses as backup destinations).
SQL Server Agent, however, won't even start if you try to load it along with a service account that doesn't have administrative privileges on the machine. And if SQL Server Agent performs activities on other machines on the network, such as replication or multiserver jobs, you should use a domain account that also has the appropriate permissions on the other machines. Figure 6 shows a domain with three SQL Server machines in a multiserver environment in which a master server controls automation activities on target servers. Srv1 acts as the master server, and Srv2 and Srv3 act as the target servers. Because both sides (master and target) need to communicate with each other, you need to make sure that the SQL Server Agent service account on the master server has the appropriate privileges on the target server and vice versa. The easiest way to configure such an environment is to create one domain account (e.g., Domain1\SQLService, as Figure 6 shows), make it a member of the local Administrators group in all servers, and load all SQL Server Agent services through that account. (For more information about the multiserver environment, see Michael D. Reilly, "Multiserver Task Administration," April 2002, InstantDoc ID 23982.)
In the Authentication Mode dialog box that Figure 5 shows, you can choose whether to allow only Windows-authenticated logins (Windows Authentication Mode) or both Windows and SQL Server logins (Mixed Mode). You can also specify a password for the sa SQL Server login. Windows-only authentication is the default and commonly recommended security mode. However, for security reasons, I recommend that you choose Mixed Mode and supply a password for the sa account, changing the authentication mode to Windows-only after installation and after handling a few other security items. The installation program creates a SQL Server login for sa and makes it a member of the sysadmin server role. If you choose Windows-only authentication as your server's security mode, the installation process creates the sa login as disabled (because SQL Server authentication is disabled) and with no password. You can change sa's password after installation—and I strongly recommend that you do—but choosing Windows authentication initially is dangerous because you might forget to change the password or leave it blank, trusting that sa is disabled.
No matter which mode you select, the installation program also creates a Windows-authenticated login for the BUILTIN\Administrators group, which is mapped to the local machine's Administrators group. The creation of this login means that all members of the local Administrators group, including the domain group Domain Admins, are also members of your SQL Server's sysadmin role. Giving the network and machine administrators unrestricted permissions on SQL Server isn't always a good idea because it introduces security risks, so you might decide to remove BUILTIN\Administrators from the sysadmin SQL Server role. Or you could completely remove the automatically created login from SQL Server and create a login with sysadmin membership for a group whose members are DBAs—not network administrators.
If you decide to follow either of those recommendations, it's essential that you first create the login with the sysadmin membership for the DBA group, then remove the BUILTIN\Administrators login. If your server's authentication mode is Windows-only and you remove all logins that have sysadmin membership before creating the login for the DBAs, you might find yourself locked out of SQL Server with no way to perform administrative tasks—such as creating new logins. If you fall into that trap, you can still change SQL Server's authentication mode to Mixed through the registry by editing the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName> \MSSQLServer\LoginMode registry subkey. Change the subkey value to 2, then restart the SQL Server service.
Although being able to control SQL Server's login mode through the registry is convenient, it has a disadvantage as well. Anyone who has permissions to edit that registry subkey, including the network and machine administrators, can change SQL Server's authentication mode. If you install SQL Server with Windows Authentication Mode, sa is disabled but still has a blank password. If you then change SQL Server's authentication mode to Mixed (thus enabling the sa login), anyone can log in as sa. So, make absolutely sure you either change the sa password as soon as you finish the installation or choose Mixed Mode and provide a password for sa during the installation.
Collation
Next, you need to choose collation settings. Collation settings in SQL Server 2000 govern language-related behavior, uniqueness of object names and column values, and sorting rules. In the Collation Settings dialog box, which Figure 7 shows, you specify collation settings and choose either a SQL Server collation or a Windows collation. Choose SQL Server collation if you need backward compatibility with previous releases of SQL Server—for example, if you're going to use replication between SQL Server 2000 and a previous SQL Server release. Otherwise, choose a Windows collation. SQL Server 2000's collation settings, both Windows and SQL Server, merge three independent settings that existed in previous releases: Character Set, Sort Order, and Unicode Collation. Besides encapsulating the older three settings into one, SQL Server 2000 also gives far greater flexibility in the collation settings than previous releases. A discussion of the older settings is out of the scope of this article, but you can find the details in SQL Server 7.0 and 6.5 Books Online (BOL).
The collation settings you choose during SQL Server 2000 installation determine the collation settings of the system databases. To change the collation settings of the system databases after installation, you need to script all your system objects (e.g., logins, messages, jobs) and run rebuildm.exe, which rebuilds all the system databases with new collation settings. However, you don't need to export all the data in the user databases out of SQL Server and import it back after running rebuildm.exe, as you do in SQL Server 7.0. You only need to reattach the user databases to SQL Server. You can configure your user databases with a different collation than the default server's collation (that of the model system database) or even attach or restore a database with collation settings different from the server's settings. You can alter a user database's default collation later on. For a specific column, you can specify a collation that's different from the default, which is the database collation; you can even alter the column's collation later if no index has been created on the column.
Prev. page
1
[2]
3
4
next page 
