Can 2005 mirroring be temporarily 'turned of' programatically?

In 2005 there is a new'ish' kind of replication called Mirroring which
is only available in SQL 2005 sp1. When you create a mirrored database
on another server, the 'Principal Database' stays online, while the
'mirrored database' goes into a constant state of restoring. My
question is can 2005 mirroring be temporarily 'turned of'
programmatically? I have a situation where the developers do a BCP of
a large amount of data to a 'staging server' from their 'online
processor' box. For various reasons, they tell me it is to difficult
to do the bcp to both the staging and reporting server. The reporting
server is for Business Objects reporting which affords very little
control over the size and scope of the queries submitted. As a result,
Disk I/O on the reporting server during the day can be extreme. I was
considering creating a mirror between the Staging and Reporting server
database and just allow the BCP to continue on the Staging Server.
Then, after the mirror is completely synchronized, I would turn off the
'mirroring' and use the mirror for reporting. Then, near the end of
the day, I would re-create the link between the Principal and the
Mirror and let the BCP process start again.
How feasible is this?I don't think that you want to create a mirror between staging and
reporting.
As you mentioned, when you are using Mirroring the mirror is unavailable.
How are you going to report from your reporting server if Business Objects
cannot connect to the Mirror server.
You might want to look at log shipping, replication, DTS (SSIS), or a simple
backup and restore. These might be more suitable for your purposes.
Keith Kratochvil
"togbabe" <togbabe@.yahoo.co.uk> wrote in message
news:1154931847.145022.63740@.p79g2000cwp.googlegroups.com...
> In 2005 there is a new'ish' kind of replication called Mirroring which
> is only available in SQL 2005 sp1. When you create a mirrored database
> on another server, the 'Principal Database' stays online, while the
> 'mirrored database' goes into a constant state of restoring. My
> question is can 2005 mirroring be temporarily 'turned of'
> programmatically? I have a situation where the developers do a BCP of
> a large amount of data to a 'staging server' from their 'online
> processor' box. For various reasons, they tell me it is to difficult
> to do the bcp to both the staging and reporting server. The reporting
> server is for Business Objects reporting which affords very little
> control over the size and scope of the queries submitted. As a result,
> Disk I/O on the reporting server during the day can be extreme. I was
> considering creating a mirror between the Staging and Reporting server
> database and just allow the BCP to continue on the Staging Server.
> Then, after the mirror is completely synchronized, I would turn off the
> 'mirroring' and use the mirror for reporting. Then, near the end of
> the day, I would re-create the link between the Principal and the
> Mirror and let the BCP process start again.
> How feasible is this?
>|||Hi
There is a section on pausing and resuming database mirroring in Books
Online also see http://msdn2.microsoft.com/en-us/library/ms175539.aspx You
should also
John
"togbabe" wrote:

> In 2005 there is a new'ish' kind of replication called Mirroring which
> is only available in SQL 2005 sp1. When you create a mirrored database
> on another server, the 'Principal Database' stays online, while the
> 'mirrored database' goes into a constant state of restoring. My
> question is can 2005 mirroring be temporarily 'turned of'
> programmatically? I have a situation where the developers do a BCP of
> a large amount of data to a 'staging server' from their 'online
> processor' box. For various reasons, they tell me it is to difficult
> to do the bcp to both the staging and reporting server. The reporting
> server is for Business Objects reporting which affords very little
> control over the size and scope of the queries submitted. As a result,
> Disk I/O on the reporting server during the day can be extreme. I was
> considering creating a mirror between the Staging and Reporting server
> database and just allow the BCP to continue on the Staging Server.
> Then, after the mirror is completely synchronized, I would turn off the
> 'mirroring' and use the mirror for reporting. Then, near the end of
> the day, I would re-create the link between the Principal and the
> Mirror and let the BCP process start again.
> How feasible is this?
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uR%237WbiuGHA.4336@.TK2MSFTNGP06.phx.gbl...
> I don't think that you want to create a mirror between staging and
> reporting.
> As you mentioned, when you are using Mirroring the mirror is unavailable.
> How are you going to report from your reporting server if Business Objects
> cannot connect to the Mirror server.
> You might want to look at log shipping, replication, DTS (SSIS), or a
simple
> backup and restore. These might be more suitable for your purposes.
I second Keith's recommendation of a simple backup & restore. It works well
and it is too basic to break often.
Jonathan|||I might have found a solution to this guys. What I did was set up
'2005 mirroring' between the two servers. Then I can programmatically
take a 'snapshot' of the 'perpetually restoring mirror' and report from
the snapshot. At the end of the 'reporting day,' I can delete the
snapshot. This appears to work really well.
Jonathan Roberts wrote:
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uR%237WbiuGHA.4336@.TK2MSFTNGP06.phx.gbl...
> simple
> I second Keith's recommendation of a simple backup & restore. It works we
ll
> and it is too basic to break often.
> Jonathan