SQL Server 2005 : In a nutshell (Part 3)

SQL Server Versions

– Development (Not suitable for production. Tends to be used by developers).
– Enterprise (Full product that includes business Intelligence and high availability features)
– Standard
– Workgroup (No BIDS)
– Express (FREE limited feature light weight version).
– Mobile Edition (Small storage capacity for mobile devices)

Installing SQL Server

As we’ve discussed, different versions of SQL Server can be installed side by side on the same host. All that happens is that several versions of the same service (sqlservr.exe) are installed and operate independently of each other (e.g. SQLServer Service (SQLEXPRESS) and SQL Server Service (2005) can both serve database clients from the same box).  When installing SQL server 2005 the installer utilizes the native windows installer version 3.0 (installed with Win Server 2003 and XP SP2 by default) which means the customized selection of installable components is all built into the install package.  Making the upgrade from an existing SQL 2000 install is very simple and for specific installed features such as Analysis Services and Integration Services (DTS on SQL 2000) there are wizards in place for feature specific migration (e.g. Analysis Services Migration Wizard, Integration Services Migration Wizard).  Out of all upgradable features, Reporting Services is probably the most complex migration that takes place. The reporting server instance involves a fair amount of upgrade steps including the upgrading of the Report Definition Language (see books online for further information).

This release of SQL Server relies heavily on the .NET Framework, more specifically version 2.0 so this must be installed as a pre-requisite.  Other pre-requisites which the system configuration checker runs tests for include the MSXML runtime, IIS 5.0 and ASP.NET is needed for reporting services.  The .NET 2.0 SDK is not installed with SQL Server 2005 and the sample databases must be downloaded from Microsoft.

Installation Steps (the quick reference list)

1. Choose an instance name (default – host name, named instance – host name\instance name)
2. Configure SQL Service accounts (the user accounts for which the various SQL services should run under. Anything running on windows must authenticate against the security subsystem. SQL Server does this using a windows account or the local system account). You can use the same account for all services, or choose an account per service.
3. Select an authentication mode (Windows Integrated or Mixed Mode, accepting both SQL and Windows logons).
4. Configure collation is selected (which code pages to use) – If using Windows Collation, setup looks to Windows for this information.  SQL Server collation can be chosen also.
5. Report Server Install – You can select the default configuration (which takes the instance name and adds to the HTTP address – http://computername/ReportServer$SQLINSTANCENAME) or specify your own configuration. Note that ReportServer$SQLINSTANCENAME is a physical database created on the database server for the reporting server to use.
6. View the install log for all installed features (viewable via the installer).

One last point on SQL Server installation. It should be seen as ‘adding a server role’ to your existing core server so administrative rights to create files and folders in Windows (on the NTFS file system) are required.

A quick note about services

Services are essentially resident programs. They must run all the time.  Services are generally split into 2 types, local and network services. Anti-virus software and WMI (windows management instrumentation) are examples of a local service as they execute and deal with files locally. DHCP client or DHCP server are an example of a network service as they work primarily with network traffic.
Services unlike standlone programs must authenticate themselves against the security subsystem as a valid account (prior to a user manually logging in). Programs run as standalone however are authenticated by the logged on windows user therefore each SQL Service account must logon to the sub system with the accounts provided during installation.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s