Solved

MS SQL Replication / Mirroring / Log Shipping Query

Posted on 2013-07-01
4
409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

623 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