Paul Randal's questions

When using multi-instance failover clustering (used to be called active-active) in SQL Server 2005 and 2008, I'd like to know what you configure the min and max server memory to.

For a two-node multi-instance cluster (with two instances, one on each node), the best-practice is to configure min server memory to 40-45% on each instance so that when the failover occurs the two instances don't end up with radically unbalanced amounts of memory, because the new instance may apply undue memory pressure to the already-running instance.

Although this is a best practice, I'd like to hear from people who have this situation - what do you configure the memory settings for the various instances? Do you make them equal or do you prioritize by the workload running on each instance? Do you make use of WSRM? Do you dynamically alter the memory settings based on post-failover cluster configuration?

Thanks!

PS I don't need info on how memory management works, or why the best practices exist - I know all that - I'm interested in what people are doing for real.

PPS Note, I'm not interested in SQL 2000 - it's memory manager didn't respond to that kind of memory pressure so best practice was to set max server memory to 50% each instance. Yuk.

When you combine failover clustering and database mirroring in SQL Server, you need to change the mirroring partner timeout value so that the local cluster gets a chance to fail over before database mirroring fails over. I'm curious as to what people are doing when combining these technologies - I teach various HA classes and this is not too common a combination.

Here are my questions IF you are using failover clustering and database mirroring combined. If you could answer them all in each response, that would be very useful to me. I don't need an explanation of why things need to be changed or how the technologies work - I used to own them both when at Microsoft - I'm interested in industry practices now the possibility of marrying them has been out there for 4 years.

1) how long, on average, does it take for a clustered SQL Server instance to fail over for you? (I know it depends on how much crash recovery is required, but what's an average for you?)

2) for these same instances, what do you set the mirroring partner timeout to?

3) are you comfortable with the fact that a real cluster outage could occur and it may be quite a while until mirroring notices that the failure has occured because you've bumped the mirroring partner timeout up?

Thanks for all responses!

I'm onsite at a customer who's experienced a weird problem with HP PolyServe and SQL Server 2005 and I'm wondering whether anyone else has hit it.

The problem is that with default settings, a DBCC CHECKDB will fail on a database underpinned by PolyServe managed storage. This is because DBCC CHECKDB (by default) uses a hidden database snapshot which it stores in NTFS alternate streams on the existing database data files, and PolyServe by default doesn't support NTFS alternate streams.

Now - I know why this happens (I wrote DBCC CHECKDB) and I know how to work around it (create your own snapshot and check that, or restore a backup and check that, or use WITH TABLOCK as a last resort) - so I don't want answers that explain these things.

What I'd love to know is the following:

• why does PolyServe not support NTFS alternate streams by default?
• what functionality do you lose in PolyServe by enabling NTFS alternate stream support?
• has anyone else experienced this?

Edit: Btw - the last upgrade to PolyServe 3.6.1 was what turned the option OFF by default, which caused the DBCC CHECKDB failure. We're trying to figure out why it went from ON to OFF by default.

I tried to find some info on this online before asking here.

Thanks!

PS If you want background on why DBCC CHECKDB uses a snapshot, see my long blog post on CHECKDB internals at CHECKDB From Every Angle: Complete description of all CHECKDB stages.

And yet one more SharePoint question from MCM yesterday.

What kind of HA do you provision for your SharePoint search databases? I heard from one admin of a very, very large SharePoint farm that they don't have a redundant copy of the search database and they repopulate it if it gets lost. The problem is that to recrawl their 43 million items takes 8 weeks.

I'm not a SharePoint expert, but I'm guessing that without a fully functional search database, functionality is going to be degraded. Does 8 weeks sound right for this? That seems astronomically slow. What's your experience?

Thanks!

Another question from talking to the SharePoint gurus while teaching MCM yesterday. The SharePoint guidelines are that content databases above 100GB are not supported. Without getting into the reasons behind those guidelines, I'm interested to hear about content databases larger than 100GB and your experiences with them (primarily around performance, disaster recovery, and HA provisioning).

How far have you managed to push your SharePoint installation? I've heard second-hand stories of > 1TB content databases, but I'd like to hear from the SharePoint admins themselves.

Thanks for any info you have.

This isn't a how-should-I-do-this question, just to set the stage. It's a what has your experience been? Please read through the whole question before quickly replying.

I spent the day yesterday teaching the current round of SharePoint MCM (Microsoft Certified Masters - see here) students all about high-availability technologies in SQL Server, plus how the SQL log/recovery/backup/restore work. This is really important as under every Enterprise-class MOSS installation is an Entreprise-class SQL Server hidden away, usually without a DBA. Kimberly teaches them a day of database maintenance on Friday (a kind of narrowed down version of the first week of the SQL MCM which we teach).

