Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mapping SQL server mdf and ndf to a NAS

Posted on 2012-04-03
7
Medium Priority
?
2,146 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Assisted Solution

by:micropc1
micropc1 earned 668 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 668 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 664 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 43

Assisted Solution

by:Eugene Z
Eugene Z earned 664 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 668 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 70

Accepted Solution

by:
Scott Pletcher earned 668 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

604 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