Planning to Install SQL Server 2005

Different organizations have different application needs and hence installation requirements also vary. The different editions of Microsoft SQL Server cater to the varied needs and accommodate the performance, runtime and price requirements of organizations and individuals. The components that are installed on the server also will depend on the needs defined by the entity.

Selecting an edition of SQL Server 2005 to Install

SQL Server 2005 is available in a number of editions. However, organizations with mission critical information and large databases often select among three editions of the MS SQL Server 2005—the Enterprise Edition, the Standard Edition and the Work group edition. Additionally Microsoft also has come out with the Mobile edition and the Developer edition.

Each edition is directed towards fulfilling a particular need. Before installing an edition of SQL Server 2005, it is important to understand the differences between the editions and select the one that is most suitable to the needs of the organization.

The Enterprise Edition.

This edition is intended to serve high level scalability and availability needs of organizations with mission critical applications. This edition contains all the features of the standard edition plus more. The significant features are:

· Failover clustering.
· Database mirroring.
· Snapshot databases.
· Mirrored backups.
· Online page and file restore.
· Distributed partitioned views.
· Heterogeneous replication.
· Peer-to-peer replication.

This edition supports online transaction processing, highly complex data analysis, data ware housing systems and web sites. It has a comprehensive Business Intelligence capability and is ideal for large organizations. It is also available in a 120 day evaluation edition for the 32 bit or the 64 bit platform.

The Standard Edition

This edition is intended for disconnected clients and stand alone applications. It is recommended for small and medium sized organizations. It provides all the functionalities required for ecommerce, data warehousing, and line of business solutions. The Integrated Business Intelligence features provide the basic features required for the purpose.

SQL Server 2005 Workgroup Edition

The Workgroup Edition provides data management solutions for small organizations. It can also serve as a front end web server for a branch or department of the organization. It has all the core database features and can be upgraded to the standard or the Enterprise edition. It is robust, reliable and easy to use.

The Mobile Edition

This edition is used in mobile devices and provides enterprise data management for smart devices. It is capable of replicating data with SQL Server 2005 and 2000 and allows users maintain mobile data store so that it can be synchronized with enterprise data as and when required.

The Developer Edition

As the name suggests this edition is a development and test system. This edition can be used to develop applications and test the same. This edition is capable of being upgraded to Enterprise edition for production.

SQL Server 2005 Express Edition (32-bit only)

This edition comes inbuilt into Microsoft Visual Studio 2005. It is easy to develop secure data driven applications with this edition. It is free and can be redistributed and functions as a client database as well as a server database. It is an ideal choice for low end server users, non professional developers and hobbyists.

Hardware Requirements

The computer which is to host the server must have the necessary hardware to support it. The minimum hardware requirements are:

1. The Processor: Intel Pentium or compatible 600 megahertz or higher processor(recommended 1 ghtz)
2. Memory: 256 megabytes of memory (recommended 512 mb)
3. Disk Space: 95-300 MB of disk space minimum. Typical installation required 250 MB disk space
4. Monitor: screen resolution of 1024 x 786
5. Pointing Device
6. Network adapter
7. CD/DVD drive for installation from disk.
8. Cluster installation requirements: 8 node cluster installations are supported in 32 and 64 bit platforms

Software Requirements

The minimum software requirements as defined by Microsoft are:

Operating system: Windows Server 2003 (Enterprise or embedded), Windows XP with SP1, Windows 2000 Professional with SP 4 and Windows 2000 Server SP4.

SQL Server 2005 does not support Banyan VINES, Sequenced Packet Protocol, Multiprotocol, AppleTalk or NWLink IPX/SPX network protocols.

If the instances are stand alone named or default instances network protocols such as Shared memory, Named pipes, TCP/IP and VIA. Shared memory is not supported on failover clusters.

Other software requirements are:

1. Microsoft Windows Installer 3.0,
2. Microsoft Windows .NET Framework 2.0,
3. Microsoft SQL Server Native Client,
4. Microsoft SQL Server Setup Support files.
5. MDAC 2.8 if SQL Server Reporting services is being installed on Windows 2000
6. MDAC 2.6 or higher is required if SQLXML is being installed on Windows 2000.
7. Microsoft Cluster Server on one node of the server if failover clusters are being installed.

There are certain limitations which affect installations on supported operating systems. Support for Native Web Service is not available for SQL Server 2005 instances running on Windows Server 2003. MSCS software is supported only if it is installed on hardware configuration that has been tested for compatibility with MSCS software.

Collation

Fundamental to the structure and function of the Microsoft SQL Server databases is the collation settings. This includes Character set, sort order and other locale specific settings. Each organization has its own standards for collation settings and this is defined during setup. This is crucial as server to server activities can fail or yield inconsistent results if not set correctly. It is important to select the collation settings before installing SQL Server.

SQL Server 2005 supports collation settings at Server level, database level, column level or Expression level. The default collation settings must be changed only if the SQL Server installation requires that the settings must match the collation settings of another SQL Server installation or another Windows system locale in another computer.

The collation designator is used to select a specific windows collation. Customized collation settings can be specified for each service.

Sort Order

Sort order options are to be selected with the collation designator. This may be Binary which is very fast and case sensitive. This is based on bit patterns for each character and maps to a specific language locale and ANSI code. SQL collations are used for compatibility with earlier versions of SQL Server.

SQL Server 2005 provides newer collation versions to improve collation behavior. It provides supplementary character comparison for Far East collations and a new Indic_General_90 collation version has been added to support changes to sorting order. Other enhancements that are intended for use with Windows server 2003 are listed below

Unicode support

The Characters of a language are represented with two bytes to enable Unicode Character set to represent all the written languages of the world. This is promoted and maintained by the Unicode Consortium. SQL Server 2005 offer support for the same. Unicode data types include nchar, nvarchar and ntext. Users experience a significant performance gain through use of Unicode as fewer code page conversions are required. Unicode clients that interact with the SQL Server are OLE DB and Open Database Connectivity versions 3.7 or later.

Network libraries

Packets are passed between clients and servers running SQL Server using Network libraries. These are implemented as dynamic link libraries and perform all the operations required to activate specific inter-process communication mechanisms.

Multiple network libraries can be monitored or listened to by a server at any given point of time. By default SQL Server 2005 setup installs some network libraries on the computer. These libraries can be configured to enable the server listen on that library. The configurations can be changed using the Server Network utility.

It is recommended that the SQL Server instance be configured to use a dynamic port address by specifying the Port address of 0 using the Server Network Utility, else the port will not open if another application or component is using the port when SQL Server Service initializes. It is further recommended that wherever it is not possible to use a dynamic port address, then, a port address less than 1024 should be used.

User accounts

SQL Server 2005 services cannot be started or run without user accounts. User accounts can be built into the system or can be domain user accounts. When SQL Server is setup certain default user accounts are set up as defined in the table below.

A domain user account is used when the service must interact with network services. All server to server activities such as Remote procedure calls, replication, backup of network drives and remote data source joins or SQL Server Agent mail will require a domain user account.

User account properties for an SQL Service such as passwords can be changed using the SQL Server Management Studio.

Default and named instances

Named or default instances are created at setup with their own set of data files and a set of common files that are shared by all instances in the computer. Unique instance IDs are generated for each component within a given SQL Server instance and this allows users isolate install locations for each component and upgrade of a single component is possible without impacting on other server components.

The common files used by all instances on a computer are located in the folder C: (the system drive)\Program Files\Microsoft SQL Server\90. Normally the instance IDs are generated as MSSQL.1, MSSQL.2…etc. If any uninstalls are made in between gaps can occur as ids are not reassigned/generated to fill the gaps.

Server components are by default installed in directories having the format \ . The user can choose to change the path of the default installation directory.

A registry hive is created under HKLM\Software\Microsoft SQL Server\MSSQL.n for instance aware components and a map of the instance ID to the instance name is maintained.

Installation path

The path of installation is displayed during setup for various features along with a configuration file. The user must make sure that the folder is protected with limited permissions. The default drive is normally the C: drive, but a different drive can be specified. Child features can be installed only under the drive which hosts the parent features. Installation paths will have to be shared between SQL Server Integration services, Notification services and Client components. If the installation path for one component is changed, the path for the others is automatically changed. All subsequent installations will also install components to the same location. The directory will be used by all instances of the SQL Server on the computer and hence any changes to the file in the computer will impact all instances.

If a clustered configuration is contemplated, a local node available on every computer of the cluster must be selected.

When an installation path is specified during setup the instance name is used in addition to the specified location for program and data files. Setup does not use the instance name for tools and other shared files or for the Analysis Services program and data files. If an installation path is set for the Database engine then, that becomes the root directory for all instance specific folders for the installation. When USESYSDB upgrade functionality is used in the SQL Server Installation wizard the installation path should be set for the SQL Data files feature instead of the Database Engine feature.

Selecting components

When the default configuration settings are selected the following features are not enabled:

1. Integration services
2. SQL Server Agent
3. SQL Browser
4. Full text Search

Therefore, if the organization has specific needs it is better to select the required components while installing SQL Server 2005.

The Feature Selection Page of SQL Server Installation Wizard prompts the user to select the components that have to be included in the Server installation. There is no default selection of features. Users will have to select the Server components that best suits their needs.

The SQL Server Database Engine includes the Database Engine, the core service for storing, processing and securing data, Replication, full text search and all the tools required for managing relational and XML data.

The SQL Server 2005 Analysis Services provides tools for creating and managing online analytical processing and data mining applications.

The Reporting services has all the server and client components for creating, managing and deploying tabular, matrix, graphical and free form reports. It is also useful in development of reporting applications. It may be noted that Reporting service installations require Internet Information Services 5.0 or higher and Internet Explorer 6.0 with Service Pack 1 or higher.

Notification Services platform can be selected if the development and deployment of applications for sending personalized notifications is a requirement.

Integration services can be selected if graphical tools and programmable objects for moving, copying and transforming data is a need.

The Client components that need to be selected and installed during setup will again be determined by the needs of the organization.

Connectivity components may be required for communication between clients and servers, including SQL Native Client(SNAC), Network libraries for DB-Library, ODBC and OLE DB.

Management tools such as SQL Server Management Studio will be required for managing the SQL Server 2005. It provides an integrated environment for accessing, configuring, administering and developing components of SQL Server.

The SQL Server Configuration manager will be required for basic configuration management for SQL Server services, server protocols, Client protocols and client aliases.

The SQL Server Profiler is a graphical user interface for monitoring an instance of the Database Engine or Analysis services.

Database Engine Tuning Advisor is a tool that helps create optimal sets of indexes, index views and partitions.

Development tools for Business Intelligence provide and integrated environment for Analysis services and integration services solutions.

Documentation such as SQL Server books online, Programming reference and samples can be selected if required.

Once the Database Administrator and made all the right selections for the organizations, he can go ahead and install the server. In the next lesson we shall examine the actual process of installation.