I would like to create a read-only copy of my SQL Server 2008 database on a secondary server for reporting and analysis. I've been testing log shipping, configured to run every 5 minutes or so. Alas, there appears to be a stumbling block, for exclusive access is required on the target database during the restore, which in turn requires killing all active connections. This is far from ideal, especially if a user is in the middle of running a report. Any better suggestions?
Edit : I'm doing this on the Express edition.
Best Answer
If you need to keep online access to the data, you've got a couple of options (assuming you're wanting to use just SQL native functionality for the solution, if you are open to 3rd party software and/or hardware, you've got quite a few other options):
1) Replication - most likely transactional replication and a single read-only subscriber (msdn is a good start for an overview, I'd post a link but I can only use 1 at the moment, just google "sql server replication msdn" and it will be at the top)
2) Keep your log shipping configuration to get data to the secondary server and leverage database snapshots combined with a common database and rotating synonyms (see here for details on this type of architecture). This will only work if you are using the enterprise edition on the secondary server (only edition that supports snapshots).