October 25, 2009 at 11:24 pm
I thought its necessary when you do migration(backup/restore).....
October 26, 2009 at 1:54 am
No. Plans are memory only and are not included in a database backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2009 at 4:58 am
I knew I forgot something. The stats update. VERY IMPORTANT when upgrading from SQL 2000 to either 2k5 or 2k8.
Thanks for the reminder, Gail.
ravikanth - Reasons why you don't want to restart SQL server unless you have to is because after the restart, the first time everything runs it has to rebuild the plans. Upgrades are the same thing. So # 3 is a non-issue.
RE: DBCC UpdateUsage - this requires table names. Better to use "sp_updatestats" and it'll get all tables at once.
Regarding the default schema issue (something I forgot):
Mahesh.vsp, make sure to delete any login related schemas from your individual databases unless those logins actually own objects. It will save you a lot of headaches later on. During the upgrade, all 2000 logins will have schemas automatically created regardless of object ownage.
I haven't had a problem with needing to actually set default schema for them, though. Of course, none of them had CREATE permissions either. So if your logins do have CREATE perms, do the setting of the default schema to DBO (or your usual default schema).
Regarding replication, I've never migrated it. You'll want to google that issue if no one answers here on this thread.
October 26, 2009 at 5:22 am
Brandie Tarvin (10/26/2009)
RE: DBCC UpdateUsage - this requires table names. Better to use "sp_updatestats" and it'll get all tables at once.
DBCC UpdateUsage and sp_updatestats do completely different things. Are you thinking of UPDATE STATISTICS vs sp_updatestats?
DBCC UpdateUsage goes through all tables in the database and updates the metadata concerning page space usage. There were known bugs in SQL 2000 that resulted in the % used often being incorrect for pages. This could result in silly things like a database with -50% free space.
Most of the bugs are fixed in SQL 2005, so it is advised to run UPDATEUSAGE once after the upgrade to fix all lingering issues.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2009 at 6:38 am
While performing Migration, probably the first/second step which method is the best one.
1.Attach / Dettach
2.backup and restore
Is there any constraints going for either of the two methods
Also, want to confirm do we need to convert sql 2000 to 2005 and 2005 to 2008?
Is that required or else can we directly migrate 2000 to 2008?
Expecting more comments on this regard.
Thanks in advance
October 26, 2009 at 6:58 am
Neither really is better than the other. The issue with Detach / Attach, though, is if you forget to make a copy of your files, you can never "roll back" the upgrade if something goes wrong.
Never do a direct Detach / Attach. Always make a copy of the files (and / or a backup of the database) before migrating via this method. This way, if you have to revert, you have a copy you can revert back to.
October 26, 2009 at 6:59 am
GilaMonster (10/26/2009)DBCC UpdateUsage and sp_updatestats do completely different things. Are you thinking of UPDATE STATISTICS vs sp_updatestats?
I plead the "Monday Mornings" as my excuse.
Yes, that's what I meant. DOH! Silly me. Thanks for the catch, Gail.
October 26, 2009 at 7:17 am
Upgrading a SQL Server 2000 Replication Environment
I have a replication being setup on 1 server which is 2000 Server which is acting as Publisher as well as distributor
and we two more servers acting as subscribers.
What are the precautionary steps to be followed for this to take it on to 2005??
Also, can you please confirm the order of the migration checklist.
Pl correct me if am wrong.
Assuming i have 2000 server which is running LIVE and i have 2005 installed on 2003 Server ( new server)
1. Take full backups of all users and system databases of 2000.
2. Run the upgrade advisor on each 2000 server which has to be eventually migrated to 2005.
3. backup-restore method /dettach - attach method of all application databases.
4. Fixing of Orphan users
5. Script jobs from 2000 server and excute the script on 2005 server.
Here while dealing with jobs , do i need to script all jobs " maintenance plan jobs" and excute them on 2005 server.
This is what i believe from my side. correct me.
a) Assuming i would take the scripts of ordinary scheduled jobs and run them 2005 server.
b) Then for maintenance plans i would re-create them in 2005 by knowing the functionality in 2000.
Here , i need your inputs can i generate any scripts of 2000 Maintenance plans ????
or else can i use Migrate
One question over, can we separate all the jobs related to maintenance jobs and accordingly i can execute them on 2005
Also, looking at the jobs, how to identify whether this job belong to Maintenance plan or an Ordinary job.
Please suggest the best way of migrating the Maintenance plans and ordinary jobs.
This is again all my assumptions, i need to check this out pratically.
6. Is there anything do we need to do with Alerts. I can see the Alerts configured for Tempdb and for some errors.
Do i need to take the script and run it over 2005?
7. migrate the dts packages
8. Perform unit testing at each level
9. Replication setup
10. Change the compatibility levels to 80
11. rebuild all indexes
12. Run dbcc checkdb, pagetorn....,DBCC UpdateUsage,update stats commands (health checks)
Please add if i missed out any
October 26, 2009 at 7:36 am
Forgot a step,
Once i install sql 2005. Can i first install the latest service pack for 2005 and then do the all the above steps ?
October 26, 2009 at 10:09 am
Yes install SP3 then go with all steps.
October 27, 2009 at 12:50 am
Am doing a dettach and attach on sql server 2005.
Question is, do we need to attach the .ldf along with .mdf file?
Is there any use of that, or else delete the log and let the sql server create a new log?
Please suggest.
October 27, 2009 at 1:42 am
Hi Guys,
For testing purpose i have sql 2000 and sql 2005 installed on the same machine
I have created a sample database with some few basic objects i.e tables/views/indexes/stored
procedures/triggers ...
Before performing Attach / dettach, i have run the SQL 2005 upgrade advisor.
I have selected only 1 datbase which i have created and run the advisor.
I encountered an error saying
"SQL BPA command line has encounterd a problem and needs to close "
I clickedo n <dont send> button
and my Advisor failed and clciked on Launch Report which show error related to
Data Transformation services.
My question is , i dont have any dts packages created pointing to my application database.
Still it is showing and error failed.
But i can say, they were few dts packages at instance level been created.
Any comments on that.
What shall i assume?? can i upgrade my database or not ? or else do i need to do anything else to avoid these errors.
October 27, 2009 at 1:43 am
mahesh.vsp (10/27/2009)
Question is, do we need to attach the .ldf along with .mdf file?
Yes, absolutely. The transaction log is not an optional file
Is there any use of that, or else delete the log and let the sql server create a new log?
If you don't mind the possibility that the attach fails or that the database is suspect after the attach, then delete the log file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 4:14 am
The Upgrade Advisor should NOT be run the day of your migration. It should be run at least a week *before* your migration (several weeks, if possible). If the Upgrade Advisor finds issues, you will need at least that long to resolve them before doing your upgrade. In my experience, a proper "side by side" migration takes a couple of months with programming issues, package rebuilds, job recreations, etc, being done before the actual "switchover" day.
Google any errors that come up during the run of the Upgrade Advisor. Chances are someone has run into them before and has already posted a thread.
The transaction log file is "optional" in the sense that you can attach a DB without it and SQL Server will create a new one. However, as Gail has previously said, it is a BAD Idea to delete your transaction log file without cause. What happens to all those uncommitted transactions you'll have in your logfile if you do that? You lose that data.
Don't be a lazy DBA. Port the transaction log file over with your data file. It'll save you a lot of headaches.
October 27, 2009 at 4:19 am
mahesh.vsp (10/27/2009)
clciked on Launch Report which show error related toData Transformation services.
My question is , i dont have any dts packages created pointing to my application database.
Still it is showing and error failed.
But i can say, they were few dts packages at instance level been created.
Any comments on that.
What shall i assume?? can i upgrade my database or not ? or else do i need to do anything else to avoid these errors.
You need to either install the backward compatibility component for DTS in SQL 2005 or rebuild your DTS packages as SSIS packages. I advise a rebuild rather than a mere upgrade because of several reasons:
1) It's a great way to learn SSIS quickly
2) An upgrade creates stubs
3) An upgrade creates bizarre and often inefficient pathways / work-arounds for stuff DTS did
4) A rebuild helps you overcome outdated processes and create efficient package processes.
Never ignore Upgrade Advisor errors. It'll bite you in the rear during the actual migration. Fix your problems now, before your migration. If the Advisor is complaining about DTS, then you have something set up pointing to that database. It could be as simple as a maintenance package instead of an actual custom DTS package.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply