Solved

Move sharepoint database

Posted on 2011-09-12
22
501 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:rochestermn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 8

Expert Comment

by:Greg Burns
Comment Utility
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
Comment Utility
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.
0
 

Author Comment

by:rochestermn
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 5

Accepted Solution

by:
Chris-Vielife earned 500 total points
Comment Utility
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
Comment Utility
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 8

Expert Comment

by:Greg Burns
Comment Utility
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
Comment Utility
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
Comment Utility
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 8

Expert Comment

by:Greg Burns
Comment Utility
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
Comment Utility
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 8

Expert Comment

by:Greg Burns
Comment Utility
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
Comment Utility
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 8

Expert Comment

by:Greg Burns
Comment Utility
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
Comment Utility
We have decided to install a new sharepoint web instance and restore the content db.

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now