Split Ms SQL Database

[Former Member]

Hi All,

Request you to give some information and your valuable suggestion in deciding the best procedure to Split Ms SQL 2000 server Database files.

by default SQL was in auto-extandable mode and its growing, now we would like to split them in to few data files and planning to distribute the load across different spindles in RAID arrays to get max I/O.

what will be the idel/best practice to split ? is that by SQL script (Query analyzer) or 3rd party tools to split database files in to multiple files?

how can we confirm the consistency/integrity of DB after splitting?

type of query to run for spliting and mapping DB files after moving to different locations in SAN?

hope that you'll give some valuable information on this,

--

by

Harika

[Former Member]

The best practice says :

1. The no of datafiles should be equal to the number of processors.

2. The datafiles should be equal in size.

3. There should be equal amount of free space in all datafiles. So that they can grow equally.

4. Each datafiles must be kept in separate disk (i.e. separate physical I/O).

5. Datafiles must reside in RAID5 and logfiles in RAID1.

Subhadip

[markus_doehr2]

The easiest way to do this is to create a homogeneous system copy using R3load - means, you unload your system, delete it and reinstall it with the export you created.

On installation time you can enter the number of files and their sizes.

A common best practice approach is to use as much data files as you have physical disks.

Markus