0

I have 2 Storage Server use DFS-R to sync the content. MSSQL Server using UNC path the connect to the DFS-R namespace for datafiles.

Now the problem is MSSQL with lock the datafile while server is running, so the content of 2 storage server cannot getting sync, any ideas ?

user163695
  • 1
  • 1

1 Answers1

3

You shouldn't back up SQL Server databases from the filesystem level, it should be done by taking SQL backups and then backing up the SQL backup files.

To remove the locks from the files you either have to stop SQL Server, take the database offline, or detach the database.

If you want to replicate SQL Server databases you really should investigate one of the replication techniques within SQL Server itself. Using DFS-R you're more likely to corrupt your databases than anything else. That said, be careful what you wish for. It can easily take an entire full-time dba team to support SQL Server replication.

squillman
  • 37,883
  • 12
  • 92
  • 146
  • 2
    +1 - Yikes! Trying to sync SQL Server data files w/ DFS-R. Zow... – Evan Anderson Apr 26 '13 at 17:34
  • Can't agree enough. Native SQL Backups are what you need to be doing. http://support.microsoft.com/kb/2027537 – shiitake Apr 26 '13 at 19:25
  • All of the above, but it's even worse than that. If your solution isn't backing up your LDF file, you don't have a solution. SQL Server relies on being able to read the LDF when the 'other' nodes starts up after a failover. Not all committed transactions are immediately written to the MDF file. They may only be in the LDF file. If you don't have a good copy of the LDF file, you may loose transactions that the database claimed were committed. – Darin Strait Apr 26 '13 at 20:20
  • I just want to avoid single point of failure of storage server, the SQL server installed on Server 2012 failover cluster, but it is standard version, did not support HA... – user163695 Apr 27 '13 at 06:27