Max Degree of Parrallism

  • Posted by Prakash chheatry
  • 10:06 PM
  • 0 comments
  • Labels: No labels

When SQL Server runs on a computer with more than one microprocessor/CPU or on SMP (a computer-architecture where two or more identical processors can connect to a single shared main memory and I/O and can perform the same functions. In case of multi-core processors, the SMP architecture applies to the cores, treating them as separate processors), it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. In other words, the max degree of parallelism setting governs the maximum number of processors that a particular query statement or index operations (index data definition language) can utilize at run time.
The default value of 0 uses all available processors. Set max degree of parallelism to 1 to suppress parallel plan generation (serial plan only). Set the value to a number greater than 1 (up to a maximum of 64) to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

What all benefit from parallel execution plan:
· Complex/Long running queries – During query optimization, SQL Server looks for queries that might benefit from parallel execution. It distinguishes between queries that benefit from parallelism and those that do not benefit, by comparing the cost of an execution plan using a single processor versus the cost of an execution plan using more than one processor and uses the cost threshold for parallelism (By default 5, this value of cost threshold for parallelism determines which queries are considered short, and they should therefore be run using serial plans) value as a boundary point to determine short or long query. In a parallel query execution plan, the INSERT, UPDATE, and DELETE operators are executed serially. However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. The actual data changes are then serially applied to the database.
· Index data definition language (DDL) – Index operations that create or rebuild an index (REBUILD only, not applicable to REORGANIZE), or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates.
· Other Operations – Apart from the above, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP.
The degree of parallelism value is set at the SQL server instance level and can be modified by using the sp_configure system stored procedure (command shown below). You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option. Note that this can be set differently for each instance of SQL Server. So if you have multiple SQL Server instances in the same server, it is possible to specify a different Maximum DOP value for each one.

--The max degree of parallelism option is an advanced option
--and let you set only when show advanced options is set to 1
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
--configuring to use 8 processors in parallel
--setting takes effect immediately (without restarting the MSSQLSERVER service)
sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

By default, when SQL is installed on a server, the parallelism setting is set to 0 meaning that the optimizer can utilize all the available processors to execute an individual query. This is not necessarily the most optimal setting for the application and the types of queries it is designed to support. It is therefore necessary for project teams to examine the impact of parallelism on query performance and server stability and make a considered choice as discussed below.
· Scenario 1 – For OLTP application, a typical setting is 1 would help. The reason for this is that in an OLTP environment, most of the queries are expected to be point queries which address one or a relatively small number of records. Such queries do not need parallelized processing for efficient execution. If there are specific queries which have a need for a setting greater than 1, then the source code needs to be examined to see if a MAXDOP hint can be availed.
· Scenario 2 – For OLAP application, the setting should typically be default 0 (up to 8 processors) or be greater than 1, because each queries, such application will use, will typical target thousands of, millions of records and also there might a scenario when you drop the index before ETL operation and re-create it once refreshed data is uploaded in typical data warehousing application. There will definitely be performance advantages in using multiple processors to do these works in parallel fashion.
Note: Using a setting of 0 in these applications is not recommended, especially when there are more than 8 processors in order to keep the coordination costs, context switching down to manageable levels. It is typical to start with a value of 4 and experiment with the reporting queries to see if this needs to be adjusted upwards.

Please note, using higher value for his setting means using more processors, in turn it means managing more threads, requires more cache synchronization and more context switching। So it’s recommended to test and evaluate your particular scenario in your particular environment before changing the default value in production server.

Further information can be found in:
· Max Degree Of Parallelism
http://technet.microsoft.com/en-us/library/ms181007.aspx
· Parallel Query Processing
http://msdn.microsoft.com/en-us/library/ms178065(SQL.90).aspx
· Parallel Index Operations
http://msdn.microsoft.com/en-us/library/ms191292(SQL.90).aspx

Read more »

Installing a SQL Server 2008 R2 Failover Cluster

  • Posted by Prakash chheatry
  • 11:37 PM
  • 0 comments
  • Labels: No labels
To install a SQL Server failover cluster, you must create and configure a failover cluster instance by running SQL Server Setup. This topic explains the elements of a failover cluster, as well as important information about naming a failover cluster instance. It also describes basic steps for installing a failover cluster.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifElements of a Failover Cluster Instance

A failover cluster instance can run on one or more computers that are participating nodes of a failover cluster. A failover cluster instance contains:
  • A combination of one or more disks in a Microsoft Cluster Service (MSCS) cluster group, also known as a resource group. Each resource group can contain at most one instance of SQL Server.
  • A network name for the failover cluster instance.
  • One or more IP addresses assigned to the failover cluster instance.
  • One instance of SQL Server that includes SQL Server, SQL Server Agent, the Full-text Search (FTS) service, and Replication. You can install a failover cluster with SQL Server only, Analysis Services only, or SQL Server and Analysis Services.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifNaming a Failover Cluster Instance

A SQL Server failover cluster instance always appears on the network as if it were a single computer. You must use the SQL Server failover cluster instance name to connect to the SQL Server failover cluster, not the machine name of the node it happens to be running on. Doing so ensures that you are always able to connect to the failover cluster instance using the same name, regardless of which node is running SQL Server.
The name of your failover cluster instance must be unique to your domain. SQL Server does not listen on the IP address of the local servers. Instead, SQL Server listens only on the virtual IP address created during installation of the SQL Server failover cluster instance.
SQL Server depends on distinct registry keys and service names within the failover cluster to ensure that SQL Server functionality continues after a failover. Therefore, the name you provide for the instance of SQL Server, including the default instance, must be unique across all nodes in the failover cluster. Using unique instance names ensures that instances of SQL Server that are configured to fail over to a single server have distinct registry keys and service names.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifConsiderations for SQL Server Consolidation with a Failover Cluster


  • When planning to consolidate multiple stand-alone servers to SQL Server failover cluster instances, we recommend, as part of the planning process, that you verify that the cluster node hardware configuration is sufficient to support the number of instances of SQL Server to be hosted.

Presented below are two common scenarios and the basis for the recommended solutions that can be applied to your site configuration.
Scenario 1
You are a hosting site and want to offer up to 23 instances of SQL Server with the following resource requirements:
    • 2 processors for 23 instances of SQL Server as a single cluster node would require 46 CPUs.
    • 2 GB of memory for 23 instances of SQL Server as a single cluster node would require 48 GB of RAM (2 GB of additional memory for the operating system).
    • 4 disks for 23 instances of SQL Server as a cluster disk array would require 92 disks.
If you limit the failover members to only half of the available nodes, the hardware requirements would be reduced to:
    • 23 processors, 24 GB of memory per node, and availability of 46 disks.
With support for mount points, the total number of disks could be further reduced. SQL Server would require 23 disks each hosting 3-mount points or more, for increased disk space availability.
A potential limitation is if the processor and memory requirements rise, the existing hardware might not be capable of supporting those requirements without loss of performance to the existing instances of SQL Server.
Scenario 2
Migration of existing instances of SQL Server to a failover cluster
First, you must collect current baselines for the existing servers, noting any existing bottlenecks. Assuming baseline performance statistics provide the following needs, and that a single drive with mount points would cover drive requirements, that would leave you with the following configuration:
    • SQL Server 1 - 8 processors, 16 GB of RAM
    • SQL Server 2 - 4 processors, 8 GB of RAM (needs RAM, plan for 12 GB)
    • SQL Server 3 – 2 processors, 16GB of RAM (baseline shows need for additional processors)
    • SQL Server 4 – 4 processors, 8 GB of RAM (needs additional network bandwidth, add network adapters for 4 dedicated connections)
To replace these four servers, the cluster node would require the following minimum configuration:
    • 18 processors, 54 GB of RAM, and 6 network adapters
The process for calculating the minimum configuration for a single node in a failover cluster considers:
  • Total CPU requirements
  • Total memory requirements, and
  • Required disks
  • Add 2 GB of RAM for the operating system
Note that the disk constraint encountered in previous SQL Server versions does not affect SQL Server 2008. Each instance of SQL Server requires just one cluster disk for data files.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifInstalling a Failover Cluster

To install a failover cluster, you must use a domain account with local administrator rights, permission to log on as a service, and to act as part of the operating system on all nodes in the failover cluster.
To install a failover cluster by using the SQL Server Setup program, follow these steps:
  1. Identify the information you need to create your failover cluster instance (for example, cluster disk resource, IP addresses, and network name) and the nodes available for failover. For more information:
These configuration steps must take place before you run the SQL Server Setup program; use the Windows Cluster Administrator to carry them out. You must have one MSCS group for each failover cluster instance you want to configure.
  1. Run the SQL Server Setup program to start your failover cluster installation. Failover clustering has a new architecture and new work flow for all Setup scenarios in SQL Server 2008. The two options for installation are Integrated installation and Advanced/Enterprise installation. Integrated installation creates and configures a single-node SQL Server failover cluster instance. Additional nodes are added by using the Add Node functionality in Setup. For example, for Integrated installation, you run Setup to create a single-node failover cluster. Then, you run Setup again for each node you want to add to the cluster. Advanced/Enterprise installation consists of two steps. The Prepare step prepares all nodes of the failover cluster to be operational. Nodes are defined and prepared during this initial step. After you prepare the nodes, the Complete step is run on the active node—the node that owns the shared disk—to complete the failover cluster instance and make it operational.

Before Installing Failover Clustering

Before you install a SQL Server failover cluster, you must select the hardware and the operating system on which SQL Server will run. You must also configure Microsoft Cluster Service (MSCS), and review network, security, and considerations for other software that will run on your failover cluster.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifPreinstallation Checklist

Before you begin the failover cluster installation process, review the items below.

Verify Your Hardware Solution

· If the cluster solution includes geographically-dispersed cluster nodes, additional items like network latency and shared disk support must be verified. The entire solution must be on the Geographic Cluster Hardware Compatibility List. For more information, see Windows clustering and geographically separate sites in the Microsoft Knowledge Base.
· SAN configurations are also supported on Windows 2000 Advanced Server and Datacenter Server editions. The Windows Catalog and Hardware Compatibility List category "Cluster/Multi-cluster Device" lists the set of SAN-capable storage devices that have been tested and are supported as SAN storage units with multiple MSCS clusters attached. By matching the devices on this list with the complete cluster configurations defined in the Windows Catalog and Hardware Compatibility List cluster category, it is possible to deploy a set of Windows servers and clusters on a SAN fabric with shared storage devices in a way that is supported by Microsoft. For more information, see The Datacenter Program and Windows 2000 Datacenter Server Product in the Microsoft Knowledge Base.
· If you deploy a SQL Server failover cluster on iSCSI technology components, we recommend that you use appropriate caution. For more information, see Support for SQL Server 2000 on iSCSI technology components in the Microsoft Knowledge Base.
· For support information, see SQL Server support policy for Microsoft Clustering in the Microsoft Knowledge Base.
· Consider quorum disk resource sharing. In a server cluster, the quorum disk contains a master copy of the server cluster configuration, and is also used as a tie-breaker if all network communication fails between cluster nodes. Depending on the type of server cluster you implement, the quorum disk might or might not be a physical disk on the shared cluster disk array. Although it is best to reserve an entire cluster disk for use as the quorum disk, resources other than the quorum resource may be permitted to access the quorum disk.
However, making the quorum resource share the same disk with other resources forces you to choose between two undesirable alternatives. Either you must configure the resource so that its failure does not affect the group, or you must allow the group to be affected by the other resource's failures. In the first case, you lose failover support for the resource; in the second, the quorum resource fails over along with the rest of the group that contains both the quorum resource and the failed resource. As a result, the entire cluster is offline for as long as it takes the group to fail over.
For more information about proper quorum drive configuration, see the Microsoft Knowledge Base article, Quorum Drive Configuration Information.
· To install a SQL Server failover cluster when the source installation files and the cluster exist on different domains, copy the installation files to the current domain available to the SQL Server failover cluster.

Verify Your Operating System Settings

· Make sure that your operating system is installed properly and designed to support failover clustering. The following table is a list of SQL Server editions and the operating systems that support them.
NoteNote
SQL Server 2008 R2 Evaluation has the same hardware and software requirements as SQL Server 2008 R2 Datacenter.
SQL Server edition Windows Server 2003 Enterprise SP2 Microsoft Windows 2003 Datacenter Server SP2 Microsoft Windows 2008 Enterprise Microsoft Windows 2008 Datacenter Server Microsoft Windows 2008 R2 Enterprise Microsoft Windows 2008 R2 Data Center
SQL Server 2008 R2 Datacenter (64-bit) IA641 Yes Yes Yes Yes Yes Yes
SQL Server 2008 R2 Datacenter (64-bit) x641 Yes Yes Yes Yes Yes Yes
SQL Server 2008 R2 Datacenter (32-bit) Yes Yes Yes Yes

SQL Server 2008 R2 Enterprise (64-bit) IA641 Yes Yes Yes Yes Yes Yes
SQL Server 2008 R2 Enterprise (64-bit) x641 Yes Yes Yes Yes Yes Yes
SQL Server 2008 R2 Enterprise (32-bit) Yes Yes Yes Yes

SQL Server 2008 R2 Developer (64-bit) IA641 Yes Yes Yes Yes Yes Yes
SQL Server 2008 R2 Developer (64-bit) x641 Yes Yes Yes Yes Yes Yes
SQL Server 2008 R2 Developer(32-bit) Yes Yes Yes Yes

SQL Server 2008 R2 Standard (64-bit) x641 Yes Yes Yes Yes Yes Yes
SQL Server 2008 R2 Standard (32-bit) Yes Yes Yes Yes









SQL Server 2008 R2 clusters are not supported in WOW mode. That includes upgrades from SQL Server 2000 and SQL Server 2005 which had been originally installed in WOW. For those the only upgrade option is to install the new version side by side and migrate. For SQL Server 2000 IA64, in-place upgrade is not supported.
· Enable Windows Cryptographic Service Provider (CSP) on Windows Server 2003. If the CSP service is stopped or disabled on any cluster node, SQL Server Setup fails with a Windows Logo Requirement dialog.
· SQL Server supports mount points; the clustered installations of SQL Server are limited to the number of available drive letters. Assuming that you use only one drive letter for the operating system, and all other drive letters are available as normal cluster drives or cluster drives hosting mount points, you are limited to a maximum of 25 instances of SQL Server per failover cluster.
A mounted volume, or mount point, allows you to use a single drive letter to refer to many disks or volumes. If you have a drive letter D: that refers to a regular disk or volume, you can connect or "mount" additional disks or volumes as directories under drive letter D: without the additional disks or volumes requiring drive letters of their own.
Additional mount point considerations for SQL Server failover clustering:
o SQL Server Setup requires that the base drive of a mounted drive has an associated drive letter. For failover cluster installations, this base drive must be a clustered drive. Volume GUIDs are not supported in this release.
o The base drive, the one with the drive letter, cannot be shared among failover cluster instances. This is a normal restriction for failover clusters, but is not a restriction on stand-alone, multi-instance servers.
o Take extra care when setting up your failover cluster to ensure that both the base drive and the mounted disks or volumes are all listed as resources in the resource group. SQL Server Setup validates drive configuration as part of a failover cluster installation.
· SQL Server Setup automatically sets dependencies between the SQL Server cluster group and the disks that will be in the failover cluster. Do not set dependencies for disks before Setup.
· During SQL Server 2008 R2 Failover Cluster installation, computer object (Active Directory computer accounts) for the SQL Server Network Resource Name is created. In a Windows 2008 cluster, the cluster name account (computer account of the cluster itself) needs to have permissions to create computer objects. For more information, see Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory. If you are installing the SQL Server cluster on a Windows 2003 cluster, the cluster service needs to have the permissions to create the computer objects. For more information, see the following Microsoft Knowledge Base articles:
· To enable Kerberos authentication with SQL Server, see How to use Kerberos authentication in SQL Server in the Microsoft Knowledge Base.

Configure Microsoft Cluster Server

· Microsoft Cluster Server (MSCS) must be configured on at least one node of your server cluster. MSCS is only supported if it is installed on a hardware configuration that has been tested for compatibility with the MSCS software. You must also run SQL Server Datacenter, SQL Server Enterprise or SQL Server Standard in conjunction with MSCS. SQL Server Datacenter, and SQL Server Enterprise supports failover clusters with up to 8 nodes. SQL Server Standard supports two-node failover clusters.
For more information about installing and configuring MSCS on Windows Server 2003, see Server clusters.
· The resource DLL for the SQL Server service exports two functions used by MSCS Cluster manager to check for availability of the SQL Server resource. There is a simple check, LooksAlive, that queries the service status through the Windows NT Service Control Manager. There is also a more stringent check, IsAlive, that connects to SQL Server as a user probe to perform a simple query. By default, LooksAlive is fired every 5 seconds, and IsAlive is fired every 60 seconds. The LooksAlive and IsAlive polling intervals can be changed in MSCS Cluster Administrator from the Advanced tab for the SQL Server resource or by using the Cluster.exe command prompt utility.
· MSCS must be able to verify that the failover clustered instance is running by using the IsAlive check. This requires connecting to the server by using a trusted connection. By default, the account that runs the cluster service is not configured as an administrator on nodes in the cluster, and the BUILTIN\Administrators group does not have permission to log into SQL Server. These settings change only if you change permissions on the cluster nodes.
Ensure that the group or account that the Cluster Service is running under can log into SQL Server for the IsAlive check. If it cannot, the IsAlive check will fail. At a minimum, the MSCS Cluster Service account must have public rights to SQL Server so that it can run SELECT @@servername on a regular basis.
· When you install MSCS, it is very important to use separate service accounts to log on to MSCS and SQL Server. Otherwise, the cluster service password cannot be changed using the cluster command.
· When using MSCS, one node must be in control of the shared SCSI bus prior to the other node coming online. Failure to do this can cause application failover to go into an online pending state and either prevent failover to the other node, or totally fail. If your cluster system has a proprietary install process, the proprietary process should be used.

Install Microsoft Distributed Transaction Coordinator

Before installing SQL Server on a failover cluster, determine whether the Microsoft Distributed Transaction Coordinator (MSDTC) cluster resource must be created. If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances.

Configure Microsoft Distributed Transaction Coordinator

After you install the operating system and configure your cluster, you must configure MSDTC to work in a cluster by using the Cluster Administrator. Failure to cluster MSDTC will not block SQL Server Setup, but SQL Server application functionality may be affected if MSDTC is not properly configured.

Other Software Considerations

· Ensure that all cluster nodes are configured identically, including COM+, disk drive letters, and users in the administrators group.
· Verify that the cluster interconnect (heartbeat) is properly configured. For more information, see the Knowledge Base article, Recommended private "Heartbeat" configuration on a cluster server.
· Verify that you have cleared the system logs in all nodes and viewed the system logs again. Ensure that the logs are free of any error messages before continuing.
· For SQL Server installations in side-by-side configurations with previous versions, SQL Server services must use accounts found only in the global domains group. Additionally, accounts used by SQL Server services must not appear in the local Administrators group. Failure to comply with this guideline will result in unexpected security behavior.
· Windows Server 2003 cluster nodes in an environment where there are no pre-existing Windows Server 2003 domain controllers, see Windows 2000 and Windows Server 2003 cluster nodes as domain controllers.
· Install prerequisite software - Before running Setup to install or upgrade to SQL Server 2008 R2, install the following prerequisites to reduce installation time. You can install prerequisite software on each failover cluster node and then restart nodes once before running Setup.
· .NET Framework 3.5 SP1. Setup does not install .NET Framework 3.5 SP1 on a clustered operating system. You must install .NET Framework 3.5 SP1 before you run Setup. To avoid problems with the .NET Framework 3.5 installer, ensure that the Windows Update Service is running.
o Windows Installer 4.5.
o If you are using Windows Server 2003 SP2, install hotfix 937444. This is a requirement for the FILESTREAM feature of the Database Engine Services.
o SQL Server Setup support files. You can install these files by running SqlSupport.msi located on your SQL Server 2008 R2 installation media.
· To use encryption, install the server certificate with the fully-qualified DNS name of the MSCS cluster on all nodes in the SQL Server failover cluster. For example, if you have a two-node cluster, with nodes named "Test1.DomainName.com" and "Test2.DomainName.com" and a SQL Server failover cluster instance named "Virtsql", you must get a certificate for "Virtsql.DomainName.com" and install the certificate on the test1 and test2 nodes. Then you can select the Force protocol encryption check box on the SQL Server Configuration Manager to configure your failover cluster for encryption.
Important noteImportant
Do not select the Force protocol encryption check box until you have certificates installed on all participating nodes in your failover cluster instance.
· Verify that anti-virus software is not installed on your MSCS cluster. For more information, see the Microsoft Knowledge Base article, Antivirus software may cause problems with cluster services.
· Verify that the disk where SQL Server will be installed is uncompressed. If you attempt to install SQL Server to a compressed drive, SQL Server Setup fails.
· When naming a cluster group for your failover cluster installation, you must not use any of the following characters in the cluster group name:
o Less than operator (<)
o Greater than operator (>)
o Double quote (")
o Single quote (')
o Ampersand (&)
Also verify that existing cluster group names do not contain unsupported characters.

Network, Port, and Firewall Considerations

· Verify that you have disabled NetBIOS for all private network cards before beginning SQL Server Setup.
· The network name and IP address of your SQL Server should not be used for any other purpose, such as file sharing. If you want to create a file share resource, use a different, unique network name and IP address for the resource.
Important noteImportant
Microsoft recommends that you do not use file shares on data drives, as they can affect SQL Server behavior and performance.
· Even though SQL Server supports both Named Pipes and TCP/IP Sockets over TCP/IP within a cluster, Microsoft recommends that you use TCP/IP Sockets in a clustered configuration.
· To ensure correct failover cluster functionality, add exceptions to firewall configuration settings for the SQL Server port, SQL Browser port, File and Printer Sharing (TCP 139/445 and UDP 137/138), and Remote Procedure Call (TCP port 135).
· Note that ISA server is not supported on Windows Clustering and consequently is also not supported on SQL Server failover clusters.
· The Remote Registry service must be up and running.
· Remote Administration must be enabled.
· For the SQL Server port, use SQL Server Configuration Manager to check the SQL Server network configuration for the TCP/IP protocol for the instance you want to unblock. You must enable the TCP port for IPALL if you want to connect to SQL Server using TCP after installation. By default, SQL Browser listens on UDP port 1434.
· Failover cluster Setup operations include a rule that checks network binding order. Although binding orders might seem correct, you might have disabled or "ghosted" NIC configurations on the system. "Ghosted" NIC configurations can affect the binding order and cause the binding order rule to issue a warning. To avoid this situation, use the following steps to identify and remove disabled network adapters:
1. At a command prompt, type: set devmgr_Show_Nonpersistent_Devices=1.
2. Type and run: start Devmgmt.msc.
3. Expand the list of network adapters. Only the physical adapters should be in the list. If you have a disabled network adapter, Setup will report a failure for the network binding order rule. Control Panel/Network Connections will also show that adapter was disabled. Confirm that Network Settings in Control Panel shows the same list of enabled physical adaptors that devmgmt.msc shows.
4. Remove disabled network adapters before you run SQL Server Setup.
5. After Setup finishes, return to Network Connections in Control Panel and disable any network adapters that are not currently in use.

Other Considerations

· To create a failover cluster, you must be a local administrator with permissions to log on as a service, and to act as part of the operating system on all nodes of the failover cluster instance.
· Before you install or update a SQL Server failover cluster, disable all applications and services that might use SQL Server components during installation, but leave the disk resources online.
· On Windows Server 2008, service SIDs are generated automatically for use with SQL Server 2008 R2 services. For SQL Server 2008 R2 failover cluster instances upgraded from SQL Server 2000 or SQL Server 2005, existing domain groups and ACL configurations will be preserved.
· Domain groups must be within the same domain as the machine accounts. For example, if the machine where SQL Server will be installed is in the SQLSVR domain which is a child of MYDOMAIN, you must specify a group in the SQLSVR domain. The SQLSVR domain may contain user accounts from MYDOMAIN.
· On Windows Server 2003, create domain groups for the clustered services that will be installed as part of your SQL Server failover cluster. The SQL Server service, SQL Server Agent service, Analysis Services service, and iFTS service must run as domain accounts that are members of the domain group. If necessary, ask your domain administrator for the names of existing domain groups, or to create domain groups for your failover cluster.
· If you are installing a SQL Server 2008 R2 failover cluster instance in a Windows 2000 mixed mode domain, you must use domain global groups for SQL Server Clustered Services.
NoteNote
Windows 2000 domain controllers can operate in mixed mode and native mode. Mixed mode allows down-level domain controllers in the same domain.
· SQL Server failover clustering is not supported where cluster nodes are domain controllers.
· Configure Domain Name Service (DNS) or Windows Internet Name Service (WINS). A DNS server or WINS server must be running in the environment where your SQL Server failover cluster will be installed. SQL Server Setup requires dynamic domain name service registration of the SQL Server IP interface virtual reference. If the dynamic registration cannot be completed, Setup fails and the installation is rolled back. If no dynamic registration is available, you must have pre-registered your server in DNS.
· Consider whether the SQL Server tools, features, and components you want to use are supported with failover clustering. For more information, see Getting Started with SQL Server 2008 R2 Failover Clustering.
· Consider how you will monitor and maintain your failover cluster to achieve your high availability goals. For more information, see Maintaining a Failover Cluster and Using SQL Server Tools with Failover Clustering.

Security Considerations for a SQL Server Installation

SQL Server 2008 R2
Other Versions
http://i3.msdn.microsoft.com/Hash/a19e30a4020fe81d2b1209058013a360.png
Security is important for every product and every business. By following simple best practices, you can avoid many security vulnerabilities. This topic discusses some security best practices that you should consider both before you install SQL Server and after you install SQL Server. Security guidance for specific features is included in the reference topics for those features.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifBefore Installing SQL Server

Follow these best practices when you set up the server environment:

Enhance Physical Security

Physical and logical isolation make up the foundation of SQL Server security. To enhance the physical security of the SQL Server installation, do the following tasks:
· Place the server in a room accessible only to authorized persons.
· Place computers that host a database in a physically protected location, ideally a locked computer room with monitored flood detection and fire detection or suppression systems.
· Install databases in the secure zone of the corporate intranet and do not connect your SQL Servers directly to the Internet.
· Back up all data regularly and secure the backups in an off-site location.

Use Firewalls

Firewalls are important to help secure the SQL Server installation. Firewalls will be most effective if you follow these guidelines:
· Put a firewall between the server and the Internet. Enable your firewall. If your firewall is turned off, turn it on. If your firewall is turned on, do not turn it off.
· Divide the network into security zones separated by firewalls. Block all traffic, and then selectively admit only what is required.
· In a multi-tier environment, use multiple firewalls to create screened subnets.
· When you are installing the server inside a Windows domain, configure interior firewalls to allow Windows Authentication.
· If your application uses distributed transactions, you might have to configure the firewall to allow Microsoft Distributed Transaction Coordinator (MS DTC) traffic to flow between separate MS DTC instances. You will also have to configure the firewall to allow traffic to flow between the MS DTC and resource managers such as SQL Server.
For more information about the default Windows firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configuring the Windows Firewall to Allow SQL Server Access.

Isolate Services

Isolating services reduces the risk that one compromised service could be used to compromise others. To isolate services, consider the following guidelines:
· Run separate SQL Server services under separate Windows accounts. Whenever possible, use separate, low-rights Windows or Local user accounts for each SQL Server service. For more information, see Setting Up Windows Service Accounts.

Configure a Secure File System

Using the correct file system increases security. For SQL Server installations, you should do the following tasks:
· Use the NTFS file system (NTFS). NTFS is the preferred file system for installations of SQL Server because it is more stable and recoverable than FAT file systems. NTFS also enables security options like file and directory access control lists (ACLs) and Encrypting File System (EFS) file encryption. During installation, SQL Server will set appropriate ACLs on registry keys and files if it detects NTFS. These permissions should not be changed. Future releases of SQL Server might not support installation on computers with FAT file systems.
NoteNote
If you use EFS, database files will be encrypted under the identity of the account running SQL Server. Only this account will be able to decrypt the files. If you must change the account that runs SQL Server, you should first decrypt the files under the old account and then re-encrypt them under the new account.
· Use a redundant array of independent disks (RAID) for critical data files.

Disable NetBIOS and Server Message Block

Servers in the perimeter network should have all unnecessary protocols disabled, including NetBIOS and server message block (SMB).
NetBIOS uses the following ports:
· UDP/137 (NetBIOS name service)
· UDP/138 (NetBIOS datagram service)
· TCP/139 (NetBIOS session service)
SMB uses the following ports:
· TCP/139
· TCP/445
Web servers and Domain Name System (DNS) servers do not require NetBIOS or SMB. On these servers, disable both protocols to reduce the threat of user enumeration.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifAfter Installing SQL Server

After installation, you can enhance the security of the SQL Server installation by following these best practices regarding accounts and authentication modes:
Service accounts
· Run SQL Server services by using the lowest possible permissions.
· Associate SQL Server services with low privileged Windows local user accounts, or domain user accounts.
· For more information, see Setting Up Windows Service Accounts.
Authentication mode
· Require Windows Authentication for connections to SQL Server.
· Use Kerberos authentication. For more information, see Registering a Service Principal Name.
Strong passwords
· Always assign a strong password to the sa account.
· Always enable password policy checking for password strength and expiration.
· Always use strong passwords for all SQL Server logins. For more information, see the SQL Server 2008 Security Overview for Database Administrators white paper.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gif

Stay Up to Date with SQL Server Install, and Upgrade information
For the latest downloads, articles, videos and troubleshooting information from Microsoft, as well as selected solutions from the community, visit the SQL Server Setup page
For automatic notification of these updates, subscribe to the RSS feeds available on the page.
Community Content Add
AnnotationsFAQ
Managing Kerberos Authentication
If you are using Kerberos authentication in your environment, you will want to read the TechNet article "Manage Kerberos Authentication Issues in a Reporting Services Environment" at http://technet.microsoft.com/en-us/library/ff679930(SQL.100).aspx. This document covers all of the issues around configuring Kerberos authentication for Reporting Services, SharePoint, SQL Server, SharePoint Sites, and Central Administration. It explains how the DBA can work with the Domain Administrator in using the SETSPN command line utility to add Kerberos Service Principle Names (SPN) to services that need to talk to each other. The document also contains links to all of the relevant links for configuring Kerberos.

There is another article of value on TechNet titled “How to Implement Kerberos Constrained Delegation with SQL Server 2008” at http://technet.microsoft.com/en-us/library/ee191523(SQL.100).aspx. This article explains how to configure the connection strings for client applications that need to connect using Kerberos. The article also includes the scenario of using Linked Servers with Windows Authentication.

Security Considerations for a SQL Server Installation

Security is important for every product and every business. By following simple best practices, you can avoid many security vulnerabilities. This topic discusses some security best practices that you should consider both before you install SQL Server and after you install SQL Server. Security guidance for specific features is included in the reference topics for those features.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifBefore Installing SQL Server

Follow these best practices when you set up the server environment:

Enhance Physical Security

Physical and logical isolation make up the foundation of SQL Server security. To enhance the physical security of the SQL Server installation, do the following tasks:
· Place the server in a room accessible only to authorized persons.
· Place computers that host a database in a physically protected location, ideally a locked computer room with monitored flood detection and fire detection or suppression systems.
· Install databases in the secure zone of the corporate intranet and do not connect your SQL Servers directly to the Internet.
· Back up all data regularly and secure the backups in an off-site location.

Use Firewalls

Firewalls are important to help secure the SQL Server installation. Firewalls will be most effective if you follow these guidelines:
· Put a firewall between the server and the Internet. Enable your firewall. If your firewall is turned off, turn it on. If your firewall is turned on, do not turn it off.
· Divide the network into security zones separated by firewalls. Block all traffic, and then selectively admit only what is required.
· In a multi-tier environment, use multiple firewalls to create screened subnets.
· When you are installing the server inside a Windows domain, configure interior firewalls to allow Windows Authentication.
· If your application uses distributed transactions, you might have to configure the firewall to allow Microsoft Distributed Transaction Coordinator (MS DTC) traffic to flow between separate MS DTC instances. You will also have to configure the firewall to allow traffic to flow between the MS DTC and resource managers such as SQL Server.
For more information about the default Windows firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configuring the Windows Firewall to Allow SQL Server Access.

Isolate Services

Isolating services reduces the risk that one compromised service could be used to compromise others. To isolate services, consider the following guidelines:
· Run separate SQL Server services under separate Windows accounts. Whenever possible, use separate, low-rights Windows or Local user accounts for each SQL Server service. For more information, see Setting Up Windows Service Accounts.

Configure a Secure File System

Using the correct file system increases security. For SQL Server installations, you should do the following tasks:
· Use the NTFS file system (NTFS). NTFS is the preferred file system for installations of SQL Server because it is more stable and recoverable than FAT file systems. NTFS also enables security options like file and directory access control lists (ACLs) and Encrypting File System (EFS) file encryption. During installation, SQL Server will set appropriate ACLs on registry keys and files if it detects NTFS. These permissions should not be changed. Future releases of SQL Server might not support installation on computers with FAT file systems.
NoteNote
If you use EFS, database files will be encrypted under the identity of the account running SQL Server. Only this account will be able to decrypt the files. If you must change the account that runs SQL Server, you should first decrypt the files under the old account and then re-encrypt them under the new account.
· Use a redundant array of independent disks (RAID) for critical data files.

Disable NetBIOS and Server Message Block

Servers in the perimeter network should have all unnecessary protocols disabled, including NetBIOS and server message block (SMB).
NetBIOS uses the following ports:
· UDP/137 (NetBIOS name service)
· UDP/138 (NetBIOS datagram service)
· TCP/139 (NetBIOS session service)
SMB uses the following ports:
· TCP/139
· TCP/445
Web servers and Domain Name System (DNS) servers do not require NetBIOS or SMB. On these servers, disable both protocols to reduce the threat of user enumeration.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifAfter Installing SQL Server

After installation, you can enhance the security of the SQL Server installation by following these best practices regarding accounts and authentication modes:
Service accounts
· Run SQL Server services by using the lowest possible permissions.
· Associate SQL Server services with low privileged Windows local user accounts, or domain user accounts.
· For more information, see Setting Up Windows Service Accounts.
Authentication mode
· Require Windows Authentication for connections to SQL Server.
· Use Kerberos authentication. For more information, see Registering a Service Principal Name.
Strong passwords
· Always assign a strong password to the sa account.
· Always enable password policy checking for password strength and expiration.
· Always use strong passwords for all SQL Server logins. For more information, see the SQL Server 2008 Security Overview for Database Administrators white paper.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gif

Stay Up to Date with SQL Server Install, and Upgrade information
For the latest downloads, articles, videos and troubleshooting information from Microsoft, as well as selected solutions from the community, visit the SQL Server Setup page
For automatic notification of these updates, subscribe to the RSS feeds available on the page.
Community Content Add
AnnotationsFAQ
Managing Kerberos Authentication
If you are using Kerberos authentication in your environment, you will want to read the TechNet article "Manage Kerberos Authentication Issues in a Reporting Services Environment" at http://technet.microsoft.com/en-us/library/ff679930(SQL.100).aspx.
This document covers all of the issues around configuring Kerberos authentication for Reporting Services, SharePoint, SQL Server, SharePoint Sites, and Central Administration. It explains how the DBA can work with the Domain Administrator in using the SETSPN command line utility to add Kerberos Service Principle Names (SPN) to services that need to talk to each other. The document also contains links to all of the relevant links for configuring Kerberos.

There is another article of value on TechNet titled “How to Implement Kerberos Constrained Delegation with SQL Server 2008” at http://technet.microsoft.com/en-us/library/ee191523(SQL.100).aspx. This article explains how to configure the connection strings for client applications that need to connect using Kerberos. The article also includes the scenario of using Linked Servers with Windows Authentication.

How to: Create a New SQL Server Failover Cluster (Setup)

Setup operations for SQL Server failover clustering have changed in this release. To install or upgrade a SQL Server failover cluster, you must run the Setup program on each node of the failover cluster. To add a node to an existing SQL Server failover cluster, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node to manage the other nodes.
Follow these steps to create a failover cluster instance.
For more information about how to add nodes to or remove nodes from an existing failover cluster instance, see How to: Add or Remove Nodes in a SQL Server Failover Cluster (Setup).
For more information about remote installation, see Version and Edition Upgrades.
For more information about how to install a stand-alone instance of SQL Server , see How to: Install SQL Server 2008 R2 (Setup).
The following options are available for SQL Server failover cluster installation:
· Option1: Integration Installation with Add Node
SQL Server integrated failover cluster installation consists of the following steps:
1. Create and configure a single-node SQL Server failover cluster instance. When you configure the node successfully, you have a fully functional failover cluster instance. At this point, it does not have high availability because there is only one node in the failover cluster.
2. On each node to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.
· Option 2: Advanced/Enterprise Installation
SQL Server Advanced/Enterprise failover cluster installation consists of the following steps:
1. On each node that will be a possible owner of the new SQL Server failover cluster that you are creating, follow the Prepare Failover Cluster setup steps that are listed in the Prepare section. After you run the Prepare Failover Cluster on one node, Setup creates the Configuration.ini file that lists all the settings that you specified. On the additional nodes to be prepared, instead of following these steps, you can supply the autogenerated Configuration.ini file from first node as an input to the Setup command line. For more information, see How to: Install SQL Server 2008 R2 Using a Configuration File. This step prepares the nodes ready to be clustered, but there is no operational instance of SQL Server at the end of this step.
2. After the nodes are prepared for clustering, run Setup on one of the prepared nodes, preferably on the node that owns the shared disk that has the Complete Failover Cluster functionality. This step configures and finishes the failover cluster instance. At the end of this step, you will have an operational SQL Server failover cluster instance and all the nodes that were prepared previously for that instance will be the possible owners of the newly created SQL Server failover cluster.
NoteNote
Complete Failover Cluster requires that the underlying Windows failover cluster exists. If the Windows failover cluster does not exist, Setup gives an error and exits.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifPrerequisites

Before you begin, review the following SQL Server Books Online topics:
· If you are installing SQL Server 2008 R2 on operating systems earlier than Windows Server 2008, make sure that you also read and explicitly follow instructions in the Recommended private "Heartbeat" configuration on a cluster server Knowledge Base article.
NoteNote
Take note of the location of the shared drive in the Cluster Administrator before you run SQL Server Setup. You must have this information to create a new failover cluster.
· Setup does not install .NET Framework 3.5 SP1 on a clustered operating system. You must install .NET Framework 3.5 SP1 before you run Setup.
Important You may have to apply cumulative updates to the original media before you install SQL Server 2008 R2, if you are affected by a known issue in the Setup program. For more information about known issues and detailed instructions, see How to update SQL Server 2008 R2 Setup by running a newer version of the SQL Server 2008 R2 Setup support files.

To install a new SQL Server 2008 R2 failover cluster using Integrated Install with Add Node.

1. Insert the SQL Server installation media, and from the root folder, double-click Setup.exe. To install from a network share, browse to the root folder on the share, and then double-click Setup.exe. For more information about how to install prerequisites, see Before Installing Failover Clustering. You may be asked to install the prerequisites, if they are not previously installed.
2. Windows Installer 4.5 is required, and may be installed by the Installation Wizard. If you are prompted to restart your computer, restart and then start SQL Server 2008 R2 Setup again.
3. After prerequisites are installed, the Installation Wizard starts the SQL Server Installation Center. To create a new cluster installation of SQL Server 2008 R2, click New SQL Server failover cluster installation on the installation page
4. The System Configuration Checker runs a discovery operation on your computer. To continue, click OK. You can view the details on the screen by clicking Show Details, or as an HTML report by clicking View detailed report.
5. On the Language Selection page, you can specify the language for your instance of SQL Server if you are installing on a localized operating system and the installation media includes language packs for both English and the language corresponding to the operating system. For more information about cross-language support and installation considerations, see Local Language Versions in SQL Server.
To continue, click Next.
6. On the Setup Support Files page, click Install to install the Setup support files.
7. The System Configuration Checker verifies the system state of your computer before Setup continues. After the check is complete, click Next to continue. You can view the details on the screen by clicking Show Details, or as an HTML report by clicking View detailed report.
8. On the Product key page, indicate whether you are installing a free edition of SQL Server, or whether you have a PID key for a production version of the product. For more information, see Editions and Components of SQL Server 2008 R2.
9. On the License Terms page, read the license agreement, and then select the check box to accept the license terms and conditions. To help improve SQL Server, you can also enable the feature usage option and send reports to Microsoft. Click Next to continue. To end Setup, click Cancel.
10. On the Feature Selection page, select the components for your installation. A description for each component group appears in the right pane after you select the feature name. You can select any combination of check boxes, but only the Database Engine and Analysis Services support failover clustering. Other selected components will run as a stand-alone feature without failover capability on the current node that you are running Setup on.
You can specify a custom directory for shared components by using the field at the bottom of this page. To change the installation path for shared components, either update the path in the field provided at the bottom of the dialog box, or click the ellipsis button to browse to an installation directory. The default installation path is C:\Program Files\Microsoft SQL Server\.
NoteNote
When you select the Database Engine Services feature, both replication and full-text search are selected automatically. Unselecting any of these subfeatures also unselects the Database Engine Services feature.
11. On the Instance Configuration page, specify whether to install a default or a named instance. For more information, see Instance Configuration.
SQL Server Network Name — Specify a network name for the new SQL Server failover cluster. This is the name that is used to identify your failover cluster on the network.
NoteNote
This is known as the virtual SQL Server name in earlier versions of SQL Server failover clusters.
Instance ID — By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for your instance of SQL Server. This is the case for default instances and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a nondefault instance ID, select the Instance ID box and provide a value.
NoteNote
Typical stand-alone instances of SQL Server 2008 R2, whether default or named instances, do not use a nondefault value for the Instance ID box.
Instance root directory — By default, the instance root directory is C:\Program Files\Microsoft SQL Server\. To specify a nondefault root directory, use the field provided, or click the ellipsis button to locate an installation folder.
Detected SQL Server instances and features on this computer - The grid shows instances of SQL Server that are on the computer where Setup is running. If a default instance is already installed on the computer, you must install a named instance of SQL Server 2008 R2. Click Next to continue.
12. The Disk Space Requirements page calculates the required disk space for the features that you specify, and compares requirements to the available disk space on the computer where Setup is running. For more information, see Disk Space Requirements.
13. Use the Cluster Resource Group page to specify the cluster resource group name where SQL Server virtual server resources will be located. To specify the SQL Server cluster resource group name, you have two options:
o Use the drop-down box to specify an existing group to use.
o Type the name of a new group to create. Be aware that the name "Available storage" is not a valid group name.
14. On the Cluster Disk Selection page, select the shared cluster disk resource for your SQL Server failover cluster. The cluster disk is where the SQL Server data will be put. More than one disk can be specified. The Available shared disks grid displays a list of available disks, whether each is qualified as a shared disk, and a description of each disk resource. Click Next to continue.
NoteNote
The first drive is used as the default drive for all databases, but can be changed on the Database Engine or Analysis Services configuration pages.
15. On the Cluster Network Configuration page, specify the network resources for your failover cluster instance:
o Network Settings — Specify the IP type and IP address for your failover cluster instance.
Click Next to continue.
16. Use this page to specify Cluster Security Policy.
o Windows Server 2008 and later versions - Service SIDs (server security IDs) are the recommended and default setting. The option to specify domain groups is available but not recommended. For information about service SIDs functionality on Windows Server 2008, see Setting Up Windows Service Accounts.
o On Windows Server 2003, specify domain groups for SQL Server services. All resource permissions are controlled by domain-level groups that include SQL Server service accounts as group members.
Click Next to continue.
NoteNote
If you are installing a SQL Server 2008 R2 Failover Cluster Instance in a Windows 2000 mixed mode domain, you must use domain global groups for SQL Server Clustered Services.
NoteNote
Windows 2000 domain controllers can operate in mixed mode and native mode. Mixed mode enables down-level domain controllers in the same domain.
17. Work flow for the rest of this topic depends on the features that you have specified for your installation. You might not see all the pages, depending on your selections (Database Engine, Analysis Services, Reporting Services).
18. On the Server Configuration — Service Accounts page, specify login accounts for SQL Server services. The actual services that are configured on this page depend on the features that you selected to install.
You can assign the same login account to all SQL Server services, or you can configure each service account individually. The startup type is set to manual for all cluster-aware services, including full-text search and SQL Server Agent, and cannot be changed during installation. Microsoft recommends that you configure service accounts individually to provide least privileges for each service, where SQL Server services are granted the minimum permissions they have to have complete their tasks. For more information, see Server Configuration - Service Accounts and Setting Up Windows Service Accounts.
To specify the same logon account for all service accounts in this instance of SQL Server, provide credentials in the fields at the bottom of the page.
Security Note Do not use a blank password. Use a strong password.
When you are finished specifying login information for SQL Server services, click Next.
19. Use the Server Configuration - Collation tab to specify nondefault collations for the Database Engine and Analysis Services. For more information, see Server Configuration - Collation.
20. Use the Database Engine Configuration — Account Provisioning page to specify the following:
o Security Mode - select Windows Authentication or Mixed Mode Authentication for your instance of SQL Server. If you select Mixed Mode Authentication, you must provide a strong password for the built-in SQL Server system administrator account.
After a device establishes a successful connection to SQL Server, the security mechanism is the same for both Windows Authentication and Mixed Mode. For more information, see Database Engine Configuration - Account Provisioning.
o SQL Server Administrators - You must specify at least one system administrator for the instance of SQL Server. To add the account under which SQL Server Setup is running, click Add Current User. To add or remove accounts from the list of system administrators, click Add or Remove, and then edit the list of users, groups, or computers that will have administrator privileges for the instance of SQL Server. For more information, see Database Engine Configuration - Account Provisioning.
When you are finished editing the list, click OK. Verify the list of administrators in the configuration dialog box. When the list is complete, click Next.
21. Use the Database Engine Configuration - Data Directories page to specify nondefault installation directories. To install to default directories, click Next.
Important noteImportant
If you specify nondefault installation directories, make sure that the installation folders are unique to this instance of SQL Server. None of the directories in this dialog box should be shared with directories from other instances of SQL Server. The data directories should be located on the shared cluster disk for the failover cluster.
23. Use the Database Engine Configuration - FILESTREAM page to enable FILESTREAM for your instance of SQL Server. For more information about FILESTREAM, see Database Engine Configuration - Filestream. Click Next to continue.
24. Use the Analysis Services Configuration — Account Provisioning page to specify users or accounts that will have administrator permissions for Analysis Services. You must specify at least one system administrator for Analysis Services. To add the account under which SQL Server Setup is running, click Add Current User. To add or remove accounts from the list of system administrators, click Add or Remove, and then edit the list of users, groups, or computers that will have administrator privileges for Analysis Services. For more information, see Analysis Services Configuration - Account Provisioning.
When you are finished editing the list, click OK. Verify the list of administrators in the configuration dialog box. When the list is complete, click Next.
25. Use the Analysis Services Configuration — Data Directories page to specify nondefault installation directories. To install to default directories, click Next.
Important noteImportant
If you specify nondefault installation directories, make sure that the installation folders are unique to this instance of SQL Server. None of the directories in this dialog box should be shared with directories from other instances of SQL Server. The data directories should be located on the shared cluster disk for the failover cluster.
27. Use the Reporting Services Configuration page to specify the kind of Reporting Services installation to create. For failover cluster installation, the option is set to Unconfigured Reporting Services installation. You must configure Reporting Services services after you complete the installation.
For more information about Reporting Services configuration modes, see Report Server Installation Options.
28. On the Error Reporting page, specify the information that you want to send to Microsoft to help improve SQL Server. By default, option for error reporting is disabled. For more information, see Error Reporting.
29. The System Configuration Checker runs one more set of rules to validate your configuration with the SQL Server features that you have specified.
30. The Ready to Install page displays a tree view of installation options that were specified during Setup. To continue, click Install.
31. During installation, the Installation Progress page provides status so that you can monitor installation progress as Setup continues.
32. After installation, the Complete page provides a link to the summary log file for the installation and other important notes. To complete the SQL Server installation process, click Close.
33. If you are instructed to restart the computer, do so now. It is important to read the message from the Installation Wizard when you have finished with Setup. For information about Setup log files, see How to: View and Read SQL Server Setup Log Files.
34. To add nodes to the single-node failover you just created, run Setup on each additional node and follow the steps for AddNode operation. For more information, see How to: Add or Remove Nodes in a SQL Server Failover Cluster (Setup).
NoteNote
If you are adding more than one node, you can use the configuration file to deploy the installations. For more information, see How to: Install SQL Server 2008 R2 Using a Configuration File.
The SQL Server edition you are installing must match across all the nodes in a SQL Server failover cluster. When you add a new node to an existing SQL Server failover cluster, make sure that you specify that the edition matches the edition of the existing failover cluster.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifPrepare

Advanced/Enterprise Failover Cluster Install Step 1: Prepare

1. Insert the SQL Server installation media, and from the root folder, double-click Setup.exe. To install from a network share, browse to the root folder on the share, and then double-click Setup.exe. For more information about how to install prerequisites, see Before Installing Failover Clustering. You may be asked to install the prerequisites, if they are not previously installed.
2. Windows Installer 4.5 is required, and may be installed by the Installation Wizard. If you are prompted to restart your computer, restart and then start SQL Server 2008 R2 Setup again.
3. After the prerequisites are installed, the Installation Wizard starts the SQL Server Installation Center. To prepare the node for clustering, move to the Advanced page and then click Advanced cluster preparation
4. The System Configuration Checker runs a discovery operation on your computer. To continue, click OK. You can view the details on the screen by clicking Show Details, or as an HTML report by clicking View detailed report.
5. On the Setup Support Files page click Install to install the Setup support files.
6. The System Configuration Checker verifies the system state of your computer before Setup continues. After the check is complete, click Next to continue. You can view the details on the screen by clicking Show Details, or as an HTML report by clicking View detailed report.
7. On the Language Selection page, you can specify the language for your instance of SQL Server if you are installing on a localized operating system and the installation media includes language packs for both English and the language corresponding to the operating system. For more information about cross-language support and installation considerations, see Local Language Versions in SQL Server.
To continue, click Next.
8. On the Product key page, click to indicate whether you are installing a free edition of SQL Server, or whether you have a PID key for a production version of the product. For more information, see Editions and Components of SQL Server 2008 R2.
NoteNote
You must specify the same product key on all the nodes that you are preparing for the same failover cluster.
9. On the License Terms page, read the license agreement, and then select the check box to accept the license terms and conditions. To help improve SQL Server, you can also enable the feature usage option and send reports to Microsoft. Click Next to continue. To end Setup, click Cancel.
10. On the Feature Selection page, select the components for your installation. A description for each component group appears in the right pane after you select the feature name. You can select any combination of check boxes, but only the Database Engine and Analysis Services support failover clustering. Other components will run on a single failover cluster node as a stand-alone feature without failover capability.
You can specify a custom directory for shared components by using the field at the bottom of this page. To change the installation path for shared components, either update the path in the field provided at the bottom of the dialog box, or click the ellipsis button to browse to an installation directory. The default installation path is C:\Program Files\Microsoft SQL Server\.
NoteNote
When you select the Database Engine Services feature, both replication and full-text search are selected automatically. Unselecting any of these subfeatures also unselects the Database Engine Services feature.
11. On the Instance Configuration page, specify whether to install a default or a named instance. For more information, see Instance Configuration.
Instance ID — By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for your instance of SQL Server. This is the case for default instances and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a nondefault instance ID, select the Instance ID text box and provide a value.
NoteNote
Typical stand-alone instances of SQL Server 2008 R2, whether default or named instances, do not use a nondefault value for the Instance ID text box.
Important noteImportant
Use the same InstanceID for all the nodes that are prepared for the failover cluster
Instance root directory — By default, the instance root directory is C:\Program Files\Microsoft SQL Server\. To specify a nondefault root directory, use the field provided, or click the ellipsis button to locate an installation folder.
Installed instances - The grid shows instances of SQL Server that are on the computer where Setup is running. If a default instance is already installed on the computer, you must install a named instance of SQL Server 2008 R2. Click Next to continue.
12. The Disk Space Requirements page calculates the required disk space for the features that you specify, and compares requirements to the available disk space on the computer where Setup is running. For more information, see Disk Space Requirements.
13. Use this page to specify Cluster Security Policy.
o Windows Server 2008 and later - Service SIDs (server security IDs) are the recommended and default setting.The option to specify domain groups is available but not recommended. For information about service SIDs functionality on Windows Server 2008, see Setting Up Windows Service Accounts.
o On Windows Server 2003, specify domain groups for SQL Server services. All resource permissions are controlled by domain-level groups that include SQL Server service accounts as group members.
Click Next to continue.
NoteNote
If you are installing a SQL Server 2008 R2 failover cluster instance in a Windows 2000 mixed mode domain, you must use domain global groups for SQL Server Clustered Services.
NoteNote
Windows 2000 domain controllers can operate in mixed mode and native mode. Mixed mode enables down-level domain controllers in the same domain.
14. Work flow for the rest of this topic depends on the features that you have specified for your installation. You might not see all the pages, depending on your selections.
15. On the Server Configuration — Service Accounts page, specify login accounts for SQL Server services. The actual services that are configured on this page depend on the features that you selected to install.
You can assign the same login account to all SQL Server services, or you can configure each service account individually. The startup type is set to manual for all cluster-aware services, including full-text search and SQL Server Agent, and cannot be changed during installation. Microsoft recommends that you configure service accounts individually to provide least privileges for each service, where SQL Server services are granted the minimum permissions they have to have complete their tasks. For more information, see Server Configuration - Service Accounts and Setting Up Windows Service Accounts.
To specify the same logon account for all service accounts in this instance of SQL Server, provide credentials in the fields at the bottom of the page.
Security Note Do not use a blank password. Use a strong password.
When you are finished specifying login information for SQL Server services, click Next.
16. Use the Server Configuration - Collation tab to specify nondefault collations for the Database Engine and Analysis Services. For more information, see Server Configuration - Collation.
17. Use the Server Configuration - Filestream to enable FILESTREAM for your instance of SQL Server. For more information about FILESTREAM, see Database Engine Configuration - Filestream. Click Next to continue.
18. Use the Reporting Services Configuration page to specify the kind of Reporting Services installation to create. For failover cluster installation, the option is set to Unconfigured Reporting Services installation. You must configure Reporting Services services after you complete the installation.
For more information about Reporting Services configuration modes, see Report Server Installation Options.
19. On the Error Reporting page, specify the information that you want to send to Microsoft to help improve SQL Server. By default, option for error reporting is enabled. For more information, see Error Reporting.
20. The System Configuration Checker runs one more set of rules to validate your configuration with the SQL Server features that you have specified.
21. The Ready to Install page displays a tree view of installation options that were specified during Setup. To continue, click Install.
During installation, the Installation Progress page provides status so that you can monitor installation progress as Setup continues. After installation, the Complete page provides a link to the summary log file for the installation and other important notes.
22. To complete the SQL Server installation process, click Close.
23. If you are instructed to restart the computer, do so now. It is important to read the message from the Installation Wizard when you have finished with Setup. For information about Setup log files, see How to: View and Read SQL Server Setup Log Files.
24. Repeat the previous steps to prepare the other nodes for the failover cluster. You can also use the autogenerated configuration file to run prepare on the other nodes. For more information, see How to: Install SQL Server 2008 R2 Using a Configuration File.
http://i.msdn.microsoft.com/Hash/030c41d9079671d09a62d8e2c1db6973.gifComplete

Advanced/Enterprise Failover Cluster Install Step 2: Complete

1. After preparing all the nodes as described in the prepare step, run Setup on one of the prepared nodes, preferably the one that owns the shared disk. On the Advanced page of the SQL Server Installation Center, click Advanced cluster completion.
2. The System Configuration Checker runs a discovery operation on your computer. To continue, click OK. You can view the details on the screen by clicking Show Details, or as an HTML report by clicking View detailed report.
3. On the Setup Support Files page, click Install to install the Setup support files.
4. The System Configuration Checker verifies the system state of your computer before Setup continues. After the check is complete, click Next to continue. You can view the details on the screen by clicking Show Details, or as an HTML report by clicking View detailed report.
5. On the Language Selection page, you can specify the language for your instance of SQL Server if you are installing on a localized operating system and the installation media includes language packs for both English and the language corresponding to the operating system. For more information about cross-language support and installation considerations, see Local Language Versions in SQL Server.
To continue, click Next.
6. Use the Cluster node configuration page to select the instance name prepared for clustering, and specify the network name for the new SQL Server failover cluster. This is the name that is used to identify your failover cluster on the network.
NoteNote
This is known as the virtual SQL Server name in earlier versions of SQL Server failover clusters.
7. Use the Cluster Resource Group page to specify the cluster resource group name where SQL Server virtual server resources will be located. To specify the SQL Server cluster resource group name. You have two options:
o Use the list to specify an existing group to use.
o Type the name of a new group to create. Be aware that the name "Available storage" is not a valid group name.
8. On the Cluster Disk Selection page, select the shared cluster disk resource for your SQL Server failover cluster. The cluster disk is where the SQL Server data will be put. More than one disk can be specified. The Available shared disks grid displays a list of available disks, whether each is qualified as a shared disk, and a description of each disk resource. Click Next to continue.
NoteNote
The first drive is used as the default drive for all databases, but can be changed on the Database Engine or Analysis Services configuration pages.
9. On the Cluster Network Selection page, specify the network resources for your failover cluster instance:
o Network Settings — Specify the IP type and IP address for your failover cluster instance.
Click Next to continue.
10. Work flow for the rest of this topic depends on the features that you have specified for your installation. You might not see all the pages, depending on your selections.
11. Use the Database Engine Configuration — Account Provisioning page to specify the following:
o Security Mode - select Windows Authentication or Mixed Mode Authentication for your instance of SQL Server. If you select Mixed Mode Authentication, you must provide a strong password for the built-in SQL Server system administrator account.
After a device establishes a successful connection to SQL Server, the security mechanism is the same for both Windows Authentication and Mixed Mode. For more information, see Database Engine Configuration - Account Provisioning.
o SQL Server Administrators - You must specify at least one system administrator for the instance of SQL Server. To add the account under which SQL Server Setup is running, click Add Current User. To add or remove accounts from the list of system administrators, click Add or Remove, and then edit the list of users, groups, or computers that will have administrator privileges for the instance of SQL Server. For more information, see Database Engine Configuration - Account Provisioning.
When you are finished editing the list, click OK. Verify the list of administrators in the configuration dialog box. When the list is complete, click Next.
12. Use the Database Engine Configuration - Data Directories page to specify nondefault installation directories. To install to default directories, click Next.
Important noteImportant
If you specify nondefault installation directories, make sure that the installation folders are unique to this instance of SQL Server. None of the directories in this dialog box should be shared with directories from other instances of SQL Server. The data directories should be located on the shared cluster disk for the failover cluster.
14. Use the Analysis Services Configuration — Account Provisioning page to specify users or accounts that will have administrator permissions for Analysis Services. You must specify at least one system administrator for Analysis Services. To add the account under which SQL Server Setup is running, click Add Current User. To add or remove accounts from the list of system administrators, click Add or Remove, and then edit the list of users, groups, or computers that will have administrator privileges for Analysis Services. For more information, see Analysis Services Configuration - Account Provisioning.
When you are finished editing the list, click OK. Verify the list of administrators in the configuration dialog box. When the list is complete, click Next.
15. Use the Analysis Services Configuration — Data Directories page to specify nondefault installation directories. To install to default directories, click Next.
Important noteImportant
If you specify nondefault installation directories, make sure that the installation folders are unique to this instance of SQL Server. None of the directories in this dialog box should be shared with directories from other instances of SQL Server. The data directories should be located on the shared cluster disk for the failover cluster.
17. The System Configuration Checker runs one more set of rules to validate your configuration with the SQL Server features that you have specified.
18. The Ready to Install page displays a tree view of installation options that were specified during Setup. To continue, click Install.
19. During installation, the Installation Progress page provides status so that you can monitor installation progress as Setup continues.
20. After installation, the Complete page provides a link to the summary log file for the installation and other important notes. To complete the SQL Server installation process, click Close. With this step, all the prepared nodes for the same failover cluster are now part of the completed SQL Server failover cluster
Read more »
Rss: Top
 

Copyright © 2010 SQL Cached, All Rights Reserved. Design by DZignine