Managing the Core Database Engine - Microsoft SQL Server 2008 R2 Administration - page 112

Designing and refreshing a Scalable Shared
database
Designing a Scalable Shared Database (SSD) feature in SQL Server 2008 R2, allows the
DBAs to scale out a read-only database (reporting database), which is a copy of a production
database, built exclusively for reporting purposes. SSD feature has been part of SQL Server
from 2005 Enterprise Edition onwards, that has been enhanced since SQL Server 2008 and
this is supported in Enterprise edition and Data Center editions only. To host this reporting
database, the disk volumes must be dedicated and read-only, and the scalable shared
database feature will permit the smooth update process from production database to the
reporting database.
The internals behind such a process of building or refreshing a reporting database are known
as the build phase or refresh phase, depending on whether a new reporting database is
being built or a stale reporting database is being refreshed. The validity of a scalable shared
database begins from building a reporting database on a set of reporting volumes and that
reporting data eventually becomes too outdated to be useful, which means that the stale
database requires a data-refresh as part of each update cycle. Refreshing a stale reporting
database involves either updating its data or building a completely new, fresh version of the
database. This scalability feature is supported in Enterprise Edition and Data Center editions
only. This recipe will cover how to design and refresh a reporting database that is intended for
use as a scalable shared database.
Getting ready
Keeping the reporting database refreshed is a prerequisite as part of each update cycle. The
key aspect of having an updated reporting database can be achieved by using the data-copy
method, which requires the following:
F Create or copy a database by designing a SSIS package to use. Execute SQL Task
method or Transfer Database task method.
F From SSMS, use SQL Server Import and Export wizard to copy required objects for the
reporting purpose.
F Restore a backup of the production database into the reporting volume, which will
involve a full database backup file to be used.
F The essential components such as, SAN storage hardware, processing environment,
and data access environment must be used. The reporting database must have the
same layout as the production database, so we need to use the same drive letter for
the reporting volume and the same directory path for the database.
Additionally, verify that the reporting servers and the associated reporting database are
running on identical platforms.