Microsoft SQL Server Database Basics
112 Agent for Microsoft SQL Server Guide
Partitioning
In SQL Server 2005, Microsoft introduced Partitioning, a method that makes
large data sets easier to manage by limiting how much data is read during a
single query. Partitioned tables can be divided among more than one FileGroup
in a database. As you plan a partition scheme, determine the FileGroup or
FileGroups you will put the partitions on. Assigning partitions to separate
FileGroups ensures you can perform independent backup and restore
operations. There are two types of table partitioning:
Horizontal Partitioning
Analyze your data for access trends. Partitioning a table horizontally means
each FileGroup contains the same number of columns, but fewer rows. This
is often done for tables containing data accumulated over time, so that a
smaller window of time may be searched during a query.
Vertical Partitioning
Partitioning a table vertically means each FileGroup contains fewer columns,
but the same number of rows. There are two types of vertical partitioning:
Normalization
This moves redundant columns from a table and stores them in smaller
tables linked to it by a primary key relationship.
Row Splitting
This segments a table into smaller tables with fewer columns so that
joining the
n
th
row from each new table reassembles the row in the
original table.
Database Mirroring
In SQL Server 2005, Microsoft introduced database mirroring, a method of
increasing your database availability. With database mirroring, two copies of a
database reside on computers in different locations, with one database in the
active role and the other acting in the mirror role. Database mirroring is
permitted on only databases using the full recovery model and is not permitted
on the master, msdb or model databases.
Databases acting in the role of mirror are not eligible for backup. Therefore, CA
ARCserve Backup does not show them unless they become "active". However, if
you select the entire SQL Server for backup, mirrored databases in the active
role are included by default, even if they served as the "mirror" when the job was
created.
