SQL Server Authentication Mode

Microsoft SQL Server supports two authentication modes; Windows Authentication and Mixed Mode. Before we move on, I would like to introduce another terminology called authentication method. Authentication method is the way a user is authenticated. Authentication mode on the other hand determines the authentication methods accepted by Microsoft SQL Server.

There are two authentication methods supported by Microsoft SQL Server. They are named Windows Authentication and SQL Server Authentication. The former relies on the Microsoft Windows operating system to authenticate users. The latter will have the user authenticated by Microsoft SQL Server based on the username and password supplied by the client application. The powerful "sa" is an example of login authenticated by SQL Server Authentication.

The following figure shows the login screen of Microsoft SQL Server Management Studio. Note the drop down list with the authentication methods offered.

Let us get back to authentication mode. The mode Windows Authentication will accept the first method namely Windows Authentication only. As for Mixed Mode, both Windows Authentication and SQL Server Authentication are accepted. In other words, logins such as "sa" will be denied if the mode Windows Authentication is active. The following figure shows the configuration of Authentication mode. You will find it at SQL Server Properties Security page.

Windows Authentication is the preferred mode because it provides the highest level of security. It is the default authentication mode for Microsoft SQL Server 2005. The authentication and account policy are enforced by Microsoft Windows operating system.

Mixed Mode is typically used with legacy systems that do not use Windows user accounts. It is also used when the environment consists of clients that need access but is running on operating systems that cannot authenticate with the Microsoft Windows operating system. This happened to clients running Mac OS, UNIX, etc. These clients could login using SQL Server Authentication.

When using Mixed Mode, extra attention has to be given to the login "sa". This account is highly privileged and could pose security threat. It is advisable to create a strong password for this account in case Mixed Mode is used.

Multiple Instances of SQL Server

You may install multiple instances (or copies) of Microsoft SQL Server 2005 on the same computer. This is applicable to installing alongside different editions as well as earlier versions Microsoft SQL Server. In other words, you can have more than one set of Microsoft SQL Server installed on one machine working independent from one another.

This capability enables us to strategize the utilization of hardware resources to achieve our needs. It allows test for different customer utilizing different SQL Server edition and version on one server. It is also useful when you host database for customers that requires full administrative control of their SQL Server instance.

Default Instance and Named Instance

There are two types of installation allowed for SQL Server installation; default instance and named instance.

The default instance is an installation where you do not give a name to the instance of Microsoft SQL Server. In this case, the instance name will take the name of the server. Hence, you refer to the Microsoft SQL Server by specifying the server name. Due to the use of server name as instance name, you are only allowed to install one default instance on one machine.

A named instance is an installation where you have to specify a name for the instance of Microsoft SQL Server during installation. The instance of Microsoft SQL Server will be referred in the form of [SERVER NAME]\[INSTANCE NAME]. You may install more than one named instance provided the name of each instances are different.

When you start Microsoft SQL Server installation wizard, it detects whether the default instance already exists. If it could not find one, you will be given the option to install a default instance or a named instance. Otherwise, you will only be allowed the option of installing a named instance.

The following figure shows the screen where you choose the type of instance to be installed. It is part of the installation process.

Conclusion

This capability leaves rooms to creatively strategize installations of Microsoft SQL Server to our needs. However, we have to be wary of the administration overhead and the duplication of components. Each instance will take up additional hard disk space, memory utilization and processor capacity.

SQL Server 2005 Editions

I came across associates installing SQL Server 2005 Standard Edition on their laptop. I would not say it is inappropriate from functionalities standpoint. The features that are present in SQL Server 2005 Enterprise edition would not likely to be required. I do not see their laptops exceeding the processor and cluster node limit either.

So what is my problem? I would say there could be a more appropriate edition for the purpose. Determining the appropriate SQL Server 2005 edition in accordance to the intention of the installation is a vital part of the installation process. Let us check out what editions are offered with their intended environment.

SQL Server 2005 Enterprise Edition (32-bit and 64-bit)

Enterprise Edition is the top edition in the series. There is no limitation in both hardware resources and functionalities. It has a complete set of SQL Server 2005 functionalities. It is designed to support the largest enterprise online transaction processing (OLTP) environments with highly complex data-analysis requirements and data warehousing.

You should select Enterprise Edition if the installation requires high processing on large amounts of data. Enterprise Edition allows unlimited processors, unlimited clustering nodes, and features that help to improve data operation performance.

SQL Server 2005 Standard Edition (32-bit and 64-bit)

The Standard Edition is a trimmed down version of the Enterprise Edition. It includes the essential functionality required for business solution, e-commerce, and data warehousing for small and medium sized organization. The Standard Edition supports a maximum of 4 processors but has no limit on memory and database size.

You may consider Standard Edition if the installation host large amounts of data but do not need the features of Enterprise Edition.

SQL Server 2005 Developer Edition (32-bit and 64-bit)

Developer Edition offers all the functionality of SQL Server 2005 Enterprise Edition but is licensed for use as a development and test system. This edition is not meant to be installed as a production server.

You should choose Developer Edition if you would like to develop application that will use Enterprise Edition but do not want to install Enterprise Edition on development or test server.

SQL Server 2005 Workgroup Edition (32-bit)

Workgroup Edition is designed for small business that would like no limits on database size and number of users. It is capable to serve departmental or branch operation, and small Web servers. There is no clustering, analysis service, and features that are meant for larger databases. There is also a limit in hardware resources. It supports up to 2 processors and a maximum of 3 GB of memory.

Workgroup Edition is right for installation with small amounts of data on smaller servers.

SQL Server 2005 Express Edition (32-bit)

Express Edition has the least features with hardware resources limit. It is however free to use and can be distributed to function as client database. Do take note that the data size allowed is limited to 4 GB.

Express Edition is meant to be desktop database. It is commonly use in client applications that requires data store such as a POS terminal.

SQL Server 2005 Mobile Edition

Mobile Edition is designed to run on MS Windows Mobile platform. It is not meant for desktop or servers. It makes good client database on mobile device very much like the Express Edition for desktop.

Summary

Decision on the right edition for a particular installation very much depends on the needed features, intended data size, and hardware you have or planned.

If you are installing on Window Mobile, you have only SQL Server 2005 Mobile Edition to choose from. If you are installing on 64-bit Windows, you may write off the Express and Workgroup Editions as they do not support 64-bit. If it is for production use, Developer Edition is out of the question.

I personally use Developer Edition for my laptop as well as development server. Why don't you identify the edition and version you have installed on your laptop or desktop and share with us?