March 18, 2008 at 7:00 am
We have a SQL2000 SP4 install that we want to move to a new server.
The new server will ultimately be called the same as the current server, but for now has a different name just to live on the network/domain etc.
The only significant difference between old and new will be the disk structure, on the old server we had C, E (logs), F (Databases) and on the new server we will have C (logs), E (databases).
I know it's recommended to have logs on separate spindles but with our backup policy and as this is a VMware server sat on a SAN LUN we consider performance/recoverability acceptable for our needs.
I've looked at various guides/howto's and it all looks relatively straightforward if it weren't for the (proposed) change in drive letters.
I should add that within the next 6 months we'll most likely be migrating to SQL Server 2005 so I'd prefer to get the install "right" than to try and kludge it just to get a smoother migration.
Really appreciate any info.
March 18, 2008 at 3:52 pm
detach the databases on the old server, copy the data and log files to new location, then attach on new server.
Another way I like to do it is just pull backups of all the databases, then restore them on the new server using the with move clause in the restore to put the data and logs where they need to be.
March 18, 2008 at 4:14 pm
I would not try and move over the master database just script out logins, server roles, default database and language and run in on the other side (no problems with orphaned userids if you use sp_help_revlogin). If all windows account can use DTS transfer login task.
MSDB you can restore over if exact same SQL version, thus maintaining DTS packages,alerts, jobs. Just run query
update msdb..sysjobs set originating_server = 'new server name in here'
after restore (whilst agent down.)
app databases as per last post.
---------------------------------------------------------------------
March 21, 2008 at 9:24 am
Paul, Are you a dba or infra person or both?
We have multiple SQL servers on VMware and have moved them on multiple occasions.
If the machine will have the same name as the old one then there are a few questions that need to be answered.
1. Are you wanting to rebuild the server OS for some reason? If yes, then previous posts will put you on the right track. If no, see next Q.
2. Is the server moving from one VM to another? If yes, possible v-motion to new VM box. If no, you might be able to use V2P (virtual to physical VM utillity)
There are almost as many ways to move the data as there are days in a week, here is what we had to do a few weeks ago.
Requrements:
Move SQL server to new SAN with as little downtime as possible and with as little work as possible.
Keep the same server name.
Split DBs to additional drives.
Have fallback if any of the Action plan fails.
Action plan:
Attached new SAN drives to old server. (Infra Team)
Detached DBs and copied DB files to new SAN drives. (DBA)
Move server OS to new hardware (ghost or acronis) (Infra Team)
Configure OS for new hardware (Infra Team)
Attach new SAN drives to new server.(infra Team)
Attach DBs to instance in there new home. (DBA)
Now if you are the infra part it will be a little more involved, but this was our situation. All in all it took about 5 hrs. No porting of jobs, DTS packages, users, or usp_'s
_______________________________________________________________________
Work smarter not harder.
March 21, 2008 at 10:04 am
Action plan:
Attached new SAN drives to old server. (Infra Team)
Detached DBs and copied DB files to new SAN drives. (DBA)
Move server OS to new hardware (ghost or acronis) (Infra Team)
Configure OS for new hardware (Infra Team)
Attach new SAN drives to new server.(infra Team)
Attach DBs to instance in there new home. (DBA)
Matt,
so you detached system dbs as well? and these ended up in the same directory structure on the new server they started in on the old server?
when you copied the OS this included all sql server binaries?
---------------------------------------------------------------------
March 21, 2008 at 10:28 am
Ah, so your system dbs are located on the D: drive?
I haven't had to do that in a while but this should get you going.
The system dbs are moved like so:
Before shutting down the SQL services, move the MASTER database in Enterprise Manager. (or you can modify the registry with SQL stopped)
1. Open SQL Enterprise Manager, and go to the SQL Server you are moving the master database on.
2. Right-click on the server and click properties
3. Click the startup button and change the following values:
Old location -> New location
-dD:\mssql\data\master.mdf -> -dF:\mssql\data\master.mdf
-lD:\mssql\data\master.ldf -> -lF:\mssql\data\master.ldf
4. Stop SQL Server.
5. Move the D:\mssql\data\master.mdf and the D:\mssql\data\master.ldf files to F:\mssql\data directory.
6. Move the D:\mssql\data\model.mdf and the D:\mssql\data\modellog.ldf files to F:\mssql\data directory
7. Move the D:\mssql\data\msdbdata.mdf and the D:\mssql\data\msdblog.ldf files to F:\mssql\data directory
8. Move the D:\mssql\data\tempdb.mdf and the D:\mssql\data\templog.ldf files to F:\mssql\data directory
9. Go to a dos prompt and change directories to D:\mssql\binn (or wherever your binaries are)
10. Run the following command: sqlservr -c -f -T3608 -Q
11. When prompted that SQL Server is accepting client connections, press Control-C, then enter Y to stop SQL server
12. Go to Computer Management -> Services and start the MSSQLSERVER service. Also start the SQLSERVERAGENT service.
Basically it allows you to move the master db to be able to start the instance. Then you can go from there. You can also test this by moving the master db from c:\program...\ to a different sub directory if you have a SQL sandbox.
_______________________________________________________________________
Work smarter not harder.
March 22, 2008 at 9:05 pm
As long as the pathing is the same, you can move master over to the new server without issues (same pathing for all databases). Be sure you rename the new server (Windows side) before bringing up the SQL Server.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply