Sharepoint 2007 Database Migration from SQL Server 2005 to SQL Server 2008
Hi,
I've got to move the sharepoint databases from their old server on sql5005 to a new server, with a different name, on sql2008r2
I have found a number of articles on moving the databases to a server with a new name on the same database level, or moving the databases to a server with the same name on a different database level.
Does anyone have the steps i need to do both?
It is a single instance of MOSS with just the one database server and one MOSS server
Thanks
Jon
March 17th, 2011 7:21am
Hi Jon,
this is a very good article explains you step by step procedure for SQL migration
http://ahmed-ammar.blogspot.com/2009/05/how-to-move-sharepoint-content-database.htmlWarm Regards, Pratik Vyas | SharePoint Consultant | http://sharepointpratik.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
March 17th, 2011 7:40am
also see this link
http://www.packtpub.com/article/moving-a-database-from-sql-server-2005-to-sql-server-2008-in-three-steps
Moving a Database from SQL Server 2005 to SQL Server 2008 in Three StepsMai Omar | Senior Solutions Developer | ITEgyptCorp | Email: maio@ITegyptCorp.com | Blog: http://maiomar.itegyptcorp.com
March 17th, 2011 8:08am
Pre-requites:
Verify the hardware and software requirements
Write down version of your SharePoint farm.Backup Entire FarmSSP full backups with "stsadm" commandMoving databases makes all farm sites and assets unavailable to users until the process is completed.Prepare the new database serverensure that all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases from the source server have also been configured correctly on the destination serverIn the Services Microsoft Management Console snap-in, stop all of the services related to SharePoint Server 2007 and Internet Information Services (IIS) and documented
On the database server from which the databases are being moved, you must be a member of the following:
The Administrators groupThe db_backupoperator fixed database roleOn the database server to which the databases are being moved, you must be a member of the following:The Administrators groupThe db_owner fixed database role
The new database server must be running the same version of Windows Server and Microsoft SQL Server as the existing database server.Name of all Services which has running : On the server that is running the Central Administration Web site, in the Services snap-in, stop/start Services List
To check and set up permissions on the new server
On the destination database server, open SQL Server Management Studio and transfer your logins and permissions from the original instance to the destination instance.New SQL server needs to have mixed security mode set and TCPIP access enabled.Local SQL user is required for DBaccess and creation, together with domain search account, that will perform crawling.
Step 1 Move Sharepoint_AdminContent database
Central Administration website is stored in Sharepoint_AdminContent Database, thus it can not be moved from administration interface, however you can see it there under its web
application.
Move all databases to a different database server
Note:
This process requires shutting down the SharePoint Services and will make all sites unavailable to users until the process is complete. Hence, downtime should be planned before starting this process
The following databases need to be moved to complete the process
Databases for Shared Services Providers (SSPs)Search databases for SSPsContent databasesSearch databaseCentral Administration content databaseConfiguration database
Note: The following are the minimum required to perform this procedure:
You must be a member of the Farm Administrators SharePoint group.
On the computer that is running the SharePoint Central Administration Web site, you must be a member of the Administrators groupOn the database server from which the databases are being moved; you must be a member of the Administrators group and should have at least the
db_backupoperator fixed database role.On the database server to which the databases are being moved; you must be a member of the Administrators group and you should have at least the
db_owner fixed database role.
te: If you do not have permissions on Database server, please co-ordinate with SQL Server DBA to move
the databases to the new server.
Move all databases to a different database server
Prepare the new database server by using the
Prepare the database servers procedure.Record which Web applications are associated with the SSP by performing the following steps:
On the SharePoint Central Administration Web site, on the Application Management page, in the
Office SharePoint Server Shared Services section, click
Create or configure this farms shared services.Record the associated Web applications that are listed on the Manage This Farms Shared Services page.
Record the list of Web Applications, SSPs and associated Databases by going to the Perform a Backup page under operations in Central AdminStop the farm by performing the following steps:
On the server that is running the Central Administration Web site, in the Services snap-in, stop the following services:
Microsoft Single Sign-On serviceOffice Document Conversions Launcher serviceOffice Document Conversions Load Balancer serviceOffice SharePoint Server Search serviceWindows SharePoint Services Administration serviceWindows SharePoint Services Search serviceWindows SharePoint Services Timer serviceWindows SharePoint Services Tracing serviceWindows SharePoint Services VSS Writer service
On the server that is running the Central Administration Web site, at the command prompt, type
iisreset /stop.Repeat step 4 on each server in the farm.
Note: If you do have sufficient privileges on the SQL Server, you can Skip steps 5-7 and let your SQL
Server DBA team handle the Database Move. The DBAs can follow backup and restore method as illustrated in Steps 5-7 or DBAs can detach the Databases in the old server and attach the databases back on the new server.
5. Back up the databases on the source database server.
Start SQL Server Management Studio and connect to the database server.In
Object Explorer, expand Databases.Right-click the configuration database (usually named
SharePoint_Config), point to Tasks, and then click
Back Up.In the
Back Up Database dialog box, in the Source area, select the type of backup that you want to perform from the
Backup type list. Click Database.In the
Backup set area, in the Name text box, type a name or use the default.In the
Description text box, type a description of the backup.Specify how long the backup should be kept, or use the default. When the backup set expires, the backup set can be overwritten by any subsequent backups
that have the same name. By default, the setting for the backup set is 0 days so that it never expires.
In the
Destination section, specify a location to store the backup set, or use the default.Click
OK to back up the database.Repeat steps c through j for the remaining databases in the farm.
6. In Windows Explorer, locate the database backup (.bak) files that you want to move, and then copy or move them to the destination server.
Restore databases on the destination database server.
Start SQL Server Management Studio and connect to the database server.In
Object Explorer, expand Databases.Right-click the database that you want to restore, point to
Tasks, point to Restore, and then click
Database.In the
Restore Database dialog box, specify the destination and the source, and then select the backup set or sets that you want to restore.
The default values for destination and source typically suit most recovery scenarios.In the
Select a page pane, click Options.In the
Restore options section, select only Overwrite the existing database. Unless your environment or policies require otherwise, do not select the other options in this section.In the
Recovery state section:
If you have included all the transaction logs that you must restore, select
RESTORE WITH RECOVERY.If you must restore additional transaction logs, select
RESTORE WITH NORECOVERY.The third option,
RESTORE WITH STANDBY, is not used in this scenario.
Click
OK to complete the restore operation.Repeat steps c through h for each database that you are restoring.
7. Use SQL Server to copy to the destination server the logons for all service accounts, including SQL Server logins, fixed server roles, fixed database
roles, and permissions for the databases.
8. Refer the farm to the new database server.
Note: The renameserver operation does not rename named instances of SQL Server.
For Unnamed/Default Instance:
For unnamed instances of SQL Server, follow the steps below
On the disk on which SharePoint Products and Technologies is installed, change to the following directory: %COMMONPROGRAMFILES%\Microsoft Shared\Web
server extensions\12\Bin.Type the following command, and then press ENTER:
stsadm -o renameserver -oldservername
<name of the source database server> -newservername
<name of the destination database server>
2. Repeat steps a and b for each of the servers in the farm.
For Named Instance:
Use the following procedure if you are using a named instance of SQL Server:
Start the SQL Server Native Client Network Utility, click start, Run, type CLICONFG.EXE and Click OK.On the
General tab, verify that TCP/IP is enabled.
On the Alias tab, click Add.
The
Add Network Library Configuration dialog box appears.
In the
Server alias box, enter the name of the current instance of SQL Server i.e. OLDSERVERNAME.In the
Network libraries area, click TCP/IP.In the
Connection parameters area, in the Server name box, enter the new server name and instance to associate with the alias, i.e. NEWSERVERNAME\SERVERINSTANCE Uncheck the dynamically determine port and enter the port number as 2433.
Note: By default named instances uses port 2433, please check with the SQL Team.
************************************************************************
Important Note: Create Alias for the all the server names recorded at the beginning which runs Windows
SharePoint Services Database Services
***********************************************************************
2. Repeat steps on all servers in the farm that connect to SQL Server.
9. Start the new farm by performing the following steps:
In the Services snap-in, start the following services:
Microsoft Single Sign-On serviceOffice Document Conversions Launcher service (optional)Office Document Conversions Load Balancer service (optional)Office SharePoint Server Search serviceWindows SharePoint Services Administration serviceWindows SharePoint Services Search serviceWindows SharePoint Services Timer serviceWindows SharePoint Services Tracing serviceWindows SharePoint Services VSS Writer service (optional)
At the command prompt, type
iisreset /start.Repeat steps on each server in the farm.
10. Test the process has been completed successfully
By Opening Central Admin SiteBy Opening the Individuals SSP Admin SiteBy Opening Individual Web Applications
By Opening Individual Site Collections which is not mandatory but would be a good test.
Deepesh Yevle MCTS
Free Windows Admin Tool Kit Click here and download it now
July 9th, 2012 5:00am