SQL Server 2008 comes with a new tool called System Configuration Checker that  can potentially report any Installation or Upgrade related problems.

Author: Saleem Hakani (Saleem@sqlcommunity.com)

System Configuration Checker performs the following checks and reports any potential problems that could prevent the installation of SQL Server 2008.

·       �Checks If the SQL Server registry keys are consistent or not

·       �Checks whether the path of SQL Server installation is too long

·       �Checks whether the computer meets the minimum O/S version requirements

·       �Checks if a pending computer reboot is required or not.

·       �Checks whether the account running SQL Server Setup has Administrator rights or not

·       �Checks whether the WMI service is started and running on the computer or not

·       �Checks for the previous releases of SQL Server 2008 Business Intelligence Studio

·       �Check if there’s any previous CTP installation of SQL Server 2008

·       �Checks if the computer is a domain controller

·       �Checks if Windows PowerShell is installed or not

·       �Checks if the SQL Server setup is supported on the current O/S platform

·       �Checks if the performance counter registry hive is consistent or not

·       �Checks if SQL Server 7.0 or OLAP 7.0 services is installed

·       �Checks if the computer is connected to the internet as it needs to perform .NET security check to validate a certificate.

If any of the above checks fail, you can click on the “Failed” link from the status column to find out the reason of the failure. Alternatively, you can click on the “View detailed report” to find out the success and failure details of each checks performed by System Configuration Checker (SSC)

Permissions Required for SQL Server 2008 Folders

Author: Saleem Hakani (Saleem@sqlcommunity.com)

During the installation of SQL Server 2008, setup program grants the following folder permissions to the service account running SQL Server Service and SQL Server Agent service as listed below: (Instid is the id number of the SQL Server instance)

MSSQLServer Folder	Permissions
Instid\MSSQL\Backup	Full Control
Instid\MSSQL\Binn	Read, Execute
Instid\MSSQL\Data	Full Control
Instid\MSSQL\FTData	Full Control
Instid\MSSQL\Install	Read, Execute
Instid\MSSQL\Log	Full Control
Instid\MSSQL\Repldata	Full Control
100\Shared	Read, Execute
SQLServerAgent Folder	Permissions
Instid\MSSQL\Binn	Full Control
Instid\MSSQL\Log	Read, Write, Delete
Instid\MSSQL\Log	Execute
100\com	Read, Execute
100\shared	Read, Execute
100\shared\ErrorDumps	Read, Write

Once you have determined and have gone thru all the security requirements, you are now ready to start the installation of SQL Server 2008. SQL Server 2008 installation goes thru a series of steps that requires very minimal understanding. Thanks to the SQL Server Development team who have worked hard on making the installation process easy and straightforward.

Network Software Requirements for SQL Server 2008

Author: Saleem Hakani (Saleem@sqlcommunity.com)

Although SQL Server 2008 network software requirements are fulfilled by the operating system on which SQL Server is being installed, it’s always wise to check and confirm that the below network software’s are installed on the computer where you wish to install SQL Server 2008.

Network software supported by SQL Server 2008 is listed below:

1)  �Shared Memory: Shared memory protocol can only connect to a SQL Server instance running on the same computer) Many DBA’s use this protocol to connect to their local SQL Server when they experience problems with other network protocols to connect to SQL Server.

2)  �Named Pipes: This protocol is mostly used in a Local Area Network environment.

3)  �TCP/IP: Transmission Control Protocol and Internet Protocol is a common protocol widely used over the internet. It offers standards for routing the network traffic to appropriate destination and offers truly advanced security features not found in any other network protocol that are supported by SQL Server.

4)  �VIA: Virtual Interface Adapter protocol works with specific VIA hardware only. You must contact your hardware vendor on how to use this network protocol.

Software Requirements for SQL Server 2008

Author: Saleem Hakani (Saleem@sqlcommunity.com)

SQL Server 2008 Editions and their features

Author: Saleem Hakani (Saleem@sqlcommunity.com)

Editions	Features
SQL Server 2008 Enterprise Edition	SQL Server Enterprise is a comprehensive data platform that provides enterprise-class scalability, performance, high availability, and advanced business intelligence capabilities for running secure, business-critical applications.   Some of the features supported by the enterprise edition are: Partitioning, Data Compression, Resource Governor, Partition Table Parallelism, Database Snapshots, Fast Recovery, Online indexing, Online restore, Mirrored Backups, Hot add memory, Hot add CPU, Backup compression, Fine grained auditing, Transparent database encryption, Distributed partition views, Parallel index operations, Change data capture, and many other features that make SQL Server a truly enterprise product.
SQL Server 2008 Standard Edition	SQL Server Standard is a very good choice for mid-size companies that provide ease of use and manageability. This includes built-in business intelligence capabilities to run departmental applications.   None of the above features mentioned in the enterprise edition are supported with Standard edition of SQL Server.
SQL Server 2008 Developer Edition	Note: Developer edition MUST NOT be used in a production environment.   SQL Server 2008 Developer edition allows developers to build any kind of application on top of SQL Server. It includes all the functionality of SQL Server 2008 Enterprise, but is licensed for use as a development and test system, not as a production server. SQL Server 2008 Developer is an ideal choice for people who build and test applications.   You can upgrade SQL Server 2008 Developer to a Standard or Enterprise Edition for production use.
SQL Server 2008 Workgroup Edition	Workgroup edition of SQL Server is good for running franchise or branch location databases. (ex: Restaurant chain, Hotel / Motel chain) This edition of SQL Server provides a reliable database management and reporting platform tha includes: Secure, Remote Synchronization and SQL Server management capabilities.
SQL Server 2008 Web Edition	Web edition of SQL Server 2008 is a good candidate for web hosting companies that require database systems. This is a very low total-cost-of-ownership for web hosting companies and websites running SQL Server 2008.
SQL Server 2008 Express Edition	SQL Server 2008 Express Edition is a replacement of Microsoft Database Engine (MSDE) which also comes integrated with Visual Studio. This edition of SQL Server is supplied free of cost and can help in developing rich data-driven applications at no cost.
SQL Server 2008 Express With Advanced Services	SQL Server 2008 Express Edition with Advanced Services has all the features of Express edition and is a good choice for building small server applications and can be distributed freely by independent software vendors (subject to agreement)
SQL Server 2008 Compact Edition	This edition of SQL Server is supplied free of cost for building stand-alone and occasionally connected applications for mobile devices, desktops and web clients.

Software Requirements for SQL Server 2008

Author: Saleem Hakani [Saleem@sqlcommunity.com]

SQL Server 2008 is no longer just a database system but instead it’s a Data Platform that provides rich services that include: Relational Engine, Analysis Services, Integration Services and Reporting Services. Installation of SQL Server 2008 has some software requirements that need to be fulfilled before the setup program is kicked off.

Below are the software requirements for installing SQL Server 2008:

·       �.NET Framework 3.5 Required

o �If you are running SQL Server 2008 on a Windows Server 2003 (64-bit IA64 machine), at a minimum you would need .NET Framework 2.0 SP1. All others would require a minimum of .NET 3.5 Framework installed on the computer.

·       �Windows Installer 4.5 or later version is required to run the setup program for SQL Server 2008.

·       �MDAC 2.8 SP1 or later is required for installing SQL Server 2008.

·       �IE 6 SP1 (Internet Explorer 6.0 with SP1) or later is required for using SQL Server Management Studio, Business Intelligence Development Studio, Report Designer, HTML help, etc. Without IE6 SP1 or later these tools may not function.

It is important to identify what features or services your business requires apart from the relational engine of SQL Server. You should obtain this information before you begin the installation of SQL Server. However, You can always install other SQL Server services as listed below at a later time.

SQL Server Database Engine: This is the core engine behind SQL Server and is required for running SQL Server Relational Database Engine.

SQL Server Agent: This service helps with many SQL Server administrative tasks that include: SQL Server Job scheduling and executing, Monitoring and managing alerts for SQL Server.

Analysis Services: This service helps with Business Intelligence applications that provide analytical processing and data mining.

Integration Services: This service provides the support for data extraction, transformation and loading (ETL).

Browser Service: This service provides information about the instances installed on the computer by listening to all incoming client requests that accesses SQL Server resources.

Full-Text Search Service: This service helps with indexing the content of structured and semi structured data in SQL Server.

Active Directory Helper Service: This service helps in publishing and managing SQL Server objects in Active Directory.

Best Practices for selecting Network Protocol for your SQL Server

Many-a-times people ask which network protocol between TCP/IP and Named Pipes is best to use. The answer to this question depends on your network environment whether you are on a local computer with no networks or whether you are on a Local Area Network or whether you are on a Wide Area Network. Below list is a recommendation that works best for many SQL Server installations.   On local server, use Named Pipes: If you are running your server application on a local computer which also runs SQL Server 2008 then Named Pipes protocol is your best choice as they get loaded in kernel mode of the operating system and are much faster on the local machine. Note: This is only true if both the application and SQL Server are running on the same machine and if not then it would use Network Pipes that can be used over Local Area Networks.   On a Local Area Network, use TCP/IP As long as your Local Area Network is fast, both TCP/IP and Named Pipes would perform with the same speed. However, if there are more requests waiting to hit the server then Named Pipes may encounter pipe-busy error when trying to connect to SQL Server whereas, TCP/IP sockets doesn’t experience this problem as it supports�backlog queue for any number of requests.   On a Wide Area Network, use TCP/IP Data transmission using TCP/IP sockets is much more streamlined and has relatively less overhead compared to other network protocols. TCP/IP sockets can also take advantage of many performance features supported by the operating system that includes windowing, delayed acknowledgement which can be very helpful on slow networks.

SQL Server by default listens on all the enabled protocols and client computers or applications can connect to SQL Server by using any of the above network protocol at the same time. You must configure your client application to connect to SQL Server based on your SQL Server environment and based on the best practices provided above.

Disk Requirements for SQL Server 2008

Author: Saleem Hakani (Saleem@sqlcommunity.com)

Disk estimations are not easy to make as disk requirements vary depending on various different factors that include:

·       �SQL Server features/services being installed

·       �User database growth

·       �TempDB database growth

·       �Application type, etc.

Answering the above questions could help make the appropriate decision for disk requirements.

However, this section provides you with the minimum disk space requirements for the installation of SQL Server 2008.

Note: SQL Server 2008 requires at-least 2 GB of disk space on the Windows system drive for storing temporary files created by Windows Installer. (This requirement excludes user and system databases)

Below table provides the minimum disk requirements for the following SQL Server 2008 features:

SQL Server Feature Installation	Estimated Disk Space Requirements in MB
SQL Server Database Engine, system data files, Replication and Full-Text Search	300 MB
Analysis Services and data files	90 MB
Reporting Services	120 MB
Integration Services	120 MB
Workstation Components	850 MB
Books Online	240 MB

*This excludes the disk space required by .NET 3.5 SP1, Windows Installer 4.5, and MDAC 2.8 SP1, etc.

Security Requirements for SQL Server 2008

Author: Saleem Hakani [Saleem@sqlcommunity.com]

Before you begin the installation, you must determine what type of security your business requires for running SQL Server 2008 services.

There are two types of security you can think of with SQL Server 2008: Physical and Logical security and both of them should be tightly secured to prevent unauthorized access of SQL Server. SQL Server Database engine is actually a service that runs on the operating system. During the installation of SQL Server, you will be asked to provide the account on which SQL Server services need to run.

SQL Server and SQL Server Agent service can run using the following account types:

1)  �Domain user account

2)  �Local Service Account

3)  �Local user account

4)  �Network Service Account

You must determine what type of account you would like to use for running SQL Server services.

Important: Only an administrator or a member of the administrator’s group MUST run the SQL Server setup program. This is because SQL Server setup program grants several permissions to the service account and folders where SQL Server system files would be stored.

Domain User Account

If you plan to have your SQL Server computer connected to a corporate network for interacting with other network services, access domain resources like file shares, connect to a remote SQL Server using Linked Servers then the best option is to use a minimally privileged domain user account for your SQL Server computer. Many server-to-server activities can be performed only with a domain user account.

Local Service Account

The Local Service account is a built-in account that has the same level of access to resources and objects as members of the local users group. This limited access helps safeguard the system if individual services or processes are compromised. Services that run as the Local Service account access network resources as a null session without credentials. Be aware that the Local Service account is not supported for SQL Server Agent.

If the computer is not part of a domain, for example, a server running in the perimeter network of a Web application, a local user account without Windows administrator permissions is recommended.

Network Service Account

The Network Service account is a built-in account that has more access to resources and objects than members of the local Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account.

Best Practices for using SQL Server Service Accounts

Securing SQL Server is one of the top priority job for any DBA. Making sure that the SQL Server is not exposed to users other than authenticated is challenging and depends on the type of account you use for running SQL Server services.

When to use Domain User Account?

If your SQL Server interacts with any other servers, services or resources on the network (ex: File Shares, Printer, etc) or if uses linked server to connect to other SQL Servers on the network then you may use a low privileged domain user account for running SQL Server services.

When to use Local Service Account?

Never, Though you can run SQL Server service using Local System Account but it is not supported by SQL Server and SQL Server Agent services.

When to use Local User Account?

If your SQL Server is not a part of any domain and if it doesn’t interact with any other servers, services or resources on the network then using a Local User Account without Local Admin privileges is recommended.

When to use Network Service Accounts?

Never, Network Service accounts are shared with other services on the computer. If you have configured SQL Server services using Network Service accounts than there’s a possibility that some other service running on the computer using the network service account can access the resources of SQL Server.

SQL Server 2008 Service Account Permissions

Author: Saleem Hakani [Saleem@sqlcommunity.com]

SQL Server setup program not only installs SQL Server on your computer but would also grant the following permissions to the service account specified during the installation of SQL Server 2008:

1)  �Log on as a service

2)  �Log on as a batch job

3)  �Replace a process-level token

4)  �Bypass traverse checking

5)  �Adjust memory quotas for a process

6)  �Permission to start SQL Server Active Directory Helper service

7)  �Permission to start SQL Writer

8)  �Permission to read the Event Logs from Event Log service

9)  �Permission to read the RPC (Remote Procedure Call service)

SQL Server setup program would grant the following permissions to the service account for SQL Server Agent service:

1)  �Log on as a service

2)  �Log on as a batch job

3)  �Replace a process-level token

4)  �Bypass traverse checking

5)  �Adjust memory quotas for a process

Note: SQL Server Agent service by default is disabled on SQL Server 2008 Express and SQL Server 2008 Express with Advanced Services.