Reporting Database for SQL Server

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.
 
LVL 3
mganeshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mganeshAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mganeshAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Split between 24741312, 24741663 and 24743177
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.