Befriending Dragons

Transform Tech with Anti-bullying Cultures


Leave a comment

Data Gathering – SQL Server Setup Problems

Data Gathering – SQL Server Setup Problems

Cindy Gross, Dedicated Support Engineer

 

Gathering the right data during a problem can get you a long ways towards resolving the problem. When you ask the right questions and clearly define the problem it changes the way you approach the remaining steps. Sometimes the answer to a seemingly simple question leads you right to the solution. In this series of data gathering blogs I am NOT going to tell you how to solve the problem, but I am going to tell you how to get a good set of troubleshooting data you can use to do your own troubleshooting.

Any time you have a SQL Server setup issue you will need to collect this information:

 

  • Date/time of the failed setup
  • Type of install and parameters used – GUI, unattended/command line, slipstream, sysprep
  • Number of attempts, any actions you took to cleanup or remove prior installs
  • Version/Edition/Exact build #/32bit or x64 or IA64 for
    • Windows
    • SQL Server
    • Virtualization software (Hyper-V, VMWare for example)
  • How many cluster nodes and SQL instances or state that it’s standalone
  • Whether the problem is with RTM, SP, and/or CU
  • Whether the problem is with a new install or an upgrade
  • What components you are installing (Client tools, SSIS, SQL, RS, AS, BOL, samples) and whether any succeed
  • Exact error message
  • Repro steps and/or failure scenario
  • All setup logs (i.e. %ProgramFiles%Microsoft SQL Serverx0Setup BootstrapLOG)
  • Which step in the build doc you are at when you see the setup failure
  • What permissions have been enabled/granted for MSDTC
  • Any steps you take to attempt to resolve the problem and the result
  • If on a cluster, have you re-run the cluster verification log and addressed all errors and warnings?
  • Did you run setup using “run as administrator”?
  • What account did you log into Windows with when you ran setup and what groups does it belong to?

Armed with this information you are now ready to solve the problem!


3 Comments

Using Mount Points with SQL Server

Using Mount Points with SQL Server

Author: Cindy Gross, Dedicated Support Engineer

Tech Reviewer: Min He, SQL Cluster PM

 

Summary: Microsoft does NOT support installing or putting databases on the “root” of a mount point in SQL Server 2005, 2008, 2008 R2 unless you manually manage the ACLs/permissions yourself (modified 1/13/12).

 The key points for mount points in combination with SQL Server 2005, 2008, 2008 R2 are:

  • A valid mount point that can be used by SQL Server databases is one that is mounted to a host volume (a shared drive in a cluster) that is associated with a drive letter. Multiple mount points can be hosted by a single drive meaning multiple mount points share a drive letter.
  • In a cluster, SQL Server must depend on each mount point it uses to avoid database corruption.
  • Do not put a SQL Server 2000 instance in a cluster where mount points exist, whether or not SQL Server 2000 is expected to use them (which it cannot). Do not add mount points to a cluster where SQL 2000 is already installed or future SQL Server 2000 patches will break.
  • Do not install SQL Server to the root directory of a mount point, always specify a subdirectory for all files. This has to do with how permissions are granted. If you must put files in the root of the mount point you must manually manage the ACLs/permissions.
  • Do not put DTC on a mount point.

For various reasons such as standardization, flexibility, space management, and just not enough letters in the alphabet many people use mount points on their servers. A mount point (aka mounted drive or volume junction) is a separate file system that is “mounted” onto a host drive so that it appears to be a subdirectory of the host drive. For example, say you have LUN volume A that is made visible to Windows as drive X:. You have a LUN volume B from another storage array and you want to present it to Windows. You might choose to mount it as X:SQL1. To SQL it looks like a subdirectory, but it’s really a whole different file system. Because it is a different file system, permissions are not inherited from the host system. So when you grant permissions to X: and say to propagate them to child folders, they are NOT applied to the mount point!

For testing purposes, try mounting a USB drive to your client box as C:USBMountPoint. You can move files to C:USBMountPoint and they will appear on the USB drive. Disconnect the USB drive and attach it to another box and those files are available on the new system but NOT on the old C:USBMountPoint location. That’s because the files aren’t really on C:, they’re on the USB drive that was mounted to C: for a while.

Example of creating a mount point on non-clustered disks:

  • Attach a USB drive formatted to NTFS
    • In my example it showed up as G:
    • It has a volume name of CGROSSFLASH
    • There is a file called BeforeMount.txt on the drive
  • Open “Disk Management” (Diskmgmt.msc)
  • Right click on CGROSSFLASH and choose “Change Drive Letter and Paths ”
  • Click “Remove” for G: then “Yes”
  • Right click on CGROSSFLASH  and choose “Change Drive Letter and Paths ”
  • Choose “Add” then “Mount in the following empty NTFS folder”
  • Click the “New Folder” button and give the directory a name, I’ll use USBMountPoint
  • Click “OK”
  • C:USBMountPoint is the root of my mount point. C: is the root of the host. You can see test.txt in C:USBMountPoint.
  • Try creating various files and directories in C:USBMountPoint.
  • Now remove the mount point then re-add it but create new folders first. You will end up mounting C:USBMountPointLevel2Level3. The files you created before now show up in the Level3 directory, not in the USBMountPoint directory.

 SQL Server 2000 and later standalone boxes and SQL Server 2005 and later clustered instances are supported on mount points mounted to a host volume (a shared drive in a cluster) that is associated with a drive letter. However, you must put all databases, including the system databases, on a subdirectory of the mount point, not in the root of the mount point, unless you manually manage the ACLs/permissions. The root of a mount point is the entire directory as defined when you mount it. For example, say I mount d:SQL1. That is the root of the mount point and I cannot install to it. If I mount d:SQL1ALL_DATA that is the root and I cannot install to d:SQLALL_DATA. I could install SQL to a subdirectory/subfolder such as d:SQL1ALL_DATADATA. If you install SQL to the root of a mount point setup makes the assumption that permissions are propogated up from the host root (d: in this example). However, that is not how Windows works with mount points. When you create a subdirectory setup creates permissions explicitly and therefore setup works when you install to a subdirectory of the mount point. The same logic applies to adding new databases or moving existing databases, they should not be in the root of a mount point.

In addition to putting the databases in subdirectories, you also have to make SQL depend on each individual mount point. When you make SQL depend on a mount point you force that mount point to come online before SQL does and therefore prevent certain types of potential corruption.

It is becoming more and more common in Windows 2008 and later clusters to have DTC depend on one of the lesser used/less important SQL Server drives. However, if you are using mount points for your SQL files you cannot do this as currently DTC is not supported on mount points.

 Mount points are a very useful tool. As long as you follow the rules for SQL Server you can increase your flexibility, spread your IO out over more IO paths, and/or add space to the system from multiple storage system with mount points.

 References:


Leave a comment

New SQL Server Setup Portal is Live!

There’s a new setup portal to help you out with any SQL Server 2008 or SQL Server 2008 R2 setup issues. You can search for setup content, get downloads, watch videos and more. Learn how to slipstream (new in SQL 2008 SP1) or sysprep (new in R2). Check it out and let us know what you think!

SQL Server Setup http://technet.microsoft.com/en-us/sqlserver/ff625277.aspx


Leave a comment

How to slipstream SP1 based CU5 into SQL Server 2008

Starting with SQL Server 2008 SP1 you can “slipstream” SP1 and/or SP1 + SP1 based CUs to avoid a multi-step installation process.

 

http://blogs.msdn.com/petersad/archive/2009/04/16/create-a-merged-slipstream-drop-containing-sql-server-2008-server-pack-1-and-a-cumulative-update-cu-based-on-server-pack-1.aspx

 

To install a slipstreamed instance with SQL08+SP1+SP1-based-CU5 I followed these steps based on the above blog by Peter Saddow:

1)      You will probably want to rename the directory (and therefore update the CUSource entry in the Defaultsetup.ini files) to indicate exactly which CU you have slipstreamed.

2)      Instead of the blog reference in his step 1, you can optionally follow the more official KB 955392, choosing option/procedure 2 (Create a merged drop) to create the slipstream files of RTM+SP1.

a.       To make sure I got the commands exactly right I cut/pasted them into a .cmd file and ran that.

b.      Do NOT do step 7 “Start the Setup program” because you still need to add the CU files.

3)      Download your CU of choice which is likely different than what is listed in the blog. For SP1 based CU5 you will download the first file (SQL_Server_2008_SP1_Cumlative_Update_5) from http://support.microsoft.com/kb/975977.

4)      For CU5, run 399273_intl_x64_zip.exe, 399273_intl_ia64_zip.exe, and 399273_intl_ia64_zip.exe to extract the files SQLServer2008-KB975977-{architecture}.exe (which are the actual setup files for each architecture). You will need the password from the hotfix email you got from the 975977 link/email.

5)      In the blog’s step 4 which tells you to extract files from the setup files, use your actual KB number. For SP1-based-CU5 the KB is 975977 so the commands will be SQLServer2008-KB975977-{architecture}.exe /x:c:SQLServer2008_FullSP1_CUCU

6)      Run setup with the “run as admin” option on Windows 2008/Vista/Windows 7.

7)      If you later move the setup files to a different location you will need to update the Defaultsetup.ini file in each of the architecture directories (x86, x64, ia64).


Leave a comment

SQL Server 2008 setup may report a Windows 2008 cluster validation failure

You may receive the error “The cluster either has not been verified or there are errors or failures in the verification report” while installing a SQL Server clustered installation on Windows 2008. When see this error you must fix the underlying error that caused Windows validation to fail. Even though in some cases you could avoid the error and continue SQL Server setup with the undocumented SkipRules setting, this is not advisable and will leave you in an unsupported state (unless you are using a geo cluster with no shared storage).

 

The Microsoft Support Policy for Windows Server 2008 Failover Clusters

http://support.microsoft.com/kb/943984

For the Windows Server 2008 Failover Clustering solution to be considered an officially supported solution by Microsoft Customer Support Services (CSS), the solution must meet the following criteria:

·         All hardware and software components must meet the qualifications to receive a “Certified for Windows Server 2008” logo.

·         The fully configured solution must pass the Validate test in the Failover Clusters Management snap-in.

 

Failover Cluster Step-by-Step Guide: Validating Hardware for a Failover Cluster

http://technet.microsoft.com/en-us/library/cc732035(WS.10).aspx

What to do if validation tests fail

In most cases, if any tests in the cluster validation wizard fail, then Microsoft does not consider the solution to be supported. There are exceptions to this rule, such as the case with multi-site (geographically dispersed) clusters where there is no shared storage. In this scenario the expected result of the validation wizard is that the storage tests will fail. This is still a supported solution if the remainder of the tests complete successfully.

 

Error message when you install SQL Server 2008 on a Windows Server 2008-based cluster: “The cluster either has not been verified or there are errors or failures in the verification report. Refer to KB953748 or SQL server books online”

http://support.microsoft.com/kb/953748

What to do if validation tests fail

In most cases, if any tests in the cluster validation rule fail, Microsoft does not consider the solution to be supported. There are exceptions to this rule, such as the case with multiple-site (geographically dispersed) clusters where there is no shared storage. In this scenario, the expected result of the cluster validation wizard is that the storage tests will fail. This is still a supported solution if the rest of the tests finish successfully.


3 Comments

How to tell what edition your SQL Server setup files are

The setup files for SQL Server look pretty much the same for all editions. If you saved the files somewhere but didn’t indicate in your directory name or a readme file which edition they are for, how do you tell the edition? Running setup doesn’t necessarily help, the initial setup screens for SQL Server 2005 Enterprise and Standard both show the same EULA that includes “MICROSOFT SQL SERVER 2005 STANDARD AND ENTERPRISE EDITIONS”. To see the edition of the setup files, look in the Servers directory of your installation files from the setup CD (there are two high level directories: Servers and Tools). In Servers there is a file called default.htm and when you open it, you will see the edition of your SQL Server setup files.


Leave a comment

SQL Server 2005 Clustering Tips/References

I have copied this over from an older blog. I have cleaned it up a bit to clarify a few areas and added some links.

SQL Server 2005 Clustering Tips/References

This information will supplement the clustering information I wrote in chapter 10 of SQL Server 2005 Practical Troubleshooting: The Database Engine  

— Handy cluster related info

select SERVERPROPERTY(‘IsClustered’) as _1_Means_Clustered

, SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as CurrentNode

, SERVERPROPERTY(‘Edition’) as Edition

, SERVERPROPERTY(‘MachineName’) as VirtualName

, SERVERPROPERTY(‘InstanceName’) as InstanceName

, SERVERPROPERTY(‘ServerName’) as Virtual_and_InstanceNames

, SERVERPROPERTY(‘ProductVersion’) as Version

, SERVERPROPERTY(‘ProductLevel’) as VersionNameWithoutHotfixes

select * from sys.dm_io_cluster_shared_drives

select * from sys.dm_os_cluster_nodes

Best practices

Setup (RTM, Service Pack, Cumulative Update, or hotfix)

·         The account you use to launch setup must be a local admin on all nodes. However, it is not required that the account you choose to assign as the service account for each service be a local admin. Only setup requires local admin permissions.

·         Install and cluster DTC before installing any SQL instance. For Windows 2003, DTC should ideally go in its own group with its own disk and IP address. Second best is to place it in the cluster group and make it depend on the quorum disk and IP address. For Windows 2008 see this blog.

·        For SQL 2005, the client tools, SSIS, NS, and RS are only installed on the node where setup was run because they are not cluster aware. If you are installing as many or more instances than nodes, you can run the install program for each instance from a different node so the non cluster aware components are installed on each node. Otherwise install the non cluster aware components later on the other node(s). This means that the service packs and hotfixes must be installed on each node as well so that the non cluster aware components are updated. Setup is run once per instance and that will update all the cluster aware and components for that instance. The first time setup (RTM, SP, hotfix) is run from each node it will also update any non cluster aware components on the box such as the tools, SSIS, NS, and RS.

·         All nodes should be configured identically.

·         Windows level policies should be the same on all nodes.

·         Remote Registry, Cryptography services, and Task scheduler must be started on all nodes during the setup process.

·         No Terminal Services users can be logged in on any remote nodes during setup.

·         Do not use quotes in the password for SQL service account.

·         Do not allow <, >, ‘, “, & in the cluster group names.

·         Virtual Server name should be 14 characters or less.

·         NIC name cannot have trailing spaces.

·         Stop any non-essential applications or services as they may hold open files that setup needs to modify or may otherwise interfere with setup.

·         All nodes must have access to setup files without prompting for credentials.

·         All disks in all groups must be online during setup.

·         All other, existing instances in the cluster must have valid, non-UNC paths for the SQL Server registry keys

·         If there are many trusts for the domain the nodes/service account reside in, see KB 910070 before running setup

·         For Windows 2003, the complete node must be on HCL, for Windows 2008 each node in the cluster must pass a validation.

·         Copy the setup files to the “primary” node or at least make sure all nodes can access the setup files without being prompted for credentials.

·         Any mounted drives must have an associated drive letter and be clustered, even if they will not be used by SQL Server. Do not use mounted drives anywhere on a cluster if SQL Server 2000 will exist in the cluster. A mounted drive must be in SQL resource group and SQL Server must depend on them if you want that instance of SQL Server to use them for data or log files.

·         Verify you have not installed terminal services, there are no compressed drives, and no node is a domain controller.

·         Disable netbios on private NICs.

·         Pre-create the domain groups needed by setup.

·         Make sure the SQL Server resource is set to “affect the group”. Often it’s best to leave SQL Agent, FTS, and DTC to not “affect the group” but that depends on your business needs.

·         Test failover of all groups before any SQL setup, test again before any service packs or hotfixes. If any group gets an error during failover, address that problem before running SQL setup.

After setup

·         Go back to the cluster administrator and take the SQL Server resource offline. Make SQL Server dependent on the disk(s) and mount points in their group (any disk or mount point where you need to create data files, log files, or full text catalogs) then bring SQL Server, SQL Agent, and FTS back online.

·         For the SSIS service, %ProgramFiles%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml must have the SQL virtual server name instead of “.”

·         Configure memory to handle instances moving due to failover.

·         If you installed NS, you now have to configure it separately.

·         Leave SQL services set to manual in the services applet.

Maintenance

·         Use add/remove programs to add/remove nodes.

·         Add/remove non-cluster aware components must be done from cmd line.

·         Can rename the virtual server name but not the instance name.

·         Make IP changes in cluster admin instead of setup.

·         Cluster service account must have a login in SQL, must be sysadmin only for FTS.

·         Clustered SQL Server startup account can only be a local admin if SQL 2000/7.0 is not installed side-by-side with 2005. Even if a lower version is not installed side-by-side it is best not to make the SQL Server startup account a local administrator or otherwise give it excessive permissions.

·         After adding new node, fail to that node to  apply SPs/hotfixes.

·         For Kerberos on a cluster, you need two SPNs per instance, one with and one without the port. They must belong to the current SQL Server startup account and not to any other accounts.

·         If you use SSL encryption, install certificates on all nodes before turning on the SSL encryption.

·         If antivirus is installed, see http://support.microsoft.com/kb/250355

References:

·         Updated Books Online: http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

·         915846  Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;915846

·         819546  SQL Server 2000 and SQL Server 2005 support for mounted volumes http://support.microsoft.com/default.aspx?scid=kb;EN-US;819546

·         913815  Error message when you install a SQL Server 2005 failover cluster on a node: “The drive specified cannot be used for program location” http://support.microsoft.com/default.aspx?scid=kb;EN-US;913815

·         922670  How to use the Add or Remove Programs item in Control Panel to add or remove components for stand-alone installations and clustered installations of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;922670

·         910230  How to install SQL Server 2005 Analysis Services on a failover cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;910230

·         910233  Migrate a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;910233

·         912397  The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid http://support.microsoft.com/default.aspx?scid=kb;EN-US;912397

·         910851  You receive error messages when you try to set up a clustered instance of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;910851

·         926621  Error message when you try to install SQL Server 2005 in a cluster environment: “SQL Server Setup could not validate the service accounts” http://support.microsoft.com/default.aspx?scid=kb;EN-US;926621

·         327518  The Microsoft SQL Server support policy for Microsoft Clustering http://support.microsoft.com/default.aspx?scid=kb;EN-US;327518

·         254321  Clustered SQL Server do’s, don’ts, and basic warnings http://support.microsoft.com/default.aspx?scid=kb;EN-US;254321

·         942176  Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service http://support.microsoft.com/default.aspx?scid=kb;EN-US;942176

·         922209  The SQL Server 2005 Setup program does not remove all IP address cluster resources when you uninstall SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;922209

·         295732  How to create databases or change disk file locations on a shared cluster drive on which SQL Server was not originally installed http://support.microsoft.com/default.aspx?scid=kb;EN-US;295732

·         263712  How to impede Windows NT administrators from administering a clustered instance of SQL Server http://support.microsoft.com/default.aspx?scid=kb;EN-US;263712

·         932881  How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult http://support.microsoft.com/default.aspx?scid=kb;EN-US;932881

·         934749  BUG: Error message when you try to install SQL Server 2005 Service Pack 1 or SQL Server 2005 Service Pack 2 from the existing active node: “The product instance <InstanceName> been patched with more recent updates” http://support.microsoft.com/default.aspx?scid=kb;EN-US;934749

·         283811  How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;283811

·         910070  FIX: The SQL Server 2005 Setup program may take much longer than expected to finish running http://support.microsoft.com/default.aspx?scid=kb;EN-US;910070

·         909967  How to uninstall an instance of SQL Server 2005 manually http://support.microsoft.com/default.aspx?scid=kb;EN-US;909967

Windows 2003 SP2 is better than SP1 because:

·         918483  How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483

·         922658  SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer http://support.microsoft.com/default.aspx?scid=kb;EN-US;922658

·         904160 Network performance is slower than expected in Windows Server 2003 SP1 http://support.microsoft.com/?id=904160

Cluster specific:

·         923830  Recommended hotfixes for Windows Server 2003 Service Pack 1- based server clusters http://support.microsoft.com/default.aspx?scid=kb;EN-US;923830