0

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.

Improve this question

2 Answers 2

Reset to default
1

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).

Improve this answer
1
  • I'm using SQL Server Express, which can not serve as a replication publisher or distributor.
    – Patrick J Collins
    Nov 10, 2009 at 7:51
1

If you can handle a slight delay in the data you are reporting on then a easy solution would be to restore the database less frequently.

You can still backup and copy every 5 minutes but just change the frequency of the restore job to once an hour or whatever is appropriate.

Users may still get disconnected but the frequency will be lower.

Improve this answer
2
  • I was thinking about perhaps programming the restore job to wait for all active connections to close, so the moment there is a break in reporting activity the latest data will be applied. The only problem with this is that reporting services seems to keep connections open for a long time. And if I kill the connection and the user tries to enter some new parameters and rerun the report, reporting services returns an error (it is not intelligent enough to open a new connection).
    – Patrick J Collins
    Nov 10, 2009 at 10:28
  • It seems that setting Pooling=False in the reporting services connection stops the blocking.
    – Patrick J Collins
    Nov 10, 2009 at 11:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .