alternative for backup/restore for seperate Reporting DB

  • Right now we have backup/restore from live system to a Reporting only Database, to alleviate I/O to one server/DB all the time.

    What are some good options/alternatives to this model?

    This DB is 110GB. It takes about 45 minutes to backup/restore; we do it 3 times a day for this system. The backup is done to the network drive, that both the servers can see, and hence restore happens easily.

    Thx for ur input.

    Dan

  • On one of our SQL2k systems we had log-shipping set up for our 1TB database to a read-only copy so users could run reports against that copy: more-or-less what you've got set up (although it sounds like you're using full backups).

    When we migrated it to SQL2k5 we canned the log-shipped copy and instead enabled "read committed snapshot" on the database. Users now report directly against the live database even though it's grown to ~1.3TB, although the tempdb has blown out from ~100GB to ~300GB (at least some of that tempdb expansion is due to one bad report: we've never got around to optimising that).

    An added benefit of snapshot locking is that it greatly reduced the number of deadlocks we are getting: from a few a day to less than a couple a fortnight.

  • Glenn

    What about I/O? Do the users see slow responses? Why did you not consider a separate server or a different model like Snapshot replication etc.

    I see you gained in deadlock issues. But what do you like to have or miss from your existing setup?

    Ours is a heavy read reporting DB; hence we need to keep it separate.

    Just trying to learn from other who have done it differently.

    Thx

    Dan

  • I'd ask how much data changes? And how often? You could use transactional replication, either on a schedule or continuous, to move data across. That would avoid the downtime with restoring.

  • I would think that your networking people are a bit upset with you 3 times per day. That is quite a load, performing a full backup and restoral across thenetwork. You do not say if your database is growing at all, or even if the users need all of the data for their reporrts.

    We have numerous I/O intense databases and many power users who want to be able to perform reporting on up to the minute data. A lot of business decisions are made on this data at times hourly.

    We are performing transactional replication on only the tables required for reports over to a reporting server. At times, we are also using an ETL tool to pull/push data from various servers and loading it into the reporting server. On the reporting server side, we can make use of performing agregations on some data in order to make reports more efficient. We use Crystal Reports for the majority of our reporting.

    Doing this will eleminate your down time making your users happy as well as drastically reduce your network utilization making your networking people as well as other users happy. It also keeps your data up to date real time. (You did not say if real time is a requirement, but it is inferred by the number of times that you do a backup/restore.)

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • If the data for both servers are stored on a SAN, then SAN Snapshots are the best in my option. Once setup, it only takes a minute or two to snapshot a tera-byte server and requires no downtime or even "slow time" on the production server. The reporting server will go down for the duration but it's a very short duration.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve, it is an OLAP. So 200MB of bulk data a day, spread throughout, in chunks.

    Jim, yes, it is growing!, and yes, 100s of users need reports everyday. The data does not have to be up-to-the-minutes, but fresh once in a few hours.

    Both of you have pointed me to Transactional Replication. Generally speaking, does it well with OLAP?

  • Jeff

    Does the DB folks setup the SAN Snapshots (if you have access to the drives where the mdf database is stored) or is it part of the network team's job?

    This is not related to Glenn's thought of read committed snapshot, is it?

    I do not know about SAN Snapshots, but is it like Database snapshots in SS2005?

    http://www.sqlservercentral.com/articles/Administration/2733/

    Thx

    Dan.

  • A database snapshot is not like a SAN snapshot. Once the SAN snaps the disks, a complete, and separate copy is available on separate disks. That takes advantage of the SAN, and your SAN administrators should be able to help you here.

    A read committed snapshot is an isolation level for your queries. The issue with log shipping is that when you restore the db, you need to disconnect users. Depending on your needs, that may or may not work.

    Replication works in smaller chunks, essentially moving the data across as transactions from one system to another. That may or may not work as well. If not having a large chunk of data moved together, this can be an issue. In terms of OLAP, if these are still relational databases, the architecture is the same. The difference is how you may index and lay out files.

    I think you might need to check on each of these technologies, spend a few hours reading on MSDN, SQLCAT.com, etc. Then make a smart decision. It sounds like the SAN snapshot would be great here if that works for you.

  • Steve

    Thanks for the nice comparison of the different technologies that can help.

    Does the SAN enabled for Snap feature, or does it take extra hardware/software to make it work?

    Thanks again

    Dan

  • Does the SAN enabled for Snap feature, or does it take extra hardware/software to make it work?

    Thanks again

    Dan

    Most of the SAN's are Snapshot enabled check with with your Network / Storage Engineer.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • If you are running Enterprise Edition on your reporting server, you can create a database mirror and a database snapshot off of the mirrored database.

    Perform the db snapshot every 4/6/8 hours as needed.

    However, if you have the ability to perform SAN snapshots - that is going to be the better option. Just be aware that some SAN's require the database files be closed before you can snap - others are integrated with SQL Server (using VDI) to freeze SQL Server and perform the snap. Your SAN vendor can help you out with that.

    One other thing, if you do perform SAN snapshots - you might want to consider setting up the databases as read only copies. This will eliminate some locking and contention on the reporting server and could also give you an opportunity to scale out and use multiple instances of SQL Server to access those databases. Lookup scalable shared databases in Books Online (note: Enterprise Edition feature only).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I believe the SAN snapshots we were doing operated kind of like transactional replication where the "clone" was always kept up to date in a parallel fashion. The production source database never needed to be "frozen" and suffered virtually no performance loss. It does take a wee bit of extra disk space though I thought it was very well worth it. We could do a "restore" on the reporting database as many times as we needed right in the middle of the day.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff - without knowing which SAN it was, it's hard to say what is actually possible. For example, I have two types of SAN's available. Netapp and HP EVA's - on the EVA's we can create snapshots easily enough but the database cannot be accessed when it is done (I believe the ability is there on later models, but as with most vendors it is an added cost to buy the additional software needed).

    On the Netapp, we have the ability to create a SAN mirrored database and snapshots off that mirror. So, we could essentially do the same thing that you have outlined and it would work the same way.

    If you need your system up 24/7 - and you have not invested in the utilities to allow for this, then are you truly a 24/7 shop?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams-493691 (10/18/2009)


    Jeff - without knowing which SAN it was, it's hard to say what is actually possible.

    Heh... Agreed... Everything is impossible if you don't know what the possibilities are. I was just stating what we did. It was a while back so I'm not 100% sure bit I believe the SAN was a Clarion. They did the same thing at the last job I left and the only thing I know about the SAN is that it wasn't a Clarion.

    Excellent point on the 24/7 thing.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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