I just attended the pre-conference of Kimberly Tripp at SQL Pass 2005 Europe. The topic was “New SQL Server 2005 features that a DBA need to learn” and I took advantage of this day to cover an area of SQL2005 I still haven’t explored.
One consideration that worth a mention on this blog is the possible use of Database Snapshots to get a consistent state of a OLTP database and transforms its data into a data warehouse. Database Snapshot eliminates the possible locking scalability problem, while it still maintains the load on the OLTP server during query. If this kind of load is unacceptable for your user (but I imagine a lot of cases where, once eliminated locking problems, this is not a real thing to worry about) you can still use a replica of principal database (or a log shipping copy). A SQL2005 feature which apparently could be useful is the Database Mirroring in asynchronous configuration (so you don’t slow down the principal database) but my understanding is that this configuration can’t permit a parallel synchronous configuration (necessary if you want to achieve high availability and data protection) so I presume that a traditional approach still maintains some advantage. Database Mirroring is really orientated to offer data protection feature more than a database copy useful to build reporting solutions.
Comments about this are welcome.