788
Part V: Enterprise Data Management
SQL Server requires that a database have a PRIMARY fi legroup and that the system tables
(the database metadata) reside in that fi legroup. It is recommended that you keep your
application data out of the PRIMARY fi legroup, both from a manageability perspective and
a performance perspective. When creating a database using SQL Server Management Studio
(SSMS), it can be tedious to create a database with the wanted size and fi le location and
with a separate, default fi legroup to hold the application data. This is a relatively simple
process using SMO.Best Practice
Don’t place application data in the primary fi legroup because the size and location of application fi le
groups are easier to control and manage, especially as the size of the database grows. The database
metadata must exist in the primary fi legroup, and after the primary fi legroup is online, the database
is considered available. As each additional fi legroup becomes available, the data within it is usable by
applications, but smaller, discrete fi legroups can improve the overall uptime for the application data.Listing 30-3 shows the script to create a database. The example database is a database
called MyAppDB, which has a 5MB fi le in the primary fi legroup to hold the database meta-
data. This fi le grows by 25 percent when required, but it shouldn’t be required. The percent-
age or fi xed growth size chosen depends on the actual usage history for the application,
but a growth size too small can cause excessive fragmentation, and a growth size too large
can take too much time when the autogrow event occurs. The logical name MyAppDB_
SysData is used for this fi le and placed in the default data path for the server.The application data is located in a second fi legroup called AppFG, which is then set to be
the default fi legroup for the database. The fi legroup contains one fi le with a logical name of
MyAppDB_ AppData and also is housed in the default data path for the server. The initial
size is set to 25MB and allowed to grow by 25 percent each time it is required, but it is set
to a maximum size of 100MB.Log fi les in SQL Server do not use fi legroups, so a log fi le named MyAppDB_Log is added
to the LogFiles collection of the database and housed in the default log fi le path for
the server. Its initial size is set to 10MB and allowed to grow by 25 percent each time it is
required, but you won’t set a maximum size for the log fi le.After the structural objects have been created for the database, the Create() method is
executed, but SQL Server automatically sets the default fi legroup to primary when a data-
base is created. After it has been created, the script sets the default fi legroup to AppFG
using the Alter() method at both the FileGroup and Database levels. Because of a bug
in SMO, the DefaultFile and DefaultLog properties in the Server object’s Settings
collection don’t properly initialize. Therefore, the script places the fi les in the same loca-
tion as the master database data and log fi les, as defi ned in the MasterDBPath and
MasterDBLogPath properties in the Server object’s Information collection.c30.indd 788c30.indd 788 7/31/2012 9:46:23 AM7/31/2012 9:46:23 AM
http://www.it-ebooks.info