Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
smccreadie
Aged Yak Warrior
505 Posts |
 Posted - 2001-10-04 : 06:36:27
|
| We will be upgrading soon and I have the option to install SQL2000 on a fresh server and migrate databases to it rather than trying to update an existing box.The plan is to install Win2K, then SQL2K and use sp_attach_db to attach databases that were detached from the SQL 7 box. (The mdf and ldf files would be moved to the new box).Are there any issues with this approach that I need to be aware of? What about jobs and DTS packages? We currently don't use replication. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-19 : 19:55:00
|
| Hey Scott, how about an update on this? We are starting to talk about moving from SQL 7 / NT4 to SQL 2K / Win2K Avanced Server. We will take a similar approach to that which you described in that we will setup a new clean machine and then look to move databases. Did you run into any issues doing Detach/Attach? Would it be any safer to run this through DTS? I think I read something about a CopyDatabase utility (other than DTS)? We want as clean of a conversion as we can get, with as little effort as possible (but then, doesn't everybody?).Thanks for any insights you or any others who have done this can shed. We are definitely NOT going to run an UPGRADE on the existing box.-------------------It's a SQL thing... |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-20 : 08:31:04
|
Mark, I am in the final stages of testing for the exact same conversion here. Detaching databases, copying all files to new location and attaching them back seems to work quite well. It is also pretty safe -- if we experience any problems in the process, I will postpone the conversion to a later day and keep databases running on the old servers. So far I've had no trouble with _wa objects... Most stored procedures work fine after the database transfer. The only problem I did see was that SQL2k does not allow using lock and index hints when querying linked servers, so I had to take the hints out to get those queries running again. Also, be sure that you know exactly how all of your apps connect to the database. Try to put together a document that lists all the config files/DSNs etc that need to be changed in order to get client apps talking to the new servers. HTH Edited by - izaltsman on 12/20/2001 08:38:09 |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2001-12-20 : 11:53:49
|
| A couple of other points. If you are not migrating the Master DB, you will need to recreate the logins for all of your users, then use sp_change_users_login to reconnect them with the database users.Also, be sure to rebuild all of your indexes, and update your statistics once the migration is over.-Chad |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-20 : 15:25:16
|
| Thanks for that info Ilya and Chad. I'll lookup sp_change_users_login in BOL. I remember seeing something about that in another post too. As a follow-up, I was reading on Microsoft's site at [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_upgrade_6nqr.asp?frame=true[/url] about a "Copy Database Wizard". It sounds like this might get around the Logins issue. Any experience or thoughts?-------------------It's a SQL thing... |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-20 : 16:27:05
|
I am a bit apprehensive when it comes to letting wizards do things to my databases, which is why I decided not to go that route... But maybe (likely) I am just paranoid.  Wizard would probably make things easier (then agin, sp_change_users_login isn't too hard to use either)... Oh, one more thing... Don't try to copy system databases -- I experimented with migrating MSDB the same way, and that didn't work (actually, it attached ok, but then SQL Agent refused to start). |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-20 : 19:09:15
|
Ilya, thanks for the additional info. I had planned not to mess with the system databases just out of my own paranoia  . And I understand your concern re: wizards. As I read more about the Copy Database Wizard it says that it is "based on detach and attach functionality that allows user databases to be moved or copies... A Data Transformation Services package performs the acutal move or copy operation..." So it sounds to me like this would be slower than doing it manually, and not much different technology wise. One advantage is that you could schedule this job. But then again, if I worked at it a little, I could probably write a SQL Job to do all the work too.Looks like this will be my first project for the new year. Should be fun!  -------------------It's a SQL thing...Edited by - AjarnMark on 12/20/2001 19:10:04 |
|
|
|
|
|
|
|
|
upgrade from SQL7 to SQL2000