Moving Databases to a new database server


  • I have a SQL alias set up on the original database server and the new database server.  I can connect to the old server from the new server via SQL Server Management studio. The configuration in SQL Server Configuration Manager on both servers is as follows:

    sqlAlias TCP/IP Oldserver

    Client Protocols
    TCP/IP Enabled Named Pipes Enabled

    SQL Server Services

    Service  Running   Automatic   Log On As
    SQL Server Browser  Yes Yes  DOMAIN\SQLservice
    SQL Server  Yes Yes  DOMAIN\SQLservice
    SQL Server Agent  Yes Yes  DOMAIN\SQLservice

    When I change both servers configuration by changing the alias to the new server, I cannot connect to the new server from the old server.  All server security logins match in SQL Server Management Studio.  What am I missing?

    James Grizzle

    samedi 16 juin 2012 15:41


Toutes les réponses

  • Does the Application event log on the SQL Server report any issues with failed logins?  I'm assuming you're not changing the alias name, but just its pointer.

    samedi 16 juin 2012 15:51
  • I did not mention that I have to specify an entry in the HOSTS file that points the SQL Server.  I checked the logs from SQL Server Management Studio (Management | SQL Server Logs | Current) and could not find any entries from the error.  I get the nondescript 'A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible...error 40 -....(Microsoft SQL Server, Error: 53)'  I also checked the windows logs, but there are no entries that could further explain the error.

    James Grizzle

    samedi 16 juin 2012 16:17
  • Can you telnet to the SQL Server port using the alias name from the SharePoint server?

    samedi 16 juin 2012 16:23
  • Neither server has telnet client or server.  From the old server, I can ping the new server's IP address and the alias.  I can only ping the alias if I specify the alias as a hostname in the HOSTS file.

    James Grizzle

    samedi 16 juin 2012 16:38
  • You can use putty or install the telnet client and attempt to telnet to sqlaliasname:1433 (assuming you're using the default port).

    Ok, so the process that should be followed:

    You can create a DNS CNAME if you don't want to edit the host file with the name of the SQL Alias.

    Create SQL Alias on the new SQL Server, using the same alias name.

    Using cliconfig, modify the SQL Alias on the SharePoint Server(s).

    samedi 16 juin 2012 17:37
  • I forgot to set Firewall rules for SQL on the new database server.  How silly.

    James Grizzle

    • Marqué comme réponse CapnGrizzle dimanche 17 juin 2012 00:35
    dimanche 17 juin 2012 00:35