How to move sharepoint databases to new SQL server?

Question

Hi all

I have standalone Sharepoint 2010 which uses external SQL 2008 SP2. I'd like to move all sharepoint databases to new 2008 R2 SP1 server. I found several articles and tutorials how to do it, but most require that source and destination SQL servers are running exactly the same software versions, which is not true in my case. OS versions are also different. Can you guys help to find a way to do it? Sharepoint is not heavily used, I can have evening maintenance window without problems, but I'd like to avoid configuring whole thing again.

Many thanks for any input

Chris

Answer 1

Hi ChrisCiapala

I do think most of what you would have to do is in this article: Move all databases (SharePoint Server 2010)

Regards,

- T.s -

---

SharePoint in Vietnam | Blog

Answer 2

I found this before, but there is this note in the article:

The new database server must be running the same version of Windows Server and Microsoft SQL Server as the existing database server.

While in my case both OS and SQL versions are different.

Answer 3

Hello Chris,

Though you have different OS and SQL server , you should be OK to continue since both components are of higher version than source.

---

Hrishi Deshpande – DeltaBahn Senior Consultant
Blog | < | LinkedIn

Answer 4

Right, I'll give it a try, however this aliasing part is not very clear for me. It says to create alias with old server, pointing to the new server. But where specifically should I create this alias? On sharepoint server? If yes, than I have sql2008 on old server and 2008r2 on new, so which version installer should I use on sharepoint to install management components?

Answer 5

Hi,

I think if you migrate your database from a lower SQL version to a higher SQL version (ex: 2005 to 2008, or 2008SP1 to 2008R2) it shouldn't be a problem.

So what I would suggest you is to:

1) make the new server installation (OS, SQL and all updates)

2) copy all databases from your old SQL and restore them to your new SQL server (do an SQL full backup and restore here)

3) tell your SharePoint (if you have more than one, you need to repeat this step) server to change the SQL by executing the command:

Stsadm -o renameserver -oldservername -newservername   and for example it would be stsadm -o renameserver -oldservername oldsql -newservername newsql

You might need to update the farm credentials with this command:

Stsadm -o updatefarmcredentials -userlogin -password  where this is your system account or application account used to install/run your SharePoint.

And finally, you might need to re-create your search index if you have one.

Note: you should try this in a test environment.

Hope it helps,

Cheers,

Answer 6

Hi Esad

I did exactly what you suggested, stsadm said everything went fine, I rebooted the server, but now it still wants to talk to the old one. Also when I start Sharepoint Products configuration Wizard it shows old DB server. I tested this by stopping old sql and once I stopped it, sharepoint refused to work.

Answer 7

Hello Chris,

Since you already tried above stsadm commands, I think to make the farm available as soon as possible you should configure SQL server alias on each SharePoint server

From run window execute cliconfg and navigate to alias tab and use TCP\IP library to point old  server to new.

---

Hrishi Deshpande – DeltaBahn Senior Consultant
Blog | < | LinkedIn

Answer 8

Hi Chris,

I am surprised, but you can also try Hrishi's method, and what it is, it just creates a route to your new sql.

If you need help, maybe we can do screen share via skype and try to help you, as have done it many times with the renameserver command.

Cheers,

Answer 9

Thanks guys for your help. While stsadm didn't really work and I have no idea why, alias method worked well.

Article in Technet could be revised slightly, to let people know that it will work also when changing OS and SQL version, also there is no need to install SQL Client just to create alias, just the cliconfg is enough. Description of aliasing itself could also be clarifed, currently is doesn't really say what is what, Hrishi post was way better than whole section in the Technet article.

Many thanks again, my sharepoint is working well on the new server.

cheers

chris

Answer 10

Hello Chris,

Glad to know that your SharePoint is back online. For the benefit of other please mark my earlier response as “Answer” to close the thread.

---

Hrishi Deshpande – DeltaBahn Senior Consultant
Blog | < | LinkedIn

Answer 11

Hi Chris,

I have moved my databases by using Microsoft article, SharePoint is working fine. But monitoring tools are stopped working. Web analytic, Health Analyzer and all admin reports are not generating any new report.

do you have any idea what I have missed?

Thanks