SQL 2005 to SQL 2008 move - server name constraints RRS feed

  • Question

  • I have read through a number of threads here on handling upgrading from SQL 2005 to SQL 2008 on a MOSS 2007 farm, but haven't found an answer to my specific issue.

    I'm doing the upgrade in our development environment first. We have two servers - one WFE that runs Central Admin and a single application (Internet web site) and another machine that runs the indexing. They are both VMs, running Windows Server 2003, and are 32-bit. The current SQL server is 2005 32-bit and is physical. That server is being replaced by a VM running 64-bit Windows 2008 server and SQL 2008 R2.  The SQL servers are shared with a number of other applications and are not dedicated to SharePoint. We are not using any backup or restore software that can move the daatabses for us - we just have the tools in SharePoint and SQL. Finally, I have read over the TechNet article "Move all databases (Office SharePoint Server 2007)" http://technet.microsoft.com/en-us/library/cc512725.aspx, which is where my questions come from.

    There are a few constraints in moving databases, if I've understood the article correctly. I can move the DBs to a server with a different name as long as the two servers run the same OS and SQL server. I can move the DBs to a server with a different OS and/or a different SQL server, but the dataabse server names from the old to the new must be identical.

    In my situation, the new server will not have the same name as the old server, so I have a different SQL server name, different OS and different SQL server. I have already spoken with the DBA and there is no possibility of identical naming. The old SQL server will not be upgraded to SQL 2008, just retired when the current DBs are moved to the new server.

    I seem to be in a bit of a Catch-22 here. What are my options? Have I missed something in the instructions that points me to a work-around?

    Thanks in advance for any suggestions, advice, links, etc., anyone can provide!


    Friday, November 12, 2010 9:18 PM


  • Hi,

    The easy way Local / Connection SQL Alias and ensure the User Accounts have the same Server Role and User Mappings as in the existing SQL Server. By using an alias, we can keep the application configuration the same, but tell the operating system to look somewhere else for the database. You specify the server where the database really is, including configuring the protocol, the server name, and the port

    There are two tools that you can use to create a local SQL Alias SQL Server Configuration Manager or SQL Server Client Network Utility (cliconfg.exe)

    1.Open SQL Configuration Manager if the Tools are installed on your SharePoint Servers
    2.Expand SQL Native Client. Right Click Alias then Click New
    3.In the Alias Name field enter the Old SQL ServerName, in the Server Field enter the new SQL ServerName, Protocol TCP, Port 1433

    Repeat for the protocols you are using in addition to TCP and will need to be conpleted on All SharePoint Servers. However, you can also create a SQL Alias with DNS where you create the Alias on the new SQL Server, and modify the DNS record for the Old Server but this would be only if you were retiring the old SQL Server

    Reference:  http://blogs.msdn.com/b/sql_protocols/archive/2007/01/07/connection-alias.aspx 




    Note: I did not include the obvious backup All SharePoint Dbs, Restore them to the new SQL Server and ensure you have created Logins on the New SQL server with the same UserNames with ServerRoles and User Mappings... or stop the services first.....


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    • Proposed as answer by Mina Fouad Sunday, November 14, 2010 1:57 PM
    • Marked as answer by Peng Lei Monday, November 22, 2010 9:42 AM
    Saturday, November 13, 2010 12:22 AM