Solved

MS SQL Replication / Mirroring / Log Shipping Query

Posted on 2013-07-01
4
393 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now