May 14, 2009 at 10:01 am
Is there a way to move existing database files from local storage to a network attached driev or storage array?
May 14, 2009 at 10:08 am
I assume you are looking to just move user databases and not the system databases?
Check out the following Microsoft reference via the magic of Google and the phrase "SQL Server move database methods" 😀
May 14, 2009 at 10:13 am
You are correct. I want to move user databases.
I did see that article, but I wasn't sure it was for network attached drives or local. I've mapped a drive to another server, and from what I see in SQL Server, it doesn't recognize the mapping.
May 14, 2009 at 10:19 am
Ok so you have created a local drive mapping on the server to a NAS drive on the network somewhere called T:\ for example?
If you browse to my computer under Windows Explorer can you see the drive mapping?
You may also find the following thread useful:
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/e2c45400-062b-41a7-bf80-b0baa08e835d/[/url]
May 14, 2009 at 11:41 am
I can see the mapped drive in Windows, but I'm not sure how to get SQL Server to see it. The link you sent is a big help. Now the problem is, I'm out of drive space and I can't even get in to see which user I need to connect as. Can I detach it, use Windows to copy it to the mapped drive, then reattach with no local drive space available?
May 14, 2009 at 11:53 am
Please see "Description of support for network database files in SQL Server" at http://support.microsoft.com/kb/304261
Of special interest are:
If you use a non-WHQL qualified storage device that does not support the I/O guarantees for transactional database use described in this article, Microsoft cannot provide support for SQL Server or SQL Server-based applications.
If access to a disk resource requires that a share be mapped, or if the disk resource appears as a remote server through a UNC path, (for example, \\Servername\Sharename), on the network, then by default, the disk storage system is not supported as a location for SQL Server databases.
SQL = Scarcely Qualifies as a Language
May 14, 2009 at 12:04 pm
Well, thanks for bursting my bubble, Mr. Sunshine! :laugh:
So, just how to do use SQL Server on large databases? Surely there has to be a way to have the files on a remote system. On the other hand, it just says Microsoft won't support unc mounts. It doesn't say it won't work.
May 14, 2009 at 12:06 pm
You can use SAN to have very large DB.
-Roy
May 14, 2009 at 12:09 pm
doug turner (5/14/2009)
I can see the mapped drive in Windows, but I'm not sure how to get SQL Server to see it. The link you sent is a big help. Now the problem is, I'm out of drive space and I can't even get in to see which user I need to connect as. Can I detach it, use Windows to copy it to the mapped drive, then reattach with no local drive space available?
The KB Carl provided is th one you need.
However it has one big remark !!
Incorrect use of database software with a NAS product, or database use with an improperly configured NAS product, may result in data loss including total database loss. If the NAS device or network software does not completely honor data guarantees, such as write ordering or write-through, then hardware, software, or even power failures could seriously compromise data integrity.
So:
- the first advise is: DON'T !
- The second advise is: Use a SAN (availability / stability)
Also keep in mind you need startup trace flag 1807 if you want to allocate using UNC !
and your service account must have the needed windows authorities to use the drive-map !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 14, 2009 at 12:31 pm
Unfortunately, we use EMC and from what I've been told Windows and EMC don't play well together, so I'm pretty much stuck with the unc method...unless.....would a USB drive work? Hey, quit laughing! I'll try dang near anything right now.
How do I start that trace flag and what does it do?
May 14, 2009 at 12:37 pm
Flash drive is the magic word my man... Just kidding..:-)
-Roy
May 14, 2009 at 12:40 pm
Roy Ernest (5/14/2009)
Flash drive is the magic word my man... Just kidding..:-)
You're such a tease!!!
May 15, 2009 at 12:20 am
I've seen udb sticks being used as sqlserver disk system for demonstartion purposes.
You know: a usb hub with 4 usb sticks on it, just to demonstarte hardware failure (pull out a stick)and sqlservers recovery options.
Availability and reliability will be your biggest issue.
To add the startup trace flag:
- open SSCM (sqlserver configuration manager)
- Select the instance you need.
- right click on the instance name and choose properties
- then choose the advanced tab
- on that grid, you will see an item "startup parameters"
- Open that right hand side cell
- you will see something like:
-dX:\MSSQL.1\MSSQL\DATA\master.mdf;-eX:\MSSQL.1\MSSQL\LOG\ERRORLOG;-lX:\MSSQL.1\MSSQL\DATA\mastlog.ldf;
Add -T1807 to the end of that string
Click on Apply or OK to accept the new settings
Stop / start the sqlserver instance.
Should work now.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 15, 2009 at 5:47 am
Thanks for the procedures.
It looks like I'm just going to completely blow away the database and rebuild it. We can't seem to find any drives bigger than what I already have and I can't seem to able to add a datafile on another server to the database.
May 15, 2009 at 7:09 am
???????? :ermm:
You can add files of multiple disks to a filegroup, so disk size shouldn't be a problem.
Data will be spread over all files of the same filegroup (needs (clustering) index rebuild)
However, there are some restrictions you need to take into account:
e.g. the smalles max_file_size determines the max file size for all files within a userdb filegroup. (data balancing problem)
... lets me recall I didn't test this on sql2005 :Whistling:
- What sizes are we talking about !
- Play it safe ... start with a full backup !!!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply