concept filegroup in category sql

appears as: filegroup, filegroups, filegroups, filegroup
SQL Server MVP Deep Dives

This is an excerpt from Manning's book SQL Server MVP Deep Dives.

SQL Server requires that a database have a PRIMARY filegroup and that the system tables (the database metadata) reside in that filegroup (in fact their location cannot be changed). Best practices recommendations include keeping your application data out of the PRIMARY filegroup, to help in managing the disk files for the database. When using SSMS, it can be tedious to create a database with the desired size, file location, and with a separate, default, filegroup to hold the application data. This is a relatively simple process with SMO.

Listing 4. Creating a user database
#createdatabase.ps1 #Creates a new database using our specifications param ( [string]$srvname='MyServer\MyInstance', [string]$dbname='MyAppDB', [int]$datsize=25, [int]$maxsize=100, [int]$logsize=10 ) # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries $v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') $p = $v.FullName.Split(',') $p1 = $p[1].Split('=') $p2 = $p1[1].Split('.') if ($p2[0] -ne '9') { [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null } $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srvname # Instantiate the database object and add the filegroups $db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($srv, $dbname) $sysfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'PRIMARY') $db.FileGroups.Add($sysfg) $appfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'AppFG') $db.FileGroups.Add($appfg) # Create the file for the system tables $dbdsysfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($sysfg, 'MyAppDB_SysData') $sysfg.Files.Add($dbdsysfile) $dbdsysfile.FileName = $srv.Information.MasterDBPath + '\MyAppDB_SysData.mdf' $dbdsysfile.Size = [double](5.0 * 1024.0) $dbdsysfile.GrowthType = 'KB' $dbdsysfile.Growth = [double](5.0 * 1024.0) $dbdsysfile.IsPrimaryFile = 'True' # Create the file for the Application tables $dbdappfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($appfg, 'MyAppDB_AppData') $appfg.Files.Add($dbdappfile) $dbdappfile.FileName = $srv.Information.MasterDBPath + '\MyAppDB_AppData.ndf' $dbdappfile.Size = [double](25.0 * 1024.0) $dbdappfile.GrowthType = 'KB' $dbdappfile.Growth = [double]($datsize * 1024.0) $dbdappfile.MaxSize = [double]($maxsize * 1024.0) # Create the file for the log $dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($db, 'MyAppDB_Log') $db.LogFiles.Add($dblfile) $dblfile.FileName = $srv.Information.MasterDBLogPath + '\MyAppDB_Log.ldf' $dblfile.Size = [double]($logsize * 1024.0) $dblfile.GrowthType = 'KB' $dblfile.Growth = [double]($logsize * 1024.0) # Create the database $db.Create() # Set the default filegroup to AppFG $appfg = $db.FileGroups['AppFG'] $appfg.IsDefault = $true $appfg.Alter() $db.Alter()
  • Placing filegroups for partition schemes
  • The NEXT USED property of filegroups in a partition scheme