November 6, 2010 at 10:11 am
Admittedly, I am far from being a DBA or SQL expert. I only interact with SQL Server as an application developer who designs his own databases.
I had SQL Server 2005 Express on my machine. A few months ago I got myself SQL Server 2008 which proved a hassle to install. Eventually, I uninstalled 2005 and installed 2008. Or so I thought all the time.
When I launch Management Studio Express, which is my only route to using SQL Server, I see the splash screen says "SQL Server 2008 R2" but I recently discovered that the server instance is 2005, after a 2008 script failed to run.
I want to remove the 2005 instance and create a new 2008 instance. What should I do?
November 6, 2010 at 11:54 am
goodguy (11/6/2010)
I want to remove the 2005 instance and create a new 2008 instance. What should I do?
Either uninstall 2005, locate a 2008 DVD and install from there or locate the 2008 install DVD run setup and select the upgrade option.
First make sure that you don't have two instances on the server and are connecting to the wrong one (Check Administrative tools -> Services)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2010 at 12:10 pm
November 6, 2010 at 1:01 pm
goodguy (11/6/2010)
It seems there is no guarantee that Uninstall does what it is supposed to.
You must have either done something odd or the uninstaller failed with an error and you didn't notice. While the uninstaller can leave traces (files) behind, if it runs to completion, the affected item is removed.
When running the uninstaller, you have to select what you want uninstalling. If you didn't select the database engine, then it would not have been uninstalled.
OK, alternatively, can I add a 2008 instance next to the 2005 one?
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2010 at 12:26 am
I don't know what I may have I messed up during the install; of course I am not very expert on all the arcane options about named instances, pipes, etc. I just find they cramp my style (as Myra Shumway said in the James Hadley Chase novel Ms Shumway Waves A Wand). Some of these things one only learns in a college by working with a pro. Unfortunately, I have access to neither.
As for the proposed new 2008 instance, should I go about it the standard way with the DVD?
And could it be that while I have the 2008 front end (MS), the DB engine is still 2005 or is it possible that the 2005 instance sits atop the 2008 engine?
November 7, 2010 at 2:39 am
goodguy (11/7/2010)
I don't know what I may have I messed up during the install; of course I am not very expert on all the arcane options about named instances, pipes, etc
A named instance is anything but an 'arcane option'. It's how you have 2 SQL instances on the same server.
Some of these things one only learns in a college by working with a pro. Unfortunately, I have access to neither.
No, you learn by playing around and reading the docs.
As for the proposed new 2008 instance, should I go about it the standard way with the DVD?
And could it be that while I have the 2008 front end (MS), the DB engine is still 2005 or is it possible that the 2005 instance sits atop the 2008 engine?
Depends. Straightforward install (will have to be named instance if the 2005 is the default) or you can chose to upgrade if the path is supported.
I don't understand the second sentence. The 2005 and 2008 database engines would be different services installed in different places
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2010 at 5:06 am
Thanks, Gail, for helping over the weekend.
A named instance is anything but an 'arcane option'. It's how you have 2 SQL instances on the same server.
Agreed, but as I said, my forte is writing apps, not DBs, so what is very ordinary for you is a bit over-the-top for me!
In my coding experience, I have not seen a case where a client had a second instance, so naturally, I too have never known the situations where its implementation is indicated.
When we are coding, we like to keep all other connections as simple and generic as possible, and you will be amazed that most programmers' implementation of SQL Server has a blank SA password!
No, you learn by playing around and reading the docs.
Agreed again, but you will admit that nothing beats the experience of working with a pro team!
Depends. Straightforward install (will have to be named instance if the 2005 is the default) or you can chose to upgrade if the path is supported.
Thanks.
I don't understand the second sentence. The 2005 and 2008 database engines would be different services installed in different places.
Could it be possible that db engine did not upgrade during the install while Management Studio did?
Or, could it be that the engine did upgrade but inherited the instance of 2005?
Hope I have been clear.
November 7, 2010 at 5:46 am
goodguy (11/7/2010)
When we are coding, we like to keep all other connections as simple and generic as possible, and you will be amazed that most programmers' implementation of SQL Server has a blank SA password!
No I won't. The amount of data leaks that the 'simple as possible approach' has caused might surprise you. Never have a blank sa password, even on dev. You are asking for data leaks, hacks and other malicious behaviour.
No, you learn by playing around and reading the docs.
Agreed again, but you will admit that nothing beats the experience of working with a pro team!
How do you think the pros became (and stay) pro?
Could it be possible that db engine did not upgrade during the install while Management Studio did?
Or, could it be that the engine did upgrade but inherited the instance of 2005?
You said that uninstalled and reinstalled, so there was no upgrade.
The first could only happen if you ran an upgrade and it threw errors. The second cannot happen.
Install and upgrade are two different options on the installer, you can't do one by mistake (unless you don't bother reading anything and just select options at random)
I suggest uninstalling EVERY SQL server component (from the Programs and features/installed programs in Windows) and then installing 2008 from scratch.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2010 at 11:06 am
November 7, 2010 at 12:13 pm
goodguy (11/7/2010)
I was just stating the easy approach many developers go for,
I'll be blunt. That 's not the 'easy' approach, it's the lazy and irresponsible approach. Anyone who considers going that route in this day and age should not even be touching a database
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2010 at 9:34 pm
I agree wholeheartedly. I suppose you do coding too so I shall be grateful if you could help me in the right direction:
If, by convention, database servers should be secured:
1. I know we can manually edit ini/xml/config files at time of installation to configure the conn strings to connect to secure databases on production computers. How can I write dynamic connection strings?
2. In the same vein, how can I set up automatic database installation during setup that takes care of such security and access issues?
Thanks for all your help.
November 8, 2010 at 1:13 am
I decided to check "Programs & Features" in my computer's Control Panel and the attached image is a screenshot of what I found installed under SQL Server. As you can see, no mention of SQL Server 2005. But when I run SELECT @@VERSION on my default instance, this is what I get back:
"Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.0 (Build 6002: Service Pack 2)"
What are my options now as I do not see any point in uninstalling SQL Server if 2005 is not in the list.
How can I create a named instance of 2008 as I cannot find any step by step instructions online?
November 8, 2010 at 1:22 am
What do you have in the services tool? (Administrative tools-> services)
I can't tell what those are and the sizes make me think that none are the database engine (far too small)
Just a silly question - you are connecting to the right server?
p.s. Express as a default instance? It installs as a named instance - <servername>/Express or <servername>/SQLExpress
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2010 at 1:46 am
Thanks, again (don't tire of reading this!).
For performance purposes, since I do not use SQL Server a lot, I have not set SQL services to launch at startup. A check in Control Panel | Admin Tools | Services reveals that NONE is running at this moment.
As for the silly question, it is not so silly at all. The server I am connecting to is the default server, called LAPTOP\.
A fortnight ago, I downloaded some tutorials and samples on LightSwitch (a new development tool from Microsoft Visual Studio) and (for some reason) it required installing its database on SQLEXPRESS. So it seems like it created a new instance, called LAPTOP\SQLEXPRESS, which contains the solitary database called PrescriptionContose.
Running SELECT @@VERSION against this instance yields:
"Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Express Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)".
Any suggestions?
November 8, 2010 at 2:18 am
goodguy (11/8/2010)
Thanks, again (don't tire of reading this!).For performance purposes, since I do not use SQL Server a lot, I have not set SQL services to launch at startup. A check in Control Panel | Admin Tools | Services reveals that NONE is running at this moment.
How can no instances be running when you're connected and querying SQL? If you're connecting and running @@version, the service has to be running.
Besides, I didn't ask what services were running, I asked what services are present
As for the silly question, it is not so silly at all. The server I am connecting to is the default server, called LAPTOP\.
Is your computer called LAPTOP?
Running SELECT @@VERSION against this instance yields:
"Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Express Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)".
So you do have a 2008 instance installed. Use that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply