Replication or Transaction log shipping for Reporting?

[web4fun]

PROBLEM/BACKGROUND INFO:

We are currently having problems with SQL locks/blocking with one of our main production databases. This database is accessed by many applications for CRM data and for Reporting which is really hampering performance and availability at times. What we are now looking into is investigating replication strategies to replicate the production database to a replica and point the Reporting users to the replica to ease the load on the production database. The replica needs to be accessible pretty much at all times due to the reporting needs of our end-users which is why we are not looking into transactional log shipping. Our production server is running SQL 2000 SP4 and we plan on having the replica database on the same version.

QUESTION:

I understand that there are three different types of replication methods for SQL 2000; snapshot, merge, and transactional, but I'm trying to assess which method would allow us to meet the goal of alleviating performance / locking issues on the production servers while servicing our Reporting users on the replica with minimal downtime. We are also concerned about the amount of system overhead that will occur during replication which is why I am asking the question. We planned on replicating every hour or two hours at the most. The current production database is roungly 12GB of data and 14GB of indexes. Any suggestions/tips would be greatly appreciated as we this will be the first time we setup replication. Also, upgrading to SQL 2005 is NOT an option due to the incompatibility with the CRM application.
Thanks in advance.

 

[ptheriault]

My first suggestion would be to take a serious look at your indexes. If you have a db that is 12 gb of data and 14 gb of indexes then you have way to many indexes! Believe it or not but to many indexes is a bad thing. They will kill your inserts. updates and deletes.

What are the requirements of your reporting db? Must it be real time? Is once a day ok or multiple refreshes during the day a requirement?

First, merge is out. If someone makes a change to the data on the reporting db it will be merged to your OLTP db. That would negate your OLTP database as the system of record. Snap shot is also out if you plan more than one refresh a day. So that leave transactional replication. It's not a bad option and the overhead isn't that much. However, transaction replication is best when it is set to real time. You don't want a bunch of transaction waiting around to be applied over on your reporting db.

To bad you can't get to 2005. Then you could use a 2005 snap shot. That is the solution I use at my company.

However, you may want to consider this solution. It would take more time to set up but may be a better approach.
Create a DTS job that only refreshes the data that has changed. In most databases you have a set of tables that changes very often and a set that doesn't change.

- Paul
- Experience is the name every one gives to their mistakes.

 

[web4fun]

Hi Paul. Thanks again as always for your great advice. Good to know about the indexes...I will take a look at those. I never knew that having too many was a bad thing but it makes sense that the inserts, updates, and deletes would be hampered as we are already seeing this behavior.

Funny you should mention requirements for our reporting db, we met yesterday and decided that a once a night refresh of the data from production to reporting db would suffice. We do have a subset of users (Tech Support) that need to run reports from pretty much 2 tables to track calls so we're planning on just pushing a subset of data from these tables out to another db and have them run their reports there...we would create views that would run via MS Reporting Services based on their requirements. Comments regarding this plan?

Based on the "new" requirements for once a night refreshing of the data, I see that you would recommend snapshot replication? Can you explain why transaction log shipping wouldn't suffice as well? I understand that with transaction log shipping that 1) It's really only "supported" with Enterprise or Developer editions of SQL 2000 but the Wizard can be obtained via Ebackoffice 4.5 and 2) the database would be unavailable until the transactions are applied, so is it because the transaction logs would manually need to be applied on the reporting db? Just wondering what advantage snapshot replication would have vs. transaction log shipping. The nice thing is that we now only have to worry about a nightly refresh.

Thanks again Paul.

 

[ptheriault]

Given your new requirements I would definetly go with snap shot replication. With log shipping I believe the db must be left in a state of restoring, which means you can use the db for reports. Also, snap shot replication would have the least impact on performance if the snapshot can be run during non-peak business hours.

- Paul
- Experience is the name every one gives to their mistakes.

 

[web4fun]

Hey Paul....gotcha...thanks very much for the advise. We had disregarded snapshot replication for the longest time as we assumed that snapshot replication was unsupported in SQL 2000...it appears that this is not the case. Thanks again.

 

[web4fun]

Hey Paul, one further question / clarification, I had heard and read on different forums that snap shot replication is more suitable for dbs and objects that a rather static where the transactions are minimal? Is this incorrect? The database is quite active as it is our CRM database.

 

[mrdenny]

One thing to keep in mind with snapshot replication, is that when the snapshot is being taken SQL can escalate locks to the table level blocking other users from accessing the table while the snapshot is being run.

It's usually not a problem, but it can be.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ptheriault]

If your requirement is one refresh a day then snap shot is what you want. It will script out the database and the data in each table and run the scripts where ever your reporting db is. So yes the data is static in that sense. So, if you schedule your snap shot for 12 AM. then your reporting db will be as of 12 AM.

- Paul
- Experience is the name every one gives to their mistakes.

 

[web4fun]

mrdenny and Paul, thank you for the tips and clarification regarding snapshot replication. As always both of you have provided extremely valuable information and I thank you both.

 

[web4fun]

One other question to the both of you...we currently run on a NETAPP iSCSI SAN and there is a feature/module for SQL Snapshots, do either of you know or have any experience using this feature? We have heard from some other folks that it works beautifully and so wasn't sure if either of you had come across this?

 

[ptheriault]

Well that gives you another option. I haven't used SAN replication as of yet, but I've heard good things about it and if you have a good SAN admin should be easy to set up. I think Denny could offer you more advice on that than I can.

- Paul
- Experience is the name every one gives to their mistakes.

 

[web4fun]

Thanks Paul...yeah..from what I hear from others...meaning SQL dbas that do use it, they are really happy with it. Hopefully denny can chime in as well. Thanks again.

 

[mrdenny]

I'm always leery of database snaps on a SAN. I believe that netapp uses a crash restartable technique. Basically when you attach the database to the other machine SQL sees it as the system crashed and now needs to come back online. I'm not familiar with NetAPP iSCSI SANs per say so I'm not up to speed on what techniques are used behind the scenes.

I know that the higher the IO on the database the greater chance that the database won't attach correctly to the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[web4fun]

Another question I have involves the the location of the "distribution" server / db. I understand that it can be installed on the publisher server, the subscriber server, or another SQL server instance, but my question is in the case where we currently have an extremely I/O intensive production server now, would it be better to install the distribution server on the subscriber server and have it do a 'pull' from the publication server? Keep in mind that we would only run snapshot replication one a night? The goal is simply to refresh the complete db from the publisher to the subscriber but NOT vice versa...the db on the subscriber would simply be for reporting purposes. Thanks.