SQL Server Performance

Server Name Changed & Can't disable Jobs

Discussion in 'SQL Server Log Shipping' started by ceje, Sep 22, 2005.

  1. ceje New Member

    TIA,
    Production SQL server: SQL1 (running on windows server named WIN1)
    Standby SQL server: SQL2 (running on windows server named WIN2)
    SQL2 is designated as a linked server in EM on SQL1.

    I setup the backup and log shipping on SQL1 and now want to test the failover process to SQL2. I'm doing it via EM so I can get a feel for what's going on before I automate the process.

    Since SQL2, is only used for DR, I restored the master, msdb and model DB's from the SQL1. Then I restored the application DB's and and transaction files from SQL1. All went fine. The application DB's will continue to be updated by via log shipping are designated as "Loading" in EM.

    *** MY QUESTIONS:

    When I attempted to disable a SQL Server Agent job on SQL2 it displays: error "14274: Can not edit, delete any jobs or schedules that originated from a MSX server.

    Any Ideas Why? and how to correct?

    Also, the standby SQL Server is now named SQL1.
    Will this be a problem when production server SQL1 issues the restore command to be run on Standby server SQL2 (which is now SQL1 since the master DB from Production was restored to Standby)?
  2. derrickleggett New Member

    The MSX server error happens when you try to modify jobs and the originating_server column in sysjobs is different than the actual name of the SQL Server instance. You have to update the column in that table for the issue to go away.

    You shouldn't have restored the system database to the standby server. You are going to have all kinds of issues if you are using SQL Server users because they are now all going to be out of synch and need to be fixed.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. ceje New Member

    Thanks,
    The reason I restored the Master DB was in this document:
    http://www.sql-server-performance.com/sql_server_log_shipping.asp

    It says:
    Synchronize SQL Server Login IDs
    Backup the Master database on the production server and restore it onto the standby server just before you begin the log shipping process. And should you ever have to failover your server, you will then have to restore the Master database again in order to keep your user accounts in synch.

    I guess I went to far when I restored the msdb and model DB's from the production to standby server. I will retry by only restoring the master DB from the production server to the standby server. Would this be your recommendation?

    Does restoring the Master DB rename the SQL Server?
  4. satya Moderator

  5. ceje New Member

    Thanks for the responses, and sorry for the newbie questions. It appears the sql server name changes when I restore the master db from production to standby. Here is what I've done:

    All these steps are executed on the Standby server
    net stop mssqlserver
    Replace \standbymssql with a backup copy to what it was right after installing SQL Server
    start sql server
    osql -Usa -Pxxx
    select @@servername ==> Standby
    exit

    net stop mssqlserver
    net start mssqlserver -c -m

    from EM restore master using master.bak created on Production SQL server
    start sql server
    osql -Usa -Pxxx
    select @@servername ==> Production
    exit

    It appears the SQL Server name has changed. Has it?
    Will this cause problems/issues:
    Restoring DB's or Logs?
    Executing procedures on the Standby linked server when I automate log shipping?
    Failing Over to the Standby server?

    Can you give me some recommendations?
  6. satya Moderator

    Use SP_DROPSERVER and use SP_ADDSERVER to take new servername, if the servername doesn't matches then the jobs will fail. Refer to books online for more information on specified SPs.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page