Mirrored databases and Witness server

We are upgrading to 2005 and have decided to go to a mirrored 2005
environment. We have picked out the production server (principal) 16
gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
having difficulty finding out how "beefy" a box we need for the Witness
server. Does anyone have any experience with this and can you assist me
in specs. I am working with our Network Administrator.
Thanks for all the help!
KristinaAm 11 Jan 2007 06:32:20 -0800 schrieb KristinaDBA@.gmail.com:

> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina
Hi, the whitness Server don't need to be so big ;-)
You should install the right editon of SQL Server to be Witness.
The relevance of your sizing are the other roles/jobs this server will do
beside beeing witness.
thats my opinion. hope I could help a little
greetings
Rouven|||Hi,
Kristina - your witness server can be a single processor box with
1/2 GB memory. You can even use SQL Server express. The resources
needed for the witness server is minimum.
This from our Microsoft pre-sales rep.
hth
KristinaDBA@.gmail.com wrote:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina|||Hey - thanks everyone for the help! I love this group!
JoeyD wrote:[vbcol=seagreen]
> Hi,
> Kristina - your witness server can be a single processor box with
> 1/2 GB memory. You can even use SQL Server express. The resources
> needed for the witness server is minimum.
> This from our Microsoft pre-sales rep.
> hth
>
> KristinaDBA@.gmail.com wrote:|||While being a witness is not a very hard job, it is important because when
the witness is down, automatic failover won't happen so while you can get by
without a lot of processor or memory resources, you don't want to use your
kid's old game machine for this. Get a good quality machine with a good NIC
card and maybe even a UPS.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<KristinaDBA@.gmail.com> wrote in message
news:1168528668.044990.221330@.i56g2000hsf.googlegroups.com...
> Hey - thanks everyone for the help! I love this group!
> JoeyD wrote:
>

Mirrored databases and Witness server

We are upgrading to 2005 and have decided to go to a mirrored 2005
environment. We have picked out the production server (principal) 16
gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
having difficulty finding out how "beefy" a box we need for the Witness
server. Does anyone have any experience with this and can you assist me
in specs. I am working with our Network Administrator.
Thanks for all the help!
Kristina
Hi,
Kristina - your witness server can be a single processor box with
1/2 GB memory. You can even use SQL Server express. The resources
needed for the witness server is minimum.
This from our Microsoft pre-sales rep.
hth
KristinaDBA@.gmail.com wrote:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina
|||Hey - thanks everyone for the help! I love this group!
JoeyD wrote:[vbcol=seagreen]
> Hi,
> Kristina - your witness server can be a single processor box with
> 1/2 GB memory. You can even use SQL Server express. The resources
> needed for the witness server is minimum.
> This from our Microsoft pre-sales rep.
> hth
>
> KristinaDBA@.gmail.com wrote:
|||While being a witness is not a very hard job, it is important because when
the witness is down, automatic failover won't happen so while you can get by
without a lot of processor or memory resources, you don't want to use your
kid's old game machine for this. Get a good quality machine with a good NIC
card and maybe even a UPS.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<KristinaDBA@.gmail.com> wrote in message
news:1168528668.044990.221330@.i56g2000hsf.googlegr oups.com...
> Hey - thanks everyone for the help! I love this group!
> JoeyD wrote:
>

Mirrored databases and Witness server

We are upgrading to 2005 and have decided to go to a mirrored 2005
environment. We have picked out the production server (principal) 16
gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
having difficulty finding out how "beefy" a box we need for the Witness
server. Does anyone have any experience with this and can you assist me
in specs. I am working with our Network Administrator.
Thanks for all the help!
KristinaAm 11 Jan 2007 06:32:20 -0800 schrieb KristinaDBA@.gmail.com:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina
Hi, the whitness Server don't need to be so big ;-)
You should install the right editon of SQL Server to be Witness.
The relevance of your sizing are the other roles/jobs this server will do
beside beeing witness.
thats my opinion. hope I could help a little
greetings
Rouven|||Hi,
Kristina - your witness server can be a single processor box with
1/2 GB memory. You can even use SQL Server express. The resources
needed for the witness server is minimum.
This from our Microsoft pre-sales rep.
hth
KristinaDBA@.gmail.com wrote:
> We are upgrading to 2005 and have decided to go to a mirrored 2005
> environment. We have picked out the production server (principal) 16
> gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> having difficulty finding out how "beefy" a box we need for the Witness
> server. Does anyone have any experience with this and can you assist me
> in specs. I am working with our Network Administrator.
> Thanks for all the help!
> Kristina|||Hey - thanks everyone for the help! I love this group!
JoeyD wrote:
> Hi,
> Kristina - your witness server can be a single processor box with
> 1/2 GB memory. You can even use SQL Server express. The resources
> needed for the witness server is minimum.
> This from our Microsoft pre-sales rep.
> hth
>
> KristinaDBA@.gmail.com wrote:
> > We are upgrading to 2005 and have decided to go to a mirrored 2005
> > environment. We have picked out the production server (principal) 16
> > gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
> > having difficulty finding out how "beefy" a box we need for the Witness
> > server. Does anyone have any experience with this and can you assist me
> > in specs. I am working with our Network Administrator.
> >
> > Thanks for all the help!
> >
> > Kristina|||While being a witness is not a very hard job, it is important because when
the witness is down, automatic failover won't happen so while you can get by
without a lot of processor or memory resources, you don't want to use your
kid's old game machine for this. Get a good quality machine with a good NIC
card and maybe even a UPS.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<KristinaDBA@.gmail.com> wrote in message
news:1168528668.044990.221330@.i56g2000hsf.googlegroups.com...
> Hey - thanks everyone for the help! I love this group!
> JoeyD wrote:
>> Hi,
>> Kristina - your witness server can be a single processor box with
>> 1/2 GB memory. You can even use SQL Server express. The resources
>> needed for the witness server is minimum.
>> This from our Microsoft pre-sales rep.
>> hth
>>
>> KristinaDBA@.gmail.com wrote:
>> > We are upgrading to 2005 and have decided to go to a mirrored 2005
>> > environment. We have picked out the production server (principal) 16
>> > gigs memory, dual proc quad core, Server 2003 Enterprise Edition. I am
>> > having difficulty finding out how "beefy" a box we need for the Witness
>> > server. Does anyone have any experience with this and can you assist me
>> > in specs. I am working with our Network Administrator.
>> >
>> > Thanks for all the help!
>> >
>> > Kristina
>

Minimun SP Level for Cluster Upgrade

I'm upgrading MSSQL2000 (RTM) cluster to 2005. I haven't seen any information regarding a minimum service pack level for 2000. This upgrade just makes me overly caution.

To upgrade to SQL 2005, you need to be running SQL 2000 SP3(or SP3a) at a minimum.

Thanks,

Sam Lester (MSFT)

|||

Here's the link from Books Online:

http://msdn2.microsoft.com/en-us/library/ms144245.aspx

Sam

Migration data from SQL 7.0 to SQL 2005

I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
on to of the existing 7.0 instance and perfrom a full system upgrade. What I
don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
import the SQL 7.0 database and convert it for use inder 2005? I don't want
to burn my tracks by upgrdaing the existing server. If the install goes bad,
I can't fall back if nercessary. The books online for the lates preview don't
indicate the abaility migrate data fromj 7.0 as a stand alone process
sepparate from a full upgrade.
You should be able to detach the database from SQL Server 7.0 and attach it
to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
procedures. For more information about upgrading databases from previous
versions to SQL Server 2005, see SQL Server 2005 Books Online.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> I know that sQL 2005 support upgrading a SQL 7.0 database if you install
it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
want
> to burn my tracks by upgrdaing the existing server. If the install goes
bad,
> I can't fall back if nercessary. The books online for the lates preview
don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.
|||I have read the books online and I don't get the impression that you can
either directly attach a 7.0 database or alternatively, use the "copy
database wizard". Attempts to directly attach a SQL 7.0 database have failed
when initiated by scripts generated using the new scripting wizard. The “copy
database wizard” clearly states in its opening text that it can only be used
when copying SQL 2000 or 2005 data. There is no mention of copying SQL 7.0
data at all.
The books online recommend not using “sp_attach_db” command since it will no
longer be supported in the future. They suggest using the “CREAT DATABASE”
command in its place which is what the script generated by the wizard used.
In eaither case, the action failed.
"Narayana Vyas Kondreddi" wrote:

> You should be able to detach the database from SQL Server 7.0 and attach it
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> it
> I
> want
> bad,
> don't
>
>
|||OK, I got it to work. For some reason, scheduling the script to run via the
agent causes it to fail when executed. However, when I copied the script to
the query analyzer and executed it from there, it worked correctly.
"Narayana Vyas Kondreddi" wrote:

> You should be able to detach the database from SQL Server 7.0 and attach it
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> it
> I
> want
> bad,
> don't
>
>
|||For additional help with upgrading to SQL Server 2005 from earlier versions
of SQL Server, you may be interested in the SQL Server 2005 Upgrade Advisor:
http://www.microsoft.com/downloads/d...DisplayLang=en
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
>I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> I can't fall back if nercessary. The books online for the lates preview
> don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.

Migration data from SQL 7.0 to SQL 2005

I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
on to of the existing 7.0 instance and perfrom a full system upgrade. What I
don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
import the SQL 7.0 database and convert it for use inder 2005? I don't want
to burn my tracks by upgrdaing the existing server. If the install goes bad,
I can't fall back if nercessary. The books online for the lates preview don't
indicate the abaility migrate data fromj 7.0 as a stand alone process
sepparate from a full upgrade.You should be able to detach the database from SQL Server 7.0 and attach it
to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
procedures. For more information about upgrading databases from previous
versions to SQL Server 2005, see SQL Server 2005 Books Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> I know that sQL 2005 support upgrading a SQL 7.0 database if you install
it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
want
> to burn my tracks by upgrdaing the existing server. If the install goes
bad,
> I can't fall back if nercessary. The books online for the lates preview
don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.|||I have read the books online and I don't get the impression that you can
either directly attach a 7.0 database or alternatively, use the "copy
database wizard". Attempts to directly attach a SQL 7.0 database have failed
when initiated by scripts generated using the new scripting wizard. The â'copy
database wizardâ' clearly states in its opening text that it can only be used
when copying SQL 2000 or 2005 data. There is no mention of copying SQL 7.0
data at all.
The books online recommend not using â'sp_attach_dbâ' command since it will no
longer be supported in the future. They suggest using the â'CREAT DATABASEâ'
command in its place which is what the script generated by the wizard used.
In eaither case, the action failed.
"Narayana Vyas Kondreddi" wrote:
> You should be able to detach the database from SQL Server 7.0 and attach it
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> > I know that sQL 2005 support upgrading a SQL 7.0 database if you install
> it
> > on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> > don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> > import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> > to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> > I can't fall back if nercessary. The books online for the lates preview
> don't
> > indicate the abaility migrate data fromj 7.0 as a stand alone process
> > sepparate from a full upgrade.
>
>|||OK, I got it to work. For some reason, scheduling the script to run via the
agent causes it to fail when executed. However, when I copied the script to
the query analyzer and executed it from there, it worked correctly.
"Narayana Vyas Kondreddi" wrote:
> You should be able to detach the database from SQL Server 7.0 and attach it
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> > I know that sQL 2005 support upgrading a SQL 7.0 database if you install
> it
> > on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> > don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> > import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> > to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> > I can't fall back if nercessary. The books online for the lates preview
> don't
> > indicate the abaility migrate data fromj 7.0 as a stand alone process
> > sepparate from a full upgrade.
>
>|||For additional help with upgrading to SQL Server 2005 from earlier versions
of SQL Server, you may be interested in the SQL Server 2005 Upgrade Advisor:
http://www.microsoft.com/downloads/details.aspx?FamilyID=cf28daf9-182e-4ac2-8e88-f2e936558bf2&DisplayLang=en
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
>I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> I can't fall back if nercessary. The books online for the lates preview
> don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.

Migration data from SQL 7.0 to SQL 2005

I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
on to of the existing 7.0 instance and perfrom a full system upgrade. What I
don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
import the SQL 7.0 database and convert it for use inder 2005? I don't want
to burn my tracks by upgrdaing the existing server. If the install goes bad,
I can't fall back if nercessary. The books online for the lates preview don'
t
indicate the abaility migrate data fromj 7.0 as a stand alone process
sepparate from a full upgrade.You should be able to detach the database from SQL Server 7.0 and attach it
to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
procedures. For more information about upgrading databases from previous
versions to SQL Server 2005, see SQL Server 2005 Books Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> I know that sQL 2005 support upgrading a SQL 7.0 database if you install
it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
want
> to burn my tracks by upgrdaing the existing server. If the install goes
bad,
> I can't fall back if nercessary. The books online for the lates preview
don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.|||I have read the books online and I don't get the impression that you can
either directly attach a 7.0 database or alternatively, use the "copy
database wizard". Attempts to directly attach a SQL 7.0 database have failed
when initiated by scripts generated using the new scripting wizard. The “c
opy
database wizard” clearly states in its opening text that it can only be us
ed
when copying SQL 2000 or 2005 data. There is no mention of copying SQL 7.0
data at all.
The books online recommend not using “sp_attach_db” command since it wil
l no
longer be supported in the future. They suggest using the “CREAT DATABASE
command in its place which is what the script generated by the wizard used.
In eaither case, the action failed.
"Narayana Vyas Kondreddi" wrote:

> You should be able to detach the database from SQL Server 7.0 and attach i
t
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> it
> I
> want
> bad,
> don't
>
>|||OK, I got it to work. For some reason, scheduling the script to run via the
agent causes it to fail when executed. However, when I copied the script to
the query analyzer and executed it from there, it worked correctly.
"Narayana Vyas Kondreddi" wrote:

> You should be able to detach the database from SQL Server 7.0 and attach i
t
> to SQL Server 2005 using sp_detach_db and sp_attach_db system stored
> procedures. For more information about upgrading databases from previous
> versions to SQL Server 2005, see SQL Server 2005 Books Online.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AJStein" <AJStein@.discussions.microsoft.com> wrote in message
> news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
> it
> I
> want
> bad,
> don't
>
>|||For additional help with upgrading to SQL Server 2005 from earlier versions
of SQL Server, you may be interested in the SQL Server 2005 Upgrade Advisor:
http://www.microsoft.com/downloads/...&DisplayLang=en
"AJStein" <AJStein@.discussions.microsoft.com> wrote in message
news:520F2B5E-6ECA-4EC3-90CD-643348C44A80@.microsoft.com...
>I know that sQL 2005 support upgrading a SQL 7.0 database if you install it
> on to of the existing 7.0 instance and perfrom a full system upgrade. What
> I
> don'y know is, if I set up a sepparate SQL 2005 server, is there a way to
> import the SQL 7.0 database and convert it for use inder 2005? I don't
> want
> to burn my tracks by upgrdaing the existing server. If the install goes
> bad,
> I can't fall back if nercessary. The books online for the lates preview
> don't
> indicate the abaility migrate data fromj 7.0 as a stand alone process
> sepparate from a full upgrade.

Migrating to 2005, need advice

Help. I have been tasked with upgrading a 2000 instance to 2005.

I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.

Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.

One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?

Also, why can't I seem to find a comprehensive list of TO DO's when
upgrading? Doesn't microsoft provide this? You would think so. I will
run upgrade advisor first, but isn't there also documentation
somewhere?

I seem to recall lots of permissions issues that arose with 2005.

HELP

Thanks(tootsuite@.gmail.com) writes:

Quote:

Originally Posted by

Help. I have been tasked with upgrading a 2000 instance to 2005.
>
I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.
>
Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.
>
One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?

It's certainly not a bad idea to install a second instance on the
same machine, and the migrate by BACKUP/RESTORE. This is great if
you run into performance issues and want ot compare query plans. The
drawback if you install a new instance is that a lot of clients will
be affected.

A second alternative is to install SQL 2005 on a new machine, and when
the install has completed, you change the names and IP-address of the
machines, so that the clients can't tell the difference. Of course,
this means that you need to shop for hardware.

This makes it sounds like an in-place upgrade should be avoided at
all cost, but it's not that bad. But I will have to admit that I
have never done one, nor do I plan to. If you have to go that path,
I recommend that you start with installing a second instance of SQL 2000,
and restore databases on this instance. Yes, I still think it is a
good idea to keep SQL 2000 for reference for a while.

In any case, you should first set up a test environment, so that you
can test doing in-place upgrades, and at least conduct some testing
of your applications.

Here is a short list of must-do:

1) Change the compatibility level of all databases to 90. If too many
things break, you may to move back to 90, but be optimistic.

2) Run sp_updatestats on all databases. Old statistics are voided by
the upgrade.

3) If you move databases from another server, you need to rematch
users with logins. (This applies even if you don't make upgrades.)
This includes setting the database owner, if the owner is not sa.

There a few things that can break. Here is a list of the most
probable cases:

* Old-style outer-join, *= and =*. Caught by the Upgrade Advisor,
and can be avoided with compat level 80.

* WITH is now required for hints with more than one work. Caught by the
Upgrade Advisor, and can be avoided with compat level 80.

* Views that uses SELECT TOP 100 PRECENT ORDER BY. In SQL 2000 a
SELECT without ORDER BY from this view seemed to get the order of
the ORDER BY in the view definition. This was mere chance, and it
does not happen that often on SQL 2005. This is *not* caught by
the Upgrade Advisor, as far as I know, and you cannot save the
day with compat level 80.

* Passwords are now always case-sensitive.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 12, 5:52 am, tootsu...@.gmail.com wrote:

Quote:

Originally Posted by

Help. I have been tasked with upgrading a 2000 instance to 2005.
>
I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.
>
Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.
>
One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?
>
Also, why can't I seem to find a comprehensive list of TO DO's when
upgrading? Doesn't microsoft provide this? You would think so. I will
run upgrade advisor first, but isn't there also documentation
somewhere?
>
I seem to recall lots of permissions issues that arose with 2005.
>
HELP
>
Thanks

The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.

For tools that make this process easy please visit www.dbghost.com
Regards,

Malcolm|||Mork69 (mleach@.bigfoot.com) writes:

Quote:

Originally Posted by

The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.

I agree that running the scripts is a good idea, because you can
catch all compilation errors.

However, for the actual migration, I strongly recommend to use
backup/restore or detach/attach. Scripting is a more complex and a
process more prone to errors.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 13, 10:10 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Mork69 (mle...@.bigfoot.com) writes:

Quote:

Originally Posted by

The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.

>
I agree that running the scripts is a good idea, because you can
catch all compilation errors.
>
However, for the actual migration, I strongly recommend to use
backup/restore or detach/attach. Scripting is a more complex and a
process more prone to errors.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

In fact, if both approaches are used then the migration should be
perfect. i.e. You script out and build in order to highlight and fix
all the problems. If you keep the scripts for the objects that were
fixed then you can do the detach/attach and then recreate them.