Help

Creating data files for the BIS workspace in SQL Server

Standard GIS storage recommendations favor keeping index and log files separate from vector and tabular business tables. For performance reasons, it is better to position the business, feature, and spatial index tables separately and position FileGroup data files based on their usage pattern. For a multiversioned, highly active editing geodatabase, database files of the VERSIONS FileGroup may be separated and dispersed across available disks to avoid input/output contention.

The following table has the recommended FileGroups to be created for storing the BIS features and table.

FILEGROUPCONFIGURATION_KEYWORD_PARAMETER

BIS_BDATA

Business table

BIS_BINDEX

Business table index

BIS_FDATA

Feature table

BIS_FINDEX

Feature table index

BIS_SDATA

Spatial Index table

BIS_SINDEX

Spatial Index table index

BIS_ADATA

Adds table (versioned)

BIS_AINDEX

Adds table index

BIS_DDATA

Deletes table (versioned)

BIS_DINDEX

Deletes table index

BIS_XMLDOC

XML table

BIS_XMLDOCINDEX

XML table index

BIS_RASTER

Raster table

BIS_RASTER_INDEX

Raster table index

FileGroups

The following script can be used to add FileGroups through SQL Server Management Studio.

USE MASTER
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_BDATA] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Bdata01', FILENAME = N'C:\mssql\data\bisdb\bis_Bdata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_BDATA]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_BINDEX]
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Bindex01', FILENAME = N'C:\mssql\data\bisdb\bis_Bindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_BINDEX]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_FDATA] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Fdata01', FILENAME = N'C:\mssql\data\bisdb\bis_Fdata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_FDATA]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_FINDEX] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Findex01', FILENAME = N'C:\mssql\data\bisdb\bis_Findex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_FINDEX]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_SDATA] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Sdata01', FILENAME = N'C:\mssql\data\bisdb\bis_Sdata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_SDATA]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_SINDEX] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Sindex01', FILENAME = N'C:\mssql\data\bisdb\bis_Sindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_SINDEX]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_ADATA] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Adata01', FILENAME = N'C:\mssql\data\bisdb\bis_Adata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_ADATA]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_AINDEX] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Aindex01', FILENAME = N'C:\mssql\data\bisdb\bis_Aindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_AINDEX]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_DDATA] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Ddata01', FILENAME = N'C:\mssql\data\bisdb\bis_Ddata01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_DDATA]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_DINDEX] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Dindex01', FILENAME = N'C:\mssql\data\bisdb\bis_Dindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_DINDEX]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_XMLDOC] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Xmldoc01', FILENAME = N'C:\mssql\data\bisdb\bis_Xmldoc01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_XMLDOC]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_XMLDOCINDEX] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Xmldocindex01', FILENAME = N'C:\mssql\data\bisdb\bis_Xmldocindex01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_XMLDOCINDEX]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_RASTER] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Raster01', FILENAME = N'D:\mssql\data\bisdb\bis_Raster01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_RASTER]
GO
ALTER DATABASE [BISDB] ADD FILEGROUP [BIS_RASTER_INDEX] 
GO
ALTER DATABASE [BISDB] ADD FILE(NAME = N'bis_Raster_Index01', FILENAME = N'D:\mssql\data\bisdb\bis_Raster_Index01.NDF' , SIZE = 1, MAXSIZE = 800, FILEGROWTH = 1) TO FILEGROUP [BIS_RASTER_INDEX]
GO

By setting the data files initial size to 1 MB, there is no delay in the creation of the FileGroups. You can then resize the data files to avoid fragmentation.

ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Bdata01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Bindex01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Fdata01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Findex01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Sdata01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Sindex01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Adata01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Aindex01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Ddata01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Dindex01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Xmldoc01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Xmldocindex01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Raster01', SIZE = 400MB )
ALTER DATABASE [BISDB] MODIFY FILE ( NAME = N'bis_Raster_index01', SIZE = 400MB )

Use the following to verify FileGroups and data files:

EXEC sp_helpdb bisdb
Feedback on this topic?