SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 upgrade from SQL7 to SQL2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smccreadie
Aged Yak Warrior

USA
505 Posts

Posted - 10/04/2001 :  06:36:27  Show Profile  Visit smccreadie's Homepage  Reply with Quote
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  Show Profile  Visit penumatsa's Homepage  Reply with Quote
You can also move the database or copy the database to SQL Server 2000.

suresh penumatsa
Go to Top of Page

jharwood
Starting Member

41 Posts

Posted - 10/04/2001 :  13:21:45  Show Profile  Reply with Quote
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

Go to Top of Page

dtong004
Constraint Violating Yak Guru

USA
281 Posts

Posted - 10/04/2001 :  14:36:34  Show Profile  Visit dtong004's Homepage  Reply with Quote
How do you remove the _WA objects?

Daniel

Go to Top of Page

jharwood
Starting Member

41 Posts

Posted - 10/05/2001 :  10:38:37  Show Profile  Reply with Quote
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




Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/19/2001 :  19:55:00  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 12/20/2001 :  08:31:04  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 12/20/2001 :  11:53:49  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/20/2001 :  15:25:16  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_upgrade_6nqr.asp?frame=true about a "Copy Database Wizard". It sounds like this might get around the Logins issue. Any experience or thoughts?

-------------------
It's a SQL thing...
Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 12/20/2001 :  16:27:05  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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).


Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/20/2001 :  19:09:15  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000