Installing and configuring SQL Server Instances

Installing and configuring SQL Server Instances

In this review the following topics will be covered:

• Creation of service accounts
• Installation of an SQL Server 2008 instance
• Collation sequences
• Authentication modes
• Installation of sample databases (next issue)
• Configure a SQL Server Instance (next issue)

Service Accounts

Core SQL Server components run as services. Each component should be configured, for security reasons, with several service accounts, experts recommend using an account for every separate service. Each one should have it own set of administrator rights. You will need dedicated service accounts at least for the following components:

• Database Engine
• SQL Server Agent

Each service account provides for SQL Service both data and scheduling services and also defines a security boundary. Each SQL Service services will be granted with permissions to use all the resources needed by the instance (memory, processors, disk space, and networking). The service account should grant all permissions needed by SQL Server Database Engine (for example) for it to accomplished all required tasks.

Later we will discussed SQL Server Security with deeper review.

Remember when choosing service accounts this ones should be local accounts for a local instance and domain accounts if local machine is registered within a domain (the service account should be a registered domain account with all permissions needed granted).

Collation Sequences

Collation sequences control how SQL Server deals with your data. Natively it will work with a previously default collation sequence, it is a SQL Server instance level collation sequence (it could be an instance, database, table or even a column level collation sequence, each of these could be selected at design time). SQL Server default instance collation sequence will be selected at installation time.

The instance collation is mandatory and it will be overridden anytime in databases, tables, or columns when necessary.

By definition collation sequences treats data at character level in special way so storage, retrieval, sorting and comparison operations can be fulfilled without any trouble regarding the culture involved. So to speak if we use a collation sequence that is Case Insensitive and Accent Insensitive (CI & AI) will store, retrieve, sort and compare the way that we do in English language. If we chose AS (accent sensitive) and CS, we are using a French Collation to storage, retrieve, sort and compare data. If we are talking of "e" character, in French we can see e, é, E, É, è, È are treated differently.

Authentication modes

Authentication modes are set either to:

• Windows Only (integrated security)
• Windows and SQL Server (mixed mode)

When SQL Server is set with Windows-only authentication, use only Windows accounts to log in to the instance. If SQL Server is set in mixed mode, you can use either Windows accounts or SQL Server–created accounts to log in to the SQL Server instance anytime.

Best regards,

Jaime Gibertoni