How to backup SQL Raw Data per 10 mins for reporting

Replication
1

Hello all,

First off thank you all for your patience as I am not a DB Guy. I am an Infrastructure Engineer.

I have been given a problem to find a solution for and I'd love to get your input on this.

My client has on a physical server (A), the following:

  1. MS SQL Server 2008R2
  2. MS SQL Server (Reporting Server)

The data base is backed up fully daily to another physical server (B).

What the client would like to do is:

  1. Backup just the Raw SQL Data with Tables and Views to Server B every 10 mins

Point to be noted is, there isn't any dedicated storage (NAS or SAN) just local disk storage on the server.

The reason for this is they want to run reports against this data every 10-15 mins...

How and what do I need to do to facilitate this? Can someone please point me in the right direction?

Thank you,
Jainesh.

2

You could use Log Shipping.

Database A is set to FULL Recovery Model (ought to be anyway, if it is having continuous data entry during the working day).

Log Backups are taken at intervals (e.g. 10 minutes as you describe)

The log backup is "shipped" (copied) to Server B and restored onto the database there.

The database on Server B is "read only" (you would need full replication if you wanted the database on Server B to be read-write)

This is not quite how you describe it ("Backup just the Raw SQL Data"), so possibly the answer to a different question?!!

To copy "just data" you would have to use SQL Commands.

INSERT INTO ServerB.RemoteDatabaseName.dbo.TableName
SELECT *
FROM ServerA.LocalDatabaseName.dbo.TableName

(The actual code needs to be smarter than this - Insert new records, Update modified ones, Delete stale ones)

for this to work well, particularly on large tables, it needs some "clue" as to what has changed - e.g. a Modify Date. But for large tables, and large volumes of data, it is likely to be very intrusive to database users. We only use this type of approach (on large volumes of data) as an overnight process.

Can they not just run reports on the Live database and save all this aggravation?

1 Like
3

Thank you so much for your answer... Really appreciate you taking out the time to do so!