Post reply

‘Near Real Time’ Reporting Copy of OLTP databases (many of them) with a twist! Help

  • April 2, 2013 at 12:35 pm

    #394509

    Version is 2008 R2

    Number of Databases: 1,328 (and growing)

    Database Size: 5MB – 600GB (Depends on client’s size)

    Schema: Single Tenant (All Db’s schemas are the same)

    Cluster: Currently 2 node active\passive

    Right now clients log into a portal and do run reports and since the DB is more of an OLTP these reports as clients grow start to become an issue. For the last 3 years I have optimized the hell out of these DB’s to co-exist in a OLTP\Reporting world but I think it’s time to create reporting copies (or something??) so that locking is at bay and we can start building larger reports without effecting the production DBs.

    Requirements:

    1.Near real-time (1-10min)

    2.A way to automate new reporting replication when new client database is created

    3.Be able to work with thousands of databases

    Currently I’m looking at all kinds of ways but I just don’t know where to start? We only really report on certain databases and would it be best to replicate data to a single data cube where the portal just connects to that database for reporting?

    I wouldn’t be opposed to hiring someone either to help.

    Thanks.

    -Richard King

  • April 2, 2013 at 3:37 pm

    #1602990

    Hi Richard,

    Wow, that is a tough one.

    All of the ideas that come to mind... Log shipping, Replication, Database Mirroring with a reporting Snapshot... all of these would be an administrative nightmare to configure and maintain. Even if you were up to SQL 2012, I can't think of anything that would make this really easy (i.e. read-only secondary replicas). Even all tidy and scripted out, any of those solutions would

    I wonder about backend-SAN replication. If there is a way to replicate the whole stinking instance to another instance just for reporting, it would be pretty easy to direct the connection string from clients to the second instance. What kind of storage do you have? Can you talk to the vendor about replicating the storage?

    Other than that... I could imagine building a DataWarehouse, using SSIS or even native TSQL (maybe Change Data Capture or Change Tracking) to update the warehouse from OLTP.

    Wow, I've dealt with maybe 300 databases on a single instance, over 1,000 is something I care not to see!

    ~Todd

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

Viewing 2 posts - 1 through 1 (of 1 total)

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