October 20, 2004 at 3:51 am
Seeking 'best practice' recommendation for a server hardware upgrade.
Current system: SQL 2000 Standard Edition on W2K; data (MDF files) held on (Compaq) SAN, about 60GB over 20 DBs. O/S, logs, backups on local drives (with further backup to tape). Box is at end of life.
New system: new hardware running Win 2003; continue with SQL 2000 SE and with SAN for the data. Server name needs to remain the same (to avoid changing application configurations).
Company can tolerate just a few hours' outage, and wants to be able to roll back to the existing server if everything turns to mush. No spare space currently available on SAN but new box will have about 140GB local storage available so various scenarios are possible for the rebuild process.
Had the logs also been on the SAN it seems as though it should have been feasible to just point the new h/w to the existing master / tempdb / msdb on the SAN by changing SQL startup command lines.
TIA...
October 20, 2004 at 9:49 am
Where is the system database files?
October 20, 2004 at 2:50 pm
Very good question, made me wish I'd asked that myself. Sorry about delay but I had to wait for the sys admin to get the answer.
Turns out the system database files are on a locally attached disk. The logs for the non-system app databases are on a different locally attached disk - it's only the data files for the non-system DBs that are on the SAN at present. There would be enough room in the SAN partition to accommodate system DBs but not the logs.
October 20, 2004 at 10:58 pm
How about installing the new server with SQL Server installed and just before the upgrade detach all databases (user and system), backup all the local data, detach the old machine from the network, attach the new machine to the network, restore all the local data on the new machine, attach the databases.
This is a very simplified theory, but should work.
October 21, 2004 at 3:28 am
I would go with the approach of C70070540 (Are you related to R2D2?), with a few tweaks.
First build the new server with a different name, connect it to the SAN, and place a few small databases on the SAN to perform some sort of UAT. Work out how/if you are going to transfer the system database data in the old machine, and practice this on the new machine.
When you are satisfied the new server is working correctly and you can do a smooth transfer, clean its files off the SAN and rename the server to match the existing machine.
When you come to swap the machines, shrink the log files of all the user databases prior to detaching the databases and copying the log files - this will reduce the copy time significantly. Remember to reset their size on the new machine.
I would also recommend setting up a DNS alias for your database server, and as time allows move your existing apps to accessing the DB server via its alias instead of its machine name. In this way, when you have to do your next h/w upgrade you will not be concerned about keeping the same machine name.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 21, 2004 at 5:05 am
Sounds promising - thank you both.
Once the transfer is completed would your inclination be to leave the logs on local disk or move them to the SAN? Ditto the system databases?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply