Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Move sharepoint database

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
rochestermn
Asked:
rochestermn
  • 9
  • 8
  • 5
1 Solution
 
Christopher WhiteSenior EngineerCommented:
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
 
rochestermnAuthor Commented:
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
 
Christopher WhiteSenior EngineerCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
rochestermnAuthor Commented:
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
 
Christopher WhiteSenior EngineerCommented:
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
 
Christopher WhiteSenior EngineerCommented:
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
 
Christopher WhiteSenior EngineerCommented:
Bottom line it can't move the SharePoint_Config while in an Online state. Central Administration is held with in it.
 
0
 
Christopher WhiteSenior EngineerCommented:
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
 
Greg BurnsSQL / SharePoint ConsultantCommented:
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
 
Christopher WhiteSenior EngineerCommented:
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
 
rochestermnAuthor Commented:
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
 
Christopher WhiteSenior EngineerCommented:
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
 
rochestermnAuthor Commented:
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
 
Greg BurnsSQL / SharePoint ConsultantCommented:
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
 
rochestermnAuthor Commented:
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
 
rochestermnAuthor Commented:
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
 
Greg BurnsSQL / SharePoint ConsultantCommented:
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
 
rochestermnAuthor Commented:
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
 
Greg BurnsSQL / SharePoint ConsultantCommented:
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
 
rochestermnAuthor Commented:
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
 
Greg BurnsSQL / SharePoint ConsultantCommented:
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
 
rochestermnAuthor Commented:
We have decided to install a new sharepoint web instance and restore the content db.

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 9
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now