Wednesday, March 28, 2012

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

No comments:

Post a Comment