March 23, 2012 at 8:13 am
I am being required to upgrade one of our installations of 32 bit SQL Server 2008 R2 to 64 bit as the server that the instance resides on is being upgraded to a 64 bit version of Windows Server 2008. Everything else about the server will stay the same - drive partitions, security, etc. In fact the sysadmin says that the data drives won't even be touched, just the OS drive.
Can I make this upgrade as easy as installing the 64 bit version of SQL exactly as I did for the 32 bit version and then post installation change my start up parameters for the SQL Service to point to my old master database? Currently the master db is on the data drive so it should not be impacted by the upgrade. Once I do that will all of my users databases and msdb be available since they didn't move either? Could it be that easy?
Unfortunately I don't think things are going to be as easy as that for me with the 50+ SSIS packages that all will need to be updated to use the 64 bit drivers. *sigh*
Thanks,
MWise
March 23, 2012 at 10:28 am
I hesitate to say yes, because the instant I do, everything you touch will break (because I accidentally cursed you).
I know your SSIS packages will have issues with the Excel drivers because those will still require the 32 bit dtexec.exe to run properly. And the whole idea of an "in place" upgrade, over the top of your databases really, really, really makes me squirm. I wouldn't allow anyone to do an in place upgrade / install over my stuff. I've gotten spoiled on side-by-side upgrades which give me plenty of wiggle room to fix problems.
All I can say is, backups, backups, backups. For all your databases (except TempDB), for all your share folders on that server, for all your logs, and for the OS as well from a just-in-case&better-safe-than-sorry POV. And have standy hardware too, in case the upgrade turns into a disaster recovery "test."
Additionally, it wouldn't hurt to call Microsoft and see if their tech support team sees any problem with it. Paul Randall (if he has a chance to read this thread before you do the deed) might also be able to give you some advice.
March 23, 2012 at 11:07 am
Data backups are a definite and already in place. I'll probably do one final full set just prior to shutting down SQL Server so if I have to restore via that method I don't have to apply all the T-logs. And according to the sysadmin doing the OS upgrade he has an backup image of the OS/SQL Server install drive so if it all goes to hell he can just restore the image and we are back to square one. He had to that once before when a server patch screwed the OS drive so I know that works.
I've got a "test" server where I can do most of this work. Problem is that the test server has the data drives on D: and our prod server has them on E: so I'm having trouble with that. When SQL Server starts up and reads the copy of master from prod it's loading up okay, but then can't find model and msdb since they are on D: and it's looking for E:. My sysadmin is going to try to change the drive mappings on test tonight and then I'll try again on Monday.
I hate this work though - it's not fun and it's of no benefit to our clients. But it's not something that you want to get wrong.
MWise
March 23, 2012 at 11:13 am
Who does it benefit, then, if not your clients?
March 23, 2012 at 12:50 pm
Mhlewis (3/23/2012)
I am being required to upgrade one of our installations of 32 bit SQL Server 2008 R2 to 64 bit as the server that the instance resides on is being upgraded to a 64 bit version of Windows Server 2008. Everything else about the server will stay the same - drive partitions, security, etc. In fact the sysadmin says that the data drives won't even be touched, just the OS drive.
Not sure if it's an option, but 32-bit SQL Server will run no a 64-bit OS. Would one choose that from the get-go? Hopefully not, but in your situation it may be the most prudent move. Just know it is technically a viable option.
Can I make this upgrade as easy as installing the 64 bit version of SQL exactly as I did for the 32 bit version and then post installation change my start up parameters for the SQL Service to point to my old master database? Currently the master db is on the data drive so it should not be impacted by the upgrade. Once I do that will all of my users databases and msdb be available since they didn't move either? Could it be that easy?
As long as you make sure the build numbers are the same between the two instances it could be that easy. Make sure you install all SPs, CUs, Hotfixes, etc. on the new instance so the build numbers match. The structure of the database files will not change, i.e. a database attached to a 32-bit instance can be detached and attached to a 64-bit instance, and vice-versa, without issue.
Unfortunately I don't think things are going to be as easy as that for me with the 50+ SSIS packages that all will need to be updated to use the 64 bit drivers. *sigh*
How are you calling your SSIS and how are they deployed? DTExec executing a file-system package through CmdExec step in SQL Agent? DTExec through SSIS Step in SQL Agent? If using file-system packages you can continue to run your SSIS using the 32-bit version of DTExec and it will work with the 64-bit instance with no issues. Not sure if you can use 32-bit runtime if your packages are stored in msdb, but I suspect you can. Staying with 32-bit SSIS runtime will be the least risky, and unless you are having issues with your SSIS the way it is it may be the safest option. Note that the same SSIS package can run under either runtime without code changes, i.e. there is no "upgrade" to do for moving to 64-bit SSIS runtime unless you're using external drivers that require SSIS code changes (Brandie mentioned the Excel drivers included with SSIS which is one to lookout for as it will not run in 64-bit mode).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2012 at 2:01 pm
I was told by our sysadmin that 32 bit SQL on 64 bit OS was not an option, also the small amount of research I did on that indicated performance issues.
I've been able to start up our master db on my test 64 bit install but can't get model and msdb to load because of the differences in drive letters. I'm hoping that hurdle gets fixed this weekend.
SSIS is being run DTExec through SSIS Step in SQL Agent off of file server for about 90% of them. There may be 10% left in msdb. Excel drivers and a 3rd party component for SFTP, Zip and PGP. That component will need to be upgraded and every package that uses it touched. If that wasn't at issue then yes, I'd keep the packages running in 32 bit mode.
Brandie - this is an infrastructure upgrade. The only benefit is that once our server is upgraded to Windows Server 2008 it can be virtualized and then our sysadmin can offer test environments and spin up new virtual servers as he needs to move things around to accomodate the lack of hardware purchase power.
March 23, 2012 at 2:22 pm
Mhlewis (3/23/2012)
I was told by our sysadmin that 32 bit SQL on 64 bit OS was not an option, also the small amount of research I did on that indicated performance issues.
It may not be appealing, but it is an option nonetheless. I currently admin a 32-bit instance running on a 64-bit server and have no issues with it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2012 at 4:17 am
Mhlewis (3/23/2012)
Brandie - this is an infrastructure upgrade. The only benefit is that once our server is upgraded to Windows Server 2008 it can be virtualized and then our sysadmin can offer test environments and spin up new virtual servers as he needs to move things around to accomodate the lack of hardware purchase power.
Actually, that's a pretty decent benefit, both for you and the clients. It may not seem like it now, but wait until you have the chance to do something with it.
March 26, 2012 at 8:50 am
Turns out that the sysadmin has decided to use a different server to use for virtualization. Upgrade now not necessary! Thanks for all input here. SSC is the greatest.
MWise
March 26, 2012 at 11:04 am
Glad we could help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply