September 3, 2008 at 2:48 pm
(SS 2005, SP2) I remote desktop into a 'Reports' Server machine, which has file shares onto the production server (PROD) where backup files are stored, and using VPN has access to a file share on a Disaster Recovery (DR) server where the DR database will be housed. The strategy is to perform a manual log shipping (don't ask!) from the PROD server to the DR server. So, using SSMS, I attempt to restore the data onto the DR machine. Using the GUI, it only allows me to reference the C: drive (only drive) on the DR machine, and no other location. But I'm running this process as a remote login. Can I NOT perform a restore without having the files 'locally' attached to the DR machine? Yes, I realize there are issues of lag/latency - that's part of what I'm trying to test.
Put another way, when using SSMS to access remote databases, when do the 'local' login privileges not matter for performing maintenance tasks on a database reached via VPN?
TIA, and I hope this is an interesting question. At this rate, I'll be bald by next week.
September 4, 2008 at 1:27 pm
This is an interesting question.
In my experience, VPN works much the same as your average network, but slower. Having said that I would say that you should be able to use UNC file path names to get the files you want. (i.e. \\Server\Share\file.ext).
Also, I have never connected to a SQL Server where I could just use the GUI to get a file from another Server by browsing to it. Even if I mapped the drive I could not see it using the GUI, just the local drives.
Let me know if I am close to what you are looking for.
Regards, Irish
September 4, 2008 at 2:33 pm
Does the account running SQL have access to the remote drive?
September 5, 2008 at 9:07 am
Adam Bean (9/4/2008)
Does the account running SQL have access to the remote drive?
Adam, yes, the account has full rights. There was a question whether I was using integrated security or the sql server sa account to test this, but I went back and tested again using windows admin login to prove rights were not the issue.
Jeffrey - I think you are much closer to the mark - that the limitation is within the GUI.
In the interim, in an effort to minimize transport processes, I am using 7-zip to compress the files before transporting across the VPN, which kind of moots the original question.
However, the issue holds, I believe, even for intranet shared drives, so as a general question (broader than my issue), I think it would be helpful to have this explored more fully. When referencing shared, mapped drives, CAN you use the drive mapping or must you have 'fully qualified' directory names for SQL Server to work correctly? Is there an 'alias' type instruction I'm not aware of, and if not, is there one in the works? I can see both sides of an argument for and against providing a means for this, either within a GUI structure or externally. The tradeoff is being squirrelly for security reasons vs ease of support/maintenance/revision.
Thanks for your responses!
(As a 'reformed' COBOL/FORTRAN programmer, I have a natural objection to any GUI which does not allow me to see all the 'stuff' that goes on behind the scenes. However, given the huge volume of housekeeping that modern software requires to do anything (when was the last time you actually looked at all the code required to create a new instance of an object in .NET?), I've softened my objection considerably.
September 5, 2008 at 10:58 am
Steve - I've not tried this in 2008 yet, but I know that it was a limitation in 7.0 and 2000. In all cases you could backup/restore to network locations, but to do it you had to set up the share with the appropriate level of rights and you had to enter the exact UNC file path name to get it.
I think you are stuck unless you have a SAN that both of the Servers are connected to, but that sounds worse than what you are trying to attempt now.
Regards, Irish
September 5, 2008 at 11:59 am
Not to mention that a shared SAN would defeat some of the design principles behind establishing a disaster recovery site. :w00t:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply