Monday, August 29, 2011

Clustered SQL 2008 R2 Installation Failure

Installing SQL 2008 R2 on a Windows 2008 R2 cluster can be a challenge!   

An example of the problems experienced during installation is when one of the DBAs from my team had a failed SQL Server clustered installation.  Specifically, SQL Server installed on the initial node, but failed to add itself to the second node of a two node cluster.  Attempts to fix the installation were not initially successful as the process seemed to end after the Microsoft SQL Server Setup program with no further action.

The fix to the problem I performed the following steps:
  • Uninstall Microsoft SQL Server Setup
  • Delete the C:\Program Files\Microsoft SQL Server directory
  • Delete HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server hive
The problem seems to be with the SQL Server Setup program that executes prior to any install or fix of SQL Server 2008.  I'm not sure, but there seems to be residual information from previous install attempts that keep additional install attempts from being successful.  There are a number of articles and blog posts on this subject.....enough of them that this program seems to be problematic for many people.

We also had another issue.  The cluster check failed on the node where we were adding the SQL Server node to the SQL clustered instance.  The cluster check succeeded on the other nodes, so I'm working on the assumption that the cluster is actually fine.  How to get past this cluster check?  Use the undocumented setup option skiprules
Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
This setup option is referred to in kb953748.

Friday, August 26, 2011

SQL 2008 R2 Install Error: 2337

We seem to be getting 2337 errors on a regular basis when installing SQL Server 2008 R2 x64.  Very frustrating.   It seems that when one issue occurs (bad password, invalid entry, etc), the likelihood of getting a 2337 increases.  The best solution for the 2337 is a Microsoft KB article found here.

Wednesday, August 17, 2011

Benefits of Using soft-NUMA

SQL Server is Non-Uniform Memory Access (NUMA) aware.  SQL Server uses NUMA out of the box without additional configuration.

SQL Server also allows you to group CPUs into nodes.  This is referred to as soft-NUMA. You usually configure soft-NUMA when you have many CPUs and do not have hardware NUMA, but you can also use soft-NUMA to subdivide hardware NUMA nodes into smaller groups.  Only the SQL Scheduler and SQL Network Interface use soft-NUMA.

Why use soft-NUMA?

The benefits of soft-NUMA include reducing I/O and lazy writer bottlenecks on computers with many CPUs and no hardware NUMA.  Configuring four soft-NUMA nodes provides four I/O threads and four lazy writer threads, which could increase performance.

Details on MSDN here.

Wednesday, August 3, 2011

FILESTREAM Use with High Availability

FILESTREAM is not supported by all replication/HA features.  Technet details are here.  A brief summary:
  •   Database Snapshots:  NO, NOT supported
  •   Mirroring:  NO, NOT supported
  •   Replication:  YES, Is supported
  •   Log Shipping:  YES, Is supported
  •   Failover Clustering:  YES, Is supported
Note that when FILESTREAM used with clustering, the filestream filegroup is required to be on a clustered shared resource.
 

Database TRUSTWORTHY Property

The TRUSTWORTHY database property indicates whether the SQL instance trusts the database to access objects external to the database.  When the TRUSTWORTHY property is set to ON, it allows database modules that use an impersonation context to have access to resources outside the database. 

For example, user-defined functions, stored procedures, or assemblies may access resources outside the database.   Also, impersonation of database level principals or users (known as extending database impersonation) by using the EXECUTE AS USER statement allows access outside the database.

When migrating a database that requires external access (and the TRUSTWORTHY property is ON), you need to reset the property after migration.  Since the SQL instance does not automatically trust databases as a default, all databases are set to TRUSTWOTHY OFF when created or attached. 

To set the TRUSTWORTHY property:
ALTER DATABASE <database name>
SET TRUSTWORTHY { ON | OFF }


Modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully.