Planning for SharePoint 2010 – Upgrade Planning Part 2 Moving the Databases
Please read Part 1 of this post series before you continue.
In this post I will highlight how to move your (32 bit) SQL Server tier to 64 bit.
In the case of moving your data tier there are few prerequisites that you should consider. In this case we have the assumption that your new 64bit SQL server deployment has followed best practice guidelines for SQL server. From a SharePoint point of view you should consider the following to enable effective management of your existing content databases or any new databases you are creating. SQL Server 2008 or 2005 (64bit) are both supported when moving to SP2010. Details about preparing your database server can be found here (TechNet).
This may be a good time to also plan what your SQL maintenance regime would look like for your new data tier. Start with Database maintenance guidance for SharePoint Server on TechNet. Most important though is that you should read up about Kimberly L. Tripp’s extended blog post series on these tasks in detail. If you are not an SQL server expert or have a DBA who usually looks after your SharePoint SQL databases make sure that they read the series of posts as well.
Remember we are now moving our data tier. At the end of the process you will have your 32 bit farm (APP and WFEs) pointing to a new 64bit data tier.
When moving from your 32bit source SQL server to a new 64bit SQL server there are two paths you have to consider. In my example the topology of the final destination 64bit farm is as follows. SPWEB1 , SPWEB2, SPAPP1 and SQLSERVER08 (all 64 bit). At the end of the database only tier you will have a configuration that will reflect MOSSWEB1, MOSSWEB2, MOSSAPP1 (32bit) and SQLSERVER08 (64bit). The options that you have is to completely build a new farm provided that you have the hardware available or just build as you go along. In the case of you doing the big bang approach you can choose to build an entirely new farm and only move the associated web application content databases or you can move all of your databases.
One of the recommendations when deploying your SharePoint farm is to use a combination of DNS record or a SQL Server alias for SharePoint servers to connect to, rather than the actual name of the SQL Server. This gives you the flexibility to move SharePoint databases to another SQL Server instance in the event of general maintenance.
Example: You can move from SQL\sharepointsql to SQLSERVER08\sharepointsql
By using an alias name to connect to (ex: sharepointsql.yourcompany.com), you can save a lot of effort of doing manual steps and a full re-index post deployment. In the case of this scenario both options are considered. To set this straight what you should do is setup this alias and use the stsadm renameserver command to rename the name of the SQL instance prior to carrying out your database move.
Let’s first look at what databases we need to backup. You can find out about all the databases used in your SharePoint deployment by doing a site audit as described in my previous post. At a high level these are.
You can get a full view of your backup tree using ***stsadm –o backup –showtree ***you can use this to note down the databases which are are part of your deployment.
Note: We are not backing up the databases using STSADM or the SharePoint GUI but rather using SQL server management tools on the source 32bit SQL server to backup the databases. Please note that if you are using Single Sign On (SSO) in your deployment you should read this TechNet article first.
Once you have a list of your databases you can choose to backup and restore the databases or alternatively detach all the databases from the source 32bit SQL Server and copy and move the MDF and LDF files and re-attach them to the 64bit destination SQL server and restart the farm.
Before you do that you need to document and identify and test what the overall steps are for your chosen scenario. (Please make sure you read and understand and plan everything before you do anything that involves your PRD SharePoint deployments)
In the case of moving to a SQL server with the same instance name you can follow these steps. (We are only moving the data tier in this scenario)
stsadm -o backup -directory
](https://www.chandima.net/Blog/Lists/Posts/Attachments/226/BackUPSSPFull_2_10D32DF3.png)
Microsoft Single Sign-On service Office Document Conversions Launcher service Office Document Conversions Load Balancer service Office SharePoint Server Search service Windows SharePoint Services Administration service Windows SharePoint Services Search service Windows SharePoint Services Timer service Windows SharePoint Services Tracing service Windows SharePoint Services VSS Writer service
By the end of this process your deployment should look like this!
](https://www.chandima.net/Blog/Lists/Posts/Attachments/226/image_2_7BE1AB7F.png)
In the next post we’ll look at the next two tiers Tier B and Tier C.