• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

MS SQL Replication / Mirroring / Log Shipping Query

Hi EE,

I've got a couple of 2005 MS SQL Databases and we are wanting to create them on another server for reporting purposes only... (i suppose a data warehouse of sorts).

The databases don't need to be live to the minute, however they are all pretty small... e.g 3-4gb

I'm just looking for some advice on the best options and any pitfalls anyone has encountered.

Many thanks

Ross Turner
Ross Turner
  • 3
1 Solution

This would allow you to show live up to the minute information if you used transactional replication but you would probably need to update the schema of your database as its probably not completely replication compatible.

Log Shipping

This would be in a read only state but the connections would have to be disconnected every time a transaction log restore happened. Also log shipping generally requires more admin intervention to keep running as it can be a little flakey.


If you mirror a database you can create a readable snapshot on the second server. You would have to create a new snapshot how ever often you wanted the data to be refreshed.


You could set up an SSIS package to export the new data on a scheduled basis. There would be more initial configuration and development.

I would go for either mirroring with a daily snapshot or daily ETL process, just my preference though as all would work.
Any further assistance required?
Ross TurnerManagement Information Support AnalystAuthor Commented:
Hi  EvilPostIt <---- love the name by the way

I just been testing the mirroring solution... i just been having some bother with it but i'm getting there slowly.

Once i got it tested i'll assign point :)


Cool, let me know if you need any further assistance. A friend of mine started another one a while ago called the EvilStapler but he got bored.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now