MS SQL database on the network ball

Warning: The author does not bear any responsibility for problems that may arise when using this method of connecting databases.



Microsoft SQL SERVER by default does not allow creating SQL databases on network resources, but this is completely turned out to be possible to bypass using the internal MSSQL flags.

DBCC TraceOn(1807)

GO

CREATE DATABASE MYDB

ON

(NAME = MYDB,

FILENAME = '\\server\share\MYDB.mdf' )

GO

DBCC TraceOff(1807)



The wonderful bundle of TraceON (1807) - TraceOff (1807) disables the checking of the location of the database files. That allows you to attach a database located on a remote resource.

')

Any operations related to the maintenance of the newly created database will be possible only after changing flags.

Instead of creating a database, you can connect an existing database by first copying database files to a network resource.

DBCC TraceOn(1807)

GO

sp_attach_db 'SomeDB',

'\\server\share\SomeDB.mdf',

'\\server\share\SomeDB.ldf'

go

DBCC TraceOff(1807)

GO



Due to the fact that MSSQL caches the contents of the database, it is possible to detect its heap only when working with non-cached tables, or when performing update or insert procedures. At the same time, depending on the number of connected clients, the database itself may fall into Suspect (meaning when the resource on which the base files are located is disconnected). Raising the base is possible via the sp_detach and sp_attach commands with changing flags. With the hands of the GUI can not raise the base. Strictly prescribing the value of flags in the registry (as recommended by Microsoft) has no effect. Therefore, be careful.

The speed of your connected database will be limited by the speed of your network interface, so think ahead about the server connection topology.

Good luck and good fun :))



(C) Aborche 2009