SQL Express 2005 upgrade to 2008 R2

I have a server that was using SQL EXPRESS 2005.  I need to upgrade it to 2008 R2 to support our new software that runs on the SQL database.  I downloaded the SQL Express 2008 R2 installed and ran through the upgrade process. However, after the installation, the server still has the 2005 management software and it appears that the database is still version 2005.  If I run through the install again it acts like 2008 is already installed.  Not sure what is going on here or what to do to resolve this.  Do I need to uninstall both versions and install 2008 from scratch?  Any help would be appreciated.
David BarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anoo S PillaiCommented:
Could you post the result of the query select @@version when connected to the upgraded instance
0
David BarmanAuthor Commented:
From the 2005 management studio express software?
0
David BarmanAuthor Commented:
The select statement returned
SQL Server 2005 9.00.2005.00 (Intel X86)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Scott PletcherSenior DBACommented:
Did you maybe install a new, separate instance of SQL 2008 while also keeping the existing SQL 2005 instance?  I.e., new install instead of an upgrade?

If so, you could try to uninstall SQL 2008 and re-install it as an upgrade, although there are often issues around uninstalling SQL Server.
0
David BarmanAuthor Commented:
No. I definitely chose the upgrade option from the installer.
Would any screenshots help?
0
Anoo S PillaiCommented:
There is a chance that you have both the instances installed in your machine now. Before commenting on that , Could you please check this from Add/Remove programs OR  Services applet ( if two instances are there, you can see two entries in Add/Remove programs OR services applet ) .
0
David BarmanAuthor Commented:
See attached screenshot for installed programs.
Programs-and-Features.PNG
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please post a screenshot of the SQL Server services from the SQL Server Configuration Manager.
0
Deepak ChauhanSQL Server DBACommented:
Could you please show the screenshot of SQL server configuration manager.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
It isn't a SQL 2008 X64 you are attemting to upgrade to?
SQL 2005 Express only came in i86 flavour, and that upgrade path is not possible.

It's not very hard to do it manually though.

answer if the SQL 2008 is X64 architecture, and I'll take it from there.

Regards Marten
0
arnoldCommented:
install the SQL 2008 SSMSE as well.
use the configuration wizard for sql 2008.  I suspect that you did not upgrade the existing version, but installed another instance of sql. Make sure the browser service for sql 2008 is configured to run, configure it to listen on TCP/IP the issue

you can not use sql 2005 management to access sql 2008 instances. You could using sql 2008 SSMSE access both instances.
0
David BarmanAuthor Commented:
Attached is the screenshot of the SQL Services
0
David BarmanAuthor Commented:
Yes, I downloaded and installed SQLEXPR_x64_ENU.exe
0
David BarmanAuthor Commented:
I also downloaded the SQLManagementStudio_x64_ENU.exe.  See attached for the SQL program options on the start menu.
Start-menu.JPG
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Now we know what happened.
Do a backup of the databases from the old x86 sql, and restore on the new x64.
Recreate logins to the databases, or migrate using sp_help_rev procedure from Microsoft. Now shutsown old sql and se if everything works. When it Does uninstall i86 sql.
Regards Marten
0
David BarmanAuthor Commented:
If I try to install the SQL Express 2008 x86 I get the attached error
Sql-express-2008-x86-install-error.JPG
0
David BarmanAuthor Commented:
If it helps, attached are the SQL services
SQL-services.JPG
0
David BarmanAuthor Commented:
Is there any other way without migrating?  This SQL is a product/use for the company database/accounting system.  I don't know what users, etc they have setup for it.  If there is another way to just upgrade the old SQL 2005 that would be preferred.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
That sql service is it the i86 or x64 version?
Regards Marten
0
David BarmanAuthor Commented:
Not 100% sure what SQL service you are talking about.  Is the attached what you are looking for?
SQL-Server-Properties.JPG
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
I believe you need To love to X64 anyway. To migrate accounts read this through thourouly: http://www.experts-exchange.com/questions/25193164/Remove-the-need-to-manually-run-sp-change-users-login-AUTO-FIX-after-a-mirror-failover-automatic-or-forced.html

Regards Marten
0
David BarmanAuthor Commented:
Ok. I will take a look.  The thing is I don't see any 2008 instances or 2008 management software.  If I try to install again, it just acts like it's already installed.
0
arnoldCommented:
You have to are sure you select the workstation tools to install the management tool for 2008.

The image  pointed to omsql instance.
0
David BarmanAuthor Commented:
Omsql is the database I need to upgrade.
0
David BarmanAuthor Commented:
Ok. I read the article, but I am kinda lost as how to proceed.
0
arnoldCommented:
In services, do you have two ms SQL (OMSQL) and one sqlexpress?
Rerun the SQL 2008 setup, make changes, add workstation tools.

Do you have. Two SQL server configuration, one for 2005 anyone for 2008?
0
David BarmanAuthor Commented:
I don't see anything for 2008, not even in services, yet the system seems to think it is installed.
0
arnoldCommented:
Look under programs, do you have SQL server 2008 there?
0
David BarmanAuthor Commented:
Check my previous post for the screen shot of the installed programs and another one of the start menu program list.   Any help would be great.
0
arnoldCommented:
Your system reflects a need to restart to apply updates.
Look at the SQL server 2008, make changes to make sure I'll components are installed,.
0
David BarmanAuthor Commented:
Arnold. I don't understand what you mean. Please elaborate.
0
arnoldCommented:
In your image, look at the programs 2008 server, config, install, rerun the setup looking/validating what is installed.go through making sure what features/components are installed.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is the second time I'm requesting this:
Please post a screenshot of the SQL Server services from the SQL Server Configuration Manager.
0
David BarmanAuthor Commented:
Vitor,
Attached is the SQL services.
SQL-Services.JPG
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you installed the SQL Server 2008 where's the Configuration Manager from MSSQL 2008?
0
David BarmanAuthor Commented:
That is one of the issues.  I can't find it.  You can see my previous post with the screenshot of the start menu program group listing.  Also, if I re-run SQL setup and select "add features", see the attached screenshot for my options.  All options as shown are already checked.
SQL-2008-Setup.JPG
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's shows Replication feature so it can't be an Express Edition. You're not upgrading the Express Edition but installing a totally new SQL Server instance.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The best thing you can do is to uninstall the SQL Server 2005 Express Edition and go for a clean install of the SQL Server 2008R2. Then attach the databases in the new created SQL Server 2008R2 and change they compatibility level to 10 (SQL Server 2008).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BarmanAuthor Commented:
That setup was launched from the following file:  SQLEXPR_x64_ENU.EXE

Please advise.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't also upgrade from 32bit to 64bit. It really needs a new installation.
0
David BarmanAuthor Commented:
Ok.  Can I somehow undo what I have done with the 2008 and just upgrade to 2008 x86?
0
arnoldCommented:
You can backup restore, at this time it is not clear that you actually installed 2008 including db file/instances.
You can go through appwiz.cpl and remove the 2008.

IMHO, do not go the upgrade path. Setup ms SQL 2008 as its own instance. When it is running, device to stop, the access to the current version. Backup/restore the database, make the requisite change on the application to now access the data on the new instance and see if that works.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can I somehow undo what I have done with the 2008 and just upgrade to 2008 x86?
Only Uninstall it from Add/Remove Programs. But why don't you go for the 64bit version?
0
David BarmanAuthor Commented:
If I uninstall will that effect the 2005 version?
0
David BarmanAuthor Commented:
I am hoping for a seamless upgrade so I don't need to involve the software vendor who's database is running on the SQL server
0
arnoldCommented:
You gave to involve them ahead of time to make sure their software is supported with the new db.

IMHO, upgrades are prone to issues without a road back.

In your current process, the upgrade did not complete the task.
It might have installed some of the  SQL components but not the service nor did it seem to upgrade the DBs.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If I uninstall will that effect the 2005 version?
No. They should be in different directories as well in different Registry structure.
0
David BarmanAuthor Commented:
So, do you think I can uninstall the 2008 x64 and then install 2008 x86 and will it just upgrade my exisint 2005 installation and databases?
0
arnoldCommented:
Having gone down this road once, why not try a different route?
Have you check with the software vendor whether their application supports sql 2008?
2) add a second IP on the system if you are insistent on minimizing changes on the application side. Using a second IP, you can reconfigure the installed sql 2008 x64 to use the same instance while bound to the new IP.
3) transfer the logins from the 2005 to the newly installed 2008. decide when you transition. backup sql2005 DB/restore on sql2008. Reconfigure the IP bindings on the sql2005 bindingit to the localhost 127.0.0.1, then bind the sql2008 to both IPs.  Recheck the application functionality,

if it does not work, you can always revert back to having the DB stored on the sql2005 by reversing steps followed in 3 above.
0
David BarmanAuthor Commented:
Yes.  The reason we are even upgrading is the software vendor is requiring 2008 in order to support their new software.  We cannot install their new software version until we are at the SQL 2008  platform.

Ok.  Can you give me some reference on how to reconfigure the ip bindings as you have suggested?  I am very computer savvy but have little experience with SQL
0
arnoldCommented:
If you need to reinstall a new version of the software, you will likely have the option of specifying the new reference.  Does the software run on the same system as the sql server or the software is installed on each client system with the database residing on a server designated system?


decide when you are planing on transitioning test.
Make sure everyone gets out of the current software.
under the sql server configuration tools is where you can adjust the IPs to which each instance is bound.

Try on a separate system/workstation install an sql 2005 express \someinstance
then go through and enable tcp/Ip
add a new IP (DHCP configured client will not work) static Ip use is needed.
make sure to install SSMSE.
and make sure it works accessing the database. transfer logins from the current sql 2005. backup/restore the database on this sql 2005 instance.
Then reconfigure sql 2005 to bind to localhost and install
sql 2008 R2 express using the same instance name if prompted binding it to the second IP.

.make sure you are comfortable with the process from begining to end.

Will the existing software work with sql 2008 if you go the upgrade route of x86??
0
David BarmanAuthor Commented:
The software resides on each of the three client pcs.
Am I installing on a separate workstation for testing or is that what is necessary to get this working?
0
arnoldCommented:
The software resid on the individual system while the database server is on a separate system or does the software along with the SQL database installed on each client?

Checking the transition in a separately configured setup to avoid destroying data etc.
0
David BarmanAuthor Commented:
The sql database is on the network server.  The client pcs have the software application that connects to the SQL database on the server.
0
arnoldCommented:
I think you should install the SQL 2008 R2 as its own instance on the network server.

Copy the logins from the current 2005 to the 2008.
When you plan to transition from the old software to the new one, make sure all others stop using the software. Backup the DB on SQL 2005 and restore it on SQL 2008.
Change the SQL 2005/2008 TCP ip settings to await requests on different ports with SQL 2008 being on 1433.
Then install the first new client pointing to the new SQL 2008 instance.  Test the software making sure it works as expected.
Upgrade the other clients.
0
David BarmanAuthor Commented:
Well that brings us back to my original question. I tried installing SQL 2008 on the server but I don't see any 2008 running or an option for the management tools.

See my previous posts for screen shots of installed programs and services on the server computer
0
arnoldCommented:
I understand that, but I am unclear whether you ran the upgrade route versus installing a new instance which are two separate things.
Run the setup through the control panel's appwiz.cpl and select change, the. See what options do you have, you need the db engine.
0
David BarmanAuthor Commented:
I selected upgrade when I did the installation
0
arnoldCommented:
I understand and as others pointed out it failed because the installed version of SQL 2005 could not be updated./upgraded.
Install a new instance.

You have to be careful to avoid the new clashing with the old.
0
David BarmanAuthor Commented:
There are two SQL 2008 items in the installed program list:  Microsoft SQL Server 2008 Management Objects AND Microsoft SQL Server 2008 R2 Setup (English)

When I perform a "change" on the first, it only shows an option of REPAIR or REMOVE
The second item does NOT have a "change" option.  It only has Uninstall or Repair.
0
arnoldCommented:
Be careful to make sure when you enable TCP/IP on the 2008 to make sure to change the port it uses to something other than 1433 which is currently being used by the SQL 2005 instance.

Rerun the setup displayed in your program image, and go through the install option new instance.
0
David BarmanAuthor Commented:
Ok. After install, what should I be doing next?
0
arnoldCommented:
You now have two SQL instances running, one SQL 2005 and the other SQL 2008.
The first step is to transfer logins.
https://support.microsoft.com/en-us/kb/918992

What the login transfer accomplishes us sets up your new instance to have the correct security as well as retain the same access when you restore the SQL 2005 db from backup on the SQL 2008.

The backup/restore process should take into account that no changes are made to the SQL 2005 data after the backup us taken, if that data will be missing once the new is rolled out.
0
David BarmanAuthor Commented:
I install SQL 2008 R2, as a new instance. I also installed the management studio tools.  I changed the port number from 1433 to 1533.  I can't get Management Studio to see the 2008 instance.  It only sees the 2005 instance.

Ideas?
0
arnoldCommented:
You know the junta de type it in. Local\newinstancename.

One option is to make sure SQL 2008 services are also running the SQL browser server.

Are you using the SQL 2008 management interface!  You can not use SQL 2095 management interface to access/see the SQL 2008 one.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm already confused now.
What exactly you have done until now?
0
David BarmanAuthor Commented:
Yes I am using the SQL 2008 management tools

I have installed a SQL 2008 R2.  I have set the tcpip port to 1533.  I am now trying to connect to it.

I have tried typing it in, but it won't connect to the instance
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you stop the SQL Server 2005 Express instance? Then change the port of the SQL Server 2008R2 back to 1433 and restart it. Now try to connect.
0
David BarmanAuthor Commented:
Yes. That work. How can it find it without typing it manually?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I didn't understand your question.
0
David BarmanAuthor Commented:
So management tool will find the instance name with having to type it in.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You always need to type it unless is registered (CTRL+ALT+G) then you'll need to double-click on the instance name.
0
David BarmanAuthor Commented:
How do we register it?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Go to View/Registered Servers or press CTRL+ALT+G and then right-click on Local Server Groups (you can create more groups to organize them logically) as image shows:
RegisterServer.PNG
0
arnoldCommented:
Make sure sql server browser service for this instance is running.  browser is the discovery option.  local\instancename,1533 will get you there.  Switching the port prevents the other from running and being accessed.
0
David BarmanAuthor Commented:
Thank you everyone for their feedback.  I was able install the SQL Express 2008 R2 and create a new instance.  I was then able to restore the backups from the 2005 version and restore them to the new installation.  In addition, I disabled the old 2005 service so the two would not conflict.  Finally, I was able to update the client software to connect to the new instance.  Everything is working and we were able to update our client software to the new version.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.