How to deploy SQL Server 2008 Express as a prerequisite with ClickOnce

  • Article

People often ask me how to deploy SQL Server 2008 Express with applications as a prerequisite with Visual Studio 2008. Most often this situation is encountered when people want to deploy applications using the ClickOnce or they want to build a MSI package with SQL Server 2008 using Visual Studio 2008 and include the SQL Server packages with the setup.

Now I am providing you with a set of steps to demonstrate deployment of SQL Server 2008 with your application as a prerequisite. You can try these steps on your test environment first. SQL Server 2008 Express needs Windows Installer 4.5 and .Net 3.5 SP1 Framework.

In case if you are not on the MSDN blogs then I would request you to please visit my blog at https://blogs.msdn.com/manisblog because at times I improve the existing articles after reading emails from people who enthusiastically provide their feedback. These improvements might not be reflected on the other blog sites who have indexed this article.

  1. Download and install Visual Studio 2008 Express edition because the SQL Server 2008 bootstrapper will be found with the VS2008 Express. Now the steps that I am suggesting would be done using your usual VS 2008 SP 1 development environment like VS 2008 Professional, Team Suite etc.

    NOTE: If you are using any other language version other than English like German etc. then please download the appropriate language version of Visual Studio 2008 Express and SQL Server 2008 express.

    Visual Studio 2008 Express and SQL Server 2008 Express could be downloaded from : https://www.microsoft.com/express/download/ 

  2. Using VS 2008 Development Environment, create a small .Net 3.5 Windows application that connects to SQL Server 2008 Express Edition.

  3. Add a Setup Project to this Visual Studio 2008 Solution.

  4. Add the project output of the Windows Application to the setup project.

  5. In the setup project you would not be able to locate Windows Installer 4.5, .Net 3.5 SP1 and SQL Server 2008.

  6. Now download all the files from https://www.microsoft.com/downloads/details.aspx?FamilyId=5A58B56F-60B6-4412-95B9-54D056D6F9F4&displaylang=en  to the Bootstrapper/packages folder which looks like C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages. This will provide you with the Windows Installer 4.5 Bootstrapper.

  7. Download SQLEXPR_x64_ENU.exe , SQLEXPR_x86_ENU.exe, SQLEXPR32_x86_ENU.exe from https://www.microsoft.com/downloads/details.aspx?FamilyID=58ce885d-508b-45c8-9fd3-118edd8e6fff&DisplayLang=en and place them in the SQLExpress2008/en folder within the Bootstrapper/packages folder. This makes sure that the SQL Server 2008 Express is present.

  8. Now to download the .Net 3.5 SP1 framework please navigate to https://download.microsoft.com/download/2/0/e/20e90413-712f-438c-988e-fdaa79a8ac3d/dotnetfx35.exe and download the 231 MB file.

  9. At the command prompt navigate to the folder that contains the dotnetfx35.exe file and type : dotNetFx35.exe /x:. This will extract the Framework files to a folder named "WCU" in the current directory.

  10. Copy the contents of the WCU\dotNetFramework folder and paste them in the %Program Files%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 folder (%ProgramFiles(x86)%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 on x64 operating systems). Note: Do not copy the WCU\dotNetFramework folder itself. There should be 5 folders under the WCU folder, and each of these should now appear in the DotNetFx35SP1 folder. The folder structure should resemble the following:

    1. DotNetFx35SP1 (folder)
      dotNetFX20 (folder
      dotNetFX30 (folder)
      dotNetFX35 (folder)
      dotNetMSP (folder)
      TOOLS folder)
      en (or some other localized folder)
      dotNetFx35setup.exe (file)

  11. Now for the successful installation of the .Net 3.5 SP1 Framework on the client computer you need to take further following actions so that the framework installs successfully. After these steps you would be able to add .Net 3.5 SP1 Framework, Windows Installer 4.5 and SQL Server 2008 Express with your application setup.

  1. Open the [Program Files]\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 folder or %ProgramFiles(x86)%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 on x64 operating systems
  2. Edit the Product.xml file in Notepad.
  3. Paste the following into the <PackageFiles> element: <PackageFile Name="TOOLS\clwireg.exe" /> <PackageFile Name="TOOLS\clwireg_x64.exe" /> <PackageFile Name="TOOLS\clwireg_ia64.exe" />
  4. Find the element for <PackageFile Name="dotNetFX30\XPSEPSC-x86-en-US.exe" and change the PublicKey value to: 3082010A0282010100A2DB0A8DCFC2C1499BCDAA3A34AD23596BDB6 CBE2122B794C8EAAEBFC6D526C232118BBCDA5D2CFB36561E152BAE 8F0DDD14A36E284C7F163F41AC8D40B146880DD98194AD9706D057447 65CEAF1FC0EE27F74A333CB74E5EFE361A17E03B745FFD53E12D5B0CA5 E0DD07BF2B7130DFC606A2885758CB7ADBC85E817B490BEF516B6625D ED11DF3AEE215B8BAF8073C345E3958977609BE7AD77C1378D33142F13 DB62C9AE1AA94F9867ADD420393071E08D6746E2C61CF40D5074412FE 805246A216B49B092C4B239C742A56D5C184AAB8FD78E833E780A47D8 A4B28423C3E2F27B66B14A74BD26414B9C6114604E30C882F3D00B707 CEE554D77D2085576810203010001
  5. Find the element for <PackageFile Name="dotNetFX30\XPSEPSC-amd64-en-US.exe" and change the PublicKey value to the same as in step 4 above
  6. Save the product.xml file
NOTE : If the table above is not properly displayed then you can refer Section 2.3.1.1 in readme file. In the section 2.3.1.1 you would get the options to install additional language packs as well. You should copy the PUBLICKEY from the URL below. Please navigate to the following URL:
https://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm.

I referred the following documents / blogs to put these steps together :

  1. https://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm#Product%20Issues
  2. https://blogs.msdn.com/sqlexpress/archive/2008/09/12/faq-installing-the-sql-server-2008-express-clickonce-bootstrapper-for-visual-studio-2008-sp1.aspx 

Thank you very much for visiting my blog and this article. Please keep a watch on this blog for new posts.