Mirroring Failover/Failback

I have been using Mirroring now for a while and put the system into
production now after applying SQL sp 1.
We have a setup with a witness, principal and mirror with certificates (the
witness is on a webserver in a different domain, so we can't use integrated
security).
Automatic failover works fine if we take down the principal. But when the
principal is up and running again, no automatic failback occur. If this by
design, or is there something I've missed out in the configuration?
Per SchjetneThe roles for mirroring are soft roles. So, you cannot define a preferred node (like you can for a
cluster). If you want to fail back, you have to ask for it (ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into production now after applying
>SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates (the witness is on a
> webserver in a different domain, so we can't use integrated security).
> Automatic failover works fine if we take down the principal. But when the principal is up and
> running again, no automatic failback occur. If this by design, or is there something I've missed
> out in the configuration?
> Per Schjetne
>|||"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
Sorry, there isn't an option for automatic failback. You will have to do
that manually.
--
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp|||If you think about it, failing back will cause all client connections to be
dropped again so you probably don't want SQL Server to decide when is a good
time to fail back. It's probably much better if you pick a time when the
change won't affect too many users.
--
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
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
> Per Schjetne
>|||I agree on that. Is there a way to query any system tables telling me which
database is acting as principal vs mirror? Is the witness aware of this, or
is it just monitoring which server is replying, being able to do a failover?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> If you think about it, failing back will cause all client connections to
> be dropped again so you probably don't want SQL Server to decide when is a
> good time to fail back. It's probably much better if you pick a time when
> the change won't affect too many users.
> --
> 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
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when the
>> principal is up and running again, no automatic failback occur. If this
>> by design, or is there something I've missed out in the configuration?
>> Per Schjetne
>|||There's an event that you can monitor to be notified when the state changes.
The sys.dm_database_mirroring view can be used to determine the mirroring
state of all the databases of an instance. The
sys.dm_database_mirroring_witnesses can be used from the witness to see
which server is the primary.
--
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
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>I agree on that. Is there a way to query any system tables telling me which
>database is acting as principal vs mirror? Is the witness aware of this, or
>is it just monitoring which server is replying, being able to do a
>failover?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> If you think about it, failing back will cause all client connections to
>> be dropped again so you probably don't want SQL Server to decide when is
>> a good time to fail back. It's probably much better if you pick a time
>> when the change won't affect too many users.
>> --
>> 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
>> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
>> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when
>> the principal is up and running again, no automatic failback occur. If
>> this by design, or is there something I've missed out in the
>> configuration?
>> Per Schjetne
>>
>|||That's not exactly correct.
The reason it did not failback, is because that database is no longer the
principal. Failover causes a role reversal. When it fails over to the
mirror, that database is promoted to a principal and begins serving the
database. When the other database comes online, its role is automatically
demoted to mirror and the transactions start flowing back to it.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDV9lIeaGHA.4936@.TK2MSFTNGP05.phx.gbl...
> The roles for mirroring are soft roles. So, you cannot define a preferred
> node (like you can for a cluster). If you want to fail back, you have to
> ask for it (ALTER DATABASE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when the
>> principal is up and running again, no automatic failback occur. If this
>> by design, or is there something I've missed out in the configuration?
>> Per Schjetne
>|||Thanks! Just what I needed. By the way, the correct tablename is:
sys.database_mirroring_witnesses
Per :-)
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23bsLDGkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> There's an event that you can monitor to be notified when the state
> changes.
> The sys.dm_database_mirroring view can be used to determine the mirroring
> state of all the databases of an instance. The
> sys.dm_database_mirroring_witnesses can be used from the witness to see
> which server is the primary.
> --
> 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
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>>I agree on that. Is there a way to query any system tables telling me
>>which database is acting as principal vs mirror? Is the witness aware of
>>this, or is it just monitoring which server is replying, being able to do
>>a failover?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> If you think about it, failing back will cause all client connections to
>> be dropped again so you probably don't want SQL Server to decide when is
>> a good time to fail back. It's probably much better if you pick a time
>> when the change won't affect too many users.
>> --
>> 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
>> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
>> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when
>> the principal is up and running again, no automatic failback occur. If
>> this by design, or is there something I've missed out in the
>> configuration?
>> Per Schjetne
>>
>>
>

Mirroring Failover leaves 2 databases as Principal

We have seven databases mirrrored on our production server. Two of
these failed over, one correctly. The other database is marked as
"Principal/Disconnected" on BOTH the primary and secondary servers. I
can browse the tables in each database. Obviously this is not meant to
happen - one of the databases should be in Restoring mode.Transactions
are only going into the one database - on the Primary server. Has
anyone come across this before? Any ideas on how to proceed?
If I attempt to pause mirroring on the primary server it fails with
the message "The database mirror is busy, re-issue the command later".
I can see this getting really bad if I am unable to (at worst) remove
mirroring from either database due to them thinking that they are the
principal and cannot connect to the other mirrored database.
(both servers on SP2.)On Sep 19, 7:03 pm, Calculated <sarahjco...@.gmail.com> wrote:
> We have seven databases mirrrored on our production server. Two of
> these failed over, one correctly. The other database is marked as
> "Principal/Disconnected" on BOTH the primary and secondary servers. I
> can browse the tables in each database. Obviously this is not meant to
> happen - one of the databases should be in Restoring mode.Transactions
> are only going into the one database - on the Primary server. Has
> anyone come across this before? Any ideas on how to proceed?
> If I attempt to pause mirroring on the primary server it fails with
> the message "The database mirror is busy, re-issue the command later".
> I can see this getting really bad if I am unable to (at worst) remove
> mirroring from either database due to them thinking that they are the
> principal and cannot connect to the other mirrored database.
> (both servers on SP2.)
My plan was to come in and backup both databases and then restart the
SQL Service on the secondary, hoping that the state would change.
However when I got in to check this (maybe 6 hours later), the
database on the Secondary had elevated itself to be the Principal
(connected) and the Primay server's database was Synchronised/
Restoring. Good. I have since manually failed the databases back over
to primary. All seems ok so far but I will need to check this
throughout the day. (Hope no-one else runs into this!)

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
>

Migration to new cluster enviroment

My production system is on stand alone Server and it is a publisher on the
replication system. I need to Migrate to a new hardware with fail over
cluster and upgrade to SQL 2005 .
I need to do that with out impacting replication. What is my options? Can I
do that Or not
If this was to stand alone new hardware I will have no issue.
Thanks for your help
You can't do it without any impact to replication, but you should be able to
with minimal impact.
Build out your new system and test thoroughly. Once you are confident that
the new system performs adequately, then drop your publication on the
production database and copy it (several options here for you) to the new
system.
You will have to redirect your clients to the new host name and reestablish
replication publication.
This should be doable during a reasonable off hours maintenance window.
Sincerely,
Anthony Thomas

<msnews.microsoft.com> wrote in message
news:ek7ECL6THHA.1552@.TK2MSFTNGP05.phx.gbl...
> My production system is on stand alone Server and it is a publisher on the
> replication system. I need to Migrate to a new hardware with fail over
> cluster and upgrade to SQL 2005 .
> I need to do that with out impacting replication. What is my options? Can
I
> do that Or not
> If this was to stand alone new hardware I will have no issue.
> Thanks for your help
>

Migration replication jobs

Hi all,
I will be migrating a production database to a new box. I will be using
detach and attach db method to migrate, but I forsee that my replication job
will break. Can anyone advise me on how to migrate the replication jobs over
to the new db? Here is the sencario:
DB-1 in Box A detach and move over to Box B to be reattached,
DB-1 in Box A has some publications and subscriptions, and the distribution
is in Box C.
Let say Box B will resume the ip of Box A but different hostname.
You will be unable to detach replicated databases. You have to script out
the publications, edit them for the new server, remove all job names, and
then drop the publications, unpublish the databases and detach them. Attach
them on the new server, run the publication script and do a non sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"YK" <YK@.discussions.microsoft.com> wrote in message
news:25A6D0F1-6C2C-4B95-84F5-C96B963335D0@.microsoft.com...
> Hi all,
> I will be migrating a production database to a new box. I will be using
> detach and attach db method to migrate, but I forsee that my replication
job
> will break. Can anyone advise me on how to migrate the replication jobs
over
> to the new db? Here is the sencario:
> DB-1 in Box A detach and move over to Box B to be reattached,
> DB-1 in Box A has some publications and subscriptions, and the
distribution
> is in Box C.
> Let say Box B will resume the ip of Box A but different hostname.
>

Migration from 2000 ro 2005 Cluster

Hey All,
We have SQL Server 2000 in our production environment and we have a
brand spanking new cluster that has more CPU power and RAM than our
current server. The issue I have is how to move the data. We have 10
Databases, most are small, but we have a few that are 5 to 9 GB and
one that is over 100 GB.
I've already loaded SQL Server 2005 SP2 on the cluster for testing
etc.
My goal is to reduce downtime, and my stress, when we go live with the
cluster. My fall back plan is, backup the databases, copy the backups
to the cluster and restore them on the cluster.
I had a thought of installing SQL Server 2000 on the clusters and log
ship the databases to the SQL 2000 instance on the cluster, since I
can't log ship from 2000 to 2005. Then when go live day comes, I just
need to restore the existing logs, recover the databases and detach
the databases from 2000, then attach the databases to the 2005. From
what I know this is doable, but has anyone done this or something
similar? Are there any gotchas?
Thanks
Dave Ott<DaveOtt26.2@.gmail.com> wrote in message
news:1172770995.057390.293120@.j27g2000cwj.googlegroups.com...
> Hey All,
> We have SQL Server 2000 in our production environment and we have a
> brand spanking new cluster that has more CPU power and RAM than our
> current server. The issue I have is how to move the data. We have 10
> Databases, most are small, but we have a few that are 5 to 9 GB and
> one that is over 100 GB.
> I've already loaded SQL Server 2005 SP2 on the cluster for testing
> etc.
> My goal is to reduce downtime, and my stress, when we go live with the
> cluster. My fall back plan is, backup the databases, copy the backups
> to the cluster and restore them on the cluster.
> I had a thought of installing SQL Server 2000 on the clusters and log
> ship the databases to the SQL 2000 instance on the cluster, since I
> can't log ship from 2000 to 2005.
You can't? Why not? (I'm 99% sure a you can restore a 2000 database with
NORECOVERY in 2005. You can't however go the other way).
I'd probably go this route, but do it manually.
I.e. backup the databases, restore to the 2005 cluster with NORECOVERY.
Take a logbackup, restore to 2005 with NORECOVERY (to take care of the lag
in the original backup/restore cycle).
Repeat until you know the next round of log backup/restores will be quick
and then schedule the downtime, do one last log backup/restore and then
switch over to the 2005 server.
(I'd actually practice this once with a test client and then redo the entire
process if possible.)
Script it all out so it's fast and few mistakes.

> Then when go live day comes, I just
> need to restore the existing logs, recover the databases and detach
> the databases from 2000, then attach the databases to the 2005. From
> what I know this is doable, but has anyone done this or something
> similar? Are there any gotchas?
Seems to me more complicated than it's worth.

> Thanks
> Dave Ott
>|||If you do it all by scripts, you can log ship from SQL 2000 to SQL 2005.
You cannot use the STANDBY option but NORECOVERY works just fine. There is
no need for a SQL 2000 instance cluttering things up.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Ozz1v$CXHHA.528@.TK2MSFTNGP03.phx.gbl...
> <DaveOtt26.2@.gmail.com> wrote in message
> news:1172770995.057390.293120@.j27g2000cwj.googlegroups.com...
> You can't? Why not? (I'm 99% sure a you can restore a 2000 database with
> NORECOVERY in 2005. You can't however go the other way).
> I'd probably go this route, but do it manually.
> I.e. backup the databases, restore to the 2005 cluster with NORECOVERY.
> Take a logbackup, restore to 2005 with NORECOVERY (to take care of the lag
> in the original backup/restore cycle).
> Repeat until you know the next round of log backup/restores will be quick
> and then schedule the downtime, do one last log backup/restore and then
> switch over to the 2005 server.
> (I'd actually practice this once with a test client and then redo the
> entire process if possible.)
> Script it all out so it's fast and few mistakes.
>
> Seems to me more complicated than it's worth.
>
>|||Geoff,
Once again you are there with the answer!
Thanks
Dave

Migration from 2000 ro 2005 Cluster

Hey All,
We have SQL Server 2000 in our production environment and we have a
brand spanking new cluster that has more CPU power and RAM than our
current server. The issue I have is how to move the data. We have 10
Databases, most are small, but we have a few that are 5 to 9 GB and
one that is over 100 GB.
I've already loaded SQL Server 2005 SP2 on the cluster for testing
etc.
My goal is to reduce downtime, and my stress, when we go live with the
cluster. My fall back plan is, backup the databases, copy the backups
to the cluster and restore them on the cluster.
I had a thought of installing SQL Server 2000 on the clusters and log
ship the databases to the SQL 2000 instance on the cluster, since I
can't log ship from 2000 to 2005. Then when go live day comes, I just
need to restore the existing logs, recover the databases and detach
the databases from 2000, then attach the databases to the 2005. From
what I know this is doable, but has anyone done this or something
similar? Are there any gotchas?
Thanks
Dave Ott<DaveOtt26.2@.gmail.com> wrote in message
news:1172770995.057390.293120@.j27g2000cwj.googlegroups.com...
> Hey All,
> We have SQL Server 2000 in our production environment and we have a
> brand spanking new cluster that has more CPU power and RAM than our
> current server. The issue I have is how to move the data. We have 10
> Databases, most are small, but we have a few that are 5 to 9 GB and
> one that is over 100 GB.
> I've already loaded SQL Server 2005 SP2 on the cluster for testing
> etc.
> My goal is to reduce downtime, and my stress, when we go live with the
> cluster. My fall back plan is, backup the databases, copy the backups
> to the cluster and restore them on the cluster.
> I had a thought of installing SQL Server 2000 on the clusters and log
> ship the databases to the SQL 2000 instance on the cluster, since I
> can't log ship from 2000 to 2005.
You can't? Why not? (I'm 99% sure a you can restore a 2000 database with
NORECOVERY in 2005. You can't however go the other way).
I'd probably go this route, but do it manually.
I.e. backup the databases, restore to the 2005 cluster with NORECOVERY.
Take a logbackup, restore to 2005 with NORECOVERY (to take care of the lag
in the original backup/restore cycle).
Repeat until you know the next round of log backup/restores will be quick
and then schedule the downtime, do one last log backup/restore and then
switch over to the 2005 server.
(I'd actually practice this once with a test client and then redo the entire
process if possible.)
Script it all out so it's fast and few mistakes.
> Then when go live day comes, I just
> need to restore the existing logs, recover the databases and detach
> the databases from 2000, then attach the databases to the 2005. From
> what I know this is doable, but has anyone done this or something
> similar? Are there any gotchas?
Seems to me more complicated than it's worth.
> Thanks
> Dave Ott
>|||If you do it all by scripts, you can log ship from SQL 2000 to SQL 2005.
You cannot use the STANDBY option but NORECOVERY works just fine. There is
no need for a SQL 2000 instance cluttering things up.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Ozz1v$CXHHA.528@.TK2MSFTNGP03.phx.gbl...
> <DaveOtt26.2@.gmail.com> wrote in message
> news:1172770995.057390.293120@.j27g2000cwj.googlegroups.com...
>> Hey All,
>> We have SQL Server 2000 in our production environment and we have a
>> brand spanking new cluster that has more CPU power and RAM than our
>> current server. The issue I have is how to move the data. We have 10
>> Databases, most are small, but we have a few that are 5 to 9 GB and
>> one that is over 100 GB.
>> I've already loaded SQL Server 2005 SP2 on the cluster for testing
>> etc.
>> My goal is to reduce downtime, and my stress, when we go live with the
>> cluster. My fall back plan is, backup the databases, copy the backups
>> to the cluster and restore them on the cluster.
>> I had a thought of installing SQL Server 2000 on the clusters and log
>> ship the databases to the SQL 2000 instance on the cluster, since I
>> can't log ship from 2000 to 2005.
> You can't? Why not? (I'm 99% sure a you can restore a 2000 database with
> NORECOVERY in 2005. You can't however go the other way).
> I'd probably go this route, but do it manually.
> I.e. backup the databases, restore to the 2005 cluster with NORECOVERY.
> Take a logbackup, restore to 2005 with NORECOVERY (to take care of the lag
> in the original backup/restore cycle).
> Repeat until you know the next round of log backup/restores will be quick
> and then schedule the downtime, do one last log backup/restore and then
> switch over to the 2005 server.
> (I'd actually practice this once with a test client and then redo the
> entire process if possible.)
> Script it all out so it's fast and few mistakes.
>
>> Then when go live day comes, I just
>> need to restore the existing logs, recover the databases and detach
>> the databases from 2000, then attach the databases to the 2005. From
>> what I know this is doable, but has anyone done this or something
>> similar? Are there any gotchas?
> Seems to me more complicated than it's worth.
>
>> Thanks
>> Dave Ott
>|||Geoff,
Once again you are there with the answer!
Thanks
Dave

Migrating to SQL Server 2000

Hi,
I am new to SQL Server and one of my Production Servers that has SQL Server
2000 running has been giving problems and I need to transfer all the
databases, logins, packages, Stored Procedures to the new server that will
again have SQL Server 2000.
What are the steps going to be for the migration? What's the testing process
for the new server? Thanks.
Hi
http://vyaskn.tripod.com/moving_sql_server.htm
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E45D1819-3FB6-4DC4-BB20-1D9D69AB9991@.microsoft.com...
> Hi,
> I am new to SQL Server and one of my Production Servers that has SQL
> Server
> 2000 running has been giving problems and I need to transfer all the
> databases, logins, packages, Stored Procedures to the new server that will
> again have SQL Server 2000.
> What are the steps going to be for the migration? What's the testing
> process
> for the new server? Thanks.

Migrating to SQL Server 2000

Hi,
I am new to SQL Server and one of my Production Servers that has SQL Server
2000 running has been giving problems and I need to transfer all the
databases, logins, packages, Stored Procedures to the new server that will
again have SQL Server 2000.
What are the steps going to be for the migration? What's the testing process
for the new server? Thanks.Hi
http://vyaskn.tripod.com/moving_sql_server.htm
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E45D1819-3FB6-4DC4-BB20-1D9D69AB9991@.microsoft.com...
> Hi,
> I am new to SQL Server and one of my Production Servers that has SQL
> Server
> 2000 running has been giving problems and I need to transfer all the
> databases, logins, packages, Stored Procedures to the new server that will
> again have SQL Server 2000.
> What are the steps going to be for the migration? What's the testing
> process
> for the new server? Thanks.

Migrating to SQL Server 2000

Hi,
I am new to SQL Server and one of my Production Servers that has SQL Server
2000 running has been giving problems and I need to transfer all the
databases, logins, packages, Stored Procedures to the new server that will
again have SQL Server 2000.
What are the steps going to be for the migration? What's the testing process
for the new server? Thanks.Hi
http://vyaskn.tripod.com/moving_sql_server.htm
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E45D1819-3FB6-4DC4-BB20-1D9D69AB9991@.microsoft.com...
> Hi,
> I am new to SQL Server and one of my Production Servers that has SQL
> Server
> 2000 running has been giving problems and I need to transfer all the
> databases, logins, packages, Stored Procedures to the new server that will
> again have SQL Server 2000.
> What are the steps going to be for the migration? What's the testing
> process
> for the new server? Thanks.