Solved

SQL2005 migration to SQL2008 from SBS2003 to Server 2012 std

Posted on 2014-03-03
19
395 Views
Last Modified: 2014-03-13
Hi Learned ones,

I need to migrate an existing SQL2005 express database from sbs2003R2 premium to the replacement environment on a Server 2012 std box.

The existing environment is of course 32bit and Server 2012 std is 64bit.

The client runs through 32bit or 64bit ODBC as required according to OS.

I don't want to make too bigger hop forward on the SQL side so have opted for SQL2008 (ie one major revision forward) There is some merge replication involved to allow a user to take a snapshot away on a laptop and make changes which are merged when he returns. The data is not huge.

So my questions are:
1. Is SQL2008 express supported on Server 2012 std
2. Should I install 32bit SQL2008 Express or is 64bit OK for a straight forward migration.
3. Can anyone point me to a good step by step as I understand there are many detailed steps that need performing during the migration of the legacy database files to the new environment.
4. What restrictions are there with regard to server and client side ODBC architecture (32/64bit if any)
5. Are there any advantages at this stage in going directly to SQL2012 Express.

There is a plethora of info about this but a have not been able to get the detail I need.
I have a development environment on a VM box so could experiment if required. First step is to 32bit or 64bit or not ????

Hope you guys can help


Regards
0
Comment
Question by:TrevorWhite
  • 14
  • 3
  • 2
19 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 39901067
"So my questions are:"

1. Is SQL2008 express supported on Server 2012 std
A: If by this you mean running SQL 2008 Express on Windows 8 - it is all good. I run it at home without any issues.

2. Should I install 32bit SQL2008 Express or is 64bit OK for a straight forward migration.
A: Assuming you have Win 8 64bits(never heard about a 32 bit version anyway...) I would install SQL 2008 Express 64 bits of course.

3. Can anyone point me to a good step by step as I understand there are many detailed steps that need performing during the migration of the legacy database files to the new environment.
A: Script all SQL users to transfer to new server: http://support.microsoft.com/kb/918992
    Script all SQL jobs, packages, full text, etc. you may have on the old box (my suggestion is to drop FullText if you have it enabled as it's much easier to Restore a DB without it)
    Make sure you backup ALL your user databases from old system
    You can run a Database Upgrade Advisor and fix any issues if needed    

Restore in reverse order above!! Must create users before restoring DB's to make it easier for you.

4. What restrictions are there with regard to server and client side ODBC architecture (32/64bit if any)
A: Some applications may not have 64 bit drivers

5. Are there any advantages at this stage in going directly to SQL2012 Express.
A: Sure, you can skip a step that is not mandatory - 2005->2008->2012.
http://technet.microsoft.com/en-us/library/bb677622.aspx

More details at:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8dfb2cc5-eac5-4d57-9dad-f858d3c0f0c8/is-sql-2008-compatible-with-windows-server-2012?forum=sqlsetupandupgrade
0
 

Author Comment

by:TrevorWhite
ID: 39901153
Hi Icohan

The questions were really as asked. EG Is sql2008 supported on Server2012 std (I can find no specific confirmation of this)

SQL2008 exists in two flavours of 32bit, how ever my understanding is that there are issues migrating from 32bit to 64bit - I may be corrected on this.

I can deal with the clients, I am interested in the actual migration process which is not straight forward as far as I have read. I need the specific instructions and procedure prior to detaching the legacy files and then attaching to SQL2008

Just getting to SQL2012 is only one part of this migration issue. There are SQL functions that have been deprecated and modified between 2005 -2008-2012 I need to minimise my exposure to these to ensure the application functions correctly.

Thanks for your time with this if you have specific knowledge of server 2012 that would be helpful.

REgards
0
 

Author Comment

by:TrevorWhite
ID: 39901163
With regard to the specific migration instructions. What needs to be done to ensure the database can be merge replicated on the 2012 platform under 2008 - this is always an issue which is why I sought step by step instructions.

REgards
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39901222
""The questions were really as asked. EG Is sql2008 supported on Server2012 std (I can find no specific confirmation of this)"


Check "Minimum SQL Server version requirements for Windows Server 2012 or Windows 8" at link below:

http://support.microsoft.com/kb/2681562


http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8dfb2cc5-eac5-4d57-9dad-f858d3c0f0c8/is-sql-2008-compatible-with-windows-server-2012?forum=sqlsetupandupgrade




"SQL2008 exists in two flavours of 32bit, how ever my understanding is that there are issues migrating from 32bit to 64bit - I may be corrected on this."


If you backup your DB's on old 32bit and then Restore to 64bit SQL there will be no problems. As far as migrating client applications....you need some QA time and resources here...no magic bullet exists.



"I can deal with the clients, I am interested in the actual migration process which is not straight forward as far as I have read. I need the specific instructions and procedure prior to detaching the legacy files and then attaching to SQL2008"

I think I answered this one a few times already - I always run the Upgrade advisor to see if there's anything major to deal with the use Backup/Restore for my migrations with one comment...if DB's are huge like I had a 7TB one then - Log Shipping but because you are on SQL Express edition...they must be small and upgrade is in place at restore time.


"Just getting to SQL2012 is only one part of this migration issue. There are SQL functions that have been deprecated and modified between 2005 -2008-2012 I need to minimise my exposure to these to ensure the application functions correctly."


As mentioned before, Upgrade Advisor should help you asses the damage and QA on your test VM should do it.
0
 

Author Comment

by:TrevorWhite
ID: 39905998
HI
Sorry for the delay getting back to this.
OK I have moved forward and have SQL2008R2 Express installed on Server 2012 and have managed to migrate my data using a detach and attach process after dropping all Publications.

It would seem that I cannot create a Publisher for the Merge replication using SQL 2008 Express. I think the free copy that came with SBS2003 was not Express - would have been good to have known that!! I'll live with that for the short term and upgrade to WorkGroup SQL 2008 R2 shortly

The outstanding issue now is item 4 above - or at least network connectivity to the database. I can connect from a 64bit ODBC native client driver located on the Server itself but cannot connect to the database from a Windows 8 Professional 32bit machine on that network.

Network is VM ware with 2x2012 servers (Domain controller, AD and DHCP, etc is 2012 essentials second server is 2012 standard where SQL 2008 R2 Express is installed. The windows 8 machine is joined to the domain and I have installed the 32bit ODBC native client for SQL2008R2 I use the exact same connection parameters but get :

Connection Failed:
SQLState: '08001'
SQL Server Error: -1
[Microsoft][SQL Server Native Client 10.0]SQL Server Network
Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF],
Connection failed:
SQLState: 'HYT00'
SQL Server Error: 0
[Microsoft][SQL SErver Native Client 10.0]Login timeout expired


I'm currently googling text from this error report to see if I can uncover the cause.

I have turned TCP/IP remote connections on, allowed port 1433 through the Windows 8 firewall and the 2012 firewall, I'm also concerned that there is a problem with the compatibility of the 32bit driver and how it is deployed - there seems to be more than 1 ODBC control panel to instance connections with these drivers.

Can anyone shine some light please.

Thanks
0
 

Author Comment

by:TrevorWhite
ID: 39906006
Oh one more thing. I can ping the server by name and get the correct IP address.

How do I ensure that SQL is listening on this address please??? I believe I am listing on ALLIP's for the network card.

REgards
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39907269
Hi,

You are using SQL named instances. They by default use dynamic ports. If you use a firewall you'll need to change that to a static port. Don't set both instances to 1433 as that wont work.  I just set them to the dynamic port they are currently using. Then open those ports in your firewall. And whatever additional ports the SQL Browser service is using.

For ease of testing that you can actually connect, turn the firewall off.

When specifying the server, it is servername/instancename. If you have been omitting the instance name then you wont connect and it will give a similar error message to the above.

HTH
  David
0
 

Author Comment

by:TrevorWhite
ID: 39907305
Hi David,
I'm working on this now - head getting sore !!!!
Thanks for the info - I have been using servername\instance and realising this must be to do with ports/firewall/networking etc.

Is it correct that the SQL browser generally manages the dynamic port stuff or is that only for multiple instances - just read something about this and turned the SQL browse service on even though I only have this SQLEXPRESS instance.

How does one manage dynamic SQL ports through the firewall normally ??? surely we don't 'find one that works and then make it static do we ???

Thanks for your time would be good to get this going tonight, cheers

Regards
0
 

Author Comment

by:TrevorWhite
ID: 39907365
Hi again
Have turned the firewall off on the W8 client and on the Server 2012 box still can't connect

How do I know which port is actually being used please. Have supplied a screen shot of the SQL configuration manager - I have a concern about the 32bit Network configuration stuff, nothing there !!!
SQL2008-configuration-manager.docx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 35

Expert Comment

by:David Todd
ID: 39907423
Hi,

In the bottom of your screen shot is IPALL showing a dynamic port of 36065.

Now a 32bit client can talk to a 64bit SQL. The issue is that the 32bit client needs the 32bit driver/network stack, and the 64bit server needs the 64bit driver/network stack.

For instance, a previous employer had a customised piece of software that ran to a Sybase 11 back end. Really old. 32 bit server, and we had obviously 32bit clients and it all worked.

But come the 64bit sql that we had to connect to this Sybase and we couldn't find a 64-bit Sybase driver at an acceptable price point. So this 64bit sql couldn't directly connect to the 32bit Sybase, not because Sybase is a 32bit application but because there was no suitable 64-bit driver.

HTH
  David
0
 

Author Comment

by:TrevorWhite
ID: 39907478
Yeah I have the same problem, my own fault really. I wrote some VFP7 code (when I was doing all of that sort of stuff) which relies on an ODBC DSN. Customer relies on these three aps for the business, would take a lot to port it so we have this issue with the death of SBS2003 !!!!

So I just need to establish the correct drivers for the SQL server 32bit stack ??? This is what I thought when I saw these empty !!!

OK big Q - where do I get 'em are these the 32bit equivalents of the existing 64bit server side drivers - these were installed when I installed SQL2008R2 Express. How do I get extra ones installed.

I'll google but would save time if you know or have a link

Thanks
0
 

Author Comment

by:TrevorWhite
ID: 39907597
One more ditty, I have kept the firewalls down and run the server side SQL NATIVE client and then looked at the logs and can see SQL is listening on port 36065, good.

I've never had to force the port in the ODBC DSN before and don't know where to effect this. I tried host/instance:36065 and also with a space instead of the : but no go. How is this effected please (once I get these 32bit network drivers sorted)

Thanks
0
 

Author Comment

by:TrevorWhite
ID: 39907816
Hi Dave and anyone else looking at this,

I have been stepping back from my dev environment which was being used to check the migration from an SBS2003 SQL2005 database to Server 2012 SQL2008 so 64bit. The test PC I have is 32bit Win 8 which I thought OK as the app is a 32bit application requiring a 32bit ODBC DSN.

The target PC's will be 64bit Win 7 pro.  Now I know I can setup 32bit ODBC Native clients on a 64bit OS.

There is no need for the SQL instance to be 64bit. Can I install a 32bit instance of SQLserver 2008R2 on Server 2012 and will this then resolve the problem of the 64/32bit driver stacks as made clear by David earlier.

Has anyone done this ???

Thanks in advance for your help,

Regards
0
 

Author Comment

by:TrevorWhite
ID: 39908672
Hi Guys,
Could do with some input here guys, particularly for my last post. Near to allocating points now me thinks.

Regards
0
 

Author Comment

by:TrevorWhite
ID: 39911140
Well its all gone very quiet out there - helooooo ??
I have changed the SQL2008R2 SQLExpress  for a 32bit instance and reattached the database. I can still connect using the local ODBC DSN, this does so by a named pipe rather than TCP/IP for some reason.

I have kept the fire walls down for now.

I made sure I have the 32bit Native Client installed on the Win8 32bit machine but still cannot connect. One bit of progress though, I CAN connect (at least I don't get a reject) over telnet xx.xx.xx.xx port where xx.xx.xx.xx is the IP of the SQL server and port is the port said to be listening for ALLIPs in the SQL configuration manager. Is there a way to test that telnet has completed the connect - maybe I'll look in the logs :-)

So ..  once again how do I ensure that the ODBC DSN is using the correct port. Or how can I force SQL to use a standard port when its a named instance.

I'm sure there must be one or two bright sparks out there in SQL land who know all about this . . . if so lend a hand to this aging lad!!!

Regards
0
 

Author Comment

by:TrevorWhite
ID: 39912413
I'm struggling on with this and have an update - would be soooo much quicker to have some experience on this . . . .

It would appear that if I instance a DSN 32bit ODBC connection at the client running the %windir%\syswow64\odbcad32.exe and the Native Client v 10 driver then I'm able to connect to SQL successfully and I can also run the application which finds the DSN fine.

I tried this on a 64bit device that I connected to the same domain as the SQL server.

The following is a bit academic (sorry about the pun!!) but syswow64 won't be on a 32bit machine. What's the difference between system32\odbcad32.exe and syswow64\odbcad32.exe ???

All of the new PC's will be 64bit on the new network but would be good to know if there is a solution for running on a true 32bit platform rather than virtual 32bits !!!

Look forward to some wisdom soon

Thanks
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39913449
Hi Trevor,

Is the SQL Browser service running?

What it does: It listens on the old 1433 port, and then when the connect attempt comes in asking for instance abc the browser service responds with the correct port to connect on.

Now, if you have SQL 2000 media around, in a tools folder, there is an executable called odbcping. Installation is simply copy to the hard drive. (Maybe need to rename the extension.)

Anyway, this tool attempts to connect to a sql server, either directly or via an odbc dsn, with trusted credentials or those provided. The range of responses tell you if you're close or not.

Now its good to see you getting your head around the 32-bit odbc admin vs the 64-bit odbc admin. On systems below Windows 8 they 32 bit version is hidden, but most of the client applications are 32-bit.

Only one version of the tool can be active at once.

The two clues inside the tool are: Which set of drivers are listed. There are more 32bit drivers than 64bit drivers. And does task manager note that it is a 32bit application or not.

So from the above, if struggling with the different versions of the odbc admin tool, I suggest an upgrade to Win 8.1 or Win 2012 as appropriate.

HTH
  David
0
 

Author Comment

by:TrevorWhite
ID: 39927358
Hi
Sorry for not keeping in touch here. I have sufficient info (mainly from David) to resolve what is required here now.

So points time. I feel David was most constructive with his advice and helped me forward to finding the answers I needed. I will award the points accordingly.

If anyone does have a link to a definitive description of differences between the various 32/v32bit and 64bit DSN ODBC drivers I would very much appreciate it.

Regards
0
 

Author Closing Comment

by:TrevorWhite
ID: 39927369
Whilst working with David I got to the solution. Davids comments were helpful however my notes covering what I discovered are required for anyone referring to this question..
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now