Solved

Reporting Database for SQL Server

Posted on 2009-06-29
8
195 Views
Last Modified: 2012-05-07
Hi,

We are looking for implementing Transaction Replication for creating a separate reporting database for our large SQL Server 2008 database. The source database has estimated size of 100TB for 1 year - 60% of the data is "insert once" and "reference only (no update)".  There are about 8-10 very large tables estimated to have few terabytes of data (We plan to implement partitioning and filegroups creation of I/O balancing). The tables have average rowsize ranging between 80 bytes to 3Kb and on an average the maximum no. of rows inserted per day could be 100 transactions X 4 million users in a single table.

Will transaction replication suite this kind of database for reporting database creation - (where Transaction rate is not very high but data storage needs are high)?

We are also thinking  of geocluster solution for database HA/failover. However, not sure if geo cluster will provide read only access to replicated database in windows cluster - which may be used for reporting purpose.

Any pointers in this area are highly appreciated. Thank you.
 
0
Comment
Question by:mganesh
[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
  • 4
  • 2
8 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24741312
If you plan for High Availability, then you can go for Transactional Replication / Transactional Replication with Updateable subscriptions.

Otherwise if your objective was to use the new database only for reporting purposes, then you can either do log shipping which would suffice.

Since your question is pointing with respect to high availability and Reporting database, I am not in a position to understand your exact requirement.

If it is High availability, then Server1 will be up and running and Server 2 will act as a standby with replication or whatever.
If it is for Reporting purposes, then both Server1 and Server2 will be up and running.
Main application points to Server1 and Reporting Applications point to Server2.

The difference here is that in High Availability users will be able to see both as a single server and hence both application and reporting application will use the active one.

Kindly explain whether your requirement is either High Availability or Reporting purposes.
0
 
LVL 3

Author Comment

by:mganesh
ID: 24741546
Hi rrjegan17,
Thanks for your update - we have requirement of both HA/failover solution and Reporting database.
We are exploring possibility of having a separate database for reporting to reduce load on main database server . My understanding is database on node 2 cannnot be accessed in Active/Passive cluster configuration. Hence was thinking about how to best implement the required solution.....
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24741663
>> we have requirement of both HA/failover solution and Reporting database.

Then you need to have three servers with Server1 and Server2 serving for High Availability mode and then Server3 can either be the snapshot of the Active database or the Log shipped version of Active Database.

Kindly go through the link below so that you might have more idea on what I am referring to.

http://msdn.microsoft.com/en-us/library/ms151799.aspx

Hope this helps
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 3

Author Comment

by:mganesh
ID: 24741960
Hi rrjegan17,

Yes - I have referred to this. My question is related to latency that may be caused due to transaction load involved here. Do you see any issues with the overheads / performance impact of mirroring/replication on source db?

(There are about 8-10 very large tables estimated to have few terabytes of data (We plan to implement partitioning and filegroups creation of I/O balancing). The tables have average rowsize ranging between 80 bytes to 3Kb and on an average the maximum no. of rows inserted per day could be 100 transactions X 4 million users in a single table.)
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24743177
You can experience maximum latency of 5 to 10 sec but it will not add a overhead to your performance issue.
This replication / mirroring process on source db can utilize 5 to 10 % of your CPU the max..

As you have the records split across per second itself, you wont be having any issues.
In cases like, if you try to do it at a single shot, then you may face the load issues and operational issues.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 25492043
Split between 24741312, 24741663 and 24743177
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 118
date diff with Fiscal Calendar 4 78
Parsing this XML works but the other one doesn't 9 30
the way to learn Microsoft BI 13 50
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

726 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