Upgrading 2000-25005 in place
-
Am not a true sql dba, and just barely a developer. Running sql2000 svcpk 4 on Svr2003. Want to bite the bullet and upgrade to sql2005. Read some other stuff, downloaded the MS Upgrade Tech Resource Guide and have some questions:
1. I'm not good enough to do anything to complicated so was going to do an in-place upgrade, (not using analysis, reporting or notification services, failover clusters, log shipping, and no vldb) I have one database using full-text searches, it's only updated quarterly.
2. Ran upgrade advisor on server, dts, and batch query files. Got the warnings about full-text searches, but I think I'm okay as long as I run the requery and increase the database size to accommodate?
3. None of my sql batch queries showed up after running upgrade advisor... were they not reviewed or were they okay? They show up as a line item in the upgrade advisor settings screen.
3. My four dts packages seem to be okay as long as I migrate them after the upgrade with the little wizard tool.
4. Need to tweak my db maintenance plans, but I believe they'll be okay also?
5. Concerned about something I read in the Resource Guide. It seemed to indicate that any syntax referencing something like dbname.dbo.tablename would need to be corrected to boname.sys.tablename. Yet, none of these stored procedures or queries showed up in the upgrade advisor. What to do?
(When first starting out I had heard that directly referencing the entire database, table, and owner in this manner would speed things up, so it's a habit I got into - continue this habit or am I being goofy?)
Anybody gone through this and offer any advise or gotcha tips I haven't even thought about yet? FYI - all databases (including tempdb) only total 1200 mb, largest being the 527 quarterly updated one, average being 100.
Thanks,
Janet
-
... 5. Concerned about something I read in the Resource Guide. It seemed to indicate that any syntax referencing something like dbname.dbo.tablename would need to be corrected to boname.sys.tablename. Yet, none of these stored procedures or queries showed up in the upgrade advisor. What to do?
(When first starting out I had heard that directly referencing the entire database, table, and owner in this manner would speed things up, so it's a habit I got into - continue this habit or am I being goofy?) ...
If you use system-tables (sysobjects,..) in your queries (let's hope you don't)
then you should use the sys.*** equivalent in SQL2005.
With the introduction of SCHEMA, the obvious thing they did was to move the system-catalog-objects to the SYS-schema.
Overhere we don't qualify the db, we connect to the db and then use schema and objectname !
unless we perform cross-database-queries offcourse.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Well, I guess the paranoia worked. My checklist seemed to cover most everything. Upgrade went through without a hitch - no errors. Took 3 tries for all components of the service pack to take though. That took longer than the actual upgrade. Migration of dts and maintenance plans seemed to go okay with no errors.
Two questions:
1. I also have a remote sql 2000 server I still need to connect to. Can't seem to get the tcp/ip connection to old server to work. Go back to old 2000 tool and it works fine, but I'd prefer to stay in one environment and migrate to the new tool. Any ideas?
2. On page 150 of the 2005 Upgrade Technical Guide it tells you how to upgrade the server agent proxy account. I got an account not found error on the @proxy_name line. Any ideas?
Thanks again,
Janet
-
- keep in mind to rebuild all tables / indexes and to run sp_updatestats and DBCC updateusage.
- indeed sp1 takes longer than the actual upgrade
- IMO dts-migration has been enhanced with sp1
- you should be able to connect with your sql2k instance from SSMS. Check your client protocols.
- There is an sql2k sp3/sp4 issue with linked servers (check out MS-Article ID : 906954) but it works fine after the application of the instcat.sql on your sql2000 instance.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Ummm, didn't realize I had to rebuild.... thanks.
Still can't connect using SSMS to sql2000. Looked under sql native client configuration - tcpip is enabled. Looked under aliases - ip and correct ports are listed for problem server. If I go back to the old Enterprise Manager, delete the existing registration and try a new registration to same server, the elipses for browsing the server name show the server. Type in the info and connection goes straight through. One interesting thing, if I browse local or network servers, the 2000 server I'm trying to connect to isn't listed.
Just forget the linked server thingy - bad communication - was referring to the SSMS connection above. Sorry to be so dense, brand new to this and trying to get functioning so I can start the ole' learning curve.
Thanks, Janet
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply