HARDWARE REQUIREMENTS FOR SQLSERVER CLUSTER BUILD ON A WINDOWS CLUSTER

•  Microsoft Windows Server 2008 R2 with SP1 Enterprise Edition is installed on both computers in the cluster.
• All nodes are added to the domain
• A domain-level account that is a member of the local administrators group on each node.
• A dedicated account is recommended.
• Two PCI network adapters on each node in the cluster.
• All hardware should be identical, slot for slot, card for card, BIOS, firmware revisions, and so on, for all nodes.
• This makes configuration easier and eliminates compatibility problems.
• Static IP addresses for all network interfaces on each node.
• Each node must have at least two network adapters—one for connection to the client public network and the other for the node-to-node private cluster network
• All nodes must have two physically independent LANs or virtual LANs for public and private communication.
• All shared disks, including the quorum disk, must be physically attached to a shared bus.
• Verify that disks attached to the shared bus can be seen from all nodes.
• All shared disks must be configured as basic disks with RAID 5 - Logical Array and added to the windows cluster .
• Required at least 3 shared disk/Mount for DATA, LOG and TEMPDB for each Instance
• All partitions on the clustered disks must be formatted as NTFS with 64-KB allocation unit.
• Cluster Quorum Disk Space Recommendation – 1 GB.
• Quorum Configuration – Node and Disk Majority (Quorum)
• IP Address and SQL Virtual/Cluster Name for SQL Instance
• Cluster name will be added in DNS with virtual IP
• Verify the Node Failover and Storage Failover
• Configure MSDTC in the Cluster , It requires an IP  and Disk
• MSDTC Disk Space Recommendation – 1 GB

PRE INSTALLATION CHECKS AND CONFIGURATIONS

Common Software Requirement

•  SQL Server setup program requires Microsoft Windows Installer 4.5 or later.
• DOT NET Framework 3.5.1
• Microsoft Internet Explorer (IE) 6 SP1 or later is required.
• For client connectivity, SQL Server 2008 R2 also requires the Shared Memory.Named Pipes, or TCP/IP network protocols.
• SQL Server 2008 R2 Enterprise Edition with Service Pack 1 Software setup files.
• Collation Settings Requirements for SQLServer

POST INSTALLATION CHECKS AND CONFIGURATIONS 

• Configure the Possible Owners to the SQL Instance
• Check the dependencies for SQL Instances which are properly configured
• Validate Failover of Services and Application which includes all SQL Related Services and  Disks
• Connect to SQL Instance and verify
• Add the SQL Service Account ,DBA group in the SQL instance
• Configure Min Server Memory and Max Memory  for SQL Server Instances depending on the available Physical Memory in the Sever
• Configure MAX DOP in the SQLServer depends on the number of CPU in the Server.

SQL 2008R2 SP2 installation fails on a Cluster with Windows 2008 R2 64 bit -Error Element not found. (Exception from HRESULT: 0x80070490)

When installing SP2 on a SQL 2008R2  Cluster, the installation fails with no apparent reasons. No errors are listed in the event logs

  Final result:                  The patch installer has failed to update the following instance: <Instance Name >. To determine the reason for failure, review the log files.

  Exit code (Decimal):           -568706566

  Exit facility code:            1562

  Exit error code:               14842

  Exit message:                  The patch installer has failed to update the following instance: <Instance Name >. To determine the reason for failure, review the log files.

  Start time:                    2014-08-09 21:10:32

  End time:                      2014-08-09 21:35:56

  Requested action:              Patch

Instance <,Instance Name> overall summary:

  Final result:                  The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.

  Exit code (Decimal):           -568706566

  Exit facility code:            1562

  Exit error code:               14842

  Exit message:                  Element not found. (Exception from HRESULT: 0x80070490)

  Start time:                    2014-08-09 21:24:36

  End time:                      2014-08-09 21:35:50

  Requested action:              Patch

 Cause :

 The error is caused if there are offline and removed disks inside the cluster

 Resolution:

 1) Run the Windows Cluster Validation Report and see the error   "Element not found." in Disk  Configuration "

 2)The error is caused if there are  offline and removed disks inside the cluster (that are no longer  presented to the server nodes). 

3)Removing the disks and re-run the installer will fix the problem

 

DMVS USED IN SQL SERVER 2005/2008/R2/ 2012

The DMVs Used in  SQL Server 2005

    CLR related DMVs

    Sys.dm_clr_appdomains

    Sys.dm_clr_loaded_assemblies

    Sys.dm_clr_properties

    Sys.dm_clr_tasks

    Database related Dmvs

    Sys.dm_db_file_space_usage

    Sys.dm_db_session_space_usage

    Database Mirroring related Dmvs

    Sys.dm_db_mirroring_connections

    Execution related DMVs and functions

    Sys.dm_exec_background_job_queue

    Sys.dm_exec_background_job_queue_stats

    Sys.dm_exec_cached_plans

    Sys.dm_exec_connections

    Sys.dm_exec_cursors

    Sys.dm_exec_plan_attributes

    Sys.dm_exec_query_memory_grants

    Sys.dm_exec_query_optimizer_info

    Sys.dm_exec_query_plan

    Sys.dm_exec_query_resource_semaphores

    Sys.dm_exec_query_stats

    Sys.dm_exec_requests

    Sys.dm_exec_sessions

    Sys.dm_exec_sql_text

    Full Text Search Related DMVs

    Sys.dm_fts_active_catalogs

    Sys.dm_fts_index_population

    Sys.dm_fts_memory_buffers

    Sys.dm_fts_memory_pools

    Sys.dm_fts_population_ranges

    Input/output Related DMVs and Functions

    Sys.dm_io_backup_tapes

    Sys.dm_io_cluster_shared_drives

    Sys.dm_io_pending_io_requests

    Sys.dm_io_virtual_file_stats

    Index Related DMVs

    Sys.dm_db_index_operational_stats

    Sys.dm_db_index_physical_stats

    Sys.dm_db_index_usage_stats

    Sys.dm_db_missing_index_columns

    Sys.dm_db_missing_index_details

    Sys.dm_db_missing_index_group_stats

    Sys.dm_db_missing_index_groups

   

Query Notifications Related DMVs

    Sys.dm_qn_subscriptions

    Service Broker Related DMVs

    Sys.dm_broker_activated_tasks

    Sys.dm_broker_connections

    Sys.dm_broker_forwarded_messages

    Sys.dm_broker_queue_monitors

    SQL Server Operating System Related DMVs

    Sys.dm_os_buffer_descriptors

    Sys.dm_os_child_instances

    Sys.dm_os_cluster_nodes

    Sys.dm_os_hosts

    Sys.dm_os_latch_stats

    Sys.dm_os_loaded_modules

    Sys.dm_os_memory_cache_clock_hands

    Sys.dm_os_memory_cache_counters

    Sys.dm_os_memory_cache_entries

    Sys.dm_os_memory_cache_hash_tables

    Sys.dm_os_memory_clerks

    Sys.dm_os_memory_objects

    Sys.dm_os_memory_pools

    Sys.dm_os_performance_counters

    Sys.dm_os_schedulers

    sys.dm_os_stacks

    sys.dm_os_sys_info

    sys.dm_os_tasks

    sys.dm_os_threads

    sys.dm_os_virtual_address_dump

    sys.dm_os_wait_stats

    sys.dm_os_waiting_tasks

    sys.dm_os_workers

    Transaction Related DMVs & Functions

    sys.dm_tran_active_snapshot_database_transactions

    sys.dm_tran_active_transactions

    sys.dm_tran_current_snapshot

    sys.dm_tran_current_transaction

    sys.dm_tran_database_transactions

    sys.dm_tran_locks

    sys.dm_tran_session_transactions

    sys.dm_tran_top_version_generators

    sys.dm_tran_transactions_snapshot

    sys.dm_tran_version_store

    Database related Dmvs

    Sys.dm_db_partition_stats

    Replication Related DMVs

    Sys.dm_repl_articles

    Sys.dm_repl_schemas

    Sys.dm_repl_tranhash

    Sys.dm_repl_traninfo

These new DMVs of SQL Server 2008 R2 SP1 and SQL Server 2012 

sys.dm_os_windows_info

sys.dm_os_volume_stats

sys.dm_server_services

sys.dm_exec_query_stats

sys.dm_server_memory_dumps,

sys.dm_server_services

TempDB files Recommendations for SQLSERVER

•             Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. 

•             The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions. 

•             Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.

•             Make each data file the same size; this allows for optimal proportional-fill performance.

•             Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many 

directly attached disks.

•             Put the tempdb database on disks that differ from those that are used by user databases.

•             No. Of CPU v/s Data Files Recommendation

No. of Cores       Data files recommended

<8           = No. of Cores

>=8 to <32           = No. of Cores/2

>=32      = No. of Cores/4