Best methods to migrate / replicate database

Last Post 28 Jul 2004 07:58 AM by xfonhe. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
paulveld
New Member
New Member

--
26 Jul 2004 04:54 PM
Folks,

I am hoping that someone can provide me with some suggestions as to the best way to approach my problem. First off, let me acknowledge that I am not a SQL guru- I think the term 'newbie' would be closer.

I have a SQL7.0 database on a Windows 2000 server. I need to migrate the data over to a SQL2000 database running on a Windows 2003 server. The database is constantly updated ( used for audit / reporting purposes by our messaging environment ), so I need to ensure minimal downtime. I have been looking at doing a backup / restore to get the bulk of the database over to the new server, but am unsure as to the best method of getting the latest changes over to the new server. A colleague has mentioned using publisher / subscriber (?) to get the updates across, but I have not been able to find any clear doco to explain this. Would this be the best way to achieve the desired result, or is there a better way?

Once the data is moved to the new server, the DSNs on the messaging servers will be changed to point to the new server, so this is not a long term solution I am looking for, I just need to get all the data migrated will the minimum downtime.

Thank you for any assistance / suggestions,

Paul.
xfonhe
New Member
New Member

--
27 Jul 2004 06:20 AM
Detatch the DBs from SQL7.0, copy the .MDF, .NDF and .LDF files to the SQL2K server, then Attach them in SQL2K. It will prompt you to upgrade the databases.... allow it to complete.

BE SURE YOU HAVE GOOD BACKUPS OF BOTH SERVERS FIRST! :-)
paulveld
New Member
New Member

--
27 Jul 2004 01:35 PM
Sorry folks,

I had seen a lot of the posts asking 'How big is the DB' and I resolved to make sure I put that info into my original post.........and then I didn't do it!! DOH!!

The db is around 7GB.

If I do the detach/attach method, copying the files over a 100MB network will mean an outage of about 30-45 minutes ( assuming all goes optimally ). I was hoping to have a smaller outage window than that. Are there any other methods that can help with that?

Thanks for your help,

Paul.
Ali baba_SQL
New Member
New Member

--
28 Jul 2004 04:53 AM
I agree with xfonhe, I'd detach and reatach, and then apply the logs.

Off cource you can push the snapshot overnight, and then have transactional replication applied immidiately, if you're comfortable with this.
xfonhe
New Member
New Member

--
28 Jul 2004 07:58 AM
Paul, the attach/detach process should result in no more than a few minutes downtime on your 7.0 box. The detach process allows you to make a copy of the database files; you can then reattach the failes to the server and copy the COPIES of the files over to the 2K box.

7GB file copy in the same filesystem should take no more than 5 minutes ;-)

Good luck!

X
kbailey
New Member
New Member

--
29 Jul 2004 12:50 PM
What about setting up transactional replication from 7.0 database to 2k server? BOL topic "replication, version compatibility" indicates SQL 7 can be publisher/distributor and SQL 2k can be subscriber. Do your snapshot and then transactional replication continues until you're ready to do the move. When you're ready to go live, put SQL7 in single user mode and stop the replication. Turn on SQL 2k box. It's really a warm standby / failover type solution.

Alternately, make sure database is in Full recovery mode, do a full backup and have scheduled log backups, RESTORE WITH MOVE on the SQL 2k box, practice applying the log backups over there, then when ready for move you can have target database restored up to last log backup, put SQL7 in single user mode, do a last log backup manually and restore on SQL 2k, and you're off and running.

With the first method it may take some research and testing on your part, but the switch can be almost instantaneous. Everything is already on the SQL 2k box, within network latency. With the second method, the backup and restore on the last log may take slightly longer, but if you've got it scripted and tested, and your scheduled log backups are at frequent intervals, the last backup and restore should be quick. You can map a drive on the SQL7 box and run the log backups straight to the SQL 2k box so they're already there.

Both strategies can move data online; you don't have to wait for the COPY to run as with the detach/reattach method.

I would set it up and test it thoroughly; I haven't done this and the devil is in the details.
kinsden
New Member
New Member

--
30 Jul 2004 12:15 AM
Paul,

You can use the DTS tool to copy the database and all objects from your SQL7 server to SQL2000 server OR you can generate SQL Query on the database on SQL7 and execute it on SQL2000 server. Once the database is copied, you can change the DSNs to point to the new DB.
You are not authorized to post a reply.

Acceptable Use Policy