Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2187
  • Last Modified:

Mapping SQL server mdf and ndf to a NAS

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
noamco36
Asked:
noamco36
  • 2
  • 2
  • 2
  • +1
6 Solutions
 
micropc1Commented:
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
 
noamco36Author Commented:
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
 
micropc1Commented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Eugene ZCommented:
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
 
Eugene ZCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now