Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kfarlee
Microsoft SQL Server Product Team
9 Posts |
 Posted - 2005-12-19 : 19:26:57
|
Microsoft SQL Server 2005 Enterprise Edition adds support for Scalable Shared Databases. The scalable shared database (SSD) feature enables the scale-out of databases which are used exclusively for reporting (read-only access) with periodic updates. Specifically, SSD allows you to attach a read-only reporting database to multiple server instances over a storage area network (SAN). This means that you can use multiple commodity servers to scale out your reporting access without having to copy the database and multiply your storage requirements. Because the reporting database resides on one or more read-only volumes, there is no possibility of data corruption due to un-coordinated updates from either SQL Server or the file system. BenefitsScalable shared databases offer the following benefits: - Provides workload scale-out of reporting databases using commodity servers. A scalable shared database is a cost-effective way of making read-only data marts or data warehouses accessible to multiple server instances for reporting purposes, such as running queries or using SQL Server 2005 Reporting Services.
- Provides workload isolation—each server uses its own memory, CPU, and tempdb database. This prevents a runaway query from monopolizing all resources and impacting other queries.
- Guarantees an identical view of reporting data from all servers. This assumes that all of the server instances are configured identically. For example, all servers would use a single collation.
RestrictionsThe following restrictions exist for a scalable shared database: - The database must be on a read-only volume.
- The data files are accessible over a SAN.
- The SSD feature is supported only on Windows Server 2003 SP1 or later.
What SSD is not- SSD is not for databases which need to be kept in-sync with continuous updates.
- SSD is not for long-distance replication/HA.
- SSD is not an automatic failover mechanism.
Updating a scalable shared databaseThere are three phases in the lifecycle of a scalable shared database: - Build – the database must be created on a read-write volume, detached, and the volume set to read-only
- Attach – This read-only volume is then attached to multiple reporting server instances/machines
- Detach – When the database must be refreshed, it is detached from all reporting servers, and the cycle starts over.
Note that the build phase may consist of using some form of ETL to build a new copy of a data warehouse, or it may involve temporarily making the database read-write, and updating it to bring the content up-to-date. Another option in a SAN is to use a split-mirror scheme to very quickly take a point-in-time snapshot of a production database. The Windows VSS technologies can be of great help here.Another option to increase availability is to maintain an extra set of volumes for the scalable shared database such that one copy can be rebuilt/updated while the first is being used for reporting. The two are then swapped with very little downtime for the reporting servers.Kevin FarleeSQL Server Engine PM |
|
|
|
|
|
|
|
Scalable Shared Database in SQL Server 2005