Solved

Move sharepoint database

Posted on 2011-09-12
22
518 Views
Last Modified: 2012-05-12
I have a sharepoint database on a sql server express database that I need to move to
a sql server 2008 cluster.  

Is it best to backup and restore all the baks or to attach and detach all the databases.

Seems sharepoint creates quite a few databases.  This is just a basic sharepoint wss installation on an sql express db.  There is nothing special at all about our sharepoint installation.  WSS not MOSS.  I just want to know how to move it without having too many problems.
0
Comment
Question by:rochestermn
  • 9
  • 8
  • 5
22 Comments
 
LVL 5

Expert Comment

by:Chris-Vielife
ID: 36522953
Hi

Are you moving Config DB as-well as the Content DB's? What I would do.

If you are moving everything, then I suggest you backup to bak in SQL. Create all the DB's again in the new Cluster. Create a new web/app server (front end) install sharepoint again join to farm using the NEW DB instance you have set for it.

There are other ways such as below which would do as you require but I find this more one more of a jumping in instead of taking a step in. (Few ways back if you need to roll back for what ever reason)

http://blogs.msdn.com/b/ronalg/archive/2007/09/20/change-the-sql-server-in-a-moss-2007-wss-3-0-farm.aspx

Hope that helps
0
 

Author Comment

by:rochestermn
ID: 36523012
Forgot to mention this is sharepoint 2010.  What do I lose if I do not move the Config DB.  Because Im so new at sharepoint I do not know the difference.  Im attaching a screen of my db instance in sql express.  If you look at the screenshot the names of some of these dbs have odd names like guids in them.  Im guessing this would be a bit of a nightmare if restoring bak files.  Because my guess would be those guids names are created when sharepoint is installed and our unique to the installation.  


Capture.JPG
0
 
LVL 5

Expert Comment

by:Chris-Vielife
ID: 36523166
WSS_Content hold your web application data presumably it will be for the default app which it creates on port 80, all files/lists/pitures/etc etc

SharePoint_Config26d3......... holds information on your setup email server/web application and database association etc etc.


You can and I recommend this

Create a complete new install and just recreate the default instance, on your new SQL Cluster. Then Delete the WSS_Content or overwrite it with a backup of WSS_Content. It will move everything and is a very effective way.
 
  To Key bit to this:
    1: Make sure you use the same Farm system account.
    2: Make sure that account has DBcreate rights on the SQL instance.

 
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:rochestermn
ID: 36523255
Sounds good Chris.  Im just wondering where in Central Admin I point to the new sql instance.

How do I change the database server pointer?  I see a link for Manage Content Database Settings but not sure where to change the location of the wss_content db.
0
 
LVL 5

Expert Comment

by:Chris-Vielife
ID: 36523265
You can recreate your SharePoint setup just with just the Content Databases.

1: If you have the Farm Admin Account details
2: There is no customization to the farm (Look and feel would have to be applied again
3: No added web-parts MS project pro or Other.(Would have to be added again)
4: If you do not use My sites (Not a big issue but requires extra steps)

It is rather simple in this respect.

See you can skip most of the setup setting like email server search crawl scopes etc if you take the SharePoint_Config database. But I don't find this a bother and find it something preferable just to move the content db's. You only have one so it should be a simple task.  
   
0
 
LVL 5

Expert Comment

by:Chris-Vielife
ID: 36523340
How do I change the database server pointer?  I see a link for Manage Content Database Settings but not sure where to change the location of the wss_content db.

Run the SharePoint 2010 Products Configuration wizard. Disconnect from this serverfarm (Dont worry you do not lose anything.)

Run again as you would in a new install, use the new SQL instance. Create your default web application as before.

Delete/Restore the WSS_Content DB

Or you can detach in Central Admin then re-attach once restored in SQL Studio Management. SharePoint is quite robust with this.  
0
 
LVL 5

Expert Comment

by:Chris-Vielife
ID: 36523358
Bottom line it can't move the SharePoint_Config while in an Online state. Central Administration is held with in it.
 
0
 
LVL 5

Expert Comment

by:Chris-Vielife
ID: 36523613
Hi again on the topic of what you lose when not taking the Config database. You lose some basic setup settings and if you used the default when you setup this environment it really is not a lot. Such as the email server name. Search Scopes and Crawl Jobs if you setup any.

Here is the MS guide http://technet.microsoft.com/en-us/library/cc512725.aspx#Prepare

-------This can be a problem but I have worked around this (Let me know if you have issues with this)---------

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 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 9

Expert Comment

by:Greg Burns
ID: 36523641
There is a Microsoft-presribed way to do this.  Use a SQL Alias and then backup/restore all databases to the new instance.

SharePoint 2010:
http://technet.microsoft.com/en-us/library/cc512725.aspx

If you create an alias for your old SQL instance name and point it to the new instance name, all ODBC connections coming out of the SharePoint server will be automatically redirected to the new instance. It's like a HOSTS file for ODBC.  You don't even need to have a DNS record.

There are some exceptions and complications to this (e.g. the User Profile Service) but you can work around these.  More about that here, with methods for moving/renaming each service database:
http://technet.microsoft.com/en-us/library/ff851878.aspx

But moving the farm config database is no problem.

I just did this for my company's farm and it's still working fine.  My original install used a local SQL instance on the same machine as SharePoint, and I used a SQL alias to repoint SharePoint over to a new instance.

I would back up and restore copies of the databases to the new instance so you can test it out before cutting over to the new instance.  
0
 
LVL 5

Expert Comment

by:Chris-Vielife
ID: 36523650
I do recommend using a new front end and new install of SharePoint to make the jump. I am not sure how much you use your current SharePoint but to minimize downtime installing a new front end is the least disruptive and safest.
1
 

Author Comment

by:rochestermn
ID: 36523791
Ok.  So I could backup the content blow away the installation.  Re-install sharepoint on a new sql instance and restore the wss_content db.  Thats on par with your recommendation right?
0
 
LVL 5

Accepted Solution

by:
Chris-Vielife earned 500 total points
ID: 36523907
What I recommend is:

- A new box with a new installation of sharepoint
- Using the same Farm Administrator Account
- add that Farm account to have rights in the new sql instance/server

On SQL
- Restore wss_content DB  (recommend Deleting the Database then recreating it blank and restoring into it)  

Why I recommend this is because you will have little or no down time. Since you will be working completely separate to the live setup.

What you stated above would be my second option as if anything goes wrong it will take a fare bit of time to get back up. Backing Up the Content Database is the most important thing.

That point I made about different versions of SQL is where I think you will have an issue if any.

0
 

Author Comment

by:rochestermn
ID: 36523977
Well right now were not even live.  Were a small shop im the only one using the sharepoint site.  We plan to keep the sharepoint installation on the same server.  So not sure why new box.  Same box but new installation perhaps is best we can do.  But we surely will have our database on a seperate sql cluster instance.  

Thank you Chris.  I really appreciate your quick responses and very thorough too.  I will let you know how it goes.
0
 
LVL 9

Expert Comment

by:Greg Burns
ID: 36523984
Hm, I disagree with Chris.  But I guess it depends on how much work you want to do, Rochestermn.

 The method I recommend will involve a server reboot, but that's about it.  The Microsoft article at http://technet.microsoft.com/en-us/library/cc512725.aspx is advising that you detach / attach databases to the new instance, but you don't have to: you could just back up and restore them.  

My advice:
1. Back up all databases and restore them to their new instance (in my experience the GUIDs don't really cause a problem here).
2. Schedule an outage.  Give yourself an hour. Advise users that any changes they make during the outage may be lost.
3. Create the SQL alias.
4. Restart services or just bounce the SharePoint server.  The services should reconnect normally.  
5. If things do not behave as expected, remove the SQL Alias and restart services again; they should connect to the old instance, and all you've lost is an hour (and the caveat from Step 2).

This is what I did on my own farm, and it worked.
0
 

Author Comment

by:rochestermn
ID: 36524767
Ohhh.  I figured the guids would cause all kinds of issues.  Hmm.  You may have the solution after all.  I was going to do a new sharepoint install and restore my old  wss_content bak to the new sharepoint install.  Sort of keeps the configs and content intact.  However your way keeps everything intact.  I will consider it for certain.  Thanks.
0
 

Author Comment

by:rochestermn
ID: 36524836
poortatey:  For backing up and restoring would you use sql mgmt console or central admin?  I tried backup in central admin and it seems to think the backup is still running and its several days later.  Not sure I trust it.  I was able to backup in sql mgmt console easily.  Also where is the pointer in central admin to the new database...ie the connection string?
When I click on configuration wizards, I see a blank page.


0
 
LVL 9

Expert Comment

by:Greg Burns
ID: 36524895
I do the SQL stuff in SQL Server Management Studio.  The Farm backup you're running will also back these databases up, along with configuration metadata and each website's content.  This works, too, but it will name all the databases something confusing like spbr00001.bak, spbr00002.bak, etc.  What I did was to take my most recently nightly backups (via SSMS) and restore them to the new instance (make sure all the service accounts and logins are brought over, too).  

By the way, if you don't have a daily SQL Maintenance Plan for backing up your databases, you really need to make one.  This is one DR component that SharePoint doesn't do very well because it does not automate its backups.  And this is one place were a third party tool will come in really handy (check out Idera's offering at http://www.idera.com/Products/SharePoint/SharePoint-backup/)

By the way if you have a long-running or stuck Farm Backup, that is a seperate issue.  Normally you'd see that complete in a matter of minutes (or hours if you have a lot of data).  You'll probably want to kill that job (reset the Timer service).  
0
 

Author Comment

by:rochestermn
ID: 36524925
Gotcha.  Thanks for the tips.  I will setup a maintenance plan for all the dbs in the sharepoint app.

Im still wondering though where do I set the sql server name in sharepoint ie the db connection string?

It will have to change to the new instance name.  I cant find this anywhere.
0
 
LVL 9

Expert Comment

by:Greg Burns
ID: 36526387
There is no single connection string used by SharePoint (and different services use their own connection configurations anyway).  What I would do is use the built-in SQL Client Configuration tool. It's very old, but it always seems to work.  Go to a run prompt and type  "Cliconfg".  Click on the ALIASES tab.  Pretty simple.  Once you restart services they should connect to the new instance using the old instance name.
0
 

Author Comment

by:rochestermn
ID: 36529508
Im not following you at all on that last post.  I ran that clsconfig but im not sure what that utility is for.

Im just trying to point sharepoint to a new database server instance.
0
 
LVL 9

Expert Comment

by:Greg Burns
ID: 36530738
On your SharePoint server, do the following:

1. At the command prompt, type CLICONFG (yes, it has a weird spelling) and press enter.  The SQL Server Client Network Utility will open.  http://www.dbanation.com/?p=215
2. Click the "Alias" tab.  This is where you define aliases for connections to SQL Server that originate from this machine.  This will configure every outbound connection to SQL Server.
3. Click the Add button.  You will see a dialog where you can specify the alias and its target, as well as what protocol to use to connect.
4.  Clck the radio button for TCP/IP.
5. In the Alias field, type the name of your old SQL instance.  It might be something like MyOldServerName\SharePoint.  If you don't know the instance name, you can go to central admin --> System Settings --> Manage Servers In Farm and see what the "Configuration Database Server" is.  
6. In the Server Name field, type the entire instancename of the new instance.  (e.g. MyNewServer\MyNewInstance).
7. If the new instance is running on the default port, you can leave the "Dynamically determine Port" box checked.  
8. Click OK.  Click OK.  

You now have an alias to your new instance, and all services on your SharePoint server will THINK they are connecting to the old instance name, but they will be automatically redirected to the new instance name.  This is all you need to do; you don't need to reconfigure any connections in Central Admin.

What I was saying in my previous post is that, unlike your average web application (which has a config file where a single connection string is configured), SharePoint is a massive beast designed for scalability.  Each service application is its own entity, and each of the different web services have its own configuration.  You could certainly go and reconfigure each service point and even rename databases if that's what you want.  There is more about that here from Microsoft: http://technet.microsoft.com/en-us/library/ff851878.aspx

I was just trying to save your some time and stress by offering a simple way to move everything at once.  The SQL Alias is designed for just that purpose and it is, in fact, considered a best practice to use aliases for all SharePoint deployments.  
0
 

Author Comment

by:rochestermn
ID: 36717381
We have decided to install a new sharepoint web instance and restore the content db.

0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

773 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