The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Archive for the ‘Setup’ Category

Service Pack or Update for SQL Server 2008/R2/2012 fails without generating the setup log files!

Posted by Sudarshan Narasimhan on December 12, 2012


I was recently working on an interesting setup issue with one of our Premier customers. The scenario was this – they were trying to patch their SQL Server 2008 R2 instance with SP1 (KB 2528583) and it was failing, but the log files were not getting generated fully. In fact, the path C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\ did not even have a folder with the latest timestamp. Interestingly, the log files were getting created on the D: drive location D:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\

From D:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\ folder we could only find Summary.txt and Detail.txt

Summary.txt

Overall summary:
  Final result:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
  Exit code (Decimal):           1513770790
  Exit message:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
  Start time:                    2012-11-18 00:54:46
  End time:                      2012-11-18 01:21:23
  Requested action:              Patch

Requested action:              Patch
Log with failure:              C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121118_005431\MSSQLSERVER\SQLSysClrTypes_Cpu64_1.log
Exception help link:           http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.2500.0

Note the highlighted link and that log file were not present at all!

Detail.txt

Every feature that was trying get patched logs this error –> MSI Error 1622. 1622 stands for ERROR_INSTALL_LOG_FAILURE, which means the setup program is not able to log setup messages to the log file and hence failed.

Reference: http://msdn.microsoft.com/en-us/library/windows/desktop/aa368542(v=vs.85).aspx

2012-11-18 00:56:46 Slp: Target package: "g:\c6d70a3c67aacef9d9eff6019ffaf3\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi"
2012-11-18 00:56:46 Slp: InstallPackage: MsiInstallProduct returned the result code 1622.
2012-11-18 00:56:46 Slp: Watson Bucket 1

2012-11-18 01:03:00 Slp: Target package: "g:\c6d70a3c67aacef9d9eff6019ffaf3\1033_ENU_LP\x64\setup\x64\sqlncli.msi"
2012-11-18 01:03:00 Slp: InstallPackage: MsiInstallProduct returned the result code 1622.
2012-11-18 01:03:00 Slp: Watson Bucket 5

I search for the keyword “at microsoft” to find exception stack and I see this error was raised at the end

2012-11-18 01:21:19 Slp: Result error code: 1622
2012-11-18 01:21:19 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
2012-11-18 01:21:19 Slp: Sco: Attempting to open registry subkey
2012-11-18 01:21:19 Slp: Sco: Attempting to open registry subkey Software\Microsoft\PCHealth\ErrorReporting\DW\Installed
2012-11-18 01:21:19 Slp: Sco: Attempting to get registry value DW0201
2012-11-18 01:21:21 Slp: Received request to add the following file to Watson reporting: C:\Users\EGP00\AppData\Local\Temp\2\tmpC720.tmp
2012-11-18 01:21:21 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
2012-11-18 01:21:21 Slp: Inner exceptions are being indented
2012-11-18 01:21:21 Slp:
2012-11-18 01:21:21 Slp: Exception type: System.IO.DirectoryNotFoundException
2012-11-18 01:21:21 Slp:     Message:
2012-11-18 01:21:21 Slp:         Could not find a part of the path ‘C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121118_005431\MSSQLSERVER’.
2012-11-18 01:21:21 Slp:     Stack:
2012-11-18 01:21:21 Slp:         at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
2012-11-18 01:21:21 Slp:         at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
2012-11-18 01:21:21 Slp:         at System.IO.DirectoryInfo.GetFiles(String searchPattern, SearchOption searchOption)
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.WatsonMsiBucket.ExecutePreCabTasks()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.WatsonService.ReportFailures()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Setup.Setup.Start()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Setup.Setup.Main()
2012-11-18 01:21:21 Slp: Watson Bucket 17

The above message is strange since the path clearly exists and some log files are getting created there, but no the one with the failure. From a admin perspective, while this error is being logged, if you look at the setup screen you will see it frozen at this stage (MsiTimingAction),

image

 

To make progress on this, I decided to troubleshoot the log file generation, and why the log files are created on the D: drive.

On a Windows system, the Program Files and Program Files (x86) paths are hard-coded and are set as part of the environment for each program. To open the current program files location, one can just go to Run and type in %programfiles% and hit enter. When I did this on my customer’s machine, it opened up the D:\Program Files path. Now, we’re getting somewhere.

These paths are also stored in the registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion, as shown below.

image

On this machine it was pointing to the D: drive and hence the log files were going there. But some of the log files, were expecting this to always be the C:\ location and hence failing. This explains the log file location mystery.

The solution was fairly simple from here on, since the location of the ProgramFiles cannot be configured and changing it is not supported as per http://support.microsoft.com/kb/933700

I changed the location back to the default value by editing the registry under [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion]

ProgramFilesDir         –> C:\Program Files
ProgramFilesDir (x86) –> C:\Program Files (x86)

You can also make this change for a specific user profile by adding a User Environment Variable called “ProgramFiles” and setting its value to the path of the program files dir.

image

After changing the registry key value, I rebooted for good measure (though I think a logoff and logon should suffice). Now, when I go to Run and do %programfiles% it takes me to the C: drive as expected.

Re-ran the SP1 for SQL Server and it completed fine without issues and the log files were also getting created in the default path

Just thought I’d share this issue since its an example of how system misconfiguration or issues outside of SQL can have an impact of setup and patching.

 

Advertisement

Posted in Setup | Tagged: , , , , , , | 1 Comment »

SQL Server Setup: How to validate downloaded image/media before installation

Posted by Sudarshan Narasimhan on January 27, 2012


You might be wondering why I am posting about media validation. Isn’t this a given thing, after all you are downloading straight from downloads.microsoft.com, so there should be no issues with the downloaded media (.ISO), right? Wrong!

Even if you are downloading from a trustworthy source, always ensure that you have validated the media once the download is complete. For SQL Server ISO images which you downloaded from MSDN or ordered a DVD, it is a best practise to verify that the media is clean & complete. Don’t believe me, have a look at this article for the sort of setup issues like MSI Error: 2337, that you can run into, if you don’t want to spend 5 minutes to verify media integrity 🙂


Various errors may occur when you try to install SQL Server 2008 R2
http://support.microsoft.com/kb/2449398

 

Save yourself the time & trouble with troubleshooting a failed setup. Do the following simple steps to validate SQL Server setup media:-

1. Verify ISO is valid

A) Once you have downloaded an ISO from MSDN, use Microsoft File Checksum Integrity Verifier aka FCIV.exe to verify the checksum. This tool computes and verifies the hashes of any file and will give you a MD5 or SHA1 hash key as the output.

Lets say you downloaded the media file for SQL Server 2008 R2 Standard Edition en_sql_server_2008_r2_standard_x86_x64_ia64_dvd_521546.iso to C:\SQL\ folder from MSDN website.

Download FCIV.exe from the above link and use it as shown below to validate the above ISO file.

image


Note down the SHA1 hash value highlighted above.

B) Login to your MSDN subscription and go to the Subscriber Downloads page and identify the package with same version and edition you downloaded. Click on the Details button and you will see a SHA1 hash value displayed there, like shown below.

image

This value (step A) should match the one of your downloaded ISO package (step B), which you found out using FCIV.exe If it does, then the downloaded package is valid and has no errors/issues/inconsistencies in files etc. You can begin setup using this package and it is a valid & verified stable ISO package.

 

2. Extraction from the ISO Package

The most common practice once you have downloaded an ISO image is to extract its contents to a folder structure. Most of you will have to perform these installations on multiple machines and you will extract this and put it on a common file share. I have seen issues where the extraction of these ISO packages sometimes causes inconsistencies in the media like missing files, invalid files etc. This is usually brought about by the software you used to extract from the ISO like WinRAR, WinZip, MagicISO and others.

Q. What is the correct software to use to extract this media?
Ans. My favourite answer – “It depends”. Smile

Let me explain. I am not going to suggest one piece of software over another. Instead, I’ll take a different approach to this issue.

1. Extract the verified ISO package using any of the above mentioned software. Lets assume that the folder which contains the extracted media is called “C:\SQL\SQL2008R2Media”.

2. Use FCIV.exe to generate a hash database for this extracted folder, like show below,

fciv.exe -add "C:\SQL\SQL2008R2Media" -r -XML C:\SQL\db.xml

image

What this does is computes a SHA1 hash for each file under this directory and stores it in the db.xml file. This is the way to validate an entire directory and its contents.

3. Install the SQL Server using this media. It is a successful installation and things go fine, then you know 100% that this media copy/folder/package is a good one to use for further installations.

This also proves that the extraction software used in step #1 above can be used again (same version of the s/w to be specific).
In future if you are copying this media for installation to any other machine or USB drive or on DVD etc etc, you have to validate the new destination by comparing it with the db.xml that we created above.

Here is how you do that,

Location of SQL Server directory: C:\Program Files\Microsoft SQL Server\
Location of my db.xml file: C:\SQL

Navigate to the SQL Server folder in the command prompt
1) cd C:\Program Files\Microsoft SQL Server\
2) fciv -v -xml C:\test\db.xml

where –v is to verify the hashes and –xml is to use the db.xml which we know is a good source and compare that for the files in the SQL Server folder.

That’s all there is to it. Your SQL Server media is verified clean and green. Don’t forget to put a ISO stamp on it Winking smile. Stay tuned for more SQL’lly stuff.

Posted in Setup | Tagged: , , , , , , , , , | 8 Comments »

Demystifying SQL Server Servicing (Patching, Security Updates and Service Packs)

Posted by Sudarshan Narasimhan on August 11, 2011


Today I am going to talk about the different patching options that are available with SQL Server and how they work. Often people get confused and assume that there is only one single sequence of updates for SQL Server, starting from a lower build number to higher build. This is not true, because there are different branches for patching and different types of patches released. Let me try and clarify a few of those common assumptions/misconceptions today.

SQL Server is a large product (well, it’s a Database server after all) and so requires a different patching mechanism to other products like Windows.

Types of SQL Server Patches (Product Updates)

1. Service Packs (SP):

Consider this as a consolidated list of different updates bundled into one large package. Typically, there are 2-3 Service Packs for the lifetime of a product. A typical product is in mainstream support phase for roughly about 5-6 years after which it goes into extended support phase. So if a service pack is released once every 2 years, that makes it 2-3 SP’s for the lifetime of a product.

There are exceptions to this. E.g. SQL Server 2005. This has 4 Service Packs released and probably because it was so popular that people were comfortable using it even in extended support phase instead of upgrading to the next version. Hence, this required SP4 to be released to cater to the community base that was still using SQL 2005. Makes business sense, doesn’t it? 🙂

I don’t want to get into the details of the different support phases. But you read the simplified explanation of this in Bob Ward’s blog post here.

2. Cumulative Updates (CU):

To put it simply, Cumulative updates are released once every 2 months for each branch of the product that is in mainstream support [I’ll explain about “branches” shortly]. The SQL Server release team shifted to this “Incremental Servicing Model (ISM)” some years back to make it easier for administrators to predict and plan for updates. This fits in nicely with change control and downtime planning, to apply these cumulative updates.

Branching

This is where most people get confused. I said earlier that CU’s are released, but I wasn’t totally honest. Cumulative Update packages are release for a Service Pack. Still don’t see the big picture? Let me explain. After each Service Pack, a CU is released every 2 months and this CU is only applicable for that particular service pack branch. E.g., CU2 for SP1 != CU2 for SP2. You may ask, why is this stuff so complicated?

My answer: Think Linear, cause Time flows in only 1 direction. Smile

Let me explain this with a practical example. After Service pack 1 is released, lets says 10 cumulative updates were released which makes it roughly 2×10=20 months. Now, its time for a new Service Pack. Once Service pack 2 comes out, we have SQL Servers that are in RTM build, SP1 build and others somewhere between SP1 CU1 to SP1 CU10. Can all of these be upgraded to SP2 immediately? Obviously not! People do take time to test in QA/Dev/Test etc.. before a service pack is applied (all of us go through this as part of our jobs, don’t we). Now as time goes on and 2 more months are past, the next CU for SP2 comes out. And this is exactly what Branching is all about. We now have SQL Servers that are on the base RTM, some on SP1 branch and some on SP2 branch.

You might have noticed that once a service pack is released, cumulative updates continue to be released for older service packs. Why? Think back to the mainstream vs. extended support phase. Till a service pack goes into extended support phase, Microsoft has a legal obligation to continue to service that branch of the product. This tends to be the confusion for most people, but if you understood Branching, then you will understand why CU’s are released even when SP’s are also being released.

Want me to complicate things further? The strong minded may read on …

Quirky Scenario:

You have a SQL Server instance on SP1 CU5 build. You hit a known issue (Bug) which you find out is fixed in CU6. Great! You test CU6 on your test system and the fix does solve your issue, so you implement CU6 on your production server. Life is good!

A month later, a new service pack (SP2) is released. Your boss wants you to immediately apply this SP2 on Production (due to company policy, compliance blah blah blah…). The dutiful employee that you are on, you successfully apply SP2 on Production and find out that the old issue is back again. WTF!

Think about this carefully and you will see the answer.

Service Pack 2 released just a month after CU6 was released. I mentioned that a CU is released every 2 months, which means your CU6 must have been developed/tested 2 months + 30 days = 3 months back by Microsoft. A Service Pack is considered a major upgrade, so obviously the development/testing time would be more for a SP vs. a CU. Let me point you to a piece of documentation most of you would have missed.

image(Sample taken from KB 2285068)

So, the fix for your issue which was part of SP1 CU6 is not part of SP2 because of the testing time required for a service pack. What happens in such a scenario is, the next cumulative update for SP2 (which is CU1) will contain the fixes there were present in CU6. This is called a post service pack roll-up fix. As a DBA, you need to keep in mind that if you are following the regular CU schedule of patching your SQL Servers every 2 months and then applying a Service Pack, you must immediately apply CU1 for the newly released Service Pack. CU1 for Service Packs are released pretty soon after the SP is released (usually in a week’s time). Even this minute detail is documented.

image(Sample taken from KB 2289254)

 

3. General Distribution Release (GDR):

These are special patches that are released mainly for security issues or issues having a broad customer impact. I simply like to call them “security fixes”. These are not a scheduled fix and are released by Microsoft as appropriate. The important thing about these updates are that it impact every SQL Server and is applicable for most SQL Server versions. That is why, whenever a GDR is released it is available for public download in download centre, available through Windows Updates as a High/Critical fix and in all likeliness you IT team will push them to your machines for automatic update Smile.

The other thing to keep in mind with GDR releases it that even if your product is in extended support phase, you will still get these critical security fixes. For each GDR update, a security bulletin is posted which lists the severity rating, the affected software as well as the affected versions of the software.

If you have ever read the KB article for any SQL Server security bulletin, you may have noticed two different download options.

a. GDR Software Updates
b. QFE Software Updates

You need to choose the correct version based on the build number of your SQL Server instance. GDR updates are typically if you are on the base service pack build or earlier CU’s, whereas QFE updates are for those who have service pack + some cumulative update applied. In the latter, case the same GDR security fix is available for you with the existing CU fixes. In the former, you have the service pack + only the security fix applied.

True or False?

1. You need to apply each CU in sequence to be able to apply the latest CU: False

As the name says these are cumulative fixes, meaning the fixes from the previous CU’s are carried over to the next CU. You only need to apply the latest CU to have the fixes from the previous updates.


2. I have to apply each Service Pack to be able to apply the latest Service Pack: False

Same logic as CU. Each Service Pack is also cumulative, so only need the latest Service Pack to have all the fixes (The only exception is the Quirky Situation mentioned above).

3. I need a downtime to apply a CU or Service Pack: True

As the setup process goes on, it will automatically stop your SQL Server instance to replace the binaries (exe/DLL/other files), to apply upgrade scripts and start it back again. So you certainly need a business downtime pre-planned before running a SQL Server update. (This does not apply if you only patching the Tools or Setup Support Files).

4. I heard that SQL Express Edition comes with the Service Pack bundled?: True

SQL Express is unique in a way that for each Service Pack release, the SQL Express media itself can be used for a new install. This is called “Slipstreaming”. Slipstreaming is integrating a SQL Server update and the original installation media so that the original media and the update are installed at the same time. Starting with SQL Server 2008 this can be done manually even for other editions, but SQL Express has been this way for a long time and MS does this for you and puts the slipstreamed media in the download site. So if you install SQL Express SP4, you get a SQL Server instance that is at SP4 build right from the word Go.

5. It is not possible to uninstall a Service Pack!: False

Starting with SQL Server 2008, it is possible to uninstall Service Packs and cumulative updates. You can see each SQL update present in Add/Remove programs (or “View Installed Updates” in Vista+) and these can be uninstalled. Refer screenshot below.

image

For more information on this topic, please read the following MSDN article.

Overview of SQL Server Servicing Installation
http://msdn.microsoft.com/en-us/library/dd638062(v=sql.100).aspx


Folks, I know this was a long post, but this was something that I wanted to talk about for a long time. If you have any questions leave a comment behind or drop me an email and I will definitely try and get back to you. Cheers!

.

Posted in Setup | Tagged: , , , , , , , , , , | 2 Comments »

How to find the Installation Date for an Evaluation Edition of SQL Server

Posted by Sudarshan Narasimhan on August 9, 2011


Well, this topic seems pretty simple, but many people forget that we can have the SQL Engine, Tools and other components like Reporting Services, Analysis Services in different versions or installed on different dates, so the expiration date may change accordingly. For e.g., you may have installed the Engine first and then decided to install Management Tools later on.

If you are using an Evaluation edition of SQL Server 2005/2008 then you must already know that Evaluation Edition works for 180 days from the date of installation. So, it’s very important for you to know:-

1. When was your SQL Server Instance Installed
2. When it is going to stop working. Once the 180 days are up, the service will not start.

Read on if you don’t want to see this message once the trial period is up Smile.

Expired

So I thought I’d post the various different way a DBA can go about determining the install date for SQL Server Evaluation Edition, thus calculating when the 180 day trial period expires. Here are some ways to go about finding this information:-

1. Run the following query on the evaluation instance (assuming the 180 days are still NOT over and SQL service is still running),

SELECT create_date as ‘SQL Server Install Date’,

DATEADD(dd,180,create_date) as ‘Instance will Stop Working ON’

FROM sys.server_principals WHERE name=‘NT AUTHORITY\SYSTEM’

 

-> ‘NT AUTHORITY\SYSTEM’ is SQL Server principal which gets created during the installation. So we are just checking the creation date of that security principal.

 

2. Look at the setup logs located in "%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\". Sort by Date Modified ascending and you should get the install date. This method doesn’t work if the log directory was cleaned up or moved or deleted.

 

3. Open the Management Studio on the SQL Server, click on the “Help” of Menu Bar and then on “About”. In the new window you will see some thing like this:
Microsoft SQL Server Management Studio (expires in 104 days)
Note: This is assuming that you installed SSMS along with Engine as part of the same original install at the same time.

image

4. You can check the windows installer for the SQL Product code to pull out the install date. In registry look under,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\<GUID>\InstallProperties

The "InstallDate" key will have the date stored in YYYYMMDD format.

For SQL 2008 you can search the registry for "Microsoft SQL Server 2008 Database Engine Services" to get the correct GUID.
For SQL 2005 you can search the registry for "Microsoft SQL Server 2005" only to get the correct GUID.

Note: Keep in mind the instance name if you have more than 1 instance installed on the same machine

 

While on this topic, keep in mind there is an issue when you perform an in-place edition upgrade of an evaluation edition to a licensed edition. You will still get the error for SSMS or any tools. This is a known issue. Please follow the following KB to fix this issue.

‘Microsoft SQL Server management studio’ and ‘SQL Server Profiler’ report ‘Evaluation period has expired’ message even after upgrading to a licensed edition

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

Posted in Setup | Tagged: , , , , , | 2 Comments »