Indexing and DB 1

[traydeezy]

I was wondering if the Index could be located on my local server and have the actual DataBase located on a NAS device?

Thanks.

 

[mrdenny]

SQL Server doesn't support NASs.

All database files need to be on local storage.

While using a NAS is possible it isn't supported by Microsoft.

All that said indexes can be placed within seperate files that the data. This is done by placing the index within a different filegroup on index creation.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[miq]

Hi,
But only non clustered indexes can be saperated from its table clustered index should reside with its parent table.

I believe SAN is support by sql server mrdanny, correct me if i am wrong.

B.R,
miq

 

[mrdenny]

Yes a SAN is supported by SQL Server as it considered attached storage by the OS. NAS is not because it goes over the IP Network and leaves open to many variables that can slow down the server.

As miq said the clustered indexes must remain in the same filegroup as the table it self. I probably should have mentioned that above.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[traydeezy]

Thanks mrdenny and miq.

Have you guys read this article:

http://www.sqlteam.com/item.asp?ItemID=128

It says that you can use ms sql with nas appliance. Is there any truth to this?

Thanks.

 

[mrdenny]

It is possible, but it's not recommended. It is also not supported by Microsoft.

What this means is that if you have a problem with your data files hosted on a NAS device, Microsoft will not assist you as you have placed your database server in an unsupported configuration.

If you place your data files on a NAS then your database is now at the mercy of the network. If the network slows down the SQL Server won't be able to access it's files. Or worse, if the network has a hickup from a switch or router rebooting, then the database files can become corrupt. Causing you to loose your entire database.

I'd have to say that loosing the production database is not worth the small cost savings of putting the data files on a NAS.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[traydeezy]

Thanks again mrdenny.

One final question. I have an application that is database intensive, it is constantly making reads/writes to the database. Currently the DB resides on a Dual Xeon 3.2GHz, 4GB RAM, SCSI U320 RAID 10 configuration, 15K RPM HDDs but the server seems bogged down. What can I do to improve the performace? I was hoping that putting the DB on a NAS would improve performance.

Thanks

 

[miq]

HI,
Does the server seems to bogged down or its just the application that make it looks like that. If its server itself (to analyse - try running sample performance testing code in QA) then you can do many things to improve its performace like, more / proper use of indexes, regular DBCC checks checkdb, dbreindex etc, avoiding triggers, cursors, dynamic sql etc. also, profiler may help.
But if the culprit is off-the-shelf application, i guess you cannot do much.

B.R,
miq