Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Reporting Database for SQL Server

Posted on 2009-06-29
8
Medium Priority
?
199 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
  • 4
  • 2
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 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 2000 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2

876 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