Solved

Mapping SQL server mdf and ndf to a NAS

Posted on 2012-04-03
7
1,941 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sqlquerystress - To test db performance 8 39
T-SQL: Episode III - Revenge of The Dude 24 52
Exchange 2007 standard - defrag (eseutul /d) 10 39
Get the latest status 8 29
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now