SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL Server and Network Attached Storage
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/03/2000 :  10:36:21  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
This article delves into Network Attached Storage (NAS) including a step-by-step guide on how to move a database from local storage to NAS.

Article Link.

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 10/03/2001 :  00:36:17  Show Profile  Visit graz's Homepage  Reply with Quote
And here's a link to another post by the author on this subject:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9491&FORUM_ID=4&CAT_ID=3&Topic_Title=NAS+vs%2E+SAN&Forum_Title=General+SQL+Server

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

sumo
Starting Member

USA
45 Posts

Posted - 11/01/2002 :  08:48:24  Show Profile  Visit sumo's Homepage  Reply with Quote
I have a question about configuring something like this. We're testing out the usage of a network to store the database and log files for an SQL Server 2000 database. This is obviously not optimum, but we want to make sure it will even work before we go purchase a NAS appliance and use it from multiple servers on a Gigabit network.

However, when we set the trace flag 1807 and then try to use the UNC path to the mdf file (I'm using the single file attach system procedure), it gives an error stating that the mdf file may not be the correct format and the attach doesn't work (Device activation error. The physical file name '\\[servername]\Users\Extranet\databasetest\northwnd.mdf' may be incorrect.). What am I doing wrong?

Also, when I first tried it, it worked just fine, but I could not get a separate SQL Server to attach to the same database. Is there a trick to this? Microsoft did state to us that it is possible. I'll explain our possible solution to our problem below.

We would like to use a NAS appliance and hook up 8 rather inexpensive servers to the NAS on a gigabit network. Each server will NOT know of each other (i.e. no clustering). We were told by microsoft that SQL Server 2000 Standard could work this way, attaching to the same database files, and since locks are locks, it shouldn't have any issues. Most of the activity is in reads anyway.

Do you believe this configuration is possible and how the heck would we properly attach to a database that is on the NAS (or a network for testing purposes) and that another server may have already attached to?

I can explain further if necessary. BTW, the 8 servers will also be web servers that are load balanced by a hardware load balancer. It's a simple, yet effective way to reduce our per-server traffic (we get 5 million hits a week to 1 web server w/4 cpu's right now w/1 back-end sql server) and save money at the same time. The traditional methods of expanding servers is too costly...

quote:

This article delves into Network Attached Storage (NAS) including a step-by-step guide on how to move a database from local storage to NAS.<P>Article <a href="/item.asp?ItemID=128">Link</a>.



Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/

Edited by - sumo on 11/01/2002 08:58:18
Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 11/03/2002 :  15:22:02  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
Did you also post this on the microsoft sql server newsgroup as it looks familiar - I think I answered more fully there. Basically databases are instance specific, you cannot share databases amongst instances of SQL Server so whoever told you that was wrong.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&safe=off&threadm=%23OkudVegCHA.2544%40tkmsftngp11&rnum=1&prev=/groups%3Fas_q%3Ddatabase%2520instance%26safe%3Doff%26ie%3DUTF-8%26oe%3DUTF-8%26as_ugroup%3Dmicrosoft.public.sqlserver. %26as_uauthors%3DJasper%2520Smith%26lr%3D%26num%3D20%26as_scoring%3Dd%26hl%3Den

HTH
Jasper Smith

Edited by - jasper_smith on 11/03/2002 15:35:20
Go to Top of Page

RocketScientist
Official SQLTeam Chef

USA
85 Posts

Posted - 11/03/2002 :  23:43:01  Show Profile  Visit RocketScientist's Homepage  Reply with Quote
It does work. But there are some caveats.

First, you cannot attach multiple servers to a single read-write database. The database has absolutely got to be read-only. And the only way it'll work then is if the actual MDF/LDF files are read-only. You can't use a single file and have it attached to multiple servers for writing. Won't work. What happens is that when you attach the database in a read-write mode, SQL Server acquires a file-system level exclusive lock on the files involved (all MDF's and LDF's). When you attach the database and all of the MDF and LDF files are already attributed read-only (their read only file attributes are turned on) then SQL Server acquires a non-exclusive read lock. Since multiple servers can read from the same file at the same time, you're all set.

Second, that configuration is of "debatable supportability" from MS.

Which comes back to "why on earth would anybody ever WANT to do this if you can only do it read-only?" Well, it turns out that if you have several SQL Servers doing compute-intensive tasks, on the same read-only data, it makes a good deal of sense from an engineering standpoint if you can get past the logistical problems.

As far as the specific problems you're having, I'd expect that you don't have the file attributes set correctly, or that you're using an LDF file you copied over from local storage. So the actual procedure runs something like this:

Cleanly detach the database using sp_detach_db

Copy the MDF file ONLY out to your network storage

Re-attach the MDF file using sp_attach_db. Specify both an MDF and LDF file in the call to sp_attach_db. The LDF file doesn't exist, but that's OK, SQL Server will make one for you sized at either the minimum log file size or 1/2 MB. At this point it's critical to note a couple of things. First, the MDF has to be read-write at this point because SQL Server has to change the path to the LDF file. Second, SQL Server has to be using a service account that has full control access to the network share upon which the MDF file resides.

Finally detach the database again, set the files to read-only, and reattach the database using sp_attach_db. Then you can start attaching other servers to this set of files.


Hope this clears things up.

rocketscientist.

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 11/04/2002 :  03:02:07  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
I'm not sure it's a good idea to encourage Michael Just because if you hack about enough, you can do something , doesn't make doing it a good idea.It's technically possible to run sql without the model database but it's not supported and it's not a good idea
From the post it seems these databases are NOT read only. Plus as you state this "solution" is extremely unlikely (I will say NOT) supported by MS so any problems and they're on their own. You need to load balance your IIS servers , I doubt that on a web site SQL is the bottleneck. Have you looked at scaling up and or out before using unsupported hacks to achieve this ? Get yourself a dedicated SQL server, use NWLB on the IIS servers and point them at the dedicated SQL server. If you look at Bill Holinshead's [MS] post in reply to your post you'll lots of great links , I'd encourage you to read them and explore the supported avenues first.



HTH
Jasper Smith
Go to Top of Page

sumo
Starting Member

USA
45 Posts

Posted - 11/04/2002 :  08:33:26  Show Profile  Visit sumo's Homepage  Reply with Quote
Actually, since it must be read-only, it won't work for us. We had planned on doing that with all our databases, which includes an order database and a user database.

We're really trying to find a cost-effective solution that will give us the redundancy/failover capability that we now need. We're currently running a very active web site on 1 web server and 1 sql server, both having 4 550MHz Xeon CPU's. The sql server has 2GB RAM and the web has 1GB.

My original solution was to go with two smaller web servers and load balance them somehow (either hardware or Win2K advanced) and two large DB servers in an active/passive cluster. However, we could never come under the $90-110K for the solution. This other solution, which will not work, would have come in around $50-60K.

We really don't know what to do now, since we're very restricted budget-wise, and at the same time require a serious upgrade to our current setup.

quote:

I'm not sure it's a good idea to encourage Michael Just because if you hack about enough, you can do something , doesn't make doing it a good idea.It's technically possible to run sql without the model database but it's not supported and it's not a good idea
From the post it seems these databases are NOT read only. Plus as you state this "solution" is extremely unlikely (I will say NOT) supported by MS so any problems and they're on their own. You need to load balance your IIS servers , I doubt that on a web site SQL is the bottleneck. Have you looked at scaling up and or out before using unsupported hacks to achieve this ? Get yourself a dedicated SQL server, use NWLB on the IIS servers and point them at the dedicated SQL server. If you look at Bill Holinshead's [MS] post in reply to your post you'll lots of great links , I'd encourage you to read them and explore the supported avenues first.



HTH
Jasper Smith



Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 11/04/2002 :  11:50:31  Show Profile  Visit graz's Homepage  Reply with Quote
You might look at something like log shipping. It can keep a backup server "close" to a production server in terms of data.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

sumo
Starting Member

USA
45 Posts

Posted - 11/04/2002 :  12:04:51  Show Profile  Visit sumo's Homepage  Reply with Quote
We're currently looking into federation, too. We just want to be able to spread out our web and sql traffic so that a server can break and not take us down. It'd be easier if our web site wasn't so demanding of the sql server and our budget wasn't so limited.

quote:

You might look at something like log shipping. It can keep a backup server "close" to a production server in terms of data.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.



Michael Sumerano
Technical Web Administrator
eKeystone.com
http://www.ekeystone.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000