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):
– 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.
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
Had the same situation where I was using SQL Express and needed to use Profiler, thanks for the tip!
@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. 🙂
Tony, I’m going to use profiler with standard licensed edition – will profiler expire or not?
I don’t think there are licensing issues with Profiler, since it’s in the Tools. You should be fine.
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 =)
Im in the same boat, all installed but missing profiler. what command line did u use?
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.
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
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.
Do you have Visual Studio 2008 installed? That could be the culprit: http://it.toolbox.com/blogs/programming-life/fixing-sql-server-2008-rtm-installation-issues-due-to-visual-studio-2008-dependencies-26646. I suggest you uninstall VS 2008, including SQL Express. With VS 2010 you shouldn’t need VS 2008 on the machine. If you would still like to have it there, be sure to install it without the SQL Express option. Good luck!
Thank you very much! I will definitely try this. ~Anne
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!
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
Thanks alot, this was very helpful.
– Christian
Profiler installed and working like a charm… thanks a million.
Thank you!
Works on x64 also. I followed the same steps, just used x64 eval link.
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
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.
Worked on x86 Developer Edition as well – thanks!
Pingback: The Blomsma Code - Logging and tracing with Entity Framework
Thank you.
Profiler installed
Thank you, Tony. I wish I’d have found this post earlier today – it would have saved me a few hours of headaches.
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?
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. 😉
I found an article. There is a Developer Edition serial number…
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.
how to send mail through sql express 2008 only
Excellent work.
Great!, it works.
I try to follow your instructions. I get a simple SQL 2008 r2 setup. I seem it doesn’t work fro sql 2006 r2
Worked like a treat, Thanks
This worked like a Charm no re-install, no database mess,
Sincere thanks for Helpful Post
Have a Good Day!
Ameet Parse
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
—————————————————————————————————————-
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
Tony:
This is outstanding post!
Thanks for you help!
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.
You can also use the free, open source SQLExpressProfiler from AnjLab (http://code.google.com/p/sqlexpressprofiler/). Tested with 2008R2 Express SP1.
No, sadly I don’t think that’s available for free any more. The link you posted gives a 404, but this page https://sites.google.com/site/sqlprofiler/ does exist and leads you to http://www.datawizard.com/ which offers the utility at various license levels (cheapest $5/year)
Great thanks so much it worked
Thanks! It worked great 🙂
Hi,
If I have already have R2 installed on my machine, will this still work?
Thanks this worked like a charm for me. 🙂
this is awesome tip, thanks
Pingback: Microsoft SQL 2008 profiler | Venturini Photo & IT's
Excellent! Didn’t have to re-install anything – just ran the setup features command.
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
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
Tony,
I think I figured it out…you have to REMOVE the BASIC toolset, then reinstall the complete toolset.
Sound correct?
Try my instruction above.
and yes, you have to remove any previous installation of the BASIC TOOLS, then reinstall everything….
Great Tip! Worked like a charm for me!
Hi , its working for me, thanks and hats off.
–mohammed azfar
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
How about to do the same thing over SQL Server 2012 Express??
Should work pretty much the same way, although I haven’t tried it.
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.
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)”
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).
I have tried the same trick with SQL Server 2012 but it didn’t work, the installation just hang up at some point.
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.
Thanks Tony .. it works..
Hi, I was using SQL Express and needed to use Profiler, kindly help
Just install SQL Management Studio, which now includes the Profiler.
It’s been a long time and i’m not sure you will reply. hi anyway.
I needed sql profiller. but i have a active database, it is not possible for me to uninstall sql server. do you know a 3rd software for track queries?