Renaming server hosting SQL Server 2K | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Renaming server hosting SQL Server 2K

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.
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
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

@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…
I think one KB article refers to solve that problem which is similar to your workaround. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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.
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.
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?
Have you updated statistics after your re-indexing work?
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

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.
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

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.
To deal with null statistic seehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1123&SearchTerms=null,statistics
Ahhhh …. I’ve seen reference to that problem on the SQLTeam forums. A user eventually got a script from MS to remove the _wa objects. Finally found the post : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24777 see if that helps you! CiaO
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?

]]>