February 27, 2007 at 10:33 am
Hi All,
I have a medical information related database, and on a daily basis, feeds come into the database, i.e Personprofile20070226, Personprofile20070227 etc.
The problem now is that, we now have a new set of requirements, as we don’t want users going into the production server, we acquired a separate server for reporting, the contractor who designed the system has designed it in such a way that the production database server spools out text files, and he has put a loader on the reporting database which is on the receiving end, the idea being that all things being equal, they should have the same information.
When I questioned the developer, he said that SQL server replication isn’t that reliable to do such a thing, as the tables are daily tables with a date prefix after the name.
I was wondering if anyone here has worked in situations like this, as I am researching into other ways of replicating the 2 systems together moving away from the flat file method and not also using Ms SQL server replication, I think replication is great, but bear in mind that the size of a Days table is almost 15GB, and if replication is set up, this might affect the production database, as it’s a mission critical system that has to be up all day long, all year round.
I look forward to your ideas.
February 27, 2007 at 1:01 pm
Personal point of views.
Replication is nothing more than triggers. You can add triggers on your tables to update your reporting server. After creating triggers, you create your own replications. 🙂
If you do not want to use MS replication, you can use log shipping. But log shipping ships all transactions to your reporting server.
February 28, 2007 at 3:55 am
I don't have too many problems with ms transactional replication, there's certainly some interesting things to be aware of. but I've used it for several years without tooo many problems.
I'd advise against using triggers.
I've also used log shipping for reporting - this is slightly more intrusive in as much as your database becomes read-only and you have to exclude users during log restores, you can do this programatically, I found around 5 mins was the ( optimal ) minimun time interval.
I currently have reporting databases working with log shipping and transactional replication, although they are small fry compared to yours!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 1, 2007 at 4:31 am
I think the issue here is that there is a new table created for each new day. Therefore, to have replication, you'd have to keep creating new articles and initialising them - very messy.
John - what is the requirement for the reporting database? Does it have to be real time or can you just back up the whole database at night and restore it on to your reporting server?
John
March 1, 2007 at 7:24 am
If you are using SQL 2005 you my try with a snapshot on a daily basis.
Moreno
March 1, 2007 at 8:29 am
From Books Online, here's something that might rain on your parade:
"Database snapshots are available only in the Enterprise Edition of Microsoft SQL Server 2005"
And here's something that definitely will (my italics):
"Multiple snapshots can exist on a source database and always reside on the same server instance as the database"
John
March 16, 2007 at 4:38 am
Hi Guys,
Thanks for the reply, just been away for a well deserved break.
The reporting server needs to be near real time, sasy 2 hrs behind from current time during peaka hours, and as low as 15 mins delay in the evening, and the database itself is very huge, we talking of nearly 3TB, if i were to be backing that up/taking a snapshot everyday, that would require a lot.
As for replication, because the tables are daily tables, it means that the articles to be published canno be predefined, or can be to a certain extent as the tables are created for an extra week, but obviously the tables created for the extra week will be blank as at today.
Also the snapshop option, its only the daily profile table that needs to be replicated on reporting, and not the entire database, so this will be another problem.
What do you guys think ?
March 16, 2007 at 4:49 am
John
It sounds like you're going to use replication and automate the creation of new articles every day or week after the tables have been created. Or perhaps you can use the same feeds to load the data into the reporting database? That's unless you can change the design of the database - whose idea was it to have a new table every day?
John
March 19, 2007 at 5:40 am
Hi John,
I don’t know about creating replication articles on a daily basis, can this be scripted, and how reliable is this to automate with the ability to let it run with little or no interruption.
At the moment, the replication using flat files works, but depends on a lot of factors, and is rather complicated to write/manage.
Imaging if something went wrong, you have to find out if the problem is from the source/destination and then sort out the problem depending on where the issue is.
Also, what is the replication like on very large data, I am talking of replicating around 50GB of data daily.
Also, the 2 systems are not the same, I have SQL 2005 on live box and SQL 2000 on reporting box, will this be an issue.
As I had rather work with 2 x SQL 2005 installations
March 19, 2007 at 5:57 am
John
Yes, you can script the creation of the articles. I imagine the creation itself would be fairly reliable, but what happens after that - who knows? I've never worked with the volume of data you're talking about and I've never dynamically created replication articles.
I think you can work with heterogeneous platforms (2005 and 2000), but check out the documentation - it will tell you exactly what you can and can't do and how to do it. Sounds like you've got an afternoon (at least) ahead of you with your head buried in books!
I still think the best solution is to look at the design of this system... but I don't know how practical that is for you.
John
March 21, 2007 at 5:54 am
You could script the creation of the articles each day, I think over time that might get unwieldy unless you're also dropping off old tables at some time interval. Not sure why you wouldnt just have one table with one extra column that indicates the date/data feed name, much easier to manage - and replicate if you decide to.
Replication is very reliable in my experience, far more so than most locally built solutions. That's not to say there is never a reason not to build your own solution, but it's rare. I'll also say that replication is not just triggers. Merge runs on triggers, but transactional uses a log reader to pull matching operations out for distribution. Fast and lightweight.
Importing the files onto separate machines rather than importing onto one and replicating is a fair approach (though I think he justified it poorly) as long as the data is read only. If you're going to sync updates too, I think replication works better.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply