Help installing SQL Server 2005 or 2008

I’ve been having a nightmare getting SQL Server Express installed on my machine. I’ve had it running on this same machine before, but installing it again is driving me crazy. I think I had version 2008 before but I heard that version is still in Beta. What version do you recommend? I tried 2008 first unsuccessfully. Then I tried 2005 unsuccessfully. Don’t have any errors to show you but it would be installing for a couple hours, then give an error msg. Then I tried 2008 again, and this time it seems to have worked; at least it indicated such.

How do you actually run it…to create or view a database? When I first had SQL Server on my machine there was an icon on my taskbar showing that it was running. I could also stop it from that icon. I ended up with SQL Server 2008 R2 and SQL Server 2008. The latter only has configuration Tools (installation center). SQL Server 2008 has the same thing plus configuration manager and error and usage reporting. When I go into SQL Server 2008 configuration manager, the name, state, and startmode is like this:

SQL Server (SQLEXPRESS), running, automatic
SQL Server Agent (SQLEXPRESS), stopped, other (boot, system, disabled or unknown) [process id of this one only is 0]
SQL browser, running, automatic

I can’t seem to change the state of the agent.

I do have these 3 processes running: sqlbrowser (60k), sqlwriter (1,584k), and sqlservr (6,008k)
Please advise me on the best version of SQL Server to install and the best way to go about it. I currently have a slow old Dell running XP.

Many thanks!
Jay

 

Which version of SQL Server Express is best?
The answer depends on a few factors:
What features do you need? If you want the latest officially released / on the market version with the most up to date features and technology then SQL Server Express 2008 R2 is the one.

Do you already have a DASP SQL Server DB provisioned? If yes then it makes sense to choose the version of SQL Server Express that matches the version of DASP SQL Server instance your existing db is hosted on.

Are you running x86 or x64 on local?
Choose either the 32bit or 64bit version to match your OS version.

Is SQL Server Express supported on DASP?
Not directly but you can attach your SQL Server Express database file to the DASP hosted SQL Server instance using the attach tool in the control panel (https://support.discountasp.net/KB/a188/sql-2008-management-tools.aspx). SQL Server Express is fine for a local install / development purposes.

What version would I recommend?
If starting from scratch:
https://www.microsoft.com/betaexperience/pd/SQLEXPDBMT32/enus/ OR
https://www.microsoft.com/betaexperience/pd/SQLEXPDBMT64/enus/ depending on your OS version

I can't advise on the state of your current setup however it does sound as though you have an instance of SQL Server Express installed and running locally but no tools installed (e.g. SQL Server Management Studio) that would enable you to connect to the running instance to run queries / create databases, tables, sprocs etc.

The above download URLs for SQL Server 2008 R2 Express do include management tools but if all you need right now is SQL Server Management Studio for your existing installed instance then you just need to download and install this:
http://www.microsoft.com/download/en/details.aspx?id=7593

 

Joe and Tasslehoff, thanks a lot for your detailed response. I’ve been out of town, but yesterday I removed all traces of SQL Server from my machine (running x86 on local). I decided that SQL Server 2005 Express would meet my needs (hobby/learning/eventual e-commerce) and be easier to install. I installed it from here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21844

There is only one process running now (as opposed to with SQL Server 2008 before) -- sqlbrowser.exe.

SQL Server seems to install stopped by default.

Going into SQL Server Configuration Manager shows that SQL Server (SQLEXPRESS) is stopped and SQL Server Browser is running in automatic start mode.

Checking the properties of SQL Server (EXPRESS) shows that the start mode is disabled.

I tried to connect through Visual Studio but no luck.

Is that enough info for you to know what I need to do to connect to, create and edit databases?

Thanks in advance… by the way, I voted for DiscountASP.NET awhile back. I like your quick and helpful responses!


By the way, what is DASP?


Jay

 

Ok! Both processes are now running and start automatically.

But now how do i login and do something?

What does SQL Server Surface Area Configuration do?

 

You guys are the bomb! Works like a charm!

Lastly, I just tried to open a database and got an error that I have no editor installed for .mdf file types.

What's the best editor?

 

Yeah, I know...it just said I have no .mdf editor installed. I'm reading in another forum right now and think I just need to "attach" the database. How do you attach it?

Thanks for that link...I'll bookmark it.

 

Tasslehoff, with your help and rapid responses a few times yesterday I was finally able to get SQL Server 2005 Express installed and running. I created a couple of databases and now I'm adding tables and stored procedures to them. Just came back on here to say thanks.

Thanks again... DASP is the bomb!

 

I didn't get this error while working in SSMS to experiment creating and editing, but now that I'm trying to do that from inside visual studio 2005 express I keep getting this error:

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

There are many discussions on the net about what causes this and why. I decided to ask here instead of trying several different things. (there are several solutions suggested).

Jay

 

Plus the Community Samples of course: http://sqlserversamples.codeplex.com/

 

You know, even after making the change to the connection string, I still can't get SQL Server 2005 Express to connect to a database from within VS! Still the same message as stated before. Only the first time I tried it I had too many things opened... i guess that might be why it timed out...next time I tried, it was the original error again. No wonder, as someone said in a forum "No wonder Microsoft is giving it away!!"

This is my current connection string from web.config:

"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DiaryDB.mdf;Integrated Security=True;User Instance=False"

Speaking of which, where exactly is this "data directory" located?

By default, master.mdf ended up here:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
and microsoft's sample adventureworks databases ended up here:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

Don't the database(s) for a project go in the apps_code folder? Like here:

C:\Documents and Settings\Jay\My Documents\My Web Sites\OnlineDiary\App_Data

Thanks for your help and links... I added them to my resources.


Jay

 

You aren't trying to use an Express DB on your DASP server, right?
You have to use the Attach tool for that, the Express DB itself can not be used.

Your Data Directory is your ASP.NET /App_Data/ folder.

 

No, I haven't yet tried to use SQL Server on DASP. I'm a couple weeks away from that. Till then I'll be working on my local machine. I created a db in SSMS and saved it in the data directory. A weird thing I remember, after saving the database I searched the hard drive and there was no database with an updated date field.

 

You can use the Server Explorer in VS, it will work better that way and VS will make the right choices for you plus the conn string.
Server Explorer: Ctrl+Alt+S
Once that is showing click the yellow drive icon at the top to: Connect to Database
Your local SQL Server will be accessible from there including the data you created.

 

No, I tried before to use the database explorer inside VS, but the same "failed to generate..." error. I tried it again just now, and it does not work...

Have there been changes in SQL Server 2005 Express since it came out? I'm sure I used it back then and had none of these problems! And I remember having an icon on the taskbar that you can use to stop or start the server.

I'm not giving up, just frustrated...this is really holding up my project.

Jay

 

There are changes yes.
Are you still using the 2005 version?
if so you really should install the 2008 version to add the updates, changes and the FREE wizards it added that didn't exist in previous Express versions.
The 2008 version is Gooder. ;-)

*I'll help you with ever part of this, no prob bro.

 

Thanks wizemx... I appreciate it!

Ok, I will upgrade SQL Server 2005 Express to SQL Server 2008 and let you know. I know I've seen a page offering several different upgrade options. Could you tell me the best link to use for this upgrade?

 

Sure. . .

http://www.microsoft.com/sqlserver/en/us/editions/express.aspx

There's also a tool D/L on that bottom of that page for Access migration if you need it.

 

my god bro, this has got to be the most difficult software i've EVER installed and it STILL isn't working! SQL Server 2008 express installed without SSMS. it took forever to use the same link to add items (like SSMS!) to the current install. finally got there but after the extensive checking to see if things will work, it fails on 1 item, which it shows as part of SQL Server 2005!! was this not an upgrade from 2005, and now i have both installed?

i could not click "next" and i was afraid to close out the install window so i hit control panel to uninstall 2005. but i don't see it anywhere on the add/remove programs! i can SEE it in windows explorer, i can even GO INTO IT (SSMS) from start/all programs, but if i try to connect i get a message that it's the wrong version and it's only meant for SQL Server 2005 or earlier... NO KIDDING!!!

what is going on? started out with a thick head of hair. now i'm all but bald, and the only reason i'm still trying to get this working is because i'm stubborn and don't give up easy. well, actually, the main reason i'm still working on it is because you offered to help...

but i'm getting close to giving up on ANY version of SQL Server and deciding on another database.

 

most recent error log for continuing SQL install/upgrade problem

2011-09-21 13:58:22.29 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

2011-09-21 13:58:22.29 Server (c) Microsoft Corporation.
2011-09-21 13:58:22.29 Server All rights reserved.
2011-09-21 13:58:22.29 Server Server process ID is 1536.
2011-09-21 13:58:22.29 Server Authentication mode is WINDOWS-ONLY.
2011-09-21 13:58:22.31 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-09-21 13:58:22.31 Server This instance of SQL Server last reported using a process ID of 2768 at 9/21/2011 1:55:44 PM (local) 9/21/2011 8:55:44 PM (UTC). This is an informational message only; no user action is required.
2011-09-21 13:58:22.31 Server Registry startup parameters:
-d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
-e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
-l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2011-09-21 13:58:22.95 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-09-21 13:58:22.95 Server Detected 1 CPUs. This is an informational message; no user action is required.
2011-09-21 13:58:23.04 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2011-09-21 13:58:23.28 Server Node configuration: node 0: CPU mask: 0x00000001:0 Active CPU mask: 0x00000001:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-09-21 13:58:24.18 spid7s Starting up database 'master'.
2011-09-21 13:58:25.60 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2011-09-21 13:58:26.12 spid7s Recovery completed for database master (database ID 1) in 1 second(s) (analysis 156 ms, redo 78 ms, undo 46 ms.) This is an informational message only. No user action is required.
2011-09-21 13:58:28.26 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2011-09-21 13:58:29.29 spid7s SQL Trace ID 1 was started by login "sa".
2011-09-21 13:58:29.35 spid7s Starting up database 'mssqlsystemresource'.
2011-09-21 13:58:29.53 spid7s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
2011-09-21 13:58:35.17 spid10s Starting up database 'model'.
2011-09-21 13:58:35.23 spid7s Server name is 'JAY-B4BD529039F\SQLEXPRESS'. This is an informational message only. No user action is required.
2011-09-21 13:58:36.07 spid7s Informational: No full-text supported languages found.
2011-09-21 13:58:37.20 spid7s Starting up database 'msdb'.
2011-09-21 13:58:37.65 Server A self-generated certificate was successfully loaded for encryption.
2011-09-21 13:58:37.75 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2011-09-21 13:58:37.75 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2011-09-21 13:58:37.87 Server Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2011-09-21 13:58:38.18 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2011-09-21 13:58:38.21 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2011-09-21 13:58:40.82 spid10s Recovery is writing a checkpoint in database 'model' (3). This is an informational message only. No user action is required.
2011-09-21 13:58:41.60 spid10s Recovery completed for database model (database ID 3) in 1 second(s) (analysis 187 ms, redo 187 ms, undo 109 ms.) This is an informational message only. No user action is required.
2011-09-21 13:58:44.06 spid10s Clearing tempdb database.
2011-09-21 13:58:50.60 spid7s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2011-09-21 13:58:51.45 spid7s Recovery completed for database msdb (database ID 4) in 2 second(s) (analysis 250 ms, redo 203 ms, undo 1250 ms.) This is an informational message only. No user action is required.
2011-09-21 13:59:00.28 spid10s Starting up database 'tempdb'.
2011-09-21 13:59:10.10 spid13s The Service Broker protocol transport is disabled or not configured.
2011-09-21 13:59:10.21 spid13s The Database Mirroring protocol transport is disabled or not configured.
2011-09-21 13:59:13.00 spid13s Service Broker manager has started.
2011-09-21 13:59:13.35 spid7s Recovery is complete. This is an informational message only. No user action is required.

 

They do make skinny versions for people who need the engine but won't need the console.
Last I checked there were 4 types of installs for 2008 Express.

All of us who use it and previous versions run them together, on your system you can safely run 2005 express, 2008 express and the Developer version of 2008, which will give you access to the others when you run it.

Installing 2008, all flavors, is a little tougher than it was in the past because they gave us control over which service is used by our own Auth. i.e. User, Network, System.

Use Bing and search for Blogs that have detailed the steps:
http://www.bing.com/search?q=installing+SQL+Server+2008

Keep us posted.

 

I just noticed that now that I've reinstalled SQL Server 2008 express, the Active Directory Helper Service and the SQL Ser Agent are both disabled. should I change both to automatic?

The installation of SQL Server 2005 Express, is messed up. As I said, if I go into SSMS it gives a version error if I try to connect. The only other thing under programs is configuration tools->error and usage reporting, but just clicking on it makes it crash and send an error report to microsoft.

I cannot even see SQL Server 2005 Express on the control panel, so how can I uninstall it? Will I need to uninstall 2008 again and start over fresh??

Thanks guy!

 

If I need to start over and install both or just 2008, can you please provide links for installing it as well as installing SSMS. Hope it doesn't seem like I'm wanting you to spoon feed me, it's just that I've been on dozons of sites to locate them and forgot to bookmark the usefull ones. Thanks...

 

I went ahead and changed those to automatic start mode. The server agent is stopped and will not start. There is a new error log:

2011-09-21 13:58:22.29 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
Apr 2 2010 15:53:02
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

2011-09-21 13:58:22.29 Server (c) Microsoft Corporation.
2011-09-21 13:58:22.29 Server All rights reserved.
2011-09-21 13:58:22.29 Server Server process ID is 1536.
2011-09-21 13:58:22.29 Server Authentication mode is WINDOWS-ONLY.
2011-09-21 13:58:22.31 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-09-21 13:58:22.31 Server This instance of SQL Server last reported using a process ID of 2768 at 9/21/2011 1:55:44 PM (local) 9/21/2011 8:55:44 PM (UTC). This is an informational message only; no user action is required.
2011-09-21 13:58:22.31 Server Registry startup parameters:
-d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
-e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
-l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2011-09-21 13:58:22.95 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-09-21 13:58:22.95 Server Detected 1 CPUs. This is an informational message; no user action is required.
2011-09-21 13:58:23.04 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2011-09-21 13:58:23.28 Server Node configuration: node 0: CPU mask: 0x00000001:0 Active CPU mask: 0x00000001:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-09-21 13:58:24.18 spid7s Starting up database 'master'.
2011-09-21 13:58:25.60 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2011-09-21 13:58:26.12 spid7s Recovery completed for database master (database ID 1) in 1 second(s) (analysis 156 ms, redo 78 ms, undo 46 ms.) This is an informational message only. No user action is required.
2011-09-21 13:58:28.26 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2011-09-21 13:58:29.29 spid7s SQL Trace ID 1 was started by login "sa".
2011-09-21 13:58:29.35 spid7s Starting up database 'mssqlsystemresource'.
2011-09-21 13:58:29.53 spid7s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
2011-09-21 13:58:35.17 spid10s Starting up database 'model'.
2011-09-21 13:58:35.23 spid7s Server name is 'JAY-B4BD529039F\SQLEXPRESS'. This is an informational message only. No user action is required.
2011-09-21 13:58:36.07 spid7s Informational: No full-text supported languages found.
2011-09-21 13:58:37.20 spid7s Starting up database 'msdb'.
2011-09-21 13:58:37.65 Server A self-generated certificate was successfully loaded for encryption.
2011-09-21 13:58:37.75 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2011-09-21 13:58:37.75 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2011-09-21 13:58:37.87 Server Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2011-09-21 13:58:38.18 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2011-09-21 13:58:38.21 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2011-09-21 13:58:40.82 spid10s Recovery is writing a checkpoint in database 'model' (3). This is an informational message only. No user action is required.
2011-09-21 13:58:41.60 spid10s Recovery completed for database model (database ID 3) in 1 second(s) (analysis 187 ms, redo 187 ms, undo 109 ms.) This is an informational message only. No user action is required.
2011-09-21 13:58:44.06 spid10s Clearing tempdb database.
2011-09-21 13:58:50.60 spid7s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2011-09-21 13:58:51.45 spid7s Recovery completed for database msdb (database ID 4) in 2 second(s) (analysis 250 ms, redo 203 ms, undo 1250 ms.) This is an informational message only. No user action is required.
2011-09-21 13:59:00.28 spid10s Starting up database 'tempdb'.
2011-09-21 13:59:10.10 spid13s The Service Broker protocol transport is disabled or not configured.
2011-09-21 13:59:10.21 spid13s The Database Mirroring protocol transport is disabled or not configured.
2011-09-21 13:59:13.00 spid13s Service Broker manager has started.
2011-09-21 13:59:13.35 spid7s Recovery is complete. This is an informational message only. No user action is required.
2011-09-21 18:01:42.82 spid51 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2011-09-21 18:01:43.18 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2011-09-21 18:01:43.26 spid51 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2011-09-21 18:01:43.26 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2011-09-21 18:01:43.50 spid51 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2011-09-21 18:01:43.50 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2011-09-21 18:01:47.96 spid51 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2011-09-21 18:01:48.10 spid51 Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2011-09-21 18:01:49.28 spid51 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2011-09-21 18:01:49.28 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2011-09-21 18:01:49.29 spid51 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2011-09-21 18:01:49.29 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2011-09-21 18:01:49.34 spid51 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2011-09-21 18:01:49.34 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.


I need to take a break from this guy! I'm working on some C# till I can get this working with your help.

Jay