Planning to Install SQL Server 2012

General Recommendations

During the installation process, you have to make many choices. As a general guideline, it is best to familiarize yourself with their effects before installing your system. At the beginning, you should answer the following questions:

• Which SQL Server components should be installed?
• Where will the root directory be stored?
• Should multiple instances of the Database Engine be used?
• Which authentication mode for the Database Engine should be used?

The following subsections discuss these topics.

Which SQL Server Components Should Be Installed?

Before you start the installation process, you should know exactly which SQL Server components you want to install. Figure 1 shows a partial list of all the components. You will see this Feature Selection page again when you install SQL Server later in this chapter, but knowing ahead of time which components you want to select means you don’t have to interrupt the installation process to do research. There are two groups of components on the Feature Selection page: main features and shared features. This section introduces only the main components. For a description of the shared components, refer to SQL Server Books Online.

Figure 1. A preview of the Feature Selection page

The first item in the list of the main features is Database Engine Services. The Database Engine is the relational database system of SQL Server. Parts II and III of this book describe different aspects of the Database Engine. The first of the two features under Database Engine Services, SQL Server Replication, allows you to replicate data from one system to another. In other words, using data replication, you can achieve a distributed data environment.

The second feature under Database Engine Services is Full-Text Search. The Database Engine allows you to store structured data in columns of relational tables. By contrast, the unstructured data is primarily stored as text in file systems. For this reason, you will need different methods to retrieve information from unstructured data. Full-Text Search is a component of SQL Server that allows you to store and query unstructured data.

Besides the Database Engine, SQL Server comprises Analysis Services and Reporting Services, which are components related to business intelligence (BI). Analysis Services is a group of services that is used to manage and query data that is stored in a data warehouse. (A data warehouse is a database that includes all corporate data that can be uniformly accessed by users.)

Reporting Services allows you to create and manage reports.

Where Will the Root Directory Be Stored?

The root directory is where the Setup program stores all program files and those files that do not change as you use the SQL Server system. By default, the installation process stores all program files in the subdirectory Microsoft SQL Server, although you can change this setting during the installation process. Using the default name is recommended because it uniquely determines the version of the system.

Should Multiple Instances of the Database Engine Be Used?

With the Database Engine, you can install and use several different instances. An instance is a database server that does not share its system and user databases with other instances (servers) running on the same computer.

There are two instance types:

• Default
• Named

The default instance operates the same way as the database servers in earlier versions of SQL Server, where only one database server without instance support existed. The computer name on which the instance is running specifies solely the name of the default instance. Any instance of the database server other than the default instance is called a named instance. To identify a named instance, you have to specify its name as well as the name of the computer on which the instance is running: for example, NTB11901 INSTANCE1. On one computer, there can be several named instances (in addition to the default instance). Additionally, you can configure named instances on a computer that does not have the default instance.

Although all instances running on a computer do not share most system resources (SQL Server and SQL Server Agent services, system and user databases, and registry keys), there are some components that are shared among them:

• SQL Server program group
• Analysis Services server
• Development libraries

The existence of only one SQL Server program group on a computer also means that only one copy of each utility exists, which is represented by an icon in the program group. (This includes SQL Server Books Online, too.) Therefore, each utility works with all instances configured on a computer.

You should consider using multiple instances if both of the following are true:

• You have different types of databases on your computer.
• Your computer is powerful enough to manage multiple instances.

The main purpose of multiple instances is to divide databases that exist in your organization into different groups. For instance, if the system manages databases that are used by different users (production databases, test databases, and sample databases), you should divide them to run under different instances. That way you can encapsulate your production databases from databases that are used by casual or inexperienced users. A single-processor machine will not be the right hardware platform to run multiple instances of the Database Engine, because of limited resources. For this reason, you should consider the use of multiple instances only with multiprocessor computers.

Which Authentication Mode for the Database Engine Should Be Used?

In relation to the Database Engine, there are two different authentication modes:

• Windows mode Specifies security exclusively at the operating system level—that is, it specifies the way in which users connect to the Windows operating system using their user accounts and group memberships.
• Mixed mode Allows users to connect to the Database Engine using Windows authentication or SQL Server authentication. This means that some user accounts can be set up to use the Windows security subsystem, while others can use the SQL Server security subsystem in addition to the Windows security subsystem.

Microsoft recommends the use of Windows mode.