April 4, 2012 at 1:00 pm
I have a project to move several reporting databases from our ERP server to our main SQL box. Everything is SQL Server, currently 2005 but it will be 2008 R2 later this month (we're version limited by the ERP vendor).
I'm a little leary of replication. We're not in need for having a failover copy as we have DPM on the ERP box, so data integrity is good, and because this POS uses an application server running an alleged 4GL that does RBAR, hot standby is a very difficult concept so we're not concerned about that at the moment (I hope!).
Since this is just a reporting server and no data in the ERP database is going to be updated, I'm thinking going with a linked server pointing to the one database and giving it the datareader role. But I am a little concerned about aliasing/synonyms as there's probably a hundred or more tables that will be referenced. I suppose that I could script it out without too much difficulty.
Suggestions? Warnings? Thanks!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 4, 2012 at 1:35 pm
Any particular reason you're not considering doing a backup and restore? Even with large DBs you can fairly quickly get up to current point in time by doing something like this:
Server A: Back up database
Server B: Restore database
Server A: Take offline, take differential/final log
Server B: Restore differential/log chain
If you can put the DB in full recovery you can restore everything but the last log backup so you don't have much data to restore before server B is ready to take load. Another option I've heard of but haven't tried is to mirror the database between the two servers. This lets you fail over the mirror very quickly and server B will pick up in seconds. Either of these will likely be easier than either replication or copying data via a linked server.
April 4, 2012 at 2:03 pm
Backup/restore wouldn't work, I need real-time info and the DB is 100 gig. Failover isn't really an issue because of the application server. I don't need the data on the reporting instance, I just need to read the live data.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 5, 2012 at 7:15 am
Wayne,
Wouldn't using a linked server defeat the purpose of moving reporting db's off the ERP server? You'd still be putting much of the load on the ERP server. I think replication is probably a better solution than linked servers in this case. Once initialized it shouldn't have big impact on the ERP server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 5, 2012 at 12:37 pm
a reporting server that uses links into the production data is a bad idea. Reporting queries, especially ones that aggregate a lot of records from the operational data are long running resource intensive and cause locking issues that you do not want in your production system.
use replication or better yet mirror the database and then use snapshots on the mirror to provide acess to the data. You can schedule a drop and create of the snapshot to refresh.
April 5, 2012 at 12:59 pm
andersg98 (4/5/2012)
use replication or better yet mirror the database and then use snapshots on the mirror to provide acess to the data. You can schedule a drop and create of the snapshot to refresh.
I was thinking along the same lines but that wouldn't be real-time. SQL 2012's readable secondary would be a good fit here but that may not be an option.
April 5, 2012 at 2:05 pm
Real time reporting although discussed as a requirement often is really not a practical goal. Rarely can the business react to anything in real time and certainly the cost of actually achieving real time reporting eclipses the savings achieved by having the report.
The best solutions I have seen for real-time ETL to reporting database was:
Transaction replication of operational database to an ETL SQL server
Enable change data capture on the tables needed
Using the change data capture views determine the primary keys of the new and/or changed rows then using SSIS select out those rows to be upserted to the reporting warehouse.
April 5, 2012 at 2:44 pm
Wayne West (4/4/2012)
Backup/restore wouldn't work, I need real-time info and the DB is 100 gig. Failover isn't really an issue because of the application server. I don't need the data on the reporting instance, I just need to read the live data.
Just an FYI, even Replication has a delay. Depending on how strict those requirements are, I'd personally go with transactional replication or mirror/snapshot with the snapshot going every 5-10 minutes. Mirror/Snapshot is a lot easier to maintain, but it's not 'realtime', as you say. It can be pretty close though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 13, 2012 at 2:11 pm
Thanks for all your replies. Sorry I haven't been actively interacting, it's been a busy couple of weeks.
--The real time requirement, or near-real time, is pretty important as most of the reports need to be current. One part of the real time requirement involves building/construction inspectors. The field inspectors get a printout every AM of what is pending. As they work the list, they update the permits which makes them fall off the list, meanwhile new permit requests come in at City Hall. When they're done with the list, they pull up another on their field computer and see what new work is out there. But more importantly is the people in City Hall who enter construction project plans and need to generate reports instantly. Stupid ERP vendor reports were unusable, so I had to write a custom app that needs real time data.
--SQL 2012's readable secondary is not viable as we can't run 2012 on this application, it's not supported by the vendor.
So I guess it will be replication or mirroring. I was thinking transaction log shipping, but that's not going to give me my real time requirement. Why do I think this is going to be a massive PITB? 😉
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 16, 2012 at 11:53 am
I think replication is going to be your best bet. With mirroring you don't get a constantly updated secondary. What you need to do is create a snapshot (which is static) and then read from that snapshot. To get new info you need to drop the snapshot and make a new one which would interfere with any reports running at the time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply