Hi All
I am in process of moving a SQL 2005 solution from a development box that used local storage to UAT environment with SAN attached storage. The solution uses database snapshots
The database files are on the SAN storage but during testing I was unable to create a Database snapshot on the SAN disk. Creating snapshots on the local disk worked fine.
Is their some restriction/problem in using the database snapshot technology with SAN storage?
What edition of Sql server 2005 you have ? Database snapshot is possible only with Enterprise edition I believe|||
SQL Server Enterprise 2005 SP2 is being used on all systems.
The snapshots are working against the local disks on the server but not against the SAN attached storage.
Regards
Nadreck
|||Are the SAN attached drives formatted using the NTFS file system? Database Snapshots use NTFS Sparse Files for the underlying technology, and will not work under any other file system.
I have database snapshots on production clustered servers, using SAN storage, and haven't had any problems.
|||Your use of the term "SAN attached" has me wondering. There are Network Attached Storage (NAS) devices that often have their own proprietary file system and then there are Storage Area Networks (SAN) which are usually NTFS in the Windows world. Which type of storage are you referring to? Database snapshots require NTFS.|||There is no difference between a "SAN" and a local drive to SQL, they both look like local drives. This must be a security issue on the directory.
If you are talking about a "NAS" drive, as in you access it using "\\servername\share", SQL does not support putting data/transaction files on share drives.
|||
The storage is on a true SAN with the disks attached by Fibre Channel and being presented as Local disk.
I have spoken with the SAN administrators and they have confirmed that the disks are formatted as NTFS
The error received when trying to create the sbnapshot was
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'filename'.
|||I think the login with which you have logged into Sql server(windows authentication) might not be having modify privilege to the folder where the Sparse file is being created ! also check if the sql service account has necessary privileges to that folder ! ! !Thanxx
Deepak
|||
The logon accounts and service accounts both have local admin rights on the box so write permissions do not appear to be the issue. The mdf and ldf files for the database are both on SAN storage so SQL appears to have sufficient rights to write to these disks. Creating snapshots on local disk (with the same accounts) has no issue.
Obviously it may still be some permissions issue with the SAN but I cannot see any difference.
regards
Nadreck
