Post reply

Minor issues after renaming server (and defualt instance)

  • September 12, 2007 at 2:59 pm

    #118565

    Last night we renamed a SQL server and it seemed to go pretty well, but I still have a couple issues to sort out.  They almost seem to be related to service account permissions, but I really don't know.

    1) Maint Plan jobs error out and I can't delete them.

    I could see that the Connections for the maintenace plans were still using the old name for the server, so I decided to delete and recreate them.  The new plans/jobs run fine, but I can't delete the old ones.  I've seen recommendations to update the originating_server_id field in the sysjobs table, but when I looked, both new and old jobs have the same server_id value, which matches the current (i.e. new) server name found in sys.servers.

    The error message when I try to delete is: "An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)"

    2) Can no longer connect to Reporting Services via SSMS.

    I can still get to the Report Server through the browser, and the reports are working, but when I try to connect via SSMS I get this error: "Unable to connect to the remote server (Microsoft.SqlServer.Management.UI.RSClient)   ... A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond (System)"

    Here's the platform info and procedures:

    SQL Server 2005, Standard edition, 64-bit (9.0.3054)

    Windows Server 2003, 64-bit

    • Renamed the server and rebooted.
    • Renamed the default SQL instance using sp_dropserver and sp_addserver; restarted SQL services.
    • Ran the Reporting Services Configuration tool and updated the Database Setup.

    The goal was to migrate an existing SQL server to new hardware.  The new server had been set up and tested and was working fine, and when we switched over all I did was refresh the data, take down old server, and rename the new server.  Basically, all our client applications seem to be connecting and working just fine, but I'm having issues w/ these admin functions.  I'm still researching and trying things, but any suggestions or pointers will be greatly appreciated.

    Thanks!

  • September 12, 2007 at 4:05 pm

    #733295

    Never mind on the Reporting Services connection via SSMS.  I had to modify the UrlRoot setting in the RSReportServer.config file, as per this article: http://technet.microsoft.com/en-us/library/ms345235.aspx (entitled "Renaming a Report Server Computer", oddly enough).

    Still can't delete old maintenance plans though, so you still have an opportunity to help.

     

     

  • September 13, 2007 at 5:54 pm

    #733641

    Finally figured out how to delete the old maintenance plans as well.  I used BI Dev Studio to export .dtsx package files from SQL Server to a local drive.  Then I edited the package files and changed references to the old server name (which takes forever in BI studio because it tries to validate the steps -- it's much faster to edit them in Notepad and they would actually run when I re-imported them).  Then I connected to SSIS via SSMS and imported the package files back into SQL Server, at which point I was able to delete them.

    It's funny how sometimes you get stuck on a problem, but almost as soon as I posted my question I started finding info and getting ideas that actually worked.  Go figure...

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply