INLINE
RE: Seperate Databases and Business Logic.. SQL vs. .NET code?? Design
Ideas/Differences
Afternoon.
Say a particular shop has a few critical SQL Databases that are not
server linked. These DBs while MS SQL based, currently have no
connection (SSIS nor Replication).
A new project calls for a lot of communication accross DBs. There will
be web forms and SSRS reports.
For whatever reason, it's been stated that we will not server link.
What will be the best approach?
Laugh, otherwise you are going to cry. Okay, it may not be that bad, so
let's look at this.
If we build .NET classes with seperate connections that join tables
into ADO datasets, or maybe collections, will this code be useful in
SSRS?
It can. It really depends how much your users like to customize reports
using the RS design tools. You end up designing the aggregates and views,
etc., but users can be very creative. If they dink a lot, and your databases
have a lot of data, a stupid (or ignorant) user of the tool can effectively
bring your business down while he queries something dumb.
Or should the effort be replicated in SSRS repeating any SQL
joins already defined?
Doesn't really matter, as churn in well written .NET code and churn in RS
are both expensive. RS may be less expensive, but I doubt it, as it is not
"native" either (meaning part of the core SQL Server engine).
What complelling reason might there be for not linking DBs and
creating SQL Views?
Performance probably, but the nightmare is here whether you chew on that
elephant or not.
Regarding Views, are they evil?
No. Not at all.
Complex views force you to get away from drag and drop programming, at least
for roundtrips, but I consider that liberating, not constricting. In
addition, you learn what your programming actually does, which is a great
career builder. Note that I am not suggesting you do not code your own,
rather than drag and drop, but it is very common in .NET.
Ah, but looking at the rest of your post, I see you are meaning views across
multiple databases. Yeah, that can get really nasty, especially if you do
not know what you are doing.
Would it not be better to have a view
than to expect every form and report do their own identical join? Is
there any known perforamance difference between a view and a join?
BTW, a view may contain joins, so this is not an either or question. But, I
do understand where I think you are coming from. To that end ...
If the query is ad hoc, the view will be faster as it is precompiled. You
can set up security directly on a view, while you cannot on an ad hoc query.
If you are using stored procedures, or very common sets of sql statements,
SQL Server will create enough stats that there should be no effective
difference between the two.
As soon as you add server two into the mix, you are probably hosed on
performance anyway.
If it's determined that one of the remote DBs is very busy, and very
critical with online updates and sensitive to reporting traffic
(producing deadlock errors), and reporting and online can accept day-
old-data, should an SSIS package to copy data be considered?
Possibly, but not my first choice (see below):
Regarding SSRS. If two datasets with two connections are neccessary.
Will SSRS perform any better or worse at joining data than a linked
DBs with a View?
To add a view from two servers, you will realistically have to link the DBs
in some way. I have not tested, but I think you can do this with simple
dotted notation, but that would scare the buhjeezus out of me.
I suspect from code simplicity, the linked DBs with
the view does sound more elegant. And again, what using a .NET class
that is doing the join from two connections? Possible? good idea?
Thanks for any help or information.
Got tired of questions.
Here is my suggestion.
First, I am assuming this is a reporting database you are designing. It is
aggregating data from numerous sources that the business owners need reports
from. They need reports that span all of the databases.
If the above assumption is true, then your best bet is to set up another
server. This server will be used for reporting only.
Then, set up one-way replication from the live databases to the reporting
database. You can probably use your subscriber (the reporting server) as
your distributor, unless you are running reports all of the time and
hammering it. In this case, a second new server to distribute might be in
order or the live database server can be distributor. The actual location of
the distributor can be changed once you test the reporting application.
You then make it so each database that will be in the report is set up as a
publisher. The interval you publish on will depend on the freshness of the
data and possibly on your hardware. In many instances, one push a day will
be fine. In others, you need nearly up to the minute data.
To get this up faster, you can set one person on figuring out the
replication bits (hire a consultant if everyone is lost here; the few days
worth of consulting dollars spent here is worth its weight in gold if nobody
knows how to do this -- a bad replication set up can be deadly (not really,
but pretty bad ;->)). Another person can then work with a snapshot of each
database on the new server (or better yet, a test server that mimics the new
server) creating the reports.
As you grow, or perhaps as you test, you may find you need to actually put
RS on its own server, which is fine.
You may not like all I have written here, but I have experience with
reporting systems, which it sounds like you are building. The system I
architected, and my team built, had a live server with some reports (only
things that needed up to the minute data, like appointments), daily backed
up data, weekly data and monthly data. Each of the other three were
replicated from the live server. It was a single backup each day which was
automatically restored on one, two or three databases, depending on the
night the backup was run on.
We were only dealing with a single database, so the job was easier, in some
respects. The complexity was sending RS to the correct server for the
correct report. It was not out of the box RS, but it was fine.
In your case, I suggest something similar due to the spread out databases.
There are some other ways you can kludge this together, but they are even
more complex than what I have stated here.
You do have the option to link servers, if you can convince the owners of
the database keys. Sounds like they are not budging. If they do not budge on
replication, then you will have to link the servers with SQL alone or in
..NET datasets. Then, sit back with a cold one and wait for the train wreck.
I hope this is reporting only and some jackleg is not telling you to have it
so the user can edit the data from the report, as that just increases the
speed of the upcoming train wreck.
BTW, if you have not experienced a management induced train wreck, it is a
lot of fun. Oh, the first one, you get really uptight about, as you struggle
on a death march in a vain attempt to fix it, but you eventually get to the
point where you document the upcoming train wreck before it happens. Then
when the fingers point at you, you hold up the shield of documents and watch
the fecal matter hit all around you. That is, if you work for a good company
that was having a blonde moment. If you work for a bad company, you are
fired regardless, but you leave with a good story to post on
www.thedailywtf.com. ;-)
I do not envy you at this moment, as the constraints slapped on you are
potentially harsh. If they do not bite on that, clean up your resume, just
in case. While every developer hates reporting, as it is rote and mundane,
it is truly one of the most noticed aspects of the project when something
goes wrong. In general, nobody notices the train is even moving when it
works, like they do the prettier parts of the application. But one missing
rail on the track and you have some really pissed off passengers out to
lynch the conductor. And it is sure funny how quickly he puts the engineer
hat on your head.
Okay, I am having a bit too much fun right now, but I needed this stress
release today.
--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA
*************************************************
| Think outside the box!
|
*************************************************
