Using Network Attached Storage to share a database

[Michael Sumerano]

We are trying to set up a solution for our web site that
will be cost effective, but will give us excellent load
balancing and simple setup.

We spoke with MS to see if this was possible and they said
yes. However, we're having difficulty setting up a test.

In our theoretical configuration, we'd have 8 servers that
have IIS and SQL installed. All will be independent of
each other. On a Network Attached Storage device, we'd
have a single database (can be multiple, but for
simplification, we'll just use one). We'd like all 8
servers to attach to that single database on the NAS to
give us the capability of sharing, but without the
complexity of clustering, replication, or log shipping.

Is this type of configuration even possible? We have
figured out how to attach to a database that is on a
networked device. However, no other server can attach to
it. We have tried many different ways and have eliminated
99% of possible permissions issues accessing the data, but
no luck.

Any help on this is greatly appreciated. The answer to
this question will determine whether or not we purchase
the hardware we have in our design for this solution. If
the answer is no, we have to back to the drawing board to
find another solution.

TIA!!!

Michael Sumerano
Technical Web Administrator
eKeystone.com

[Steve Thompson]

"Michael Sumerano" <msum...@ekeystone.com> wrote in message
news:438c01c281dd$f4a3db90$3aef2ecf@TKMSFTNGXA09...

> We are trying to set up a solution for our web site that
> will be cost effective, but will give us excellent load
> balancing and simple setup.
>
> We spoke with MS to see if this was possible and they said
> yes. However, we're having difficulty setting up a test.
>
> In our theoretical configuration, we'd have 8 servers that
> have IIS and SQL installed. All will be independent of
> each other. On a Network Attached Storage device, we'd
> have a single database (can be multiple, but for
> simplification, we'll just use one). We'd like all 8
> servers to attach to that single database on the NAS to
> give us the capability of sharing, but without the
> complexity of clustering, replication, or log shipping.
>
> Is this type of configuration even possible? We have
> figured out how to attach to a database that is on a
> networked device. However, no other server can attach to
> it. We have tried many different ways and have eliminated
> 99% of possible permissions issues accessing the data, but
> no luck.

That is not possible -- only one copy of SQL Server can use the database
files, that is why additional SQL Servers can not 'attach' to the data and
log files.

The best load balancing would be achieved by clustering your IIS servers (or
use some time of network based load balanced like Altheon) and have a single
(or clustered) instance of SQL Server running on the backend with suitable
hardware.

Steve

[Jasper Smith]

No you can't do this. The database cannot be shared amongst
instances of SQL Server. You can attach the same db to multiple
instances as long as only one is started at any one time (i.e. on
my home PC I dual boot WinXP Home and .NET Server and have
SQL DEV on both and can access the same databases because only
one OS can be active at a time).

So basically no you can't do this, databases are instance specific.

--
HTH

Jasper Smith (SQL Server MVP)

Check out the PASS Community Summit - Seattle, the largest and only user
event entirely dedicated to SQL Server, November 19-22.
http://www.sqlpass.org/events/seattle/index.cfm

"Michael Sumerano" <msum...@ekeystone.com> wrote in message
news:438c01c281dd$f4a3db90$3aef2ecf@TKMSFTNGXA09...

[Bill Hollinshead [MS]]

Hi Michael,

I am alarmed to see that an MS employee suggested an unworkable solution.
If known, please send me an offline response (remove "online." from my
alias) with that employee's name or alias. Perhaps it would be best to
start with "Scaling to the Limits of a Single Database Server" in
http://msdn.microsoft.com/library/en-us/trblsql/tr_faq_238z.asp, plus the
rest of that page, and then see
http://msdn.microsoft.com/library/en-us/architec/8_ar_cs_4fw3.asp for scale
out architecture) Keep in mind that scaling out does not require
clustering, instead, scaling out (for SQL Server) uses the buzzword
"federated". Clustering's primary goal is to supply high availability, and
the term 'clustering' is itself mistakenly considered to be synonymous with
'fault tolerance' <g>. If you plan to scale up first, be sure to keep in
mind any plans to scale out during the database design phase. Also see
http://www.microsoft.com/sql/techinfo/administration/2000/BasicCapScalabilit
yR.doc and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html
/data03082001.asp.

You should consider network load balancing for IIS (although IIS is not my
area of expertise) - for example, see
http://www.microsoft.com/TechNet/itsolutions/idc/srvgde/images/IDCSVC04.GIF
(the graphic is from
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/itsolutio
ns/idc/srvgde/idcsvc.asp) and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/
bdadotnetarch08.asp. On the other hand, if IIS is only retrieving read-only
data from SQL Server, then using replication between separate SQL Servers
(with separate disks) may meet your need to distribute the load. For
example, see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/csvr2002/ht
m/cs_dp_typical_nxmf.asp and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/sql_mtappdcache.asp.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no

rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.