Solved

Mapping SQL server mdf and ndf to a NAS

Posted on 2012-04-03
7
1,971 Views
Last Modified: 2012-04-10
Hi I am creating a new DB on 2008R2 via SSMS.
When I want to change the mapping of the files to a network share (NAS), the folder tree just shows the LOCAL disk, and not any Network folders.

How can I map the new db files to a network share?

Thank you,
Noa
0
Comment
Question by:noamco36
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Assisted Solution

by:micropc1
micropc1 earned 167 total points
ID: 37804177
I very much would not recommend running a production database from a network location. You will not only lose performance, but increase your risk of database corruption. That said, you'll need to detach the database, move the files, and attach them using the CREATE DATABASE... FOR ATTACH command like shown here...

http://technet.microsoft.com/en-us/library/ms187858.aspx
http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx

You may also find this useful...
http://sqlrecoverydatabase.blogspot.com/2010/03/storing-sql-server-database-on-network.html?m=1
0
 

Author Comment

by:noamco36
ID: 37804243
Thank you.

If it is not recommended to run database from a network, then:
1- how do you manage space disk issues if you create the new db on the defualt location (usually C:\)  and C:\ keeps on growing in size?
2- why do many companies use SAN for storing their databases?
3-what about if others need to connect to your databases and if your local machine is turned off?
0
 
LVL 7

Assisted Solution

by:micropc1
micropc1 earned 167 total points
ID: 37804330
To answer your questions...
1. Add more disk space or use better measures to manage your database growth
2. A SAN (Storage Area Network) is not the same as a NAS (Network Attached Storage). SANs offer better performance as they are typically implemented with a dedicated connection to the server using fibre channel or iSCSI - unlike a NAS, which is basically just a windows share on your TCP/IP network. They are also much more costly than a NAS.
3.This made me curious... Is SQL Server running on a Workstation? If so, and this is a heavily-used production database, you should consider moving it to a server.  A database can only be attached to a single instance of SQL Server at any given time, so you would need to detach and re-attach the database every time you wanted to host it from a different instance of SQL Server. Your best option would be to host it on a high-end server that is always on.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 166 total points
ID: 37804413
About Sql server on NAS
check

Description of support for network database files in SQL Server
http://support.microsoft.com/kb/304261
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/18b9c0ef-b276-4cfa-8506-ef36f12055b1


if you use SAN -  you can use mount points :
http://www.sqlservercentral.com/Forums/Topic609005-146-1.aspx


more about MP
Use mount points if you run out of Windows drive letters
http://www.techrepublic.com/blog/datacenter/use-mount-points-if-you-run-out-of-windows-drive-letters/383
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 166 total points
ID: 37805301
and about mapping to NAS and SQl Server
SQL Server Can Run Databases from Network Shares & NAS
http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 167 total points
ID: 37806982
You can do it, but first you have to set a trace flag 1807 on.

Then:

1  ALTER the database to "tell" SQL about the new file locations
2  List the sys.master_files table for your db, to verify that SQL "knows" about the new file locations
3  ALTER the database OFFLINE --do NOT detach
4  Physically copy (or move) the files to the new drive locations
5  ALTER the database back ONLINE
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 167 total points
ID: 37806999
Step 1 details:

ALTER DATABASE database_name
MODIFY FILE ( NAME = logical_file_name1, FILENAME = 'x:\new\path\to\file\filename.mdf' )

ALTER DATABASE database_name
MODIFY FILE ( NAME = logical_file_name2, FILENAME = 'x:\new\path\to\file\filename_log.ldf' )

...[one ALTER command for every file]...


Note that until you set the db offline (or detach it), or SQL stops and restarts, the db can continue to be used normally in its current location.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question