Is SQL2005 Database Mirroring ready?

The last news I read about SQL2005 is that Database Mirroring was delayed
for further testing. Is now available? If not, when it will be?
Thanks!
Yes and No.
It is available by setting a trace flag on SQL Server startup. Database
Mirroring is not supported for production environments. The primary reason
for not including it as a fully supported feature in the initial release was
to test how it operates interactively with many of the other new SQL 2005
features and to measure its impact in more real-world situations. If you
want to set up Mirroring for testing and training, go ahead. I wouldn't use
it for any production system.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Kirsten" <noreply@.nospam.com> wrote in message
news:u93LKxJEGHA.3920@.tk2msftngp13.phx.gbl...
> The last news I read about SQL2005 is that Database Mirroring was delayed
> for further testing. Is now available? If not, when it will be?
> Thanks!
>
|||You have to turn on trace flag 1400 fr each SQL Server instance that will
participate in mirroring.
It is in the product and can be used. It is not currently supported,
because the dev team felt they didn't have sufficient customer testing of
the feature prior to launch. That is being addressed right now. I have
been using Database Mirroring and literally beating it to death for more
than a year now. I've come across some interesting issues during the betas
in terms of performance degradation in HA mode along with some inconsistent
behaviour in the GUI tools and a couple of bugs related to things which
would be extraordinarily rare in a production deployment, but will happen in
a demo scenario.
All of the issues that I've come across have been fixed as of the RTM bits.
It's been a few months since I've come up with any new issues. I trust the
feature completely, even if it isn't supported. I've deployed it into
production environments and wouldn't hesitate to do so again.
The biggest thing you need to do is to completely understand the feature and
what it will do in various failure scenarios. The best resource can be
found here:
http://www.microsoft.com/technet/pro.../dbmirror.mspx
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Kirsten" <noreply@.nospam.com> wrote in message
news:u93LKxJEGHA.3920@.tk2msftngp13.phx.gbl...
> The last news I read about SQL2005 is that Database Mirroring was delayed
> for further testing. Is now available? If not, when it will be?
> Thanks!
>
|||Do you know when it will be fully supported? Maybe SP1?
Thanks.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> escribi en el mensaje
news:u0O7ZAKEGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Yes and No.
> It is available by setting a trace flag on SQL Server startup. Database
> Mirroring is not supported for production environments. The primary
> reason for not including it as a fully supported feature in the initial
> release was to test how it operates interactively with many of the other
> new SQL 2005 features and to measure its impact in more real-world
> situations. If you want to set up Mirroring for testing and training, go
> ahead. I wouldn't use it for any production system.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Kirsten" <noreply@.nospam.com> wrote in message
> news:u93LKxJEGHA.3920@.tk2msftngp13.phx.gbl...
>
|||Thanks Michael for your answer.
Can you tell some examples of this "behaviour in the GUI tools and a couple
of bugs related to things which would be extraordinarily rare in a
production deployment"?
I've been using SQL2000 for many years and Database mirroring is THE THING I
was needing, that's why it's so important to use this feature as soon as
possible. Last year I've been reading a lot about possible scenarios,
although I still be some questions (that I guess will be answers once I can
test this new version of SQL Server).
Cheers.
"Michael Hotek" <mike@.solidqualitylearning.com> escribi en el mensaje
news:e6wGXrKEGHA.532@.TK2MSFTNGP15.phx.gbl...
> You have to turn on trace flag 1400 fr each SQL Server instance that will
> participate in mirroring.
> It is in the product and can be used. It is not currently supported,
> because the dev team felt they didn't have sufficient customer testing of
> the feature prior to launch. That is being addressed right now. I have
> been using Database Mirroring and literally beating it to death for more
> than a year now. I've come across some interesting issues during the
> betas in terms of performance degradation in HA mode along with some
> inconsistent behaviour in the GUI tools and a couple of bugs related to
> things which would be extraordinarily rare in a production deployment, but
> will happen in a demo scenario.
> All of the issues that I've come across have been fixed as of the RTM
> bits. It's been a few months since I've come up with any new issues. I
> trust the feature completely, even if it isn't supported. I've deployed
> it into production environments and wouldn't hesitate to do so again.
> The biggest thing you need to do is to completely understand the feature
> and what it will do in various failure scenarios. The best resource can
> be found here:
> http://www.microsoft.com/technet/pro.../dbmirror.mspx
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Kirsten" <noreply@.nospam.com> wrote in message
> news:u93LKxJEGHA.3920@.tk2msftngp13.phx.gbl...
>
|||The examples are irrelevant. They no longer exist and were fixed in the
Beta3 cycle. (The set of CTPs released after Beta2.) You could try to
force the GUI into allowing you to create an impossible configuration, but
since the code which allows you to do that doesn't even exist anymore, there
isn't any possibility of causing it in the first place. The same goes for
initiating mirroring and then failing it over from one instance to the next
repeatedly without ever issuing transactions. The issue simply does not
exist anymore. There are dozens of bugs that came up with mirroring during
the beta cycles, all of which were isolated and fixed. That's why we have
betas and lots of people get to test the products doing things that the dev
team couldn't even concieve of people trying.
I haven't found a nw bug in Database Mirroring in over 2 months and I've run
literally tens of thousands of test scripts at it.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Kirsten" <noreply@.nospam.com> wrote in message
news:u17jEeSEGHA.1384@.TK2MSFTNGP11.phx.gbl...
> Thanks Michael for your answer.
> Can you tell some examples of this "behaviour in the GUI tools and a
> couple of bugs related to things which would be extraordinarily rare in a
> production deployment"?
> I've been using SQL2000 for many years and Database mirroring is THE THING
> I was needing, that's why it's so important to use this feature as soon as
> possible. Last year I've been reading a lot about possible scenarios,
> although I still be some questions (that I guess will be answers once I
> can test this new version of SQL Server).
> Cheers.
> "Michael Hotek" <mike@.solidqualitylearning.com> escribi en el mensaje
> news:e6wGXrKEGHA.532@.TK2MSFTNGP15.phx.gbl...
>
|||Great post Michael!
We are testing db mirroring right now and so far, so no problems at all.
But I do have a question that maybe you have an answer to...
We have 2 SQL servers that we want setup as failover servers to our Primary.
One is in the same site as our primary and we have that setup in a mirror.
Works great. Our other SQL server is in a branch office connected via VPN.
The documention clearly explains how db mirroring and log shipping can work
together. I have also tested log shipping manual failover and it works great
too.
Now I'm trying to combine the two and having problems. I have my mirror
setup properly and my log shipping secondary too. But I don't quite
understand how the mirror database can take over the role as the log shipper.
You can't configure the mirror while it is in restoring mode. If I failover
to the mirror and try to set itself up as the log shipper, I get an error
about duplicate data in a table on the log shipping machine.
Do you have any experience combining log shipping and mirroring? Can this
even be done like I have described? The BOL shows that it can, but I can't
seem to figure it out.
Thanks for any help you can offer!
Jay
"Michael Hotek" wrote:

> The examples are irrelevant. They no longer exist and were fixed in the
> Beta3 cycle. (The set of CTPs released after Beta2.) You could try to
> force the GUI into allowing you to create an impossible configuration, but
> since the code which allows you to do that doesn't even exist anymore, there
> isn't any possibility of causing it in the first place. The same goes for
> initiating mirroring and then failing it over from one instance to the next
> repeatedly without ever issuing transactions. The issue simply does not
> exist anymore. There are dozens of bugs that came up with mirroring during
> the beta cycles, all of which were isolated and fixed. That's why we have
> betas and lots of people get to test the products doing things that the dev
> team couldn't even concieve of people trying.
> I haven't found a nw bug in Database Mirroring in over 2 months and I've run
> literally tens of thousands of test scripts at it.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Kirsten" <noreply@.nospam.com> wrote in message
> news:u17jEeSEGHA.1384@.TK2MSFTNGP11.phx.gbl...
>
>
|||Yes, it can. At least in theory. The theory is basically this. The
principal and mirror are EXACT duplicates. That means the LSN for every
transaction issued is EXACTLY the same on both principal and mirror and will
ALWAYS be synchronized. In order to restore a transaction log, it must meet
some basic requirements - it is in fact the same database and the
transaction log backup contains the next LSN in the sequence. (This is VERY
simplistic.)
So, you now look at mirroring with log shipping. Log shipping doesn't
actually link 2 servers together. You are doing nothing more than backing
up a T-log, copying it, and restoring it elsewhere. There is no actual
requirement that each subsequent T-Log being applied had to actually come
from the same PHYSICAL database. This is where mirroring comes into play
with this. From the perspective of log shipping, the database on the
principal and the database on the mirror are exact equivalents. That means
you can take logbackup1 from the principal and apply it to the secondary,
failover to the mirror, take logbackup2 on that database, apply it to the
secondary, etc. The secondary in log shipping doesn't know the difference
and it all works.
Now the trick is this. The secondary normally has the copy and restore
jobs. If all log backups are written to a single directory, regardless of
which server in the mirroring pair generated the log backup, log shipping
simply doesn't know or care. It simply sees a log backup that needs to be
applied and successfully applies it. That takes us to the log backup job.
If you simply create a log backup job against the mirror that writes all
backups to the log shipping share, you have setup an infrastructure that
will work. Since the mirror database is unavailable, this backup job will
fail, but you really don't care about that. If you fail the mirror over,
this backup job will all of a sudden start executing and then the copy job
will start picking up log backups and applying them without having the
slightest clue that you just had an entire instance go offline. (I've
gotten this to work.)
The BOL article talks about to get this to work within the log shipping
procedures. So, what you need to do is to configure 2 primaries for the
secondary - 1 primary is the principal and the other primary is the mirror.
After doing that, all of the failover/failback procedures in log shipping
will work even if you are failing the mirror over from one instance to the
other. You have to configure this using code since there isn't an option in
the GUI for it. (I have not been able to get this to work though. It would
have been REALLY nice if the article in BOL would have given a code sample
instead of just talking about the theory.)
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jay Becker" <JayBecker@.discussions.microsoft.com> wrote in message
news:5567768A-7149-4587-B537-FAEDBBFBD7A0@.microsoft.com...[vbcol=seagreen]
> Great post Michael!
> We are testing db mirroring right now and so far, so no problems at all.
> But I do have a question that maybe you have an answer to...
> We have 2 SQL servers that we want setup as failover servers to our
> Primary.
> One is in the same site as our primary and we have that setup in a mirror.
> Works great. Our other SQL server is in a branch office connected via
> VPN.
> The documention clearly explains how db mirroring and log shipping can
> work
> together. I have also tested log shipping manual failover and it works
> great
> too.
> Now I'm trying to combine the two and having problems. I have my mirror
> setup properly and my log shipping secondary too. But I don't quite
> understand how the mirror database can take over the role as the log
> shipper.
> You can't configure the mirror while it is in restoring mode. If I
> failover
> to the mirror and try to set itself up as the log shipper, I get an error
> about duplicate data in a table on the log shipping machine.
> Do you have any experience combining log shipping and mirroring? Can this
> even be done like I have described? The BOL shows that it can, but I
> can't
> seem to figure it out.
> Thanks for any help you can offer!
>
> Jay
>
> "Michael Hotek" wrote:
|||Thanks Michael.
Since you described the concept of how this all works, it all makes sense to
me know and I have it all working now. Basically, I had to fail the mirror
over, then setup log shipping on the mirror, but not define the copy/restore
details, only the backup. That works great.
One more question for you. If I do fail over to my log shipping machine (by
restoring the latest transaction log in RECOVERY mode), how do I then fail
back to my primary database? I tried to setup log shipping back to my
primary and then restoring the chain, but I am unable to. I know I'm being
vague here. Guess I'm just asking on a conceptual level, how this would work.
Thanks again for your info on Mirroring/Logshipping interaction! I really
appreciate it!
Jay
"Michael Hotek" wrote:

> Yes, it can. At least in theory. The theory is basically this. The
> principal and mirror are EXACT duplicates. That means the LSN for every
> transaction issued is EXACTLY the same on both principal and mirror and will
> ALWAYS be synchronized. In order to restore a transaction log, it must meet
> some basic requirements - it is in fact the same database and the
> transaction log backup contains the next LSN in the sequence. (This is VERY
> simplistic.)
> So, you now look at mirroring with log shipping. Log shipping doesn't
> actually link 2 servers together. You are doing nothing more than backing
> up a T-log, copying it, and restoring it elsewhere. There is no actual
> requirement that each subsequent T-Log being applied had to actually come
> from the same PHYSICAL database. This is where mirroring comes into play
> with this. From the perspective of log shipping, the database on the
> principal and the database on the mirror are exact equivalents. That means
> you can take logbackup1 from the principal and apply it to the secondary,
> failover to the mirror, take logbackup2 on that database, apply it to the
> secondary, etc. The secondary in log shipping doesn't know the difference
> and it all works.
> Now the trick is this. The secondary normally has the copy and restore
> jobs. If all log backups are written to a single directory, regardless of
> which server in the mirroring pair generated the log backup, log shipping
> simply doesn't know or care. It simply sees a log backup that needs to be
> applied and successfully applies it. That takes us to the log backup job.
> If you simply create a log backup job against the mirror that writes all
> backups to the log shipping share, you have setup an infrastructure that
> will work. Since the mirror database is unavailable, this backup job will
> fail, but you really don't care about that. If you fail the mirror over,
> this backup job will all of a sudden start executing and then the copy job
> will start picking up log backups and applying them without having the
> slightest clue that you just had an entire instance go offline. (I've
> gotten this to work.)
> The BOL article talks about to get this to work within the log shipping
> procedures. So, what you need to do is to configure 2 primaries for the
> secondary - 1 primary is the principal and the other primary is the mirror.
> After doing that, all of the failover/failback procedures in log shipping
> will work even if you are failing the mirror over from one instance to the
> other. You have to configure this using code since there isn't an option in
> the GUI for it. (I have not been able to get this to work though. It would
> have been REALLY nice if the article in BOL would have given a code sample
> instead of just talking about the theory.)
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Jay Becker" <JayBecker@.discussions.microsoft.com> wrote in message
> news:5567768A-7149-4587-B537-FAEDBBFBD7A0@.microsoft.com...
>
>
|||It works just like it does for failing over and failing back within log
shipping. BOL outlines the exact steps you have to do. These need to be
performed before you try to initiate the failover to the log shipping
secondary.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jay Becker" <JayBecker@.discussions.microsoft.com> wrote in message
news:89C2869F-399B-44A3-A817-C7F835129CFD@.microsoft.com...[vbcol=seagreen]
> Thanks Michael.
> Since you described the concept of how this all works, it all makes sense
> to
> me know and I have it all working now. Basically, I had to fail the
> mirror
> over, then setup log shipping on the mirror, but not define the
> copy/restore
> details, only the backup. That works great.
> One more question for you. If I do fail over to my log shipping machine
> (by
> restoring the latest transaction log in RECOVERY mode), how do I then fail
> back to my primary database? I tried to setup log shipping back to my
> primary and then restoring the chain, but I am unable to. I know I'm
> being
> vague here. Guess I'm just asking on a conceptual level, how this would
> work.
> Thanks again for your info on Mirroring/Logshipping interaction! I really
> appreciate it!
>
> Jay
> "Michael Hotek" wrote: