mirroring & backups

Hi,
I have a mirrored databased, which is in FULL recovery mode. I take log
backups every 1 hour and db backups every night. However, the log file keeps
increasing and due to mirroring I can’t shrink the log with shrinkfile. Is
this the recommended set-up for mirroring, am I trying to be too safe? Thanks.
Panos
You might want to try to backup more frequently. See if you have any open
transactions which might be causing log bloat.
DBCC Opentran
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
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:EDBAC239-0B9A-4A2F-BE3B-D5A2C60A03D0@.microsoft.com...
> Hi,
> I have a mirrored databased, which is in FULL recovery mode. I take log
> backups every 1 hour and db backups every night. However, the log file
> keeps
> increasing and due to mirroring I can't shrink the log with shrinkfile. Is
> this the recommended set-up for mirroring, am I trying to be too safe?
> Thanks.
> Panos
>
|||Hilary, are you talking about backing up the log or the database? or both?
There are no open transactions I've checked that before. I assume that when
the backup log happens there are no open transactions.
Do you know how many rows I should get when I do dbcc loginfo? I get 397, 6
are of status 2, 4 rows at the beginning and 2 close to the end. When I
backup manually it goes to 1 close to the end. but always have at least one
row with status 2 close to the end. Thanks.
Panos.
"Hilary Cotter" wrote:

> You might want to try to backup more frequently. See if you have any open
> transactions which might be causing log bloat.
> DBCC Opentran
> --
> 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
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:EDBAC239-0B9A-4A2F-BE3B-D5A2C60A03D0@.microsoft.com...
>
>
|||OK that seems to work fine. Even though my transaction log has stayed the
same size it hasn't increased since I've increased the frequency of the Log
backups. This is what I wanted.
"Panos Stavroulis." wrote:
[vbcol=seagreen]
> Hilary, are you talking about backing up the log or the database? or both?
> There are no open transactions I've checked that before. I assume that when
> the backup log happens there are no open transactions.
> Do you know how many rows I should get when I do dbcc loginfo? I get 397, 6
> are of status 2, 4 rows at the beginning and 2 close to the end. When I
> backup manually it goes to 1 close to the end. but always have at least one
> row with status 2 close to the end. Thanks.
> Panos.
> "Hilary Cotter" wrote:
sql

Mirroring - High Safety Mode

I'm testing DB mirroring in high safety mode without a witness server. When
I try to simulate a complete failure of the principal server (I stop the
principal SQL Server service) I can't get the mirrored DB's to come online.
The status is stuck in "mirror, disconnected/in recovery"
I don't have any errors in the event log other than an event ID 1474, SQL
communication error with the principal server.
Do I need a witness server ? CAN I GET A WITNESS ! ?
Nick
I added a witness server and this resolved my problem.
"Nick" <larosan@.yahoo.com> wrote in message
news:udadnef-_oVQGt3bnZ2dnUVZ_uKknZ2d@.comcast.com...
> I'm testing DB mirroring in high safety mode without a witness server.
> When I try to simulate a complete failure of the principal server (I stop
> the principal SQL Server service) I can't get the mirrored DB's to come
> online. The status is stuck in "mirror, disconnected/in recovery"
> I don't have any errors in the event log other than an event ID 1474, SQL
> communication error with the principal server.
> Do I need a witness server ? CAN I GET A WITNESS ! ?
> Nick
>
>
>
sql

Mirroring - Asynchronous mode not available

Hi,
I've configured mirroring on the database and selected the high safety
without automatic failover. It all seems fine.
Now the problem is in the mirroring screen I can't select the High
performance mode (Asynchronous ). I would like to test this option as well. I
know I can't select the automatic failover option cause I don't have a
witness set-up but why I can't select Asynchronous mode? Any ideas? Thanks.
Panos.
Panos.
Hello Panos,
It is only available in Enterprise edition or Developer edition
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
> I've configured mirroring on the database and selected the high safety
> without automatic failover. It all seems fine.
> Now the problem is in the mirroring screen I can't select the High
> performance mode (Asynchronous ). I would like to test this option as
> well. I know I can't select the automatic failover option cause I
> don't have a witness set-up but why I can't select Asynchronous mode?
> Any ideas? Thanks.
> Panos.
> Panos.
>

Mirror Operating Mode change problem

I am unable to change the mirror operating mode on a test database mirror I have set up. I installed (from MSDN) the RTM SQL 2005 Std Edition and applied SP1, then set up a mirror, which works fine but only on High Safety mode. I want to use High Performance mode, but the operation mode radio buttons are greyed out for me in the mirror properties page. Also, I tried running:

alter database TestDB set safety off

and recieve the error:

Msg 1473, Level 16, State 6, Line 1
This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed.

Are the various mirror modes not supported on the Std edition or am I overlooking something?

Thanks.

From the db engine server properties:
Product: Microsoft SQL Server Standard Edition
OS: Microsoft Windows NT 5.2 (3790)
Platform: NT INTEL X86
Version: 9.00.2047.00

In STD saftey can only be FULL.

Thanks,

Mark

|||I was hoping this wasn't going to be the case when mirroring became fully supported in sp1. To me it doesn't make sense to give the standard edition only synchronous which requires far more resources and infrastructure. Even microsoft's own best practices suggest you should try mirroring in asynchronous form in a production environment before you transition to synchronous to make sure your infrastructure can support it. For us that just killed 90% of the scenarios we were considering using mirroring for.|||

Asynchronous mirroring is fully supported in Enterprise Edition.

Simply upgrade your installations to Enterprise Edition, and you will have your scenarios back. Yes, it costs more, but as you have found out that asynchronous mirroring has a lot more benefits as far as disaster recovery and high performance workloads. It was natural to put it in a higher SKU.

Thanks,

Mark

|||Unfortunately that would cost a few tens of thousands of dollars. I guess my point was that if you're running the kinds of machines that are needed for synchronous mirroring chances are you won't be using standard edition anyway. I'm not trying to criticize, just letting you know that cost-benefit wise it's not even close for us and I would venture to guess for a lot of other people.|||

Understood. It's always about money, isn't it?

It all depends on the workload actually. If you have a moderate workload, and the systems are right next to each other, then synchronous mirroring will probably be OK. I have plenty of customers running like that now; in fact over half of my internal customers were synchronous at SP1 ship, and not all of them had huge powerful servers.

Thanks,

Mark

|||

Lol, yeah that's why I hate talking to hardware vendors, they only have one answer to any question.

I may look into syncrhonous mirroring more. I tried it with 2 of our test servers and it was less than stellar but I admit I didn't do any full blown application testing. Thanks for the feedback!

sql