Solved

Reporting Database for SQL Server

Posted on 2009-06-29
8
196 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

734 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