Link to home
Start Free TrialLog in
Avatar of QPR
QPRFlag for New Zealand

asked on

move DBs to new sharepoint server and version!

Current farm. SQL Server 2005 and 1x WFE
Need to move the SP DBs to a new SQL 2008 virtualised server.
Can I reattach the DBs on the new server and point central admnin to it or do I need to upgrade the SQl Server 2005 to 2008 and then do the move?
Which ever option... url(s) giving step by step examples (as opposed to vague technet articles)?
ASKER CERTIFIED SOLUTION
Avatar of Justin Smith
Justin Smith
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good comments ACH1LLES!!  

Keep in mind the referenced technet article is for SharePoint 2010, what version of SharePoint are you running QPR?

I am under the opinion that a backup and restore is a better option then just a detach and attach especially when going to a different version of SQL.  With this option there is always a backdoor, because your using a backup of the DB and not the original data.

Thanks. WHen they don't specify, I always assume the latest version.

Either way, process should remain the same.
Avatar of QPR

ASKER

Thanks guys and yes, 2010.
I put in the technect comment (although I am a regular technet reader) because I am still fairly new to SP admin and needed some hand holding rather than a general tech overview which assumes some prior base knowledge. I am a DBA who has inherited SP.

I've read comments that say that SP config is set up differently depending on the version of SQL due to new functionality in 2008/R2 and so a copy/attach or backup/restore may not be enough.

The new SQL Server has not been installed yet so I do have the option of installing 2005, doing the move and then upgrading SQL in place... if that is possible/recommended.
Basically looking for the path of least resistance after having so many headaches with a service pack and june update install (dev server) recently. There seem to be so many things that can go awry and I don't have all the skills necessary to dig them out of a hole... yet.
Avatar of QPR

ASKER

Just had a look at the technet article and it says...
"The new database server must be running the same version of Windows Server and Microsoft SQL Server as the existing database server."
That is a big gotcha!!

Check this out! - http://technet.microsoft.com/en-us/library/cc512725.aspx
Avatar of QPR

ASKER

In the context of my question it doesn't get any bigger :)
Will check the link now
Avatar of QPR

ASKER

Am I missing something? that is the url already posted above which says...
"The new database server must be running the same version of Windows Server and Microsoft SQL Server as the existing database server."

The main part of my question revolves around the SQL version not being the same
not true. When to restore the databases will be upgraded.
Avatar of QPR

ASKER

So the technet article is wrong/misleading?

Different link, I looked through it and I didn't see the same version of SQL and Windows being moved to as a qualification.

We are talking about SQL ONLY right?

As long as the correct ports are open so external servers can get to the data SharePoint is NOT gonna care what version of Windows SQL is installed on!!

For configuring SQL it will matter what version of Windows it is being installed on, so you will want to follow the recommended steps for setting up SQL for SharePoint on 2008.
Correct.
Avatar of QPR

ASKER

jodiddy, here is the text from the url you sent

To prepare the new database server
Configure the new database server by using the procedures in the article Harden SQL Server for SharePoint environments (SharePoint Server 2010).

 Note:  
The new database server must be running the same version of Windows Server and Microsoft SQL Server as the existing database server.
 
I agree that if you go the route of backup and restore that when you restore the 2005 SQL DB's to a 2008 SQL that the DB's will be upgraded to work with 2008  SQL.

There is always the option of installing SQL 2005 on the new Server and then upgrading SQL after the data has been moved, I can see how that might make you feel more comfortable.
Avatar of QPR

ASKER

So technet is excellent and not vague but the info on it is wrong?
<insert smiley>

Not trying to be flippant here but this is a production environment and I don't want to learn things on the fly on a Sunday afternoon.
I see!! Wow I totally missed that, well I think that statement is a little misleading!  

SharePoint doesn't care what version of Windows SQL is installed on!  SharePoint just cares about the proper ports being open so it can access the data, and it cares about how SQL is configured.

Microsoft is just saying that to cover there base.  The configurations for SQL are different depending on the version of Windows and version of SQL.  As far as SharePoint is concerned it just needs access to the DB Server and the permissions to access the DB's.

Again if you are concerned I recommend keeping the same version of SQL throughout, and then upgrading after.  There are a large amount of considerations that have to be taken into account when using Windows Server 2008 vs Windows Server 2003 with SQL.  The largest one is firewall, the 2008 firewall blocks all the SQL ports and you have either unblock or open the specific ports necessary .



Avatar of QPR

ASKER

a lack of definitive info makes me uncomfortable... I'm leaning toward upgrading the sql server in place and then migrating to the new server or installing sql 2005 on the new server, migrating over and then upgrading.

Avatar of QPR

ASKER

just to confirm, the windows server version (or sharepoint version) will not change.
This is purely a sql 2005 to 2008 question
Avatar of QPR

ASKER

A similar question at MS also throws up this caution about missing features....

I am doing this move also, if you have any concerns you can set the Database compatability mode to SQL 2005 in SQL management studio which should work for you. Because you installed it directly on 2005 you might be missing some sharepoint features that are only available when you install on a SQL 2008 server. You may want to check because I am not sure if you can enable those after the upgrade.

Hope this helps


--------------------------------------------------------------------------------
Allan Merolla | BEng, MPM, JD | MCP, MCTS, MCPD | SharePoint/SQLBI/.NET | My Blog at http://www.parallelfun.com/
this will be my final response. I've done this type of an upgrade more than once. The steps match what is in the article I posted. SharePoint doesn't care what version of SQL as long as it's compatible. Restore the 2005 databases to the 2008 box. This will upgrade them. At this point you have matching databases on two SQL servers. Point the farm to the new SQL server. If it doesn't work, just point it back to the old one.

As noted, keep SQL security on the databases in mind after the restore.

Also, you need a test environment :-). Actually you need a prod environment, because your current prod is really test.
Both are great options, whatever makes you feel more comfortable.  The end result will be in essence the same.

ARCHILLES is correct, his method is the most direct!  For the cautionist, taking it a little slower by either doing an in place upgrade of SQl or doing the upgrade of SQL after the data has been moved is good either way.

Good point to check on the feature mix of 2005 vs 2008, there maybe features you want to take advantage that cannot be added.  I am not what those would be, but it's worth a look into.
Avatar of QPR

ASKER

ARCHILLES I believe you... I've been around here long enough to do so.
But with so much mixed information floating about it makes me nervous.
We have a test environment, this is already virtualised and is a stand alone... more for testing code.
So while you'd be right in saying that it is not a true test environment - that is a scenario that may be too difficult/costly for us to replicate at the farm level.

I will install 2008 on the new server, attach the DBs, do the <other stuff with aliases> that needs doing, test as much as I can in the new environment and if all good, turn off the old area.
GULP