SQL2005 migration to SQL2008 from SBS2003 to Server 2012 std

"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

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

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

""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.

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

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

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

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

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

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

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

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

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

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

Regards

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

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

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

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..