| Author |
 Topic  |
|
|
smccreadie
Aged Yak Warrior
USA
505 Posts |
 Posted - 10/04/2001 : 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.
|
|
|
penumatsa
Constraint Violating Yak Guru
USA
289 Posts |
Posted - 10/04/2001 : 11:44:59
|
You can also move the database or copy the database to SQL Server 2000.
suresh penumatsa |
 |
|
|
jharwood
Starting Member
41 Posts |
Posted - 10/04/2001 : 13:21:45
|
My company ran into one issue when we migrated an inherited database to 2k. The database had several indices which were created by the index tuner. The indices became corrupt and speaking with Microsoft, we removed and recreated them. We also were told to remove any hind and _wa objects.
-Jharwood
|
|
|
|
dtong004
Constraint Violating Yak Guru
USA
281 Posts |
Posted - 10/04/2001 : 14:36:34
|
How do you remove the _WA objects?
Daniel
|
|
|
|
jharwood
Starting Member
41 Posts |
Posted - 10/05/2001 : 10:38:37
|
The _wa objects are I believe system created statistics. Below is a query we worked out with Microsoft...sorry about the format. You should be able to modify it to your needs, just cut and paste the results back into your QA. Hope this helps. -Jharwood
set nocount on
select
case
when i.name like 'hind_c_%' then 'drop index ['
else 'drop statistics ['
end,object_name(i.id) + '].[' + i.name + ']' from sysindexes i join
sysobjects o on i.id = o.id
where
i.name like 'hind_%' and
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND INDEXPROPERTY(i.id,
i.name, 'IsAutoStatistics') = 0))
order by i.name
select
case
when i.name like 'hkhk' then 'drop index ['
else 'drop statistics ['
end,
object_name(i.id) + '].['+ i.name + ']' from sysindexes i join
sysobjects o on i.id = o.id
where
i.name like '_wa%' --and
--(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
--(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND INDEXPROPERTY(i.id,
--i.name, 'IsAutoStatistics') = 0))
order by i.name
set nocount off
|
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 12/19/2001 : 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
USA
1139 Posts |
Posted - 12/20/2001 : 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
USA
1949 Posts |
Posted - 12/20/2001 : 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
USA
3246 Posts |
|
|
izaltsman
A custom title
USA
1139 Posts |
Posted - 12/20/2001 : 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
USA
3246 Posts |
Posted - 12/20/2001 : 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 |
|
|
| |
Topic |
|
upgrade from SQL7 to SQL2000
