The SQL Dude!

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

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.

Advertisements

8 Responses to “SQL Server Setup: How to validate downloaded image/media before installation”

  1. Malika said

    wow.. sqldude sharing tips with us even before the KBs are updated.
    I think I am having a crsuh on this Dude(sql) 🙂

  2. una said

    Hello, Great post! Just a quick question is there a way that FCIV can validate a drive and get a combined md5 or sha1 value for all the files. example getting a md5 value for a whole usb? I can get it to validate the files individually but not a overall value. Is this possible using FCIV?

    • Sudarshan Narasimhan said

      Thanks Una. Good question, I assume you want to place the SQL media on a USB Drive and use that for installations, which makes sense once you can validate the USB as a valid media.
      Few things, make sure the USB is formatted as NFTS and not FAT32. Secondly, FCIV.exe doesnt have an option to get a single SHA1/MD5 hash value for an entire directory or drive. Since the -r option recursively goes through each file in each sub-directory but the hashes are listed individually.

      Here is a creative solution that will accomplish what you are looking for,
      1. Use the regular method of recursively scanning through each file once you have mounted the ISO as a USB/CD/DVD drive. Store the hashes in a file called DB.XML
      fciv -add G:\ -r -sha1 -xml db.xml
      Processed 13 directories
      Processed 88 files

      2. Now run the same FCIV hash comptutation against this DB.XML
      fciv -add db.xml -sha1

      //
      // File Checksum Integrity Verifier version 2.05.
      //
      84c80198fbc48f6066aa66075d13dde4667c3544 db.xml

      3. Assumming if some files got corrupted or modified or changed in any way, then the new DB.xml file will be different compared to the previous one. To test this, I modified 1 file in the original G:\ drive, created a new ISO and calculated a new DB2.xml for this.
      fciv -add G:\ -r -sha1 -xml db2.xml
      Processed 13 directories
      Processed 88 files

      fciv -add db2.xml -sha1

      //
      // File Checksum Integrity Verifier version 2.05.
      //
      5821d6911c2101339c0cb6211fb8c72783a8841b db2.xml

      As you can see the SHA1 hash value for DB.XML is not equal to DB2.XML and that proves that the ISO image or CD drive data has changed. You can do the same with the USB drive by providing the volume letter of the drive to calculate the hash value.
      Hope this helps.

  3. una said

    This is a great help thank you. It makes sense to store the hashes in a file called db.xml and do a FCIV against the db.xml.

    I have a small query and im sure this could have a simple answer. I can run this validation process on a labtop but when validating on a desk top I recieve an error that it cannot creat the XML access denied.

    I have the same administration rights for both computers but the desk top is causing a problem…strange?

    Any ideas?

    Many thanks again for the feedback.

    • Sudarshan Narasimhan said

      Try running the command prompt as Administrator using the “Run as Admin” option to launch it in elevated mode and then run fciv from this prompt. If you run it normally with UAC on, you don’t get admin privileges. HTH.

  4. A blog on very important topic. This should be sort of a checklist for SQL installation given that there are so may weird errors/cases due to corrupt media. Great stuff, Sudarshan!

    • Sudarshan Narasimhan said

      Thanks Prashant! It took lot of effort to get MSDN to publish SHA1 hash for SQL media ISO files. Hopefully this prevents setup failures.

  5. […] https://thesqldude.com/2012/01/27/sql-server-setup-how-to-validate-downloaded-imagemedia-before-insta… […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: