SQL Server Performance

Renaming server hosting SQL Server 2K

Discussion in 'General DBA Questions' started by stefanoale, Aug 6, 2003.

  1. stefanoale New Member

    I'm planning an upgrade by replacing the server at the same time,
    Here is my plan:
    Take old Box sql7 off-line
    Copy Dbs/alerts/operators/jobs to new Box Sql2K
    Take old Box off the network
    RENAME NEW BOX WITH OLD BOX NAME(ONLY UNTESTED PART)
    Test apps
    if everything is well bring up the old box off the network rename it and then get it on the network
    if everything craps out take new box off the network and bring the old one back up

    I'm particularly worried about the renaming of the new box with SQL2K.
    Does anyone have any experience with that or any other comments?

    Thanks in advance.
  2. rushmada New Member

    U can do like this.

    First take backup of dbs in sql7 server.
    copy the backup of sql7 dbs into the proposed 2k server
    0r
    physically move the mdf and ldf files from sql7 server to proposed 2k server.
    if u want users also to be moved from sql7 to 2k server, script only logins.
    remove the sql7 server from the network.
    rename the proposed sql 2k server with name as sql7 server.
    Start installation of sql 2k with sp3.
    after that u restore user databases or use sp_attach_db for attaching databases.
    run the logins script in sql 2k.
    run sp_change_users_login 'update_one','username','username' in all the databases
    for all users.
    create jobs and schedules in msdb newly than copying of jobs,alerts etc.
    test all apps if every thing works fine connect it in the network other wise as u
    said connect the sql7 server in the network.


    thanks

    Rushendra
  3. satya Moderator

    Also you can take help of COPY DATABASE WIZARD to move the databases and logins etc. to the new server and then stop the services for the old server and on the new server you can use the script from DBJournal http://www.databasejournal.com/scripts/article.php/1496451 if you are not comfortable with SP_DROPSERVER & SP_ADDSERVER (Books online has necessary information.)

    Just to make sure, restart the SQL services after you affect the name change.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  4. SQL_Guess New Member

    @stefanole :

    Very similar to the process we followed.

    One warning I can give you. When we renamed the SQL server, the jobs that had been re-created go themselves very confused, and decided that they were part of a "multi-server administration" setup, and that the NEW server name was the "Target" and that the SQL on the "OLD servername" was the "master". The upshot - it refused to allow us to modify the jobs, although they ran fine. Eventually, we managed to get them editable again ... can't remember how, I'm afraid.

    The recommendation ? Look to see if you can't script the jobs, and only re-create them on the final server (i.e. after the rename ). Then again, maybe we just had a one off problem...
  5. satya Moderator

    I think one KB article refers to solve that problem which is similar to your workaround.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. ykchakri New Member

    quote:Originally posted by SQL_Guess

    @stefanole :

    Very similar to the process we followed.

    One warning I can give you. When we renamed the SQL server, the jobs that had been re-created go themselves very confused, and decided that they were part of a "multi-server administration" setup, and that the NEW server name was the "Target" and that the SQL on the "OLD servername" was the "master". The upshot - it refused to allow us to modify the jobs, although they ran fine. Eventually, we managed to get them editable again ... can't remember how, I'm afraid.

    The recommendation ? Look to see if you can't script the jobs, and only re-create them on the final server (i.e. after the rename ). Then again, maybe we just had a one off problem...

    Yeah, I remember getting into this problem, after renaming our server. And the way we solved this is by hacking into msdb..sysjobs and modifying the 'originating_server' to the new server name.
  7. stefanoale New Member

    Thank you guys all for all suggestions.
    I'm doing the upgrade in 24 hrs so I'll post here how it went.

    Thanks again.
  8. stefanoale New Member

    The server switch went pretty smoothly until this morning when everyone was back to work.
    Here is the corrent issue:
    There is a view that joins and unions tables from several Dbs that is taking forever to run.
    The old server (about 60% as fast as the new one) used to take 2 seconds now the new server takes 1+ mins!
    a possible cause seems to be the indexes.
    I rebuilt them this morning but nothing changed.
    It seems that if a table does not contain any data matching the criteria SQL ingnores the indexes and does a manual scan.
    I tried to index the view (new for SQL 2K) but I can't because the tables have different owners.

    Any suggestions?
  9. Cyn New Member

    Have you updated statistics after your re-indexing work?
  10. gaurav_bindlish New Member

    Is there a hardware change across servers? 1 CPU to Multi-CPU?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  11. stefanoale New Member

    The stats were recomputed automatically when we rebuilt the indexes since I did NOT check the box "Do not recompute stats(not recommended)"

    I update them again just in case but the view is just as slow.

    Both Servers are dual Xeons.
  12. gaurav_bindlish New Member

    Try turning of the parallelism for the query and checking if performance remains the same to eliminate the issue of bad performance due to parallelism.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  13. stefanoale New Member

    I turned the parallelism off but again no improvement.
    Here 2 more facts that may help:
    1) I have a Dual Processor Dell PowerEdge 6600 that uses hyper threading so that win2K task mgr performance tab shows 4 processors.

    2) I looked at the sysindexes table and I noticed that the tables that have the indexes have a lot of _WA_sys entries that show a [rows] value of 0. While my indexes do have the correct value. I checked this against my old server and on the old box all entries have the correct [rows] value. So maybe my stats are not up to date even though I ran the store procedure to update them.
  14. Luis Martin Moderator

  15. SQL_Guess New Member

  16. stefanoale New Member

    All my _wa% indexes are not null just rows = 0.
    I ran the scripts described in thehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24777 thread with no luck.

    Does the SQL 2K engine work differently than SQL 7 when it comes to indexes?
    Anyone has any other suggestions?

Share This Page