SQL server, SQLServerPedia Syndication

SQL 2005 installation oddity on multicore processors.

There is an issue with installing SQL 2005 on systems that have an odd (as in not even) number of cores/processors.  The conditions are as follows:

  • The ratio between logical processors and physical sockets is not a power of 2. For example, the computer has a single socket together with a triple-core processor.
  • The number of physical cores is not a power of 2.
  • The total number of CPUs is an odd number. For example, there are seven or nine CPUs.

Note that these issues are fixed in SP2, but the catch 22 is that SQL 2005 setup (non express editions) is not integrated. That is you need to install SQL 2005 first and then upgrade to SP2. So how will you get to SP2 if you cannot install SQL 2005 at all? There are three methods mentioned by Microsoft:

  1. Copy the SQL 2005 setup and SP2 files to local folders. Expand the SP2 package. Point the SQL 2005 setup binary to the expanded package and run setup. Run the SP2 patch. Link.
  2. Temporarily change the number of logical processors to one. Start>Run>Msconfig>BOOT.INI>Advanced options>/NUMPROC=1. Install SQL 2005 and upgrade to SP2. Change back processor count. Link.
  3. Run SQL 2005 setup. When the setup fails, you get a ‘Retry’ option. Replace the Sqlservr.exe and Sqlos.dll files in the BINN folder with the same files from the SP2 package. Retry. Link.

Note: I am not sure if the author meant ‘power of 2’ (1,2,4,8,16,32) or ‘multiple of 2’ (2,4,6,8,10). The reasoning being that I have installed SQL 2005 on a 24 core (physical) machine.

SQL server, SQLServerPedia Syndication

Changing the name of the SQL server (@@servername)

I was in the process of installing SCOM2012 and came to the part where I had to specify the SQL server name. I quickly logged onto the SQL server and ran SELECT @@servername to double check the SQL server name. Surprisingly, the server name was wrong. The OS server name was sqlcloud, but the SQL server name was testserver.

The SQL server name will usually be the name of the host/physical server. In my case the name was showing something different. I tried to add my server name using the query: sp_addserver ‘sqlcould’, ‘local’; but got the error:

Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 74

The server ‘sqlcould’ already exists

For some reason the right name was stuck up somewhere, and not allowing me to change to the correct name. What to do in this scenario is to drop the server name (sqlcould and testserver) and then add back only sqlcloud.

To change the server name, do the queries below:

sp_dropserver ‘testserver’; sp_dropserver ‘sqlcould’ — remove the conflicting names (don’t drop any linked servers)

sp_addserver ‘sqlcould’, ‘local’ — add in the correct name

Take extra care not to go ahead and drop all the server names you find. Some of them might be linked servers that are required by queries/functions.

After restarting the SQL server I was able to see the correct server name.

Other topics, SQL server

What is a Slipstream installation? See below.

When installing or upgrading operating systems you may come across terms like cumulative update or slipstream installation. So what is this ‘slipstream’ installation? Slipstream install files are those installation packs/ISO files in which the install-software and service pack are integrated. You do not need to install the OS first and then upgrade the service pack level. One installation process will give you the operating system updated to the indicated service pack level.

Needless to say, the first release version (RTM) of the software would not make sense to be a slipstream package. Once the service packs come out, you will find options for slipstream packages or just the service packs.

For example, when SQL2008R2 came out, users could install the software on their system. When SQL2008R2 SP1 came out later, users who already had SQL2008R2 installed could just download SP1 and upgrade their server. If there was a system that did not have SQL2008R2 (after the release of SP1), users had the option to grab a slipstream install and get SQL2008R2 SP1 in one install process.

SQL server

Windows service accounts provisioning for SQL.

When installing SQL on a server, we are faced with a few options as to what account to use. Do we use local network account, local service account, domain user, etc… What do we use for the other service like SSIS, SQL browser, Analysis services etc…

I found a nice BOL link with detailed explanation of what each means and their best practices. No more guessing is required during installation or explanation.

SQL server

SQL 2005 cluster patching.

I generally install SQL 2005 and patch it to SP3. The SP3 patch is a single executable and it is a cumulative patch. Some of the confusions that arises during patching are: Do I patch all the nodes, which node do I patch first, does the server have to be restarted, can SQL be running during the patching process? etc… Here are my ramblings:

Clustering is meant to be a high availability solution. Patching is one of the pro-clustering case scenarios. This means that one of the nodes can be patched, and if it breaks, a fail over to the non-patched working node will save the day. It is suggested to patch the active node first. If there are no issues after patching the active node, then the next node can be patched. SQL can be running during the patching process, but a reboot will be required after patching. If the SQL services are stopped (configuration manager), then a reboot is not required. Please feel free to add any comments if you have any.

SQL server

SQL 2005 installation troubleshooting LaunchLocalBootstrapAction threw an exception during execution.

I was installing a SQL 2005 (cluster) on a windows 2008 R2 server, and had run into an error which I had not seen before: There was an unexpected failure during the setup wizard ….  See the screenshot in the right. I tried again a couple of times to see if it was something that would go away, but it did not. Here is what I found out while troubleshooting the issue:

The setup process logs its steps under C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files. The main install log file is SQLSetup0011_SERVER-NAME_Core.log. The 0011 in the name means that it was my eleventh installation attempt, and the SERVER-NAME will be the name of the server on which SQL was being installed. On viewing the log file, I found the error message “LaunchLocalBootstrapAction” threw an exception during execution. On researching, I found out that this issue was due to the fact that I was using a RDP session to do the installation and some error messages like ‘Insert Disk 2…’ cannot be displayed back to my remote session.

I then remembered that I had unpacked only disk 1 of the 2 disks. So, I went ahead, and unpacked disk 2. Now disk1 and disk2 were in the same folder, and I restarted the install. This time everything went through smoothly. I guess 11th time is the charm in this case.

SQL server, SQLServerPedia Syndication

Unattended SQL install (or auto install).

SQL server installation is pretty easy using the setup GUI. With the many installs that I have done, there were times when multiple SQL installs with the same settings had to be done. We have a production, staging, QA, development, and testing environment for our major applications. The SQL servers had to have the same setting across these environments. One way to install these SQL servers was to do the same process repeatedly, or I could create a parameter file and provided this file to the setup.exe program. This process is called an unattended install, and one file can be passed to all 5 setup programs to install SQL server with the same settings throughout.

Under the same folder of the SQL installation files (Setup.exe) is an initialization file called template.ini. This ini file has a set of commented out examples that you can use. Once this file has been setup (use BOL to find out how), the installation can be run from the command line using:

c:\>start /wait setup.exe /qb /settings c:\path_to_ini_file\template.ini

The /qb will do a quite install with basic GUI. These multiple, quiet, unattended installs gives me a professional feeling about my DBA skills.