September 30, 2011 at 2:39 am
Hi,
I am looking at some form of replication for offload reporting.
I need to get the data from one server to another. Considered ETL as one option.
Can you Replicate from one server to another eg: snapshot replication?
I know you can do it from the same server but can you do it using different servers?
I have tried this but unsuccessfully, using UNC or network Mapped drives.
I am really trying to get a 'snapshot' of a number of tables from one machine to the other as an extract. Then working on the extract for reporting. These can be read-only.
Thanks in anticipation
September 30, 2011 at 2:03 pm
I have not heard too many good things about replication. When there are problems, my impression is that it's not simple to fix.
Is it an option to just empty the tables and re-INSERT from the source daily? If your database is really large and your pipeline is not, that could also be an issue.
Another option to consider is restoring a backup of the source database on the destination - daily, or depending on what schedule the data is needed.
Also consider SSIS if that is your thing.
September 30, 2011 at 8:38 pm
I would recommend that if you have a lot of tables, and if you have a lot of data in those tables, consider transactional replication instead of snapshot. Snapshot replication is kind of like a firehose. Turn it on and you'll get a lot of data all at once blasting over to the other SQL Server. The biggest drawback to transactional is that your tables need to have some sort of a PK. If most of your tables have this, then you're 90% there for transactional replication.
October 4, 2011 at 10:01 pm
Good points by Greg (esp. the SSIS route), but I would go with Stud's reccomendation. IMHO Transactional replication is the way to go...especially if you have a lot of data (and it's very easy to set up outside the box). The main reason I'd recommend this is I've tried many ways of doing it in the past and the headaches are far less with transactional...
Other than the overhead caused initial snapshots (i.e. TABLE locks...when all the data is BCP'd out to a storage location) it runs rather smoothly. Depending on your enviorment, meaning how many rows are generated daily in your system, you shouldn't notice much resources being used by it at all.
However, a few things to note with choosing transactional replication:
1. If possible, place your distribution DB on another server...there's a lot of "churn" going on in there with the replication commands and you'll notice a lot of disk contention going on in your arrays if you don't have sufficient hardware to support it.
2. In referencing point #1 above, make sure the replicated DB's aren't on the same set of disks as the distrubtion database
3. If your system has a lot of text/blob/xml fields and you don't need those for reporting purposes, choose to omit them when you create your publications, otherwise you'll notice some serious slowdown replicating over those columns...(be warned)
4. If your reporting system only needs the past x months of data from the main system, filter your articles (tables) to only replicate the past x months of data.
5. It's better to replicate MORE TABLES than you think are currently needed (I'd recommend all if you have the room). The reason for this is when the time comes to replicate those few extra tables that you didn't think you needed the first time, if you're not very careful about how you do it you'll end up regenerating all the initial snapshots...which in an OLTP system could be a nightmare...not sure how "unscheduled" maintenance windows are in your company, but severely frowned upon at mine 🙂
6. Make sure you choose to replicate schema changes (it's an option when you're setting it up) or every time some column changes in the production environment, it'll blow up your replicated data (and the errors you receive aren't generally too helpful)
7. Bear in mind that replication will only create the clustered indexes on your replcated server. For reporting purposes you will need to create any/all non-clustered indexes required for efficient reporting...so "space" will be something important to consider. I wouldn't reccomend creating all the same indexes from your production environment as they may not actually be needed for reporting.
At our company we use transactional replication for about 98% of our tables and snapshot replication for those which don't have primary keys. The snapshots are all on small tables and run each morning before reporting begins. Your situation may be different though...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 5, 2011 at 12:37 pm
Of course you can do it from 1 server to another! That is the main point! 🙂 Set up your second server as a linked server. I suggest Transactional replication as well. You should also find some scripts to monitor the things that are important to you; i.e. replication latency, rollbacks, insert issues, etc... Become familiar with Replication Monitor. i.e. Inserting tracers, reinitializing subscriptions, starting snapshot agent, etc.
We replicate tons of data from 4 servers through 2 distributors (2 Database servers to 1 distributor) to a reporting server. Also, many failover set-ups for certain databases between the other 4.
Thanks,
Jared
Jared
CE - Microsoft
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply