September 30, 2004 at 10:36 am
I have a requirement to replicate from a SQL cluster (MSCS) to another SQL host, so that this can be used as a reporting server. Reporting generates too much load on the SQL cluster.
I was thinking of proposing log shipping, but the owner of these systems claims to have tried this and was not happy with the result.
There are several commercial replication products which claim to be SQL-compatible.
What worries me is that the reporting server will have to be able to generate reports while also receiving replicated data.
Has anyone done this successfully?
Please explain how. Any inputs welcome.
thanks
johnhb
October 1, 2004 at 5:02 am
Since your reports need to be near real-time, then "log shipping" or "snapshot" replication would be good solutions.
"Snapshot" replication will not interfere with the reports and is simple enough to setup.
October 1, 2004 at 5:20 am
I am a SQL newbie. Please provide a brief explanation of snapshot replication and/or some pointers to where I can read up on the topic.
thanks
October 1, 2004 at 5:21 am
I would advise against snapshot and log shipping as they are not real time. With log shipping and snapshot replication you users will have to go offline on the reporting server as the tlogs are applied or the snapshot is applied.
I would use trasnsactional replication as its latency can be as low as 1 second. It does require each table to have a primary key though.
--
Hilary Cotter
Looking for a SQL Server replication book?
October 1, 2004 at 5:45 am
If the reports are near-real time, say data needs to be current within the hour, then snapshot replication will work fine, since only the changes are applied to the subscriber.
Yes transactional replication is as close as your going to get with keeping the data current. Continuous "merge" replication is within a few seconds too.
As with everything there is a cost associated with "transactional" replication. Here are a couple:
1. If you have a problem with network connectivity to the subscirber, then transactional replication will fail because it's a two-phased commit. That's a pretty robust solution for a reporting server. Is this something the business is going to want...maybe if the reports are critical?
2. ROWGUID columsn are added to the databases. This could potential affect the reports depending on how the queries are written. If the reports are "select * from..." then they'll return the addtional column, so some re-work might have to happen.
Using "snapshot" replicatin will allow for production to function without regard to the subscriber and doesn't add the ROWGUID column.
See the BOL.
Good Luck
October 1, 2004 at 7:15 am
The environment in our company is very similar to what you are attempting to setup in your company. We have our primary or production database to support our operations group then we have a reporting database for all of our reporting needs. The one difference is that our production database is not on a server cluster. The reporting database is replicated from our production database using transactional replication because needed near-real time data for our reporting. Our environment is very stable with our only downtime in the last 6 months caused by a hardware failure.
Some things to think about.
1. If you need near-real time data use transactional replication, but be sure that you set it up so that the snapshots do not lock the publisher's tables. This is important if you are generating the snapshot during business hours. The disadvantage to this set up is all transaction that take place during the time the snapshot is generated have to be applied after the snapshot is generated and delivered. If the database is very active that could take a long time for the delivery to complete.
2. Transactional replication requires that every table being replicated have a primary key. If all your tables do not have a PK, then this could eliminate this as an option.
3. Snapshot replication will lock tables during the snapshot generation. Depending on the number and size of the tables this could affect your production database.
4. Merge replication will allow you to achieve the same thing as transactional but there is the possibility that data on the production database could accidentally be update from an in advertant update on the reporting database.
5. Merge replication also does append the ROWGUID onto every row in a table to identify individual rows across multiple copies of a table. As stated in a previous post, this could be an issue if you have queries written with "Select *".
Since I have not worked in a clustering environment, I cannot say how the cluster will affect the table locks during snapshot generation. But I will say we have had very good luck with our environment.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply