MOSS MVP

I've moved my blog to http://blog.falchionconsulting.com!. Please update your links. This blog is no longer in use--you can find all posts and comments at my new blog; I will no longer be posting to this site and comments have been disabled.

Thursday, June 19, 2008

Moving Databases, the Easy Way!

I've seen this come up a lot so I figured I'd write a short post about it.  A lot of people have been asking how to move their databases to a new server and to do this the way most people are prescribing you've got a lot of reading and a lot of steps to perform.  The following two post provide just a couple of examples of the numerous steps and issues to consider: http://techacid.spaces.live.com/blog/cns!6D62FC28E76BE4B!230.entry and http://blogs.technet.com/corybu/archive/2007/06/01/detaching-databases-in-moss-2007-environments.aspx.

But that's all just way too much work - who wants to deal with all those steps and what if something goes wrong and, oh, I have to rebuild my configuration database because you can't move it using the typical approaches.  Shouldn't there be an easier way?  Well, there is - use SQL Aliasing.  SQL Aliasing basically just involves installing the SQL Server Configuration Manager on each server and configuring an Alias to point to your SQL Server and the good news is that Microsoft now recognizes this as a supported configuration for SharePoint.

So say you have an existing farm with all the databases on a server called MOSSSQL1 and you want to move all the databases to MOSSSQL2.  To do this you can either install the SQL Server Configuration Manager on each server or if you have MDAC installed (which you should) then simply open up c:\windows\system32\cliconfg.exe (recommended over installing the SQL Configuration Manager).  Go to the Alias tab and click "Add".  In the dialog that appears you can then configure your alias (Alias Name=MOSSSQL1, Server=MOSSSQL2 if using Named Pipes or the IP address if using TCP/IP).  Then click "OK" to save the alias (be sure to do this during off hours as once you click OK SharePoint will now start looking to your new server for the databases).

Using cliconfg.exe

Using SQL Server Configuration Manager

Once you have the alias configured you can now simply detach all the databases from your old server and then copy and re-attach the databases to the new server.  If you're using Kerberos make sure you remember to create your SPNs for the new server.  Keep in mind though that using this approach will mean that all of your databases will need to be moved (even non-SharePoint databases if you are connecting to those databases from any of your SharePoint servers).  You can get around this if you use aliases prior to building your farm (keep reading).  One thing to watch out for is your permissions - make sure you take note of what permissions each database has as some may change with the re-attach.

That's it - your done!  Wasn't that easier than all the other crap you'd have to deal with otherwise?  But wait, there's more!  If you are in a position where you haven't yet built your farm then why not preemptively configure aliases?  So perhaps you have just one SQL Server today but you anticipate the need to eventually distribute the databases across servers in the future to address future performance needs or maybe you just want to isolate the SharePoint databases from other databases in a shared server situation?  Simply create the aliases that you anticipate needing and point them all to the same server for now - then when you need to move a database group you can just change the alias and move the databases in that group.  If you want ultimate flexibility (perhaps you just don't have a clue as to how you will need to distribute your databases in the future) then create an alias for each database (not sure I'd take it this far myself but it would give you the most flexibility in allowing you to very easily move any one or more databases to any other server with ease).

I've been using this approach for years with both 2003 and 2007 and it's always worked great for me and hopefully others will benefit as well.

71 comments:

graydo64 said...

Sound advice this! When I set up our farm last February I used aliases because the SQL instance I was using at the time was on a non-standard port and experience tells me that not all applications are happy about that unless you create an alias. Not knowing much about SharePoint at the time I figured setting up aliases would be a safe bet. How glad am I that I did that now that we've had to move our farm to a dedicated server?! I followed your instructions - concise and clear as always - and had the lot moved in no time at all. Just a couple of points: firstly I've never needed to install the SQL Configuration Manager on the FE servers - I just use cliconfg.exe that ships, I think, with MDAC and should be in System32. Secondly I did have a bit of a headache with permissions after re-attaching the databases. On the original SQL Server the database owner was set during SharePoint installation and configuration. When I re-attached the databases I myself was set as dbo meaning that my server farm account didn't have access. I had to go through the MS article on 'Deploy using DBA-created databases' to find out who should own what. I should have documented permissions more thoroughly before the move. You live and learn!

Keep up the good work!

Gary Lapointe said...

Thanks for the feedback - I didn't realize that about cliconfig.exe being there without the tools being installed (shows I'm not a SQL guy :)). I probably should have mentioned about the permissions - forgot about that one (it's been a while since I've had to do a move). I'll update the post to mention using cliconfig.exe instead. Thanks again!

ScottH said...

Will this approach work if you're moving your MOSS 2007 databases from a SQL2000 server to a SQL2005 server?

If not, are there any sites/articles/resources you recommend that will help with this?

Gary Lapointe said...

I've not tried it but my gut tells me that it would - I've moved other databases (for custom apps) over to 2005 using a simple detach/attach and they worked fine so my guess is that it would work with SP as well - can't say for sure though. I guess what I'd do would be to do an in place upgrade of SQL and then move the DBs.

graydo64 said...

Moving from a 2k server to a 2k5 server was exactly what I was doing and Gary's right - simply detach and re-attch. Our farm's been moved for getting on a week and we haven't had any issues. The only thing I would say is that the databases are still marked as compatability level 8 on the new server. This means you can't run all of the funky reports that you get with SQL 2005 Management Studio. My guess is that the databases will be fine if you just change their compatability level, either through the GUI or using sp_dbcmptlevel but I wouldn't like to guarantee it and I won't be doing that until I've tested it on our development server!

Another note on aliasing - you should use TCP/IP connections in preference to named pipes, particularly if your web servers are not on the same box as your SQL Server. Configuring these using cliconfig is the same as for named pipes - just select the TCP/IP network library, then enter the server name. If you're running SQL on the default port and have the browser service running then you can check 'Dynamically determine port'. Otherwise uncheck it and enter the port number that your instance is running on. Note that if you're running a named instance then you don't enter the server\instance name in the server name field - just the server name or FQDN, then enter the instance's port number.

Gary Lapointe said...

Thanks for the info and I totally agree with the advice on using TCP/IP - I only showed using named pipes because it was clearer to show what servers should be used where.

Saurabh said...

thanks :)

yu said...

The server I currently have my SharePoint databases on also has a load of other databases for other applications on it also. If I use a SQL alias, will those other apps be looking at my new SharePoint server for their databases? Also, I assume that if I add and alias, the old SharePoint SQL box will have to remain on with the same ip/name to do the forwarding.

I may be getting the wrong end of the stick with this, but the whole aliasing thing is very appealing and I really want to use it.

Eric Riehl said...

Just curious, why the preference of TCP/IP over named pipes?

Gary Lapointe said...

TCP/IP performs better than named pipes: http://msdn.microsoft.com/en-us/library/aa178138(SQL.80).aspx

Gary Lapointe said...

If you have apps other than sharepoint on your sharepoint server pointing to the same database server and you set up a sql alias pointing to a new database server then those apps will be affected by the change. If you use an alias for sharepoint and point to a new db server then you will be moving all the databases (or some depending on the number of aliases you use) so you will not need the old db server up anymore (unless you use that db server for other systems).

Maddog said...

I've worked a bit with SQL before, but basically, I manage a Sharepoint 2.0 production server and my plan was to move it to a TEST machine before I run an upgrade to Sharepoint 3.0 or whatever the new version is now. I have planned this for months, but it hasn't been a priority. I wanted to see how an upgrade worked to Sharepoint 3.0.

When you say, you should detach/reattach the database - how exactly is that done? I don't have full SQL installed on this machine - just the IIS and basic SQL service manager that came with Server 2003.

Gary Lapointe said...

From your SQL machine you should be able to use the sql server management studio to do it. Otherwise you can use osql to do it via a sql statement. You can also just use do a backup and restore - either works.

Marlin7 said...

We'd like to move our team SP site to a new 64bit/W2K8/SQL2008 machine. Using sql aliases sound great, but this method makes the task sound almost too easy. So just to confirm, are you saying that 'preparetomove' isn't needed before detaching the db's? And when they're reattached, i'm sure there must be some configuration to do in Central Admin right? I'm also curious...will a new web app or site collection need to be created, or will they show up in Central Admin and the url's be seemlessly online again immediately? If you can anser these 3 questions, i'll be getting excited that we can make this move!
Thanks for the tips Gary!

Gary Lapointe said...

If all you want to do is move the databases to a new server then you can simply detach the databases and then re-attach to the new server and then set your alias to point to the new server. The only additional thing you'll need to do is to set the permissions in the database (so make note of what account has what rights for each DB (or script it) and then just ensure that the re-attached databases has those permissions). That's it - there's nothing to do in SharePoint (CA, web apps, etc.) - just set that alias on each of your SharePoint servers (note that SSRS has issues using aliases so if you're using it you may need to reconfigure those settings).

Marlin7 said...

Wow, thanks for noticing my comment, and so quickly replying. I might get this done today! But, ok, so maybe the rub is in 'if all you want to do is move the databases'. In fact i want to move the entire site off the 32-bit server. That is, i want to migrate the entire WFE to a new 64bit server, and wipe the 32-bit server clean. On the new box I've installed SP, configured services and an ssp. So is there more to do than move the databases to the new box, so i can hit the same url as now and have pages served from the new server?

Gary Lapointe said...

To move the WFE to a new server I usually just add the new server into the existing farm, transfer all roles to that server (where applicable) and then remove the existing server from the farm. There are other approaches (such as building out a new farm and migrating the content databases over) but this involves a lot more configuration level stuff to do.

Anonymous said...

Mmmok. So i need to research some more, and maybe do an uninstall and then reinstall and add the new server to the current farm, if there's no clash b/n 32 and 64bit OS, IIS. Is it recommended to move the WFE or the databases first, or does it even matter?

Thanks for your support today Gary.

I might try again next weekend to get this done.

Gary Lapointe said...

Order doesn't really matter but I'd probably do the databases first (no reason - just a quick win).

Marlin7 said...

Gary, are there any complications to aliasing a sql embedded/MSEE database and then moving it? Clicking on WSS_Content shows me the db server for the webapp is OPEDASHBOARD\Microsoft##SSEE. As expected, i don't see it in SQL Mgr.

Gary Lapointe said...

I don't really have any experience with the embedded/express/etc. editions (just standard and enterprise) but I can't imagine there would be an issue. It would be easy enough to test - create an alias and then what I do is create a udl file (name it anything, temp.udl), double click it, and then set the appropriate parameters - if you can do a successful test connection then you're good to go.

shola said...

Hi Gary would the same approach be useful in this scenario. Migration of Sharepoint 2007 and its databases from win server 2003/sql server 2005 to win server 2008/sql server 2008.

Thanks

Gary Lapointe said...

Shola, yes you can use aliases for that.

Marlin7 said...

Gary, i'm at it again and in a bind. Actually the server is in a bind, and so i'm here another weekend. What i read to remove and copy and attach the ssee databases to sql 2005 seemed simple enough. I wanted to move to sql2005 away from SSEE before adding another WFE, aliasing and moving the db's to SQL2008. But after removing all the content db's using central administration, including the admincontent db, central admin could not be found in order to 'remove wss from virtual server'. In a browser the page is rendered as 404;not found. So i continued on and attached all 5 db's in SQL server manager. That went fine. But now i'm supposed to 'connect to the restored configuration db' using central administration, but as noted it's not showing in the browser. Anything strike you as obvious about what's the cause and what to do about it?

Gary Lapointe said...

If you're using aliases then you don't/didn't have to remove the content DBs via central admin. The reason for the 404 is because it's not seeing the content db for central admin.

Marlin7 said...

Well, i did remove them, and now they're reattached to the sql server. I reinstalled moss2007sp1 and in CA have all the services required on the farm running. I'm blocked trying to setup reporting integration, as there's a red X for the web service identity that goes away only if i use the default settings for the rs virtual directory. But i'm not seeing the reporting url render ok. Do you do consulting work by phone?

Gary Lapointe said...

I do but at present all the logistics need to be worked out via an account rep from Statera. I can put you in touch with someone if you're interested - just send me an email with what you are looking for and I'll forward to the appropriate person at Statera (glapointe at statera dot com).

Greg Robinson said...

Great article. One question for you. What about the situation where we have both SharePoint and SQL Server on the same machine (SHAREPOINT1) and we want to move the databases to another machine called SQLSERVER1. Will your approach work in this case? It seems your articles was intended for the case where the farm already has a separate database server and the databases are to be moved from one server to another. Is that correct?

Gary Lapointe said...

Yup - it will work just fine for your scenario.

Greg Robinson said...

Can you please explain a little more about that? If the current configurations point to SHAREPOINT (which current has SharePoint and SQL Server) and the new machine is SQLSERVER1, how would this work? Would an alias be created on the new machine to point SHAREPOINT to SQLSERVER1? The original machine still has the name of SHAREPOINT too. What am I missing? I appreciate the clarification.

Greg Robinson said...

I want to follow up on my comment yesterday. I described my situation and you said it will work. Could you please confirm my thinking or explain what I am missing? I think I was confused thinking that the alias would be on the new machine, SQLSERVER1. However, going over this in my head and rereading your article, it sounds like the alias would actually be added the SHAREPOINT1 machine, and thus pointing the SHAREPOINT1 name to SQLSERVER1. Is that correct? I appreciate you taking the time to respond as I am trying to figure out the best course of action to take to do this migration for my client next week.

Gary Lapointe said...

Sorry for my delay - yes,the alias goes on any consumer machines.

Anonymous said...

First off I want to thank you for your help. The way you do the moves made my life a ton easier.

My question is. Once you move the databases and have everything working is there a way to change the name on the central admin of the server that it says is it's primary? I am not sure that using the change server name in stsadmn will hurt the farm?

Thanks

Gary Lapointe said...

Theoretically, if you run the renameserver command on every server and you're not using named instances then you should be okay - but in the reading I've done I've seen people have issues with this so I've generally avoided it as all you're doing is fixing a visual in SCA.

shola said...

Hi Gary,

My current set up is this, 1 Sharepoint server and 1 sql database(named mossdb), now I want to point SharePoint to a new database called mossdbnew, would I create the alias on mossdb or mossdbnew?

Thanks for the great you have doing

Gary Lapointe said...

Neither - you'd create the alias on the sharepoint server.

rezwan said...

Hi Gary - What about MOSS2007?can i move all the databases (SSP,search,content,config..etc) to a new sqlserver using aliasing method and without doing nothing to sharepoint end?What do i need to look out for except the permissions?
Thanks a lot
-Marshall

Gary Lapointe said...

This will work for all versions of SharePoint (past, present, and future). Permissions is the only thing you need to reset.

Ted Bicknell said...

Regarding named instances, I would like to throw in an experience where I had to name the named instance in the alias name for TCP/IP to work correctly.

i.e. "old" sqlserver: "servername\BI"

"new" sqlserver: "servername1"


When creating aliases, if "servername\BI" was NOT specified as the alias name, connections to the new server would not work. Adding the instanace name solved the problem.

just my 2 cents!

ee61re said...

So using this method, the backup and restore of the SSP (using either central admin or stsadm) is not required?

Its as simple as configuring the alias, and moving all the databases?


Rob

Gary Lapointe said...

That is correct. Just also make sure your permissions get set correctly on the new db server as they often get messed up when you attach.

Orlando said...

The easiest way to transfer databases between two servers is using "Copy database wizard" from "SQL Server Management Studio".
It let's you transfer databases as well as logins, permissions and other objects from one server to another.
This way you avoid setting permissions manually on your database after dettaching/reattaching your database.

Rick said...

Gary, the aliases sound great, though this will not work in the following scenario, where I need to move databases from two servers to two new servers, but cross-switching the databases, like this (not all databases have been included as the example below will work)

Central Admin on SQL01 --> SQL03
SharePoint_Config on SQL01 --> SQL03
SSP1_Content_DB on SQL01 --> SQL03
Content_AppPool1_DB on SQL01 --> SQL03
Content_AppPool2_DB on SQL01 --> SQL04
Content_AppPool3_DB on SQL02 --> SQL03
Content_AppPool4_DB on SQL02 --> SQL04

If I alias the server, this will not work, because I need to make the same alias twice. I have read the MS Technet article on moving all the databases, but as the alias method is preferred, they do not give any other methods.

Could you point me in the right direction?

Gary Lapointe said...

The hard part is moving the config and SSP/Search databases so given what you have you should be okay with an alias for those. For the content DBs you can still use an alias but you'll need to remove the content db from the web app, move the db, and then add the db back to the web app (use an alias to add it back in so you can more easily move it again in the future).

Winter said...

As far as named instances go, can I specify a named instance in the 'server name' window ? That is, can I have a common name in the 'Server Alias' window, say MOSSDB1 and have it point to PRODSQL4000\MOSS4001

ie. I want to alias:

MOSSDB1 -> PRODSQL4000\MOSS4001

then during a DR event have:

MOSSDB1 -> DRSQL4000\MOSS4001

This is for SharePoint 2007 by the way. Any help appreciated.

Gary Lapointe said...

Yes, you can point to named instances just as you would a default instance.

Dino said...

Is there a way to script the cliconfg commands?

Gary Lapointe said...

Yes, you'd basically script the creation of the registry key HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

David said...

Great info in this blog.

I have a customer that has, in time immemorial, installed his sharepoint server as a single-server install (with the standard SQL Express) (SERVER1) and not as a single-server farm. As time went on, the DB became a larger and larger bottleneck, and he wants a dedicated SQL server 2008 enterprise (SERVER2) while SERVER1 remains the web/application server.

My question is, if I can use this approach to, basically, split the single-server stand-alone installation into a 2 server 'farm'?

Gary Lapointe said...

You can create an alias to move all the databases but if you want to move just some then you'll have to create alias and detach/attach your content databaes to point to the different target.

Carl said...

I appreciate this is slightly off topic as I'm not migrating DBs, but this is by far the best resource on the web which I've found on this topic.

I have an issue which might be resolved by aliasing ...

The issue is surrounding MS DPM attempting to backup a sharepoint instance. When we attempt to do this the error we recieve is that Sharepoint cannot see the backend SQL DBs.

Our Setup:

-2 Sharepoint FE servers load balanced in failover cluster
internal.com domain

-2 SQL 2005 servers load balanced in failover cluster
internal.ltd.com domain

-DPM 2007 server
internal.com domain

The reason I mention the domains is because I think this is the reason we need to use aliases to get DPM to recognise the DB server which Sharepoint uses.
Out sharepoint farm references the SQL cluster as the cluster name "SQLcluster" and not the FQDN. I believe that when DPM contacts Sharepoint and asks where it's databases are, SP turns around and says "SQLcluster" and DPM assumes that because Sharepoint is on a different domain the sqlcluster must also be there and therefore spits out an error saying it doesnt exist ... but it does, its just that its on sqlcluster.internal.ltd.com and not sqlcluster.internal.com.

In a nutshell, is it possible to create a alias for our sqlcluster to include the FQDN without stoping SP from functioning? and if we do this and Sharepoint stops working, will deleting the alias resolve the issue without further work?

I appreciate any comments regarding this and if you would like me to clarify further please let me know and I'd be glad to explain.

steve decroubele said...

Dear Gary

We are having a questions on the aliases.
On our current production farm, can we use the stsadm -rename function to start using an alias instead of a server reference ?

Thanks
Best regards
Steve

Gary Lapointe said...

I believe you can but it's been a while since I looked at this so can't remember exactly.

Steve said...

Thanks !
regards
Steve

ArunSingh said...

Hi Gary,

Our whole standalone moss 07 setup is in one VMWare. Now we wanted to shift all moss databases into another VMWare.

In my scenario,can we implement alias, so that moss will refer these databases which are now on another machine ?

Gary Lapointe said...

Yes. I've done this many times.

Anonymous said...

Hi Gary,

In one blog, you said that we can not move sharepoint config database.
But i need to move all moss databases (SSP, content, config, admin, WSS serach)to another server.
Than how can we separate moss DBs and moss webfront server.

Gary Lapointe said...

By using a SQL alias you can move all databases including the config database. If you don't use an alias then you cannot move this database.

Anonymous said...

Hi Gary,
I backed up all moss DBs from old server and moved to new server with permission, after that i followed below steps...

1> After setting SQL alias on moss server (where sharepoint 2007 server and related sql server exist)

Server Alias-->OLD SERVER
Server Name-->NEW SERVER (SQL server where, moss DBs migrated)

under TCP/IP.

2> detached all moss DBs(including config) from SQL server on OLD SERVER.

3> Restarted all sharepoint services.

But now when i try to open site it display message: Cannot connect to the configuration database.


I dont know what went wrong here, plz advice me.

Gary Lapointe said...

First confirm that you can connect to the new sql server (I usually will create a udl file (temp.udl), double click it and set and test the properties). If you can't connect then most likely it's either a firewall issue or the sql server isn't configured to allow remote connections.

Anonymous said...

Hi Garry,

Issue get resolved, it was due to wrong sql alias name.

Thanks for your help.

Greg said...

How can I move all the content, SSP and config databases to a new SQL server without using the alias?

We have too many databases on the current SQL 2005 to point them all to a new server.

We just want to move all MOSS 2007 databases to the new SQL server.

Thank you,
Greg

Gary Lapointe said...

The alias only goes on your MOSS servers so unless you are connecting to those other databases from your moss servers then there should be no issue with using an alias.

MarkArnold said...

Gary - I moved the MOSS databases using aliases with no problem, save one: when I try to backup with Data Protection Mgr now it looks for the old non-aliased path and fails.

Original instance: LSCMS1\Apps
New instance: SQL3

When opening the sharepoint datasource in DPM it says essentially "can't find LSCMS1\Apps, make sure database in good state".

Care to hazard a guess at that one? Doesn't really add up, but might an alias also be necessary on the DPM server?

Thanks!
Mark

Gary Lapointe said...

Mark - yes, you'll need to add an alias anywhere where you were connecting to the old SQL Server and have it point to the new SQL Server. DPM isn't my thing though so it's possible you may need to make other changes as well.

Anonymous said...

We are planning to move moss databases from SQL Server 2008 32 bit to SQL server 2008 R2 64 bit. Would this method work?

Gary Lapointe said...

Yes - this approach would work for you.

JD said...

Gary, Thanks for your suggestion. The alias worked well. I configured mine for the IP address of my new db server with port 1433.

Getting the user logins right is important.

I really appreciate the work you did in this area!!
jd

Bhavi said...

i have followed the procedure. initially i wasnt able to connect administration section or the sharepoint site. But after playing around with security in new database i am now able to see the site, however Central Administration Section is still not working can someone help me with that.

Tom said...

Gary, this thread is great! Thanks for keeping up with the replies. My question is that my current db server configuration is "ds1prodmoss\moss,1437". Can this configuration be converted to an alias name? Will the alias name be "ds1prodmoss\moss,1437"? I'm afraid that the '\' character or the addition of the port number will nullify this solution.

Thanks again!

- Tom

Gary Lapointe said...

You wouldn't set the port number as the alias name - you should be able to set the server alias field as "ds1prodmoss\moss" and then set the port number field to 1437 - easy enough to test though - just create your alias on a non-sharepoint server and create a new file and name it something.udl and see if you can connect using that alias.