SQL 2005 automatically partitioning?

[Erik G]

Once I've added Files for a database across mulitple SAN drives will
SQL 2005 automatically partition data across the Files automatically?
Will it atleast grow across files automatically? Or do I have to do
Scheme Functions, Partitions, and assign the Scheme Partition to each
table in the database?

Thanks


Erik

[Erland Sommarskog]

Re: SQL 2005 automatically partitioning?

Erik G (info@fdaregula tory.com) writes:

Once I've added Files for a database across mulitple SAN drives will
SQL 2005 automatically partition data across the Files automatically?
Will it atleast grow across files automatically? Or do I have to do
Scheme Functions, Partitions, and assign the Scheme Partition to each
table in the database?

From
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/52a503e1-dc18-45b9-9640-e75388baa83b.ht m
in Books Online SQL 2005:

Filegroups use a proportional fill strategy across all the files within
each filegroup. As data is written to the filegroup, the SQL Server
Database Engine writes an amount proportional to the free space in the
file to each file within the filegroup, instead of writing all the data
to the first file until full. It then writes to the next file. For
example, if file f1 has 100 MB free and file f2 has 200 MB free, one
extent is allocated from file f1, two extents from file f2, and so on.
In this way, both files become full at about the same time, and simple
striping is achieved.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx