Moving Databases, the Easy Way!

Posted on Posted in Article, General, SharePoint 2007, SharePoint 2010

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.

123 thoughts on “Moving Databases, the Easy Way!

  1. 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!

  2. 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!

  3. 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?

  4. 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.

  5. 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.

  6. 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.

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

  8. 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).

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

  10. 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.

  11. 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!

  12. 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).

  13. 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?

  14. 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.

  15. 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.

  16. 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.

  17. 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.

  18. 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

  19. 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?

  20. 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.

  21. 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?

  22. 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).

  23. 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?

  24. 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.

  25. 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.

  26. 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

  27. 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.

  28. 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

  29. 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

  30. 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!

  31. 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

  32. 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.

  33. 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?

  34. 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).

  35. 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.

  36. 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’?

  37. 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.

  38. 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.

  39. 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

  40. 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 ?

  41. 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.

  42. 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.

  43. 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.

  44. 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

  45. 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

  46. 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.

  47. 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

  48. 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.

  49. 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

  50. 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.

  51. You’d think this would be so simple, but no such luck on my part.

    Our environments were not set up to use aliasing to being with, and we are attempting to switch over. Our test environement default database server was set to sqltestsvr\sqltestmoss and our qual environment to sqltestsvr\sqltestmoss. Both environments used non-standard ports and the instances are on the same sql server, ut40sdf.

    Following the process, I quiesced our farms, the dba detached the databases, then our server guys moved the LUNs from the old sql server to the new. The DBA reattached everything. I added server aliases to the two servers in test to point sqltestsvr\sqlestmoss to ut40sj1\sqltestmoss on port 1433, and on the qual servers to point sqltestsvr\sqlqualmoss on port 4694.

    Now, the oddity: the test environment works fine. I can connect to Central Admin and I can connect to sites. In qual, however, I can only connect to Central Admin; trying to connect to sites results in “Cannot connect to the configuration database”. The SQL connections between servers are fine, so it would seem that it is a SharePoint issue. Any thoughts on this?

      1. That was our initial thought, but we are able to connect to the database with the appropriate account using SQL Server Management Studio and access everything just fine. It’s just SharePoint that’s wonky. We confirmed the SQL connectivity with a Microsoft engineer; now we’re waiting for the SharePoint team again, which is where we started. Sound familiar?

        1. The easiest way to validate that it’s not a permission issue is to make each of your accounts a sys admin in SQL Server. If it works after doing that then you know its a permissions issue. Next step is log onto the sharepont server as the app pool account (or do a run as) and create a udl file (this is how I test connectivity – just create a text file and change the extension to UDL, double-click it and try to connect to the various databases, including the config database). If that doesn’t work then there’s something going on between the server and the sql instance (if it’s not sql r2 then make sure that remote connections are allowed over tcp and validate that your alias is configured for tcp and not named pipes).

          1. Or…one could run the SharePoint Products and Technologies Configuration wizard after noting the wfe didn’t think it was part of a farm. Interesting. I wonder what implications that will have down the road…

  52. Great stuff, my only question is what does the ‘Stsadm -o renameserver’ command do? Is that to rename the MOSS server, with implications on the DB server. I mean I know it runs on the MOSS / WFE server, but is it necessary to do if I am just going to use a SQL Alias, and change the Alternate Access Mappings?

    Here is my *cough* short to do list-

    Verify SQL Backups
    Shutdown Production servers (WFE-VMS02, SQL-VMS02)
    Clone VM’s
    Power on clone servers
    Add HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\DisableLoopbackCheck,1,DWORD
    Configure Alternate Access Mappings to point to localhost
    Stsadm -o renameserver -newservername DEV-WFE-VMS02-oldservername WFE-VMS02
    Disable NIC’s (VMWare guest)
    Configure hosts / lmhosts files to direct production host names to dev
    Sysprep servers
    Change IP Addresses
    Change DEV Hostnames (DEV-WFE-VMS02, DEV-SQL-VMS02)

    stsadm -o updatefarmcredentials -identitytype NetworkService
    iisreset /noforce
    Add SQL Alias (cliconfig.exe) to point SQL-VMS02 to DEV-SQL-VMS02

    Enable NICs
    Install wireshark on both servers
    Verify inter-host connectivity (filter for production IP’s)
    Snapshot production servers
    Power on production servers
    Test Production Servers
    Verify Dev servers are not connecting to production
    Snapshot Dev servers
    Run Pre-Upgrade Checker (http://support.microsoft.com/kb/960577)
    Install Microsoft Office servers Service 2007 Pack 2 (http://support.microsoft.com/kb/953334)
    Install Office SharePoint Server 2007 Cumulative Update Server Hotfix Package (MOSS server-package): February 22, 2011
    Product Activation (http://support.microsoft.com/kb/971620)

    Am I missing anything?

    Thanks!
    -jon

  53. Gary,

    Thanks for your blog..

    I have Sharepoint(MOSS 2007) installed on default instance of SQL server(SQLCluster1)..

    for some reason(different SQL Collation needed for 3rd party process 360 webparts) all webpart related databases moved to another cluster (SQLcluster2\abc instance)..And DBA team also moved Sharepoint db’s as well to same instance..as application team having third webparts on Sharepoint Portal..

    But Currently Sharepoint still pointing to Old SQL instance..So i have to plan & move all Sharepoint db’s should point to new instance since there are lot of performance issues due to MOSS DB’s are exist on one cluster & third party(process 360) webpart db’s exist on another cluster instance..

    in this case SQL Alias will work for us? please advice..

    1) planning to backup all updated MOSS databases(Config, SSP, content db’s) on source server and move to new SQL instance as DB backup/restore.

    2)create SQL alias on MOSS App & wfe servers(will validate SQL connectivity through temp.udl prior to create alias)

    3) Dettach from old SQL & reattach databases with associated webapps with new SQL instance..

    Kindly let me know if anything i am missing from above steps..or do i need to run config wizard & disconnect wfe’s from farm??

    Thanks,
    Nag..

  54. Gary,

    I just want to add few points to my previous comments..

    Do we need to execute below commands as a part of dettach & reattach db’s? or Simply backup old db’s & restore db’s to new SQL instance is enough? As i have MOSS DB’s on default instance of SQL, want to move Sharepoint to new instance on same server(Clustered)..in this case SQL alias will work?

    STSADM deletecontentdb
    STSADM attachdatabase

    Thanks,
    Nag..

  55. Hi!

    This just sound to simple =)

    Do you need to add the alias on the new SQLbox aswell ?
    So if any procedures or anything else in the SQLserver that might point to the old sqlserver will be redirected.

    Is there any considerations with third-party sharepoint solutions?

    Regards
    /Mattias Hansson

  56. I made the move of my databases to new SQL Server with a different name according to microsoft. I created the alias on the old sql server it all works fine but I need to remove the old sql server from production. Should I have created the alias on the Sharepoint WFE server instead of the old SQL server? I have small server farm 1 WFE and 1 SQL server.

    1. Hi!
      Did you finally remove the old sql server? any issue during this process?
      I have a similar environment and need to know if I can uninstall SQL Server without problems.

      Thanks,

  57. Gary,

    We are testing this currently in our test environment and everything is working so far except that I have tried to create a new content database using the alias’ed name (old sql server instance), and I get an “unknown error” in SharePoint. Have you seen this before? Or are we supposed to use the new name for new databases?

  58. Hi Gary,

    Thanks so much for keeping this blog going 🙂
    I am prepping for a SharePoint DB upgrade from SQL 2000 to SQL 2005 and I am realling hoping your method on SQL aliasing gets approved by the powers that be 🙂

    I’ve read all your posts at least twice now – and I am certain this will be the best way to go for me… except the part in one of your posts where it mentions does not work well with SSRS 🙁

    Anyways… So I have still want to go with this solution to move databases and upgrade to 2005 SQL but can you please help me out on the compatibility level after moving the databases to 2005? Do you know if these databases should stay on compatibilty level 8 when reattached to the new 2005 SQL DB server?

    Also just to confirm that after the migration the old 2000 SQL database can be decommisioned and a new server with that name will not intefere with the aliased SharePoint infrastructure?

    Thanks in advance,
    Angela

    1. They don’t have to stay compatible with 2000 – any reason why you’re not considering 2008R2? A new server with the same name shouldn’t be an issue so long as the old one is dead.

  59. Hi Gary,

    Thanks for your post, its really helpful. I know you have answered this in this post. But would like to get clarity on this. I have a WFE with Win 2k3 X86 and SQL on Win 2k3 X86 with SQL 2000, now I would like to move databases to X64 win2k8 with SQL 2005, does your approach still work with alias? your help would be much appreciated.

    Many Thanks
    Pramod

  60. Hi Gary,

    thanks for your post and the time you are spending helping everyone. I have a question with regards to updating service account passwords whilst sql aliasing is in use with cliconfg.

    When running the following command for example will it still work correctly? our admins said it caused a bunch of errors in the event log about not being able to find the database server (it was looking for the original instance)

    Stsadm –o updatefarmcredentials –userlogin domain\AccountName -password NewPassword

    Can farm / service account credentials be updated via stsadm when cliconfg is in use?

    thanks in advance
    Randy

    1. Yes, you can update the credentials when using an alias. It’s possible that the errors were due to cached connection info – you might try restarting your services (timer, iis, etc.) after setting the alias and before changing the password.

  61. Hello Gary,

    Is it possible to copy the MOSS databases from an MS SQL 2005 (source) to an MS SQL 2008 R2 (target) using the Copy Database wizard to carry along the relevant logins, permissions, server roles, etc.

    Thank you for sharing your expertise!

  62. Hello

    I am fairly new in my position and was given the project that we do the following:

    Move our current Sharepoint version 12.04578 to a new 2008 R2 server with the most updated Sharepoint version.
    and
    Move our sharepoint DB (sql 2000) to a sql 2005 server that also contains other Databases (not sharepoint).
    Can you tell me what it the best procedure on how to move our db and upgrade our Sharepoint? What should we do first.
    NOTE: It is preferred that we keep our current sharepoint site running until we are ready to point it to the “new” Sharepoint.

    1. The best approach will be to set up a new SharePoint 2010 environment and then use the database attach upgrade approach. There’s tons of documentation on technet about this approach.

      1. Hello Gary,

        I followed your instructions and have moved ALL my SharePoint databases to a new SQL server without any issues whatsoever.
        Thank you for your help!

  63. I love this post… I have been freaking out about moving our Sharepoint DBs over from SQL 2005 box to a SQL 2008 box.

    So this should work in my scenario?

    I have 1 Sharepoint 2007 VM that is connected to a SQL 2005 Server that needs to be decommissioned. I need to move all the DBs over to a new SQL 2008 box. The Sharepoint 2007 VM will remain the same.

    My question is this…. Do I need to include the Instance name in the Server Alias and Server Name or do I just put the SQL server names?

    For Example:

    Old Server DBs reside on : testsql01ib\testsql01ib — Dont ask me why they named the instance that way.

    New Server DBs Location: testsql03vs2\I2

    So do I use testsql01ib as the Server Alias and testsql03vs2 as the new Server name or do I include the full instance name?

    Thank you

  64. We are running a WSS 3.0 sp2 on 2008 R2 (wss front end). The databases reside on a separate server also running 2008 r2, SQL 2008 r2. We want to move the SQL 2008 r2 including all databases (its only sharepoint) to a new server.
    would the easiest way to accomplish this be utilizing the copy wizard in sql ?

  65. Thank you for this post.
    How can I leave the SQL Reporting Services working in the original (standalone) server??

    Thank you!

    1. I actually had issues with using an alias and reporting services but its been so long since I’ve tried that I don’t know/remember if it’s still an issue. You might want to do a migration to be safe.

  66. Hey Gary,
    First some quick background. WSS 3 running on a front end server. Back end server is SQL 2005. Moving to a new server with SQL 2008 however the SQL 2005 server still hosts databases.
    I’ve got a few questions.
    1. Would an SQL alias affect ONLY the front end server (sort of like modifying a local hosts record) or does it affect systems network wide (like putting a static A record in DNS) or possibly both? The old SQL 2005 server still hosts many databases and I don’t want every connection on the network to start looking at the new server.
    2. Why “move” the databases? Wouldn’t a DB backup from the old server and then a restore on the new server bring all the appropriate accounts and be easier?

    Thanks in advance!

    1. If you put the alias on your SharePoint server then it will only affect communication from that server to the DB (it’s not a network wide thing – only a server specific thing). You can do a move or a backup/restore – doesn’t really matter – I just like doing a move because it’s faster and if you’re dealing with terabytes of data it can make a big difference.

      1. Ahh, ok. I only had a few gig so it didn’t take too long to do the backup/copy/restore. I tried the process last night. I couldn’t get it to work. I created a test.udl file and with the alias in place I could see that it was pointing to the correct database but Sharepoint was gone. I guess something within sharepoint was using the SA password and I could see in the logs that login for SA was failing. I know that the 2 servers don’t have the same SA password, so it kind of made since. I couldn’t figure out how to fix it though. I eventually gave up on it, removed the alais, and STILL couldn’t get SharePoint to come up. Eventually I had to rerun the configuration wizard before I could finally get SharePoint back up and running. Looking back at IIS this AM, I realize that the “SharePoint Central Administration” and “SharePoint Default Web Site” were missing from IIS last night. Not sure what caused them to disappear. So I’m sort of back to the drawing board as to how to do this move.

        1. I ended up setting the SA password on the 2008 server to the same that it is on the 2005 password. After that I was able to make the move.

  67. @DarylMusashi and @GaryLapointe

    I’ve used the DNS Static A Name approach for a few years. Seems to be a more network friendly approach compared to SQL Alias’s that require a change on each consumer.

  68. Hi Gary,

    consider my scenario , i have SQL 2008 32 bit on one server and i want to migrate to another server having SQL 2008 64 bit. i have enable TCP\IP in cliconfg.exe and i have added an entry as

    Old server : cchqsap02tmp.smiths.net\CCHQSAP02TMP
    New server : CCHQSAP008\CCHQSAP008

    but still i am getting error cannot connect to config DB.

    I have tried to create a ULD file and tested the connection it is working fine.. I have added port number i.e. cchqsap02tmp.smiths.net\CCHQSAP02TMP,1433 and CCHQSAP008\CCHQSAP008,1433 but still no luck…..
    I have run the SharePoint configuration wizard as described in one of the article.

    kindly advise me what else I can check for?

  69. Hi Garry,
    I have a Project Server 2003 integrated with Sharepoint 2003.
    Can I use the database alias to move the databases from SQL 2005 to a SQL 2008?

    Thank you,
    Grigore

  70. Hello Gary hope you are still replying to this. I have to move my SharePoint DBs from SQL 2000 (32 bit) to SQL 2008 (64 bit). I m running SharePoint 2007 (no SPs installed). For example I’m going from SQL01 to SQL02 can I just back upi the DBs on SQL01 and Restore them on SQL02 then create the alias on my SharePoint server and call it a day. I have been reading various of articles that say to do a million steps. I came across this article and was like wow this looks good. Also I am not a SharePoint Admin and do not have to much experience with the product. Please if you have a chance can you give me some guidance?

    Thanks

  71. Can content databases of SharePoint 2007 SP2 Windows server 2003 SP2 SQL server 2005 SP4 to SharePoint 2010 SP2 Windows server 2012 SQL serve 2012?

    I have to start migration for client from source production to target Dev first. Please reply at your earliest.

  72. Great post.
    Just used this method on our old SPS 2007 2 server farm, moving 135GB of database to a new SQL 2008 R2 server.
    Worked perfectly! Thank you
    Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *

*