EDIT: I think the original question (below) was not worded very well, and probably caused confusion. The basic question is:
If I have a SQL Server 2005 database using a single
.mdf file and I change it to use 2 files, should I expect performance to increase, decrease or remain the same?
We have an archive database that stores all reports sent out from our company since the beginning of time. As you can imagine the size of the database is large and grows quite quickly (currently 150GB and a few GB a week).
The only table in this database has a numeric primary key and a
varbinary(MAX) column holding the file. All documents are retrieved via the primary key and performance is not currently an issue.
The problem we are facing is from the network team who backup and transfer the
.mdf file over to our DR site every night. For them it would be better if we had multiple smaller files, rather than one big file. They are also saying it would make it easier to manage allocating space for future growth from our virtual infrastructure that it is sitting on.
Are there likely to be any performance loss/gain in splitting the table across multiple files on different virtual drives (potentially different physical drives).
I don't want to explore the problems of storing documents in a database, or running SQL Server on a VM, and we don't really want to change the database schema if we can help it, due to the cost in rewriting chunks of multiple apps.