?
Solved

move DBs to new sharepoint server and version!

Posted on 2011-10-09
22
Medium Priority
?
237 Views
Last Modified: 2012-05-12
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)?
0
Comment
Question by:QPR
  • 11
  • 6
  • 5
22 Comments
 
LVL 38

Accepted Solution

by:
Justin Smith earned 2000 total points
ID: 36942756
The technet articles aren't vague.  This is an excellent one:  http://technet.microsoft.com/en-us/library/cc512723.aspx

I'm assuming you already ahve your 2008 SQL box set up.  You may need to backup/restore rather than just detach and attach, since it's a different version of SQL.  But I could be wrong.

You then have two options on how to tell your WFE about the new SQL server.  Either do the SQL Alias as described in the article.  Or rename the server using STSADM.  The latter only works if you are using the same SQL Instance name in both.
0
 
LVL 3

Expert Comment

by:jodiddy
ID: 36943971
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.

0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 36944239
Thanks. WHen they don't specify, I always assume the latest version.

Either way, process should remain the same.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 29

Author Comment

by:QPR
ID: 36944626
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.
0
 
LVL 29

Author Comment

by:QPR
ID: 36944634
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."
0
 
LVL 3

Expert Comment

by:jodiddy
ID: 36944673
That is a big gotcha!!

Check this out! - http://technet.microsoft.com/en-us/library/cc512725.aspx
0
 
LVL 29

Author Comment

by:QPR
ID: 36946039
In the context of my question it doesn't get any bigger :)
Will check the link now
0
 
LVL 29

Author Comment

by:QPR
ID: 36946041
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
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 36946047
not true. When to restore the databases will be upgraded.
0
 
LVL 29

Author Comment

by:QPR
ID: 36946090
So the technet article is wrong/misleading?
0
 
LVL 3

Expert Comment

by:jodiddy
ID: 36946092

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.
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 36946094
Correct.
0
 
LVL 29

Author Comment

by:QPR
ID: 36946102
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.
 
0
 
LVL 3

Expert Comment

by:jodiddy
ID: 36946103
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.
0
 
LVL 29

Author Comment

by:QPR
ID: 36946106
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.
0
 
LVL 3

Expert Comment

by:jodiddy
ID: 36946154
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 .



0
 
LVL 29

Author Comment

by:QPR
ID: 36946161
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.

0
 
LVL 29

Author Comment

by:QPR
ID: 36946166
just to confirm, the windows server version (or sharepoint version) will not change.
This is purely a sql 2005 to 2008 question
0
 
LVL 29

Author Comment

by:QPR
ID: 36946172
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/
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 36946231
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.
0
 
LVL 3

Expert Comment

by:jodiddy
ID: 36946312
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.
0
 
LVL 29

Author Comment

by:QPR
ID: 36946381
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
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Scenario: Let’s say you have a quote worksheet in Excel that you use to work up sales figures and such for your clients. You utilize SharePoint to manage and keep track of these documents. You would like values from your worksheet to populate Sh…
Pimping Sharepoint 2007 without Server-Side Code Part 1 One of my biggest frustrations with Sharepoint 2007 in the corporate world is that while good-intentioned managers lock down the more interesting capabilities of Sharepoint programming in…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month4 days, 15 hours left to enroll

601 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