Storing Sql Server Database On Network Server May Cause Corruption

  • Posted on August 22, 2014 at 12:21 am

Microsoft usually urges that you should use a SAN (Storage Area Network) or locally attached hard drive to store MS SQL Server database (MDF) files as this configuration improves SQL Server reliability and performance. Use of network database (stored on network server or NAS [Network Attached Server]) is disabled by default in Microsoft SQL Server. In order to ensure complete data safety and to prevent need of , you should not use SQL Server database on networked server.

Though, it is not recommended, but it is possible to configure MS SQL Server for storing the database on a NAS storage server or networked server. Data storage servers, that are employed to store MDF database, must meet the SQL Server needs for ‘write-through guarantees’ and ‘data write ordering’.

If you try to create SQL Server database file on UNC or mapped network location results into any of the below error message:

5105 “Device Activation Error”

Or

5110 “File ‘file_name’ is on a network device not supported for database files.”

The above behavior is quite expected. The trace flag 1807 let you to bypass the check process and enables you to configure MS SQL Server with the networked database files. Microsoft SQL Server and various other relational database management systems use the transaction log and associated recovery logic, that ensure database integrity and consistency in case of system failure or improper system shutdown.

These SQL Server Recovery protocols bank on the capability to write instantly on the hard drive media so that if any operating system I/O (Input/Output) write call returns to database manage, recovery system is assured that write operation is really complete or the completion of write process can be ensured. Any type of failure by any hardware or software components of this protocol may result in complete or partial data loss due to corruption of MDF file.

In such critical circumstances, you need to restore the damaged database from the most recent backup. However, if the backup is either not available or not updated, third-party MS SQL Recovery software is the only way to go.

The applications are powerful enough to carry out in-depth scan of entire SQL Server database and extract all inaccessible database objects. With the read-only and simple graphical user interface, they are completely safe and easy to use.

SQL Recovery is the most prominent tool that enables absolute recovery in all cases of MDF corruption. It restores all database objects such as tables, reports, forms, modules, queries, constraints, stored procedures, and triggers. The software is compatible with MS SQL Server 2008, 2005, and 2000.

Top