Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-07-03 : 10:36:21
|
| 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. |
|
|
sumo
Starting Member
45 Posts |
Posted - 2002-11-01 : 08:48:24
|
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 SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/Edited by - sumo on 11/01/2002 08:58:18 |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-11-03 : 15:22:02
|
| 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.[url]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[/url]HTHJasper SmithEdited by - jasper_smith on 11/03/2002 15:35:20 |
|
|
|
RocketScientist
Official SQLTeam Chef
85 Posts |
Posted - 2002-11-03 : 23:43:01
|
| 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_dbCopy the MDF file ONLY out to your network storageRe-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. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-11-04 : 03:02:07
|
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.HTHJasper Smith |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2002-11-04 : 08:33:26
|
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.HTHJasper Smith
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-11-04 : 11:50:31
|
| 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. |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2002-11-04 : 12:04:51
|
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 SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
|
|
|
|
|
Article: SQL Server and Network Attached Storage