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).
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.
29 comments:
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!
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!
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?
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.
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.
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.
thanks :)
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.
Just curious, why the preference of TCP/IP over named pipes?
TCP/IP performs better than named pipes: http://msdn.microsoft.com/en-us/library/aa178138(SQL.80).aspx
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).
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.
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.
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!
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).
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?
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.
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.
Order doesn't really matter but I'd probably do the databases first (no reason - just a quick win).
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.
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.
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
Shola, yes you can use aliases for that.
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?
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.
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?
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).
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?
Yup - it will work just fine for your scenario.
Post a Comment