Migrating to SQL 2005

Hi,
We are migrating from SQL 2000 personal edition to SQL 2005 developer
edition a 120 GB database. This database has around 1000 stored proc's
and 150 DTS packages.
I have read that running SQL Upgrade Advisor on the source would list
down incompatibilties (if any). Once this is done and say there are no
incompatibilties, how do I go about transferring the actual objects
with data? There seems to be quite a few options like detach/attach,
copy database wizard, restoring a backup etc
Which would be the best way to go about this? Can someone point me to
some good articles regarding this?
Thank you.
Regards,
KartHi
After you have corrected anything that the most recent upgrade advisor has
notified you of then you could use either method. Once the database is on SQL
2005 (SP1) you should make sure the compatibility is SQL 2005, rebuild all
the indexes, update usage and statistics. I also prefer to recreate the
stored procedures/functions/views etc (which should not be an issue if you
have done everything the Upgrade advisor says!) Also make sure that the
users/logins are ok and there are no missing logins or orphaned users
http://support.microsoft.com/default.aspx/kb/314546, then regression and
performance test it.
Your DTS packages will require a little more work
http://msdn2.microsoft.com/en-us/library/ms143501.aspx
You may want to look at
http://www.microsoft.com/uk/technet/itsshowtime/sessionh.aspx?videoid=135
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
I assume that your Developer Licence covers you for whatever usage this
database has?
John
"Kart" wrote:
> Hi,
> We are migrating from SQL 2000 personal edition to SQL 2005 developer
> edition a 120 GB database. This database has around 1000 stored proc's
> and 150 DTS packages.
> I have read that running SQL Upgrade Advisor on the source would list
> down incompatibilties (if any). Once this is done and say there are no
> incompatibilties, how do I go about transferring the actual objects
> with data? There seems to be quite a few options like detach/attach,
> copy database wizard, restoring a backup etc
> Which would be the best way to go about this? Can someone point me to
> some good articles regarding this?
> Thank you.
> Regards,
> Kart
>|||Hi John,
In case some of the SP's are encrypted in the source, would the copy wizard
take care of this?
Regards,
Karthik
"John Bell" wrote:
> Hi
> After you have corrected anything that the most recent upgrade advisor has
> notified you of then you could use either method. Once the database is on SQL
> 2005 (SP1) you should make sure the compatibility is SQL 2005, rebuild all
> the indexes, update usage and statistics. I also prefer to recreate the
> stored procedures/functions/views etc (which should not be an issue if you
> have done everything the Upgrade advisor says!) Also make sure that the
> users/logins are ok and there are no missing logins or orphaned users
> http://support.microsoft.com/default.aspx/kb/314546, then regression and
> performance test it.
> Your DTS packages will require a little more work
> http://msdn2.microsoft.com/en-us/library/ms143501.aspx
> You may want to look at
> http://www.microsoft.com/uk/technet/itsshowtime/sessionh.aspx?videoid=135
> http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
> I assume that your Developer Licence covers you for whatever usage this
> database has?
> John
> "Kart" wrote:
> > Hi,
> >
> > We are migrating from SQL 2000 personal edition to SQL 2005 developer
> > edition a 120 GB database. This database has around 1000 stored proc's
> > and 150 DTS packages.
> >
> > I have read that running SQL Upgrade Advisor on the source would list
> > down incompatibilties (if any). Once this is done and say there are no
> > incompatibilties, how do I go about transferring the actual objects
> > with data? There seems to be quite a few options like detach/attach,
> > copy database wizard, restoring a backup etc
> >
> > Which would be the best way to go about this? Can someone point me to
> > some good articles regarding this?
> >
> > Thank you.
> >
> > Regards,
> > Kart
> >
> >|||Hi
I am not sure why you want to use the copy wizard, I would transfer SPs by
scriptings, as we use a source control system (such as Visual Source Safe) we
already have the source. An upgrade of version is one of the times you can
see quite a few changes to stored procedures and a source control system is
essential if you want to keep track of changes. Versions prior to SQL 2005
would not transfer encrypted objects, so I would not assume it is different.
I would no use the copy wizard in an upgrade.
John
"Karthik" wrote:
> Hi John,
> In case some of the SP's are encrypted in the source, would the copy wizard
> take care of this?
> Regards,
> Karthik
> "John Bell" wrote:
> > Hi
> >
> > After you have corrected anything that the most recent upgrade advisor has
> > notified you of then you could use either method. Once the database is on SQL
> > 2005 (SP1) you should make sure the compatibility is SQL 2005, rebuild all
> > the indexes, update usage and statistics. I also prefer to recreate the
> > stored procedures/functions/views etc (which should not be an issue if you
> > have done everything the Upgrade advisor says!) Also make sure that the
> > users/logins are ok and there are no missing logins or orphaned users
> > http://support.microsoft.com/default.aspx/kb/314546, then regression and
> > performance test it.
> >
> > Your DTS packages will require a little more work
> > http://msdn2.microsoft.com/en-us/library/ms143501.aspx
> >
> > You may want to look at
> > http://www.microsoft.com/uk/technet/itsshowtime/sessionh.aspx?videoid=135
> > http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
> >
> > I assume that your Developer Licence covers you for whatever usage this
> > database has?
> >
> > John
> >
> > "Kart" wrote:
> >
> > > Hi,
> > >
> > > We are migrating from SQL 2000 personal edition to SQL 2005 developer
> > > edition a 120 GB database. This database has around 1000 stored proc's
> > > and 150 DTS packages.
> > >
> > > I have read that running SQL Upgrade Advisor on the source would list
> > > down incompatibilties (if any). Once this is done and say there are no
> > > incompatibilties, how do I go about transferring the actual objects
> > > with data? There seems to be quite a few options like detach/attach,
> > > copy database wizard, restoring a backup etc
> > >
> > > Which would be the best way to go about this? Can someone point me to
> > > some good articles regarding this?
> > >
> > > Thank you.
> > >
> > > Regards,
> > > Kart
> > >
> > >