The
reason the language is vague is that there is no hard and fast rule. It's a
tradeoff you can make between performance and risk of data loss. When oplocks
are enabled it allows the CIFS client (in this case the SQL server) to
aggressively cache data. That can result in a nice performance gain, and
everything is great as long as you don't have network problems, and the
servers never crash. If that does happen, you are more likely to have data loss
with oplocks enabled. On the other hand, many applications do write-throughs
and/or flushes to avoid that problem.
I
haven't heard of anyone losing data on MS SQL because of having oplocks enabled,
but if you're worried I'd suggest turning off oplocks on the qtree that has
your database files. If the performance suffers too much, you can consider
turning them back on if you are in a stable environment.
Mark
There are articles
on Microsoft's website as well as the NOW site that indicate the
possibility of oplocks causing data loss when using a filer to host a SQL
7 datbase device.
We are running SQL
7.0 on a Win2K server with a gigabit connection to the filer. The
database is a repository for a custom web application that we sell, so the
database is getting a lot of transactions from the web
server.
The language in
both articles is vague and inconclusive, so I am looking for those of you who
have experience running SQL 7.0 with a network connection to it devices.
I am not a dba, just the guy who looks after the filer among other
things. Oplocks are currently enabled, should I leave them this
way?
Michael Hopkins
mike@BroadDaylight.com
Systems
Administrator
phone 408.450.7450
Broad
Daylight,
Inc.
fax 408.327.0177