Setting up SQL Server 2008 Express with Profiler

When I teach my DevelopMentor course on Entity Framework 4.0 and WCF Data Services, I use the Express Edition of SQL Server 2008 R2, but I have need for the SQL Profiler tool, which comes only with the full version and is needed to inspect what SQL is sent to the database. In addition, the setup folks often have a hard time getting the permissions right.  So I wrote a script that first installs just the tools from a trial version of the Developer Edition, which include both SQL Management Studio and SQL Profiler.  Then I wrote another script that installs just the database engine of SQL Express, adding the BUILTIN\Users account to the sysadmin role, which would enable users to to do various admin tasks such as attaching a database.

Here are the steps for these installations:

Developer or Standard Edition of SQL Server 2008 R2:

Trial Edition: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=de21dffd-4c6c-4963-b00c-5153cf7e41dd

– Click on the Eval X86 Executable link to download SQLFULL_x86_ENU.exe.

– Execute the file to extract contents to an installation directory, then run the following from an admin command prompt:

setup.exe /FEATURES=Tools /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=MSSQLSERVER /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\NETWORK SERVICE” /IACCEPTSQLSERVERLICENSETERMS

– This will install only the tools for SQL Server, including SQL Profiler.

SQL Server 2008 R2 Express Edition (database engine only):

http://www.microsoft.com/downloads/details.aspx?FamilyID=8b3695d9-415e-41f0-a079-25ab0412424b&displaylang=en

– Execute SQLEXPR32_x86_ENU.exe, then after the main screen is shown, copy the contents of the temporary directory to an installation directory. Run the following from an admin command prompt:

setup.exe /FEATURES=SQLEngine /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=SQLEXPRESS /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\NETWORK SERVICE” /SQLSYSADMINACCOUNTS=”BUILTIN\Users” /IACCEPTSQLSERVERLICENSETERMS

– This will install the database engine and add BUILTIN\Users to the sysadmin role

The complete list of SQL Server installation commands is available here.

Technorati Tags:

About Tony Sneed

Married with three children.
This entry was posted in Technical. Bookmark the permalink.

61 Responses to Setting up SQL Server 2008 Express with Profiler

  1. Matthijs ter Woord says:

    Hi,

    What license is the tools of the evaluation stuff? evaluation only? or is it allowed to use in production as well?

    Regards,
    Matthijs ter Woord

  2. Justin Toth says:

    Had the same situation where I was using SQL Express and needed to use Profiler, thanks for the tip!

  3. Tony Sneed says:

    @Matthijs: I use the trial edition just for the tools, which would not normally be deployed to a production machine anyway. However, the command line installation can also be used with licensed copies too. 🙂

  4. Grigory Tamarkin says:

    Tony, I’m going to use profiler with standard licensed edition – will profiler expire or not?

  5. Ole Myrbakken says:

    Awesome tutorial! You saved me from uninstalling all sql versions yesterday. I did a full install of SQL Server 2008, but didn’t get the sql profiler cause I had an installation of Express already. (It wouldn’t upgrade the basic management that was installed with express)

    This tutorial made me able to force the installation of the tools without having to run a clean install without express in place.

    Using the tutorial again today for another computer!

    Thanks =)

    • ceci says:

      Im in the same boat, all installed but missing profiler. what command line did u use?

      • Tony Sneed says:

        SQL Profiler is installed with the tools full version (Developer, Standard, etc) of SQL Server 2008 R2. It is not installed with SQL Express. If you already have SQL Express installed and the installation included the tools, you need to uninstall SQL Express then install the tools only of the full version of SQL Server using the first command line text. Once that is installed, you should see SQL Profiler under the “Performance” program group for SQL Server 2008 R2.

  6. Chen Noam says:

    Great!, it works.

    I tried many things, that didn’t help me.

    I was skeptical, but I needed the profiler, so I’ve tried again.
    I had the express installed and visual studio and many other development tools.
    I’ve run only the setup line to install the profiler, and it worked perfectly.

    Thanks a lot,
    Chen

  7. Anne says:

    Could you help me to figure out why SQL Server 2008 Express downloaded to my desktop but could not complete the setup? The folks at Microsoft have not been helpful. Thank you.

  8. William Ramirez says:

    Thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you thank you!

  9. Sergio A says:

    Thank you!! I had MS SQL Express R2 x64 installed on my machine, but followed the same instructions here just using the SQL 2008 R2 Evaluation x64 version and worked fine! Link here: http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=de21dffd-4c6c-4963-b00c-5153cf7e41dd

  10. Christian says:

    Thanks alot, this was very helpful.

    – Christian

  11. Antonio says:

    Profiler installed and working like a charm… thanks a million.

  12. Sonja says:

    Thank you!
    Works on x64 also. I followed the same steps, just used x64 eval link.

  13. Li says:

    Hi,
    I am following the instruction and reinstalled SQL Server 2008 Express again. However the profiler does not appear on the SQL Server Management. Have I missed anything here? I have tried to run Data Profiler Viewer from the program files and got an error saying: Error: The Data Profile Viewer requires Integration Services to be installed by one of these editions of SQL Server 2008 R2: Standard, Enterprise, Developer, or Evaluation. To install.

    Li

    • Tony Sneed says:

      Yeah, for integration services you’ll need to install full-blown SQL Server, probably Developer Edition, which you would need to purchase or get from an MSDN subscription. SQL Express doesn’t have that feature. However, if all you need is SQL Profiler, and not Data Profile Viewer, then installing SQL Express will be fine.

  14. Noah says:

    Worked on x86 Developer Edition as well – thanks!

  15. Pingback: The Blomsma Code - Logging and tracing with Entity Framework

  16. COLOTTI laurent (France) says:

    Thank you.
    Profiler installed

  17. Michael Day says:

    Thank you, Tony. I wish I’d have found this post earlier today – it would have saved me a few hours of headaches.

  18. Thank you, Tony. I’ve used your solution about a half year, but now I’m getting the following message: ‘evaluation period expired…’ Do you have any solution?

    • Tony Sneed says:

      I have started to get the very same message. 🙂 What I would suggest is to uninstall SQL Server 2008 R2 using Control Panel, then reinstall another eval copy. (I haven’t tried this because I have access to a licensed version of SQL Server Dev Edition.) This would only be necessary if you do not have access to a licensed copy of SQL Server 2008 R2 Developer Edition (or higher). In that case, you would simply install the licensed version using the command line text in my blog post.

      If you are able to re-install and evaluation version of the Developer Edition of SQL Server, please let me know. 😉

  19. Lee says:

    I could marry this solution it’s that good! Genius. Saves me a whole load of work trying get around previous work colleagues shoddy work.

  20. elavarasan says:

    how to send mail through sql express 2008 only

  21. ddolcos says:

    Great!, it works.

  22. Nil Nol says:

    I try to follow your instructions. I get a simple SQL 2008 r2 setup. I seem it doesn’t work fro sql 2006 r2

  23. Manoj says:

    Worked like a treat, Thanks

  24. Ameet Parse says:

    This worked like a Charm no re-install, no database mess,
    Sincere thanks for Helpful Post
    Have a Good Day!

    Ameet Parse

  25. Krishna says:

    Hi, I am getting the following exception while installing “SQL Server 2008 R2 Express Edition (database engine only)” . Please help me to overcome the problem as I need the SQL profiler with express edition.. Thank you so much.

    —————————————————————————————————————-

    –InputValidator: Checking for existing tempdb file.
    –InputValidator: Checking AddCurrentUserAsSQLAdmin setting.
    Hosting object: Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPublic
    failed validation
    Validation for setting ‘SQLSVCACCOUNT’ failed. Error message: The specified cred
    entials that were provided for the SQL Server service are not valid. To continue
    , provide a valid account and password for the SQL Server service.
    Validation for setting ‘SqlSysadminAccounts’ failed. Error message: The Windows
    account “BUILTIN\Users” does not exist and cannot be provisioned as a SQL Server
    system administrator.

    Error result: -2068578304
    Result facility code: 1204
    Result error code: 0

    Please review the summary.txt log for further details

    ———————————————————————-

    Error result: -2068578304
    Result facility code: 1204
    Result error code: 0

    E:\mseval\engine>setup.exe /FEATURES=SQLEngine /Q /INDICATEPROGRESS /ACT
    ION=Install /INSTANCENAME=SQLEXPRESS /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACC
    OUNT=”NT AUTHORITY\NETWORK SERVICE” /SQLSYSADMINACCOUNTS=”BUILTIN\Users” /IACCEP
    TSQLSERVERLICENSETERMS
    —————————————————————————————————————-

    • Tony Sneed says:

      The exception states: The Windows account “BUILTIN\Users” does not exist. It looks like that is your problem. You can remove this part from the command line: /SQLSYSADMINACCOUNTS=”BUILTIN\Users”.

      Cheers,
      Tony

  26. Ken says:

    Tony:
    This is outstanding post!
    Thanks for you help!

  27. I was unable to make this work with 2008 R2 SP1 but once I installed 2008 R2, and after installing Profiler updated it with SP1 I got what I wanted.

  28. AK says:

    You can also use the free, open source SQLExpressProfiler from AnjLab (http://code.google.com/p/sqlexpressprofiler/). Tested with 2008R2 Express SP1.

  29. Delta says:

    Great thanks so much it worked

  30. Meln says:

    Thanks! It worked great 🙂

  31. james says:

    Hi,

    If I have already have R2 installed on my machine, will this still work?

  32. bbakkebo says:

    Thanks this worked like a charm for me. 🙂

  33. Marius Els says:

    this is awesome tip, thanks

  34. Pingback: Microsoft SQL 2008 profiler | Venturini Photo & IT's

  35. Keith Stott says:

    Excellent! Didn’t have to re-install anything – just ran the setup features command.

  36. engas programmer says:

    f you allready have a setup of MS SQL Server 2008 R2, try to uninstall the tools only:

    setup.exe /ACTION=UNINSTALL /FEATURES=TOOLS /QUIETSIMPLE

    And the install it again:

    setup.exe /ACTION=INSTALL /FEATURES=TOOLS /QUIETSIMPLE /IAcceptSQLServerLicenseTerms

  37. Michael Lappin says:

    Tony,

    I went through the exercise. Was able to add various features and toolsets. But when I look for Profiler and its still not there. Not sure why. Why would that tool be left out? We checked every box under the add features section you describe above.

    Any ideas? TIA

  38. Michael Lappin says:

    Tony,

    I think I figured it out…you have to REMOVE the BASIC toolset, then reinstall the complete toolset.

    Sound correct?

    • engas programmer says:

      Try my instruction above.

      and yes, you have to remove any previous installation of the BASIC TOOLS, then reinstall everything….

  39. Sen says:

    Great Tip! Worked like a charm for me!

  40. mazfar says:

    Hi , its working for me, thanks and hats off.
    –mohammed azfar

  41. Newton says:

    We have gone ahead and included a hyperlink back to your web page from one of my clientele requesting it.
    I have used your web page URL: http://blog.tonysneed.
    com/2010/08/05/setting-up-sql-server-2008-express-with-profiler/ and blog title:
    Setting up SQL Server 2008 Express with Profiler | Tony Sneed’s Blog to ensure you get the correct anchor text. If you woud like to check out where your hyperlink has been placed, please email me at: newtoncortes@web.de. Cheers

  42. Leonardo Pech says:

    How about to do the same thing over SQL Server 2012 Express??

  43. JohnnyHK says:

    Thank you so much, Tony. It worked great. My experience in case it helps somebody: I started with a full installation of SQL Server 2008 R2 Express, uninstalled the “Basic Tools” components, downloaded SQL Server 2008 R2 Developer from MSDN, mounted the ISO and then ran the setup.exe command Tony listed to install the full set of tools from it.

  44. guest says:

    You can use free ExpressProfiler from http://expressprofiler.codeplex.com/
    As stated at the site: “ExpressProfiler (aka SqlExpress Profiler) is a simple but good enough replacement for SQL Server Profiler with basic GUI. No requirements, no installation. Can be used with both Express and non-Express editions of SQL Server 2005/2008/2008r2/2012 (including LocalDB)”

  45. Our xSQL Profiler http://www.xsql.com/products/sql_server_profiler/ is free for one SQL Server instance (meaning you can trace one sql instance at a time).

  46. sqlserver2012 says:

    I have tried the same trick with SQL Server 2012 but it didn’t work, the installation just hang up at some point.

    • Tony Sneed says:

      The general idea is still valid, that is, to install full SQL tools only, then install SQL Express, engine only. Command parameters for the install may have changed though.

  47. jhoshibabu says:

    Thanks Tony .. it works..

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