Section 19.1. Overview of SQL Server

19.1. Overview of SQL Server

As with any piece of complex software, SQL Server comes in multiple configurations. Based on a particular organization's needs, the appropriate edition can be selected to minimize both cost and extraneous components. SQL Server 2005 comes in four primary editions and two specialized editions for a total of six from which to choose. They include the workgroup edition, standard edition, enterprise edition, developer edition, mobile edition, and express edition. Consult Microsoft documentation for the differences between the editions.

19.1.1. Connecting to and Administering SQL Server

SQL Server 2005 can be administered from the GUI or from the command line. Versions up to 2000 are administered using the Enterprise Manager, and 2005 is administered with the SQL Server Management Studio (SSMS). This tool combines many of the previous individual tools, such as the Query Analyzer and the Analysis Manger, into one. For those who prefer the command line, a new program called SQLCMD. EXE is provided in 2005. This tool replaces OSQL.EXE and ISQL.exe from previous versions. In addition, the bulk data import and export program, BCP.EXE, is included to facilitate faster exports and imports of data to and from SQL Server databases.

19.1.2. SQL Server Authentication

Because of its integration with Active Directory, there are multiple ways to authenticate database users.

19.1.2.1. User authentication

SQL Server offers user authentication either through Windows Active Directory integration or from SQL Server user accounts. There is greater control over access when using Active Directory-integrated authentication, either through permissions on specific user accounts or through group permissions.

19.1.2.2. Service authentication

The actual SQL Server service has two different types of authentication available that allow the service to start:

Domain accounts

Consider starting the service with a domain account if you manage multiple servers or your installation needs access to network resources. If using multiple servers, security administration can be simplified through domain policies, allowing preferences to propagate to specified or all servers. If access to network resources is needed, your installation needs a domain account to access shared network drives or other network resources.

Local system account

A local system account should be used if your installation does not need access to network resources or to be authenticated against the domain. This is useful to isolate the server from the rest of the domain, which is normally done for security considerations, such as a server on an insecure network.