SQL Server Options

I will discuss options when licensing, the security available to you, we will look in more detail at Named and Multiple Instances, and I will also talk briefly about Network Libraries and SQL Server options for collations and sort rules.

SQL Server Licensing

As you approach the end of the installation process as described in the previous chapter you will be prompted to select a licensing mode and then accept the terms and conditions of the licensing agreement.

With SQL Server 2000 you have two licensing modes:

• Per Processor Licensing; with this type of license you need to acquire a license for each processor running SQL Server 2000. This allows for an unlimited number of users to connect to the database server either from a LAN or WAN or internet connection. This is the most cost effective license for enterprise level applications or high transactional websites that has a large number of users.
• Per Seat Licensing; with this type of license you acquire a "seat license" for each SQL Server and a "client access license" or CAL for each workstation that connects to an instance of SQL Server either through Microsoft products or alternatively through third party software. This type of licensing is more appropriate for small organisations with a smaller user base.

Security of the Services Account

The SQL Service runs in the context of an assigned login account which can either be a domain user or the local system account.

I would recommend that a dedicated account be created to run the both the services (database engine and agent) as this will avoid confusion while carrying out administrative work.

By using a domain login account the SQL Server and the agent can communicate with other servers (remote servers) by using windows authentication. If you select a domain user account the SQL Server setup grants the right to that account to logon as a service on the Server running the SQL Server.

It is most appropriate to use a domain user account when:

• SQL Server must access other files on other servers in the domain
• You plan to configure multi-server jobs
• You want utilise SQL mail

If you selected the local system logon when installing SQL Server the service will not be able to communicate with remote servers that use Windows trusted connections.

AutoStart Services

During the installation SQL Server configures the SQL Service to start automatically when the underlying operating system is started.

After the installation has completed successfully you configure all the services installed to run either manually or automatically.

I would recommend they start automatically, this saves administrative effort by not having to login and start them all manually when the server is restarted.

Named and Multiple Instances

Another of the SQL server options you have is the option of installing named and multiple instances of SQL server on one computer.

When installing SQL Server you can specify the installation as either default or named instance.

Default Instance

The default instance can be identified by the network name of the computer on which it is running.

So for example if your server is called "MyServer" then the SQL Server installation on this machine can also be referred to as "MyServer". There can only be one default instance on any one server.

Named Instance

If you have specified a named instance during your installation, then this is identified by the name of your server, for example "MyServer", plus an instance name "MyInstance".

The format will look like this:

<ServerName><InstanceName>

In our example above the named instance will be referred to as:

<MyServer><MyInstance>

You can run up to 16 named instances on your server at one time. Although I would not recommend this as it is very likely to have an adverse affect on performance and can also become very complicated very quickly from an administrative point of view.

A named instance of SQL Server 2000 can run on the same server as an existing installation of SQL Server 6.5 and SQL Server 7

More SQL Server options:

Multiple Instances

Multiple Instances describe the situation where you have a default instance installed and also one or more named instances running concurrently.

Each instance sits on the same server but runs independently, each has their own services, directory and registry structure and the names of the service reflect the name of the specific instance.

SQL Server Security

When choosing SQL Server options during the installation process you must select a security mechanism for your SQL Server installation. There are two modes of security:

• Windows Authentication Mode; when Windows authentication is selected SQL Server will only uses the Windows Operating System security mechanism. To be able to connect to a SQL Server a user must have a valid Windows domain account and receive validation from the operating system.
• Mixed Mode; this modes allows a user to connect to the SQL Server by using combination of the windows authentication mode described above and also by using a SQL Server login account (SQL Server authentication) when installing using "mixed-mode" you must enter a password for the sa account or tick the box to leave blank. I would not recommend leaving the sa password blank. Mixed mode allows the database to be used by non-Windows based applications and users like websites and UNIX applications etc.

SQL Server Collations and Sort Rules

As part of the SQL Server options during the installation process you have the option of setting the collation and select the sort order rules. Collation refers to the rules that define how data is compared and collated.

Network Libraries

Network libraries are used by SQL Server to communicate with a specific network protocol and pass packets between a client and the database engine. Microsoft states that SQL Server supports the following network libraries:

• TCP/IP Sockets
• Named Pipes
• Unlink IPX/SPX
• VIA ServerNet II SAN
• VIA GigaNet SAN
• Multiprotocol
• Apple Talk ADSP
• Banyan Vines

Conclusion

This article describes some of the SQL Server options that can be set during the SQL Server installation process.

There are a number available to you and you should select the SQL Server options most appropriate to your needs. The remainder of this series of articles will look at how to configure your installation of SQL Server, the installation methods available to you.

I will discuss how to approach upgrading your version of SQL Server, verifying your SQL Server installation configuring the client tools particularly enterprise manager and query analyser and I will discuss troubleshooting your installation.

Return to installing the SQL Server database