We are looking to offload some of the functions we are currently running against the live database to a read-only copy of the live database. Having done our initial research there seem to be quite a few different options out there for SQL Server 2005, i.e. log shipping, replication, mirroring and snapshotting. We are not worried if our read-only copy trails the live copy by a good 30 minutes and will not be looking to use it for any failover requirements.
Anybody implemented this and have any good ideas which technology is easiest to implement and would meet our needs?
Your requirements all point to log shipping, which I’d do for an exact copy of the whole database anyway. Log shipping is relatively easy to set up. Test it out on a non-production server first to get familiar with it. I’ve heard (from one of the senior people at Microsoft’s tech lab in Germany, not some average Joe Blow) that replicating NAV databases comes with its own set of problems, although I think there are a few people who say they got it to work. If it’s management reporting that you need, I’d consider a data warehouse solution. SQL Server 2005 comes with the entire BI development studio, so you should already have all the tools.
You can also combine the two, where you ship the logs to a second server, and build a DW from there.
Thanks, I had a read of a few chapters of the SQL Server 2005 Unleashed book and it seemed to suggest that either log shipping (as you suggested) or replication would be what we are looking for, so we had a play with the replication features (also because it suggested that log shipping is relatively old hat now and might be phased out by MS in the future). It is really easy to set up and performance seems really good on the key tables we are looking to replicate. We will do a load more testing and then possibly implement it with two subscribers, one for our DW and one for our Business Continuity, but it seems relatively easy to configure.
One of the challenges when replicating is that the replication needs to be rebuilt if the table schema changes at all. That’s one reason I prefer LS replication - by allowing you to only replicate certain fields, its a little more resiliant to schema changes.
I just came back from SQL Server 2008 launch event in Detroit, and log shipping is still in there. They have even made significant performance improvements to it. Given the ease with which you can set it up I would definately think about lo shipping before doing replication.