[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Reporting Database for SQL Server

Posted on 2009-06-29
8
Medium Priority
?
201 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
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.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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