Solved

MDF and LDF files on another machine

Posted on 2003-10-31
7
1,974 Views
Last Modified: 2013-11-15
HI,

I have my LDF and MDF files on  machine 1 , but SQL Server is installed on Machine 2.  I would however like to be able to view this database on Machine 2 without installing SQL on Machine 1.  Is there any way I can create a linked server, or ever better actually view the data as a normal database on machine 1?

thanks
0
Comment
Question by:kinton
7 Comments
 
LVL 7

Expert Comment

by:mikkilineni
ID: 9658130
Hi,,


It is not possible to Have ur datafiles in one machine..and access the database from the other  Machine.

It Might be possibel if u r using SAN..
0
 
LVL 34

Expert Comment

by:arbert
ID: 9658131
Nope, you need to attach them to Machine 2.  SQL Server will not let you attach files on a network share--it can't guarantee data integrity across a network....

Brett
0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 9663798
kinton, did you need more help on this??
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 8

Assisted Solution

by:dishanf
dishanf earned 250 total points
ID: 9668988
0
 
LVL 8

Expert Comment

by:dishanf
ID: 9668993
Q.     Can I create SQL Server databases on network drives?

A.  First ask yourself what your reason is for doing this?  Your performance
will almost certainly be degraded and any network glitches (which are far more
common than SCSI/fibre bus glitches) will cause a database corruption.  Putting
i/o's across a network (even a fast, switched, network) is typically slower
than via SCSI/fibre and the latency is a lot longer.  Network packet sizes are
much smaller than scsi packets giving less throughput and more cpu utilization
by the device drivers on the host.

If you do use a network drive then make sure you have a dedicated cross-over or
switched connection between the SQL Server box and the network drive machine
otherwise your performance will be further affected by contention with other
network traffic.

SQL Server currently has no concept of sharing a database that is held on
another server.  Only one server can access the database file at any one time -
the exception being that multiple SQL Servers could probably open a read-only
database on a shared-drive.  Therefore there is no advantage to having it on a
"network drive" - it can only be backed-up/accessed from the server running SQL
Server anyway.

If the reason for wanting SQL databases on a network drive is to keep all your
storage central, then you can't completely achieve that result this way as you
can't boot NT from a network drive, so you would still need disks in local
servers for NT, pagefiles etc.  And these should be protected via hardware raid
as the loss of an NT disk will prevent users getting at your databases just as
much as the loss of a disk containing the database itself.

Now saying that it IS possible to store databases on network drives as long as
SQL is fooled into thinking they are in fact local drives.  Under 6.5 you must
map a drive letter to a network share - UNC paths will not work.  With SQL 7.0
UNC paths will work as long as you use trace flag 1807.

There is more information on this in Q196904.  This describes the support being
allowed in SQL 7.0 for use against Network Appliance networked raid units only.
 Note that these will suffer the same performance penalties as if you were
accessing a network share on an NT box, as effectively that is what they are.  
These boxes run a proprietary operating system on an embedded Alpha chip that
talks the SMB protocol required to handle NT-style network file-io.  They can
be connected to almost any LAN/WAN infrastructure as they support all the
standard network types.

If you want centralised storage another method is to use a shared-SCSI/fibre
disk array - these can be attached to servers via SCSI or fibre connectors and
can achieve distances of up to 20Km using optical extenders.  These arrays can
support up to 64 or so separate servers and are sold by Digital (Compaq)
Storageworks and EMC amongst others.  Although it is a "single raid unit", each
server sees a physically separate set of "disks" - the partitioning logic in
the raid array can allow different servers to use the same physical disks but
they are logically partitioned and the different servers cannot see this and
see their storage as dedicated.  There is no sharing of data at the
partition/file/database level.

Another method is to use a SAN - storage area network.  These are fibre or
copper based "networks" of storage and/or backup devices.  The "network" is
dedicated for data access. Each attached device is usually fibre-channel based,
or is SCSI with an appropriate connector.  Each device may be able to be
partitioned into sets of available resources (disk/tape), but each resource can
currently only be allocated to a single server attached to the "network".  
Servers attach to a SAN with a SAN "nic" card.  As SAN technology matures it
may be possible to share resources between multiple servers, but this needs
changes to the NT kernel as well as the SAN/fibre drivers.


v1.07 2000.05.25
Applies to SQL Server versions  : All
FAQ Categories                  : Database Administration
Related FAQ articles            : n/a
Related Microsoft Kb articles   : Q196904 "Support for Network Database Files"
Other related information       : n/a
Authors                         : Neil Pike


 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
0
 
LVL 2

Author Comment

by:kinton
ID: 9670496
I've set the trace flag to 1807.  Now I use the following code to try and set up the database:

EXEC sp_attach_db @dbname = N'pubs',
   @filename1 = N'\\Callsync\Data\SoftSync_CallData_Data.mdf',
   @filename2 = N'\\Callsync\Data\SoftSync_CallData_Log.ldf'

However I get the following error messaage:

Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name '\\Callsync\Data\SoftSync_CallData_Data.mdf' may be incorrect.

I've checked the location and this is definately correct

Any ideas would be greatly appreciated?
0
 
LVL 34

Expert Comment

by:arbert
ID: 9670934
dishanf, the following statement is incorrect  "SQL Server currently has no concept of sharing a database that is held on another server"


KINTON, do not try and connect to databases over the network--even with the traceflag.  This is not a support installation by Microsoft (unless you attach by SAN or NAS)....
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

By default, Carbonite Server Backup manages your encryption key for you using Advanced Encryption Standard (AES) 128-bit encryption. If you choose to manage your private encryption key, your backups will be encrypted using AES 256-bit encryption.
VM backups can be lost due to a number of reasons: accidental backup deletion, backup file corruption, disk failure, lost or stolen hardware, malicious attack, or due to some other undesired and unpredicted event. Thus, having more than one copy of …
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.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

744 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

15 Experts available now in Live!

Get 1:1 Help Now