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
LVL 7
Ross TurnerManagement Information Support AnalystAsked:
Who is Participating?
 
EvilPostItConnect With a Mentor Commented:
Replication

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.

Mirroring

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.

ETL

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.
0
 
EvilPostItCommented:
Any further assistance required?
0
 
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 :)

Cheers

Ross
0
 
EvilPostItCommented:
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.
0
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.

All Courses

From novice to tech pro — start learning today.