Seperate Databases and Business Logic.. SQL vs. .NET code?? DesignIdeas/Differences

W

wildman

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?

If we build .NET classes with seperate connections that join tables
into ADO datasets, or maybe collections, will this code be useful in
SSRS? Or should the effort be replicated in SSRS repeating any SQL
joins already defined?

What complelling reason might there be for not linking DBs and
creating SQL Views?

Regarding Views, are they evil? 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?

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?

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? 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.
 
C

Cowboy \(Gregory A. Beamer\)

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.
 
W

wildman

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.
:)


Thank you! I think the above sums it up nicely. I'll admit I have
never joined tables from two db seperate servers through two .NET
collection classes before. I'm new here, but here they already have
applications doing this and I plan to review that code Today .. if for
no other reason out of shear curiousity. I am very skeptical that
somehow that will perform anywhere as well as letting a database
server perform the join after a link. There is a school of thought
here that says moving that operation off one of the DB servers and
into an Application Server/ Layer will reduce DB overhead and make for
a more scalable solution. I'll believe when I see it - but am
Intrigued. A big arguement against it is that this logic(which can be
sometimes complicated) establishes relationships between multiple
tables would now live in a .NET class somewhere and not be as easily
accessable to things like SSRS.

Regarding a reporting DB, It apparently was not considered in the
original design (before I got here). There are web forms, and users
(apparently) needed real time data. There are .NET grids that have
columns from seperate Databases Servers. They seem to perform okay,
but one of the DBs does occassionally get really bogged down.

It's looking like a nice casestudy.

Thanks again for taking the time on this.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,906
Latest member
SkinfixSkintag

Latest Threads

Top