Home » Sharepoint 2010RSS

Procedure to move all SP 2010 databases from SQL 2005 to SQL 2008 R2

I have a single SharePoint 2010 WFE with a single SQL 2005 64-bit backend.  I need to decomission the SQL 2005 server.  What is the best procedure to move all the SharePoint associated databases to SQL 2008 R2?

I have already read this MS doc on moving all databases: http://technet.microsoft.com/en-us/library/cc512725.aspx

So the actual move procedure is standard SQL fodder.

What I'm not clear about is what to do about the "other" SharePoint databases and how to reassociate them once the DBs are on the new SQL server.  For example, the Usage and Health DB is going to "Wss_Logging" and it specifies my old SQL server.  It is greyed so I cannot chagne it in Central Admin.  How do I let SharePoint know that these databases have been moved too?

 

8 Answers Found

 

Answer 1

To minimize downtime, here's how you can proceed
 
1) Transfer sql  Server logins from the SQL Server 2005 instance to the SQL Server 2008 R2 instance as per http://support.microsoft.com/kb/918992
2) Backup all of the SP2010 databases  on the SQL Server 2005 instance and restore them on the new SQL Server 2008 R2 instance (you can also use the detach/attach option mentioned in the TechNet article, although, I try to stay away from detach/attach to avoid database consistency issues)
3) You can either create a DNS alias that points to the new SQL Server 2008 R2 instance or use the stsadm utility or SharePoint Central Administrator to rename the SQL Server instance. For disaster recovery purposes, I configure all my server connections using DNS aliases. With DNS aliases, you don't have to worry about reassociate the databases once they are in the new SQL Server as the connection string is transparent
"BroussardGroup" <=?utf-8?B?QnJvdXNzYXJkR3JvdXA=?=> wrote in message news:a81135b2-9e49-4b39-92e0-d33f9f75ec0f...

I have a single SharePoint 2010 WFE with a single SQL 2005 64-bit backend.  I need to decomission the SQL 2005 server.  What is the best procedure  to move  all the SharePoint associated databases to SQL 2008 R2?

I have already read this MS doc on moving all databases: http://technet.microsoft.com/en-us/library/cc512725.aspx

So the actual move procedure is standard SQL fodder.

What I'm not clear about is what to do about the "other" SharePoint databases and how to reassociate them once the DBs are on the new SQL server.  For example, the Usage and Health DB is going to "Wss_Logging" and it specifies my old SQL server.  It is greyed so I cannot chagne it in Central Admin.  How do I let SharePoint know that these databases have been moved too?

Advertise

 

Answer 2

Thanks for the response.  I don't think I explained myself well.  Your steps are covered in http://technet.microsoft.com/en-us/library/cc512725.aspx and that seems easy enough.

What I'm confused about is what to do about the SP 2010 Service Applications.  They all have databases  associated with them.  How do I reconnect those?  Or do they get rebuilt automatically?  I can't find any mention of them in any of the documentation I've dug up on the web.

 

Answer 3

Looking in Central Admin I'm wondering if I could just do a full farm backup then a farm restore selecting "new configuration" and then keeping all settings the same but specifying the new sql  server name in the various database fields?  Would this accomplish my goal of moving all farm db's and service application db's to the new sql server without destroying the farm?

 

Answer 4

The reason I specified the DNS aliases is because these Service Applications all point to a database residing in a SQL Server instance. Creating DNS aliases will take care of redirecting the Service Applications to the new SQL Server 2008 instance without having to do it the "hard way"
 
The approach is to shutdown all the Sharepoint-related services, move  the databases, create a DNS alias and, then, restart the SharePoint services. When the SharePoint services start up, it will look for the original SQL Server instance name which will now point to the new SQL Server 2008 instance
 

Answer 5

Yes you can copy farm config and for this refer to this TN article http://technet.microsoft.com/en-us/library/ee428315.aspx
 

Answer 6

The Move all databases  (SharePoint Server 2010) procedure was tested for moving all databases in a farm simultaneously, including the configuration, content, and service application databases.
Since I wrote the article, I'd be interested in understanding whether you followed the procedure  and used SQL Server aliases, or the stsadm -o renameserver command (or DNS aliases, as bass_player recommended) to point SharePoint to the new location for the databases, and where you ran into problems.
Thanks!
 

Answer 7

The "Move all databases" procedure  has the following Note: "The new database server must be running the same version of Windows Server and Microsoft sql  Server as the existing database server."

BroussardGroup appears to be wanting to move  their databases  from a SQL 2005 to SQL 2008 R2.  I need to do the same for a client...move all SP 2010 databases from a Win 2003 and SQL 2005 server to a new server running Win 2008 R2 and SQL 2008 R2.  Will the "Move all databases" procedure work for this?

 

Answer 8

We haven't written an updated version of the SharePoint Server 2007 article for upgrading your database layer yet. Refer to the article
Migrate an existing server farm to a 64-bit environment (Office SharePoint Server 2007) for recommended practices when upgrading a database server.
 
 
 

Sharepoint Ads

 
 
 

<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter