General question about replication

Question

I am currently researching our options for setting up a reporting source.  I initially looked into Log shipping but the more I read, it appears this may not be the answer.  I have read that if a user tries to access one of the tables during a restore the query will fail.  It appears replication may be the approch that may be the best option.  I am looking at setting up a reporting server that users will be able to run reports against with no interuption.  We currently have users running reports against our production environment which has caused performance issues.  Would replication be the best option for us?

Any advise would be appreciated.

Thanks

Answer 1

Yes, use transactional replication for something like this.

There is a limitation in that each table you replicate must have a primary key.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

U can try doing dirty reads if its ok in ur environment. Just precede every statement running thru reports with 'Set Transaction Isolation Level Read Ucommitted;'.

Answer 3

Hilary,

Is it common to use transaction replication as a reporting solution? Does it offer better performance than not having the replication at all and using same db for operations and reporting?

I mean even with the transaction replication you have the same no of writes going on in your subscriber db or do you use nolocks on subscriber db to get around that?

Answer 4

I further reading, I don't think transaction replication is going to be good for us.  We have a lot of schema changes that are done on our production database.  What about snapshot replication?

Answer 5

But transaction replication can replicate schema changes instantaneously. The decision you make on whether to use transaction replication, snapshot or some other method depends on different factors such as how big is your database, what is the recovery model and backup strategy, how recent data the users of reporting application expect etc

Answer 6

The whole goal for us is to create a data warehouse for Reporting Purposes.  We aren't implementing as a recovery model.

Answer 7

Ideally you should look into SSAS for this purpose.

Answer 8

I should have rephrased that.  We are looking for a "data source" for users to run reports off of.  We currently have the users pointing to our production database and it sometimes causes performance issues.

Answer 9

Say if your users are ok with a day old database for reporting you could simply restore the whole thing once a day before they get in. If you want almost real time data then you could still use transactional replication since as I said it replicates schema changes

Answer 10

A day would not be good enough for them.  1 or 2 hour latency was more what I was thinking.

Answer 11

whats wrong with transaction replication then

Answer 12

Cause I have read that it can be a pain when tables are added or changed.

Answer 13

The most common use of transactional replication is for reporting usage.

In general if you add a table to an existing publication you may experience some locking. Stopping SQL server agent will minimize the locking. However, normally this is minimal.

You can always create a new publication for these new articles.

HTH

Hilary

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 14

Would you suggest putting the distributor on a separate server?  I have been reading that the distribution can cause performance issues on the publisher.

Nick

Answer 15

That depends. My rule of thumb is that if the log reader agent starts locking with the distribution agent its time to move.

However for high volumn transactional replication solutions it is advisable to go to a remote distributor immediately.

It is essential that the remote distirbutor be clustered.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 16

How tough would it be to move the distributor if there were performance issues on the publisher.