Readonly copy of parts of the database on another server for reporting purposes

Question

Post reply

Readonly copy of parts of the database on another server for reporting purposes

Query Shepherd

SSCrazy

Points: 2988
More actions
October 13, 2014 at 5:31 am

#311807

Hi.
What ist the best way to have a copy of a 2.5TB database once a day on another server for reporting purposes?
I need only parts of the data and only the main file.
Greetz
Query Shepherd

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

What do you mean "main file"?

Snapshot replication may be what you're looking for.

-- Gianluca Sartori

Query Shepherd SSCrazy Points: 2988 More actions · Answer 2

spaghettidba (10/14/2014)
What do you mean "main file"?[/b]

Thanks for your answer. I got several db-files, but I only need the main-mdf-file...

Greetz
Query Shepherd

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I think filegroup restores are for you. You may need to organise your database first so that only the file in question is in the filegroup you're going to restore.

John

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

John Mitchell-245523 (10/15/2014)
I think filegroup restores are for you. You may need to organise your database first so that only the file in question is in the filegroup you're going to restore.
John

As far as I know that will work only in Enterprise Edition, so check which edition you're on.

-- Gianluca Sartori

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

Replicate the tables you need.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

spaghettidba (10/15/2014)
John Mitchell-245523 (10/15/2014)
I think filegroup restores are for you. You may need to organise your database first so that only the file in question is in the filegroup you're going to restore.
John
As far as I know that will work only in Enterprise Edition, so check which edition you're on.

Gianluca, do you have a reference for that, please? I can't find anything to back it up. But you and Sean are right: snapshot replication may be the way to go. It'll depend on what "parts of the data" means.

John

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 7

Not transactional replication? If you snapshot entire tables, that could be a lot of data, depending.

Do you need stable reporting throughout the day, or more up to date date.

Enterprise Edition has partial restores, filegroup restores, which are limited to that edition. However these might be a pain, as you'd still need log restores potentially, depending on how you do things.

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

If you want a snapshot of the database at a given time, transactional replication is not for you.

If you want data to be in sync with the publisher, it might be what you're looking for.

-- Gianluca Sartori

Query Shepherd SSCrazy Points: 2988 More actions · Answer 9

Hi,

thanks for your help. I don't need the data througout the. Snapshot replication is taking too long...the database is about 2.5TB and the snapshot takes 1:30 for the creation of the bulk files (while holding a lock on the tables) and 5:30 for the bulk inserts in the target db...

Backup and restore takes also 7:00...

It's just too long...I'm open for other ideas...

Greetz
Query Shepherd

arnipetursson SSCertifiable Points: 6557 More actions · Answer 10

You could use mirroring and and a daily snapshot for read-only access.

Log shipping is another option.

How big is the .mdf file compared to the rest of the db?

Query Shepherd SSCrazy Points: 2988 More actions · Answer 11

A normal snapshot is not possible, because afaik the databas has to be on the same instance, but she is on another server...

...log shipping was also my last idea...

Greetz
Query Shepherd

tripleAxe SSCertifiable Points: 5706 More actions · Answer 12

Do you use a SAN to provide storage to the SQL server? If so there may be something you can do at that level to get a copy of the database to another server. Obviously, you'll need to get your Storage Admin guys involved. I've had to do this a couple of times in the past with different vendor technologies such as Hitachi and NetApp.

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 13

There's no magic here. You need to transfer the data. If you have large amounts of data in an mdf, then whether you use backup/restore, replication, SAN mirroring, etc, you have to take the hit to move the data.

The question is do you move it often or not, and how can you tell what's changed. If you have a small amount that's changed regularly (daily, hourly, whatever), then I'd say you should use transactional replication and just move that data. You will have a slow snapshot to start, but once that's done, it will be quicker.

Anything else, backup/restore/LS, mirroring, SAN snapshots, are all grosser transfers, which may (or may not) move way more data than you need.

You haven't really described enough of the issue and details to give a better recommendation.

BTW, none of this requires the read only database to be on the same instance.

ScottPletcher SSC Guru Points: 101078 More actions · Answer 14

Do you have room on the target server for two copies of the db? If so, you have plenty of time, and can bring the second db fully current and up very quickly at the desired final time.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".