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

MIRROR database questions

Hello all. I was reading an article by Rich Heiges called Mirroring - A New Look at HA. It left me with 2 questions

(A) Can I change the operating modes of the principal and mirror roles on the fly, or do I have to stop the MirROR process?

(B) It said that MIRROR supports 32-bit to 64-bit H/W interoperability -- but will it support interoperability between 32-bit and 64-bit instances of the database?

Thanks!

For (A), you meant role-swithing (i.e. making mirror the principal and vice-versa). You can do manual fail over but this requires you to suspend mirroring. Active transactions are rolled back but connection to the new principal are established automatically. And finally, you will need to re-establish mirroring.

For (B), I think the answer is yes, but it needs to be confirmed.

thanks

|||(A) yes you can change all operating modes on the fly.

(B) there is no such thing as a 32-bit or a 64-bit database. the file formats are exactly the same, only the in memory operations are different. of course, if you require 64 bit address space to satisfy your database performance needs, mirroring to a 32 bit box probably isn't going to be a good idea, because your performance will not be very good after a failover.

thanks,
mark|||Thanks for the answer. I get (B). To be clear on (A), I don't mean changing the roles of the principal, I meant changing the saftey level and protection levels on the fly, or do I have to stop the process and restrat with a different setting?

Of course, even if I stop MIRROR, I'll assume the database doesn't need to go down to make the change?

Thanks..|||Not sure if I messed up my reply post -- but I meant changing the mode of the operating, not the roles, on the fly. So changing the protection level without stopping the MIRROR process. But if I did need to stop it, I don't need to take a database outage, or do I?|||

you can change the protection levels without stopping. in previous versions of db mirroring, the June CTP and earlier, i think, changing the safety level, or operating mode caused the database to be restarted, which would close all connections. we have since fixed that.

|||Thanks - -but just to clarify you mean without stopping the database right? I have to break the MIRROR pair and they will perform catchup when I bring them back together? (I should have been more clear in the question - thanks)|||you should be able to change the SAFETY level without stopping the database mirroring session or the restarting the database itself.