Solved

MS SQL Replication / Mirroring / Log Shipping Query

Posted on 2013-07-01
4
401 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:Ross Turner
  • 3
4 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 39290288
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39293393
Any further assistance required?
0
 
LVL 7

Author Comment

by:Ross Turner
ID: 39295905
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39295909
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question