We were discussing the possibilities around using database mirroring to provide high-availability to SharePoint databases, and the relative pros and cons. Now, I know database mirroring to it's lowest internal depths, as I used to own it while at Microsoft, so no need to point out behaviors and idiosyncracies in your replies. I also know the various caveats and guidelines in the mirroring whitepaper from the SharePoint folks, and yes, they are just generalized guidelines not hard-and-fast rules.

My question is this: I'd like to hear from anyone who's implemented database mirroring for SharePoint and whether you found it worked for you, or you crashed and burned. In particular, how did you find the failover behavior worked for you? Did you end up with some database principals on one server and some on the other, effectively splitting your farm and making it unusable until manual intervention failed everything over to one server? Did you use it for local or remote HA? And so on.

Any replies will be gratefully received and will help broaden the knowledge base around marrying these two technologies, and I'll feed stories back into the SharePoint product group, and the future MCM rotations I teach as well.

Thanks!

[Edit: PS I'll put together a blog post about experiences and guidelines to this over the weekend too]

ok - this is beyond my expertise so I need to ask here.

I have a bunch of test hardware that I'm configuring and I'm running into some network problems where I'm not an expert (talk SQL Server to me!).

Here's a simplified description of my network topology:

• network switch #1, connected to network switch #2, and setup to know that the internet gateway is through switch #2
• network switch #2, connected to the hub/gateway
• hub which is a DHCP server, and gateway to the outside world

The MD3000i iSCSI arrays are connected to switch 1. If I power-cycle one, it doesn't pickup an IP address. If I change the hub to be connected to network switch #1 and power-cycle the array again it gets an IP address through DHCP. I'm guessing there's something going on such that the broadcast 'give me a DHCP address' from the iSCSI array isn't being propagated through to switch #2 and to the hub/DHCP server.

Once the array gets an IP address, I need to remove the array and then automatically find it again using the DELL Modular Disk Storage Manager. Should I give them arrays static IPs (on the local network)?

Do I need to have a connection from each switch to the hub/DHCP server? Or am I missing something? I'll provide any info you want to hepl with this as long as I'm able.

Thanks!

PS Tagged with 'sqlserver' only so that the folks I know can follow this question.

As we all know, the cluster setup, maintenance, and underlying mechanisms have been changed extensively for SQL 2008 compared to 2005/2000. Although this fell under the Storage Engine group I was responsible for at Microsoft in 2005-2008, I wasn't closely involved and I haven't set up a cluster like this yet. I've got a bunch of test hardware and this is now on my list to do.

The last few times I've taught an overview-of-HA-technologies class for Microsoft recently, I've heard a mixture of experiences, some good, some bad, when setting up a SQL 2008 on Win 2008 cluster - but I've never had time to make extensive notes on what to watch out for.

So my question is this - IF you've setup this configuration, what issues did you run into and how did you overcome them?

This will help save me a lot of time, and I'll publicize the findings on my blog too.

Many thanks.

PS I wanted to make this a 'what issues have you hit' title, but I got the 'subjective question' warning.

I'm starting to see clients with hundreds of terabytes of data (in SQL Server installations). As the total volume of data in some enterprises approaches meaningful fractions of a petabyte, I'd like to canvas the collective knowledge base out there to see what people dealing with that magnitude of data are doing to safeguard it.

The obvious issue is that storing multiple backups of that much data is prohibitively expensive, using enterprise-class storage, heck, even just RAID-5.

Options I see are as follows:

1. Create a mirror copy of the data in another data-center, and continually ship differences to it (using whatever mechanism is available for your data source - e.g. log-shipping or database mirroring with SQL Server)
2. Take regular backups using a hefty compression algorithm (probably only suitable if the data lends itself well to being heavily compressed)
3. Take piecemeal backups of the critical/changing parts of the data.
4. Don't backup the data and trust the corruption-gods.

I'm seeing option #4 being adopted as the default, and as an HA/DR expert it's really scary, but what do I advise as an alternative? I think #1 is the best approach, but "I don't think so" is the usual answer when any alternatives apart from #4 and possibly #3 are suggested.

Now, of course it depends on the change-rate and criticality of the data. No need to answer with that as I used to be responsible for all the HA features of SQL Server while I worked at Microsoft so I'm well-versed in the 'it depends' arguments - that's my catch-phrase :-)

I'd be very interested to hear of any alternatives I've missed, or to hear that everyone else is in the same boat and there's no realistic alternative to spending lots of money on more storage.

Thanks in advance - due credit will be given to all well thought-out and expressed answers.