Mixed Workloads Part 1
I am presenting at the Ft. Worth SQL Server Users Group in April. Details forthcoming but it is basically going to be on running mixed workloads(OLTP and DSS) on the same server. This is part one of what I will be pulling into the presentation.
What is NOT a Reporting Server
Log Shipping
A log shipped copy is not a reporting database. It is the same database that should be optimized for OLTP. You have no control to add supporting indexes. No denormalization. No persisted computed columns. No indexed views. Disconnects can happen midquery. More hardware. However, this is often the easiest solution,
Database Mirroring with Snapshots
This configuration suffers all the limitations of logshipping. However, you must run Enterprise Edition. You can get around the disconnects with creative coding.
Nightly BackupsRestore
Just like log shipping but the data is behind which may be ok based on business requirements. You can get around the limitations of logshiping like indexing etc. However, not practical for VLDB.
Snapshot Replication
This is ok for smaller databases plus you can filter tables and columns if they are not needed. You can get around some of the limitations of log shipping and mirroring but data is stale.
Others
Offline the database, robocopy, attach. DTSSSIS the whole db. SAN Replication. Transactions replication with no reporting modifications.
The problem
You double your hardware and storage with no real reporting gains in most scenarios. This might be acceptable if the reporting environment duals as DR. However, there are better solutions.
What is next?
Moving forward, we will talk about doubling the hardware on OTLP and using SQL 2008 feature to run reporting and OLTP on the same server.
This content is published under the Attribution-Share Alike 3.0 Unported license.
Comments
My RSS
-
My LifeStream
My Presence
