architecture question: not representing inheritance in the data model

First up, I know I'll get some good answers here, but in general, are there
any newsgroups devoted to overall application architecture?
Second, sorry for cross-posting but in this case I think it's appropriate, I
want to hear answers from both sides.
Instead of representing inheritance in the data model, in the particular
project I'm working on now I've decided to do it in OO to keep it looser;
each class gets its own table, because the details vary, but implements an
interface. Each class serves a static collection of itself which it loads
from the database. I get a master list of these items by putting all the
collections together into one of the common interface type.
Obtaining a master list in this way is a great simplification over the route
I would have normally chosen - I otherwise would have had a "base" table
containing the properties of the common interface, and requiring that all
"inheritor" tables have a foreign key to it. I already know that this is a
little iffy since 1 to 1 relationships aren't exactly a good thing. Of
course, I could have thrown everything into one table containing all the
fields in every class, which poses its own problems and ugliness. Not liking
either of these options, I decided to dump representing inheritance at all
in the database. And this led me in a new direction...
Partially through the convenience of generics, I've noticed that since I'm
caching everything locally already, it's easy to just do dictionary lookups
through all this data to find subsets. I'm in effect sidestepping the normal
chore of setting up stored procedures. Actually, I use attributes to specify
table names and fields to populate in each class, so I have no pre-written
SQL at all in the entire application. I sort of like it, but this is
something new for me - I normally push a lot of stuff as far as I can down
into the data model; now I'm just using the database as a "dumb" container.
So to those other architects out there, what is your take on this approach?
Thanks,
Paul
P.S. - some additional information
1. yes, in this case, the user will often need access to all the data at
once since there will be several different global analysis views
2. the data will change only wly through an automated processPJ6 wrote:
> First up, I know I'll get some good answers here, but in general, are ther
e
> any newsgroups devoted to overall application architecture?
Not that I know of, but don't let that stop you. I've snipped your post
but it sounded mostly sound. I just thought you might find this page
(and the whole site it's on in fact) interesting. It's sometimes useful
to be reminded that generally nothing we do in software development
requires new or original thought :)
<http://www.agiledata.org/essays/mappingObjects.html>
Larry Lard
Replies to group please

architecture question: not representing inheritance in the data mo

Hi
You may want to read the patterns and practices recommendations at
http://msdn.microsoft.com/library/d...
Spatterns.asp
such as
http://msdn.microsoft.com/library/d...Gag
.asp
If you are using user input to generate you SQL make sure you code against
SQL injection techniques. Also look at using sp_executesql to run the code
you generate.
John
"PJ6" wrote:

> First up, I know I'll get some good answers here, but in general, are ther
e
> any newsgroups devoted to overall application architecture?
> Second, sorry for cross-posting but in this case I think it's appropriate,
I
> want to hear answers from both sides.
> Instead of representing inheritance in the data model, in the particular
> project I'm working on now I've decided to do it in OO to keep it looser;
> each class gets its own table, because the details vary, but implements an
> interface. Each class serves a static collection of itself which it loads
> from the database. I get a master list of these items by putting all the
> collections together into one of the common interface type.
> Obtaining a master list in this way is a great simplification over the rou
te
> I would have normally chosen - I otherwise would have had a "base" table
> containing the properties of the common interface, and requiring that all
> "inheritor" tables have a foreign key to it. I already know that this is a
> little iffy since 1 to 1 relationships aren't exactly a good thing. Of
> course, I could have thrown everything into one table containing all the
> fields in every class, which poses its own problems and ugliness. Not liki
ng
> either of these options, I decided to dump representing inheritance at all
> in the database. And this led me in a new direction...
> Partially through the convenience of generics, I've noticed that since I'm
> caching everything locally already, it's easy to just do dictionary lookup
s
> through all this data to find subsets. I'm in effect sidestepping the norm
al
> chore of setting up stored procedures. Actually, I use attributes to speci
fy
> table names and fields to populate in each class, so I have no pre-written
> SQL at all in the entire application. I sort of like it, but this is
> something new for me - I normally push a lot of stuff as far as I can down
> into the data model; now I'm just using the database as a "dumb" container
.
> So to those other architects out there, what is your take on this approach
?
> Thanks,
> Paul
> P.S. - some additional information
> 1. yes, in this case, the user will often need access to all the data at
> once since there will be several different global analysis views
> 2. the data will change only wly through an automated process
>
>I've disagreed pretty sharply with MS on the subject of data access... but I
guess that's mostly just because of their examples of implementation. The
second article is interesting; thanks for making me take a another look.
Paul
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6CD35F3-6D60-453D-802F-906DD9F7CCE7@.microsoft.com...
> Hi
> You may want to read the patterns and practices recommendations at
> http://msdn.microsoft.com/library/d.../MSpatterns.asp
> such as
> http://msdn.microsoft.com/library/d...G
ag.asp
> If you are using user input to generate you SQL make sure you code against
> SQL injection techniques. Also look at using sp_executesql to run the code
> you generate.
> John
> "PJ6" wrote:
>

Architecture question...can this be done?

All,
I have a question about setting up sql server in a particular
architecture. Below is a diagram of my situation:
[sql server] [XYZ server]
[clients A] -- [nic #1] [nic #1]
-- [clients B]
[nic #2] -- [nic #2]
The A clients can see the sql server fine. However, I want the B
clients to see the sql server also. The connection between the two
"#2" nic cards must be over a single tcp/ip port for security reasons.
Is there any way to do this? I am trying to avoid putting another
replicated sql server on XYZ server.
What about a proxy app on XYZ server? Couldn't I some how set up a
proxy to redirect a tcp/ip port traffic to the sql server? Is this
possible:
1. Set up the sql server to listen to both it's NIC's IP Addresses on
Port 1443.
2. Client B requests a connection from XYZ server's
NIC1_IP_ADDR,Port1443
3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
Am I on the right track? If anyone has any other suggestions in
solving this problem, I would really appreciate it.
Thankssorry my diagram was to big:
[sql server] [XYZ servr]
[clients A] --[nic #1] [nic #1]--[Clients B]
[nic #2]--[nic #2]|||Have you asked your network folks to use the route add command to configure
a route between clients B and sql server?
--
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:a59f0367-d8bc-4b9e-ae10-b5881750455d@.e25g2000prg.googlegroups.com...
> All,
> I have a question about setting up sql server in a particular
> architecture. Below is a diagram of my situation:
> [sql server] [XYZ server]
> [clients A] -- [nic #1] [nic #1]
> -- [clients B]
> [nic #2] -- [nic #2]
>
> The A clients can see the sql server fine. However, I want the B
> clients to see the sql server also. The connection between the two
> "#2" nic cards must be over a single tcp/ip port for security reasons.
> Is there any way to do this? I am trying to avoid putting another
> replicated sql server on XYZ server.
> What about a proxy app on XYZ server? Couldn't I some how set up a
> proxy to redirect a tcp/ip port traffic to the sql server? Is this
> possible:
> 1. Set up the sql server to listen to both it's NIC's IP Addresses on
> Port 1443.
> 2. Client B requests a connection from XYZ server's
> NIC1_IP_ADDR,Port1443
> 3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
> Am I on the right track? If anyone has any other suggestions in
> solving this problem, I would really appreciate it.
> Thanks
>|||On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Have you asked your network folks to use the route add command to configure
> a route between clients B and sql server?
>
No I have not. I looked at the route command and this is the command
for route add:
route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
destination^ ^mask ^gateway metric^ ^
Interface^
I am pretty sure the destination would be ip addr of nic#2 on the sql
server, but I am not sure about the other two parms (mask and
gateway). I assume that mask would be the subnet mask that could
identify the destination (nic#2 sqlserver). I am also assuming the
gateway would be nic#1 on XYZ Server, since it could reach the
mask... Am I on the right track?|||yes, you are correct. Your network admin should be able to help you here
though.
--
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:842426ce-f03c-4c73-b581-14370d77d2d7@.b40g2000prf.googlegroups.com...
> On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> Have you asked your network folks to use the route add command to
>> configure
>> a route between clients B and sql server?
> No I have not. I looked at the route command and this is the command
> for route add:
> route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
> destination^ ^mask ^gateway metric^ ^
> Interface^
> I am pretty sure the destination would be ip addr of nic#2 on the sql
> server, but I am not sure about the other two parms (mask and
> gateway). I assume that mask would be the subnet mask that could
> identify the destination (nic#2 sqlserver). I am also assuming the
> gateway would be nic#1 on XYZ Server, since it could reach the
> mask... Am I on the right track?

Architecture question...can this be done?

All,
I have a question about setting up sql server in a particular
architecture. Below is a diagram of my situation:
[sql server] [XYZ server]
[clients A] -- [nic #1] [nic #1]
-- [clients B]
[nic #2] -- [nic #2]
The A clients can see the sql server fine. However, I want the B
clients to see the sql server also. The connection between the two
"#2" nic cards must be over a single tcp/ip port for security reasons.
Is there any way to do this? I am trying to avoid putting another
replicated sql server on XYZ server.
What about a proxy app on XYZ server? Couldn't I some how set up a
proxy to redirect a tcp/ip port traffic to the sql server? Is this
possible:
1. Set up the sql server to listen to both it's NIC's IP Addresses on
Port 1443.
2. Client B requests a connection from XYZ server's
NIC1_IP_ADDR,Port1443
3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
Am I on the right track? If anyone has any other suggestions in
solving this problem, I would really appreciate it.
Thanks
sorry my diagram was to big:
[sql server] [XYZ servr]
[clients A] --[nic #1] [nic #1]--[Clients B]
[nic #2]--[nic #2]
|||Have you asked your network folks to use the route add command to configure
a route between clients B and sql server?
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:a59f0367-d8bc-4b9e-ae10-b5881750455d@.e25g2000prg.googlegroups.com...
> All,
> I have a question about setting up sql server in a particular
> architecture. Below is a diagram of my situation:
> [sql server] [XYZ server]
> [clients A] -- [nic #1] [nic #1]
> -- [clients B]
> [nic #2] -- [nic #2]
>
> The A clients can see the sql server fine. However, I want the B
> clients to see the sql server also. The connection between the two
> "#2" nic cards must be over a single tcp/ip port for security reasons.
> Is there any way to do this? I am trying to avoid putting another
> replicated sql server on XYZ server.
> What about a proxy app on XYZ server? Couldn't I some how set up a
> proxy to redirect a tcp/ip port traffic to the sql server? Is this
> possible:
> 1. Set up the sql server to listen to both it's NIC's IP Addresses on
> Port 1443.
> 2. Client B requests a connection from XYZ server's
> NIC1_IP_ADDR,Port1443
> 3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
> Am I on the right track? If anyone has any other suggestions in
> solving this problem, I would really appreciate it.
> Thanks
>
|||On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Have you asked your network folks to use the route add command to configure
> a route between clients B and sql server?
>
No I have not. I looked at the route command and this is the command
for route add:
route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
destination^ ^mask ^gateway metric^ ^
Interface^
I am pretty sure the destination would be ip addr of nic#2 on the sql
server, but I am not sure about the other two parms (mask and
gateway). I assume that mask would be the subnet mask that could
identify the destination (nic#2 sqlserver). I am also assuming the
gateway would be nic#1 on XYZ Server, since it could reach the
mask... Am I on the right track?
|||yes, you are correct. Your network admin should be able to help you here
though.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:842426ce-f03c-4c73-b581-14370d77d2d7@.b40g2000prf.googlegroups.com...
> On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> No I have not. I looked at the route command and this is the command
> for route add:
> route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
> destination^ ^mask ^gateway metric^ ^
> Interface^
> I am pretty sure the destination would be ip addr of nic#2 on the sql
> server, but I am not sure about the other two parms (mask and
> gateway). I assume that mask would be the subnet mask that could
> identify the destination (nic#2 sqlserver). I am also assuming the
> gateway would be nic#1 on XYZ Server, since it could reach the
> mask... Am I on the right track?
sql

Architecture question...can this be done?

All,
I have a question about setting up sql server in a particular
architecture. Below is a diagram of my situation:
[sql server] [XYZ server]
[clients A] -- [nic #1] [nic #1]
-- [clients B]
[nic #2] -- [nic #2]
The A clients can see the sql server fine. However, I want the B
clients to see the sql server also. The connection between the two
"#2" nic cards must be over a single tcp/ip port for security reasons.
Is there any way to do this? I am trying to avoid putting another
replicated sql server on XYZ server.
What about a proxy app on XYZ server? Couldn't I some how set up a
proxy to redirect a tcp/ip port traffic to the sql server? Is this
possible:
1. Set up the sql server to listen to both it's NIC's IP Addresses on
Port 1443.
2. Client B requests a connection from XYZ server's
NIC1_IP_ADDR,Port1443
3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
Am I on the right track? If anyone has any other suggestions in
solving this problem, I would really appreciate it.
Thanks
sorry my diagram was to big:
[sql server] [XYZ servr]
[clients A] --[nic #1] [nic #1]--[Clients B]
[nic #2]--[nic #2]
|||Have you asked your network folks to use the route add command to configure
a route between clients B and sql server?
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:a59f0367-d8bc-4b9e-ae10-b5881750455d@.e25g2000prg.googlegroups.com...
> All,
> I have a question about setting up sql server in a particular
> architecture. Below is a diagram of my situation:
> [sql server] [XYZ server]
> [clients A] -- [nic #1] [nic #1]
> -- [clients B]
> [nic #2] -- [nic #2]
>
> The A clients can see the sql server fine. However, I want the B
> clients to see the sql server also. The connection between the two
> "#2" nic cards must be over a single tcp/ip port for security reasons.
> Is there any way to do this? I am trying to avoid putting another
> replicated sql server on XYZ server.
> What about a proxy app on XYZ server? Couldn't I some how set up a
> proxy to redirect a tcp/ip port traffic to the sql server? Is this
> possible:
> 1. Set up the sql server to listen to both it's NIC's IP Addresses on
> Port 1443.
> 2. Client B requests a connection from XYZ server's
> NIC1_IP_ADDR,Port1443
> 3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
> Am I on the right track? If anyone has any other suggestions in
> solving this problem, I would really appreciate it.
> Thanks
>
|||On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Have you asked your network folks to use the route add command to configure
> a route between clients B and sql server?
>
No I have not. I looked at the route command and this is the command
for route add:
route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
destination^ ^mask ^gateway metric^ ^
Interface^
I am pretty sure the destination would be ip addr of nic#2 on the sql
server, but I am not sure about the other two parms (mask and
gateway). I assume that mask would be the subnet mask that could
identify the destination (nic#2 sqlserver). I am also assuming the
gateway would be nic#1 on XYZ Server, since it could reach the
mask... Am I on the right track?
|||yes, you are correct. Your network admin should be able to help you here
though.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:842426ce-f03c-4c73-b581-14370d77d2d7@.b40g2000prf.googlegroups.com...
> On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> No I have not. I looked at the route command and this is the command
> for route add:
> route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
> destination^ ^mask ^gateway metric^ ^
> Interface^
> I am pretty sure the destination would be ip addr of nic#2 on the sql
> server, but I am not sure about the other two parms (mask and
> gateway). I assume that mask would be the subnet mask that could
> identify the destination (nic#2 sqlserver). I am also assuming the
> gateway would be nic#1 on XYZ Server, since it could reach the
> mask... Am I on the right track?

Architecture question...can this be done?

All,
I have a question about setting up sql server in a particular
architecture. Below is a diagram of my situation:
[sql server] [XYZ server]
[clients A] -- [nic #1] [nic #1]
-- [clients B]
[nic #2] -- [nic #2]
The A clients can see the sql server fine. However, I want the B
clients to see the sql server also. The connection between the two
"#2" nic cards must be over a single tcp/ip port for security reasons.
Is there any way to do this? I am trying to avoid putting another
replicated sql server on XYZ server.
What about a proxy app on XYZ server? Couldn't I some how set up a
proxy to redirect a tcp/ip port traffic to the sql server? Is this
possible:
1. Set up the sql server to listen to both it's NIC's IP Addresses on
Port 1443.
2. Client B requests a connection from XYZ server's
NIC1_IP_ADDR,Port1443
3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
Am I on the right track? If anyone has any other suggestions in
solving this problem, I would really appreciate it.
Thankssorry my diagram was to big:
[sql server] [XYZ servr]
[clients A] --[nic #1] [nic #1]--[Clients B]
[nic #2]--[nic #2]|||Have you asked your network folks to use the route add command to configure
a route between clients B and sql server?
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:a59f0367-d8bc-4b9e-ae10-b5881750455d@.e25g2000prg.googlegroups.com...
> All,
> I have a question about setting up sql server in a particular
> architecture. Below is a diagram of my situation:
> [sql server] [XYZ server]
> [clients A] -- [nic #1] [nic #1]
> -- [clients B]
> [nic #2] -- [nic #2]
>
> The A clients can see the sql server fine. However, I want the B
> clients to see the sql server also. The connection between the two
> "#2" nic cards must be over a single tcp/ip port for security reasons.
> Is there any way to do this? I am trying to avoid putting another
> replicated sql server on XYZ server.
> What about a proxy app on XYZ server? Couldn't I some how set up a
> proxy to redirect a tcp/ip port traffic to the sql server? Is this
> possible:
> 1. Set up the sql server to listen to both it's NIC's IP Addresses on
> Port 1443.
> 2. Client B requests a connection from XYZ server's
> NIC1_IP_ADDR,Port1443
> 3. The proxy redirects traffic to NIC2_IP_ADDR,Port1443 ... ?
> Am I on the right track? If anyone has any other suggestions in
> solving this problem, I would really appreciate it.
> Thanks
>|||On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Have you asked your network folks to use the route add command to configur
e
> a route between clients B and sql server?
>
No I have not. I looked at the route command and this is the command
for route add:
route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
destination^ ^mask ^gateway metric^ ^
Interface^
I am pretty sure the destination would be ip addr of nic#2 on the sql
server, but I am not sure about the other two parms (mask and
gateway). I assume that mask would be the subnet mask that could
identify the destination (nic#2 sqlserver). I am also assuming the
gateway would be nic#1 on XYZ Server, since it could reach the
mask... Am I on the right track?|||yes, you are correct. Your network admin should be able to help you here
though.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jonbaxter28@.gmail.com> wrote in message
news:842426ce-f03c-4c73-b581-14370d77d2d7@.b40g2000prf.googlegroups.com...
> On Dec 14, 9:36 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> No I have not. I looked at the route command and this is the command
> for route add:
> route ADD 157.0.0.0 MASK 255.0.0.0 157.55.80.1 METRIC 3 IF 2
> destination^ ^mask ^gateway metric^ ^
> Interface^
> I am pretty sure the destination would be ip addr of nic#2 on the sql
> server, but I am not sure about the other two parms (mask and
> gateway). I assume that mask would be the subnet mask that could
> identify the destination (nic#2 sqlserver). I am also assuming the
> gateway would be nic#1 on XYZ Server, since it could reach the
> mask... Am I on the right track?

Architecture question.

Hi
I have a Windows Form running in 40 clients around the city, those apps are
communicating with a central SQLDB hosted in a hosting company (each one is
sending data, waiting for data comparisons and receiving the data back). I
have been using a Web Service to make the link between my apps and the SQLDB
,
but now I want to connect my Windows Form apps directly to de SQLServer
(because I think the roundtrip is going to be faster, which is the most
important factor in my app)
My question to the architects and everyone:
Is this a good, secure, recommended, intelligent practice or this is a big
big mistake '
thks
kengood - Prototype it and see. Depends on the specific case usage and
implementation. You may find that the old style tightly coupled connection
is faster.
secure - Unencrypted xml is easier to intercept and read than unencrypted
native sql network calls. Using an encrypted protocol, it makes little
difference between the two.
recommended, intelligent practice or this is a big big mistake - Consider
the above and decide based on your requirement priorities.
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:72C3EF9C-7DF4-4C85-A43B-8CE0C5E63388@.microsoft.com...
> Hi
> I have a Windows Form running in 40 clients around the city, those apps
are
> communicating with a central SQLDB hosted in a hosting company (each one
is
> sending data, waiting for data comparisons and receiving the data back). I
> have been using a Web Service to make the link between my apps and the
SQLDB,
> but now I want to connect my Windows Form apps directly to de SQLServer
> (because I think the roundtrip is going to be faster, which is the most
> important factor in my app)
> My question to the architects and everyone:
> Is this a good, secure, recommended, intelligent practice or this is a big
> big mistake '
>
> thks
> ken

Architecture question grouping different kinds of stuff...

Hi all,

I'm hoping somebody here might be able to point me in the right
direction.

Basically, I'm looking for a "good" way to build tables such that
different kinds of stuff can grouped.

For example, let's say I have "rooms" and they can contain "people"
and/or "books" and/or "furniture" -- each of those objects (rooms,
people, books, furniture, etc) may have their own attributes -- but the
idea is that a room may contain 0 or more of each of the others.

I'll need to do lots of selects to retrieve the contents of specific
rooms.

Is there a standard / good way to go about this sort of thing?

Somehow giving people, books, furniture a room_ID (foreign key) seems a
bit clumbsy -- ie, do I have to run a select for each?

thanks kindly, -ScottFollowing up on my own question above, let's say I have these tables:

Rooms
.. . RoomID

People
.. . PersonID
.. . Name

Books
.. . BookID
.. . Title

Furniture
.. . FurnitureID
.. . FurnitureType

I could group stuff into rooms with something like:

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID

(where ContentType could be person, book, furniture, etc)

But it seems like if there were 20 different kinds of things, I'd have
to run a select for each.

I'm hoping this is a standard SQL architecture problem, but I lack the
vocabulary to know what to search for.

thanks, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> Following up on my own question above, let's say I have these tables:
> Rooms
> . . RoomID
> People
> . . PersonID
> . . Name
> Books
> . . BookID
> . . Title
> Furniture
> . . FurnitureID
> . . FurnitureType
> I could group stuff into rooms with something like:
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> (where ContentType could be person, book, furniture, etc)
> But it seems like if there were 20 different kinds of things, I'd have
> to run a select for each.

I would look into gathering all these contents into one supertable.
If then there are specicic attributes for person, books etc you can
can have sub tables for this.

This contents table would have a type field specifying the type of object.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland, thanks -- are you suggesting something like:

Rooms
.. . RoomID

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . PersonName
.. . BookTitle
.. . FurnitureType

or even

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . VariableText1
.. . VariableText2

(where 'VariableText' would store different kinds of stuff, depending
on the ContentType)

Since some of these objects may have a few attributes, such a
supertable could have lots of nulls -- is that "ok"?

Thanks again for your help, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> Hi Erland, thanks -- are you suggesting something like:
> Rooms
> . . RoomID
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . PersonName
> . . BookTitle
> . . FurnitureType
> or even
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . VariableText1
> . . VariableText2
> (where 'VariableText' would store different kinds of stuff, depending
> on the ContentType)
>
> Since some of these objects may have a few attributes, such a
> supertable could have lots of nulls -- is that "ok"?

Nah, rather I was thinking you would keep the existing tables, and
just add this RoomContents table. Probably you should move some columns
into this table, for instance a string that represents a name of some
sort. (That is "sofa" for furnitures.)

This would make it easier to write a simple query that lists all the
contents in the room. If you need furniture-specific information you would
go to that table.

In the end it may be a trade-off where you place things. If some attributes
are common to most contents, it may be better to have it as a nullable
column in the main table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The trouble is that each time I "load a room" I'll need at least a few
of the attributes of the things in that room.

So, if I understand correctly, I would keep all of those various
objects' attributes in one "supertable," and perhaps keep some
additional attributes (those not needed when the room loads) in the
extra object-specific tables.

Does that sound about right?

Thanks again, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> The trouble is that each time I "load a room" I'll need at least a few
> of the attributes of the things in that room.
> So, if I understand correctly, I would keep all of those various
> objects' attributes in one "supertable," and perhaps keep some
> additional attributes (those not needed when the room loads) in the
> extra object-specific tables.
> Does that sound about right?

Yes, that was my thought.

In an object-oriented language, this is a little easier since you would
have "Contents" a base class (possibly virtual) and then have Furtinure
etc to inherit from that group. In SQL you cannot do this very well.
You could have a view that unites all tables, and then have an INSTEAD
OF trigger on the view, so when you inserts into the view, things fall
down in their respective places, although I am not sure that this would
be worth the effort.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Architecture question

I am a newbie to Notification service and I need your help to determine what's the best way to handle my situation. First of all, I need to determine if Notification service is the right approach.

We have a ASP.NET application that creates purchase orders to various suppliers. Few suppliers are fine with just "Email notifications". But few of them need the PO XML sent to their FTP site. We might have future suppliers and they may want a different mode of communication.

Here's my solution to this problem and I need your expert advice

Step 1: Create an event schema that captures all the item details in a PO.

Question: What is the best way to implement hierarchies [PO Header, PO Line item]? I am thinking of adding all the head fields to the schema for every line item.

Step 2: My delivery channels will be "Email" or "FTP" [custom delivery channel]. Some suppliers will subscibe using email channel, and some FTP

Question: Is there a way I can use the "File" channel to download a PO [with unique file names] and develop an external program to just FTP the file to the supplier?

Step 3: When a new PO is created, I will call my event provider to submit the event [I might use the out of the box Stored procedures]. Depending upon the Subscriber's delivery channel [protocol], the appropriate delivery channel will be chosen.

Any thoughts/help or suggesstions?

I think the easiest hierarchy is a flat structure (one root node, one level of child elements).

As for the File channel, you cannot use the built-in File delivery protocol in this way. It writes results to a single file, and is primarily intended for testing. You could write a custom File delivery protocol that produces unique file names and performs other processing.

|||As Diane mentioned, SSNS really thinks in terms of notifying people of somewhat flat data structures. If you need to have parent-child detail information, what I typically do is create a custom content formatter. In there I make a connection to the database that stores the additional information and query the supplemental information. I then format it along with the normal SSNS notification information (parent stuff) and return it to the distributor.

To write to separate files for each notification, you'll need to create a custom delivery protocol. You can base the file name off the subscriber and perhaps a datetime to make sure it's unique.

If you're parent-detail information is in a database, it's probably easiest to use the SQL Server Event Provider, but you can use the SSNS stored procedures to submit the event if you'd rather.

HTH...

Joe|||

Thanks for your replies.

Joe,

How about if I just pass the "PO" number to the "Custom Delivery Channel" that queries the database for a given PO and then, creates an XML file? Do you think that will make it easier?

Regards

|||Hi Ragas -

That's typically the tact that I take.

In your example (and I'm assuming a bit here since I don't know the details), I'd probably have the event provider submit a few vital pieces of info to SSNS; at a minimum the PO number, but if you have other pieces of information readily accessible then I'd submit that too (especially if it'll save one or more db calls from within the custom component). In your case, you may also want to submit the CustomerId, too.

I typically gather the other information in the content formatter rather than the delivery channel.

HTH...

Joe|||

I'm a NS newbie, creating my first NS app which indeed does notifications based on orders with line items (parent/child records).

However, I don't feel architecturally, pulling outside data (line items) is appropriate for either the content formatter or the delivery channel. IMHO, they should be responsible for (and only for) content formatting and delivery, respectively.

My intended solution is to flatten the order details much earlier in the process - in my custom event provider (which is a component responsible for data!). I need a custom event provider for other reasons (data coming from a web service), but this approach to handling parent/child records shouldn't require a cutom provider - I presume it'd work (even easier) with the SQL provider though I admit I haven't used that provider.

The trick is simple: I define the event class (and notification class) to contain the fields for the parent Order, and my order details (order line items) go into a single xml type column of this event class.

Simplified example snippet:

<EventClass>

<EventClassName>OrderData</EventClassName>

<Schema>

<Field>

<FieldName>CustomerName</FieldName>

<FieldType>nvarchar(128)</FieldType>

</Field>

<Field>

<FieldName>OrderDetails</FieldName>

<FieldType>xml</FieldType>

</Field>

Fill in OrderDetails with the aggregate line items and voila, a single "flat" event record with all the data needed for the notification. If all your source data is available in SQL, I imagine you could also do this flattening in the subscription action - pulling order line items out (as XML) based on the parent order ID and pushing them into the notifications view?

Note how this allows me to continue using the built-in XSLT formatter because the entire stream of event data is still just XML when it hits the XSLT. For line items that just an xsl for-each instruction loop to format the line items from the OrderDetails.

Note: to use this technique you need to turn off escaping in your ADF file so that XML flows through to the XSLT file during formatting:

<ContentFormatter>

<ClassName>XsltFormatter</ClassName>

<Arguments>

<Argument>

<Name>XsltBaseDirectoryPath</Name>

<Value>C:\SQL Notification Services\Orders\</Value>

</Argument>

<Argument>

<Name>XsltFileName</Name>

<Value>OrderSubmitted.xslt</Value>

</Argument>

<!-- we disable escaping so that our XML column (orderdetails) stays as

XML when being passed to the XSLT file for processing. The default is to

escape the embedded XML (e.g. "bar&gt;a bar&lt;/bar&gt;&lt" for "<bar>a bar</bar>" -->

<Argument>

<Name>DisableEscaping</Name>

<Value>true</Value>

</Argument>

</Arguments>

</ContentFormatter>

sql

Architecture question

I have a couple of dozen users running about 20 reports. The reports are
running from SQL Server stored procedures on a server named CORP_DB. The
reports render quickly, even running on my development box, I've never had
anyone complain about performance. My question is this: can I use a beefy
workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my server
to host IIS and Reporting Services? If I do it this way should I put the two
RS databases on CORP_DB or should I put them on CORP_RS? I may be scaling up
to about 100 users and 50 to 80 reports.
Thanks,
DougSMy personal feeling is to have the RS databases local, even if the data is
on another server. The RS databases are used as an object store (all the
report definitions are stored there, as well as everything else need by RS).
SQL Server is good at how it uses memory. I am currently using a workstation
with 2 gigs of ram and 2 processors. My suggestion is to add a gig of ram if
you can (RS is pretty ram intensive) and add a processor. You might be fine
with 1 processor and 1 gig but it up if at all possible beef it up. Also,
install Windows 2003 Server Standard edition on it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DougS" <doug@.nospam.com> wrote in message
news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I have a couple of dozen users running about 20 reports. The reports are
>running from SQL Server stored procedures on a server named CORP_DB. The
>reports render quickly, even running on my development box, I've never had
>anyone complain about performance. My question is this: can I use a beefy
>workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my server
>to host IIS and Reporting Services? If I do it this way should I put the
>two RS databases on CORP_DB or should I put them on CORP_RS? I may be
>scaling up to about 100 users and 50 to 80 reports.
> Thanks,
> DougS
>|||I have to have a sql server license for the machine that is rendering the
reports dont I? That's MS's gotcha isnt it? RS is 'free' with SQL Server but
it has to run on a machine by itself so you end up buying another license.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uxTlqRFmFHA.3552@.TK2MSFTNGP10.phx.gbl...
> My personal feeling is to have the RS databases local, even if the data is
> on another server. The RS databases are used as an object store (all the
> report definitions are stored there, as well as everything else need by
> RS). SQL Server is good at how it uses memory. I am currently using a
> workstation with 2 gigs of ram and 2 processors. My suggestion is to add a
> gig of ram if you can (RS is pretty ram intensive) and add a processor.
> You might be fine with 1 processor and 1 gig but it up if at all possible
> beef it up. Also, install Windows 2003 Server Standard edition on it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
> "DougS" <doug@.nospam.com> wrote in message
> news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>>I have a couple of dozen users running about 20 reports. The reports are
>>running from SQL Server stored procedures on a server named CORP_DB. The
>>reports render quickly, even running on my development box, I've never had
>>anyone complain about performance. My question is this: can I use a beefy
>>workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my
>>server to host IIS and Reporting Services? If I do it this way should I
>>put the two RS databases on CORP_DB or should I put them on CORP_RS? I may
>>be scaling up to about 100 users and 50 to 80 reports.
>> Thanks,
>> DougS
>|||No, you do not have to have it run on a machine by itself. I currently have
it running on the same machine as my datamart. Now, some IT departments
freak at the idea of having anything but a database run on the machine.
Especially IIS but IIS with an asp.net application really disturbs them. My
feeling is having a web server running on the database machine is the
direction all vendors are going (Oracle does this too) so the dba's need to
get with the program. Anyway, it is your own decision whether to have it on
the same box or not. Now, people that are doing a web farm by definition are
running on other boxes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DougS" <doug@.nospam.com> wrote in message
news:epbro4bmFHA.3120@.TK2MSFTNGP09.phx.gbl...
>I have to have a sql server license for the machine that is rendering the
>reports dont I? That's MS's gotcha isnt it? RS is 'free' with SQL Server
>but it has to run on a machine by itself so you end up buying another
>license.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uxTlqRFmFHA.3552@.TK2MSFTNGP10.phx.gbl...
>> My personal feeling is to have the RS databases local, even if the data
>> is on another server. The RS databases are used as an object store (all
>> the report definitions are stored there, as well as everything else need
>> by RS). SQL Server is good at how it uses memory. I am currently using a
>> workstation with 2 gigs of ram and 2 processors. My suggestion is to add
>> a gig of ram if you can (RS is pretty ram intensive) and add a processor.
>> You might be fine with 1 processor and 1 gig but it up if at all possible
>> beef it up. Also, install Windows 2003 Server Standard edition on it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>>
>> "DougS" <doug@.nospam.com> wrote in message
>> news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>>I have a couple of dozen users running about 20 reports. The reports are
>>running from SQL Server stored procedures on a server named CORP_DB. The
>>reports render quickly, even running on my development box, I've never
>>had anyone complain about performance. My question is this: can I use a
>>beefy workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as
>>my server to host IIS and Reporting Services? If I do it this way should
>>I put the two RS databases on CORP_DB or should I put them on CORP_RS? I
>>may be scaling up to about 100 users and 50 to 80 reports.
>> Thanks,
>> DougS
>>
>

architecture question

I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?Hi
"Jon" wrote:
> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the business
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp table
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hits
> when doing logic such as this on the database side, so my question is, which
> would be the best solution for this?
>
You may want to look at http://msdn2.microsoft.com/en-us/library/ms973279.aspx
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
John

Architecture question

I am a newbie to Notification service and I need your help to determine what's the best way to handle my situation. First of all, I need to determine if Notification service is the right approach.

We have a ASP.NET application that creates purchase orders to various suppliers. Few suppliers are fine with just "Email notifications". But few of them need the PO XML sent to their FTP site. We might have future suppliers and they may want a different mode of communication.

Here's my solution to this problem and I need your expert advice

Step 1: Create an event schema that captures all the item details in a PO.

Question: What is the best way to implement hierarchies [PO Header, PO Line item]? I am thinking of adding all the head fields to the schema for every line item.

Step 2: My delivery channels will be "Email" or "FTP" [custom delivery channel]. Some suppliers will subscibe using email channel, and some FTP

Question: Is there a way I can use the "File" channel to download a PO [with unique file names] and develop an external program to just FTP the file to the supplier?

Step 3: When a new PO is created, I will call my event provider to submit the event [I might use the out of the box Stored procedures]. Depending upon the Subscriber's delivery channel [protocol], the appropriate delivery channel will be chosen.

Any thoughts/help or suggesstions?

I think the easiest hierarchy is a flat structure (one root node, one level of child elements).

As for the File channel, you cannot use the built-in File delivery protocol in this way. It writes results to a single file, and is primarily intended for testing. You could write a custom File delivery protocol that produces unique file names and performs other processing.

|||As Diane mentioned, SSNS really thinks in terms of notifying people of somewhat flat data structures. If you need to have parent-child detail information, what I typically do is create a custom content formatter. In there I make a connection to the database that stores the additional information and query the supplemental information. I then format it along with the normal SSNS notification information (parent stuff) and return it to the distributor.

To write to separate files for each notification, you'll need to create a custom delivery protocol. You can base the file name off the subscriber and perhaps a datetime to make sure it's unique.

If you're parent-detail information is in a database, it's probably easiest to use the SQL Server Event Provider, but you can use the SSNS stored procedures to submit the event if you'd rather.

HTH...

Joe|||

Thanks for your replies.

Joe,

How about if I just pass the "PO" number to the "Custom Delivery Channel" that queries the database for a given PO and then, creates an XML file? Do you think that will make it easier?

Regards

|||Hi Ragas -

That's typically the tact that I take.

In your example (and I'm assuming a bit here since I don't know the details), I'd probably have the event provider submit a few vital pieces of info to SSNS; at a minimum the PO number, but if you have other pieces of information readily accessible then I'd submit that too (especially if it'll save one or more db calls from within the custom component). In your case, you may also want to submit the CustomerId, too.

I typically gather the other information in the content formatter rather than the delivery channel.

HTH...

Joe

architecture question

I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?
Hi
"Jon" wrote:

> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the business
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp table
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hits
> when doing logic such as this on the database side, so my question is, which
> would be the best solution for this?
>
You may want to look at http://msdn2.microsoft.com/en-us/library/ms973279.aspx
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
John

architecture question

I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?Hi
"Jon" wrote:

> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the busine
ss
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp tabl
e
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hit
s
> when doing logic such as this on the database side, so my question is, whi
ch
> would be the best solution for this?
>
You may want to look at [url]http://msdn2.microsoft.com/en-us/library/ms973279.aspx[/ur
l]
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
Johnsql

Architecture design to support global data replication

Hi,
In our global company, there is a proposal to have SQL Server hubs at
specific locations, say one in North America (NA) and the other in Asia (with
scalability option that other hubs could be added as our company grows).
With this mind, we are in the process of designing a strategy to support
bi-directional (and may be 3-way or X number of way in the future)
transactional replication of our PORTAL database between NA and Asia so that
any changes made in the DB in NA server is reflected to the DB in the Asia
server and vice versa. NA server will be used by NA clients and the Asia
server, by Asian clients.
Any ideas what would be the best strategy to implement this, keeping
latency, network speed, and most importantly choice of replication method in
mind? The PORTAL database is designed to be less than 15 GB.
Your thoughts and comments are appreciated. TIA.
I would recommend transactional replication. It if it two way replication I
would use bi-directional transactional replication. With careful filtering
you should be able to scale this to several more nodes.
Peer-to-peer replication is also an option in SQL 2005, however all updates
should originate on one node.
Merge can be used, but it adds latency to each DML.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C84C50BF-571D-4F43-BBB7-BF81BE06E34F@.microsoft.com...
> Hi,
> In our global company, there is a proposal to have SQL Server hubs at
> specific locations, say one in North America (NA) and the other in Asia
> (with
> scalability option that other hubs could be added as our company grows).
> With this mind, we are in the process of designing a strategy to support
> bi-directional (and may be 3-way or X number of way in the future)
> transactional replication of our PORTAL database between NA and Asia so
> that
> any changes made in the DB in NA server is reflected to the DB in the Asia
> server and vice versa. NA server will be used by NA clients and the Asia
> server, by Asian clients.
> Any ideas what would be the best strategy to implement this, keeping
> latency, network speed, and most importantly choice of replication method
> in
> mind? The PORTAL database is designed to be less than 15 GB.
> Your thoughts and comments are appreciated. TIA.

Architecture design to support global data replication

Hi,
In our global company, there is a proposal to have SQL Server hubs at
specific locations, say one in North America (NA) and the other in Asia (wit
h
scalability option that other hubs could be added as our company grows).
With this mind, we are in the process of designing a strategy to support
bi-directional (and may be 3-way or X number of way in the future)
transactional replication of our PORTAL database between NA and Asia so that
any changes made in the DB in NA server is reflected to the DB in the Asia
server and vice versa. NA server will be used by NA clients and the Asia
server, by Asian clients.
Any ideas what would be the best strategy to implement this, keeping
latency, network speed, and most importantly choice of replication method in
mind? The PORTAL database is designed to be less than 15 GB.
Your thoughts and comments are appreciated. TIA.I would recommend transactional replication. It if it two way replication I
would use bi-directional transactional replication. With careful filtering
you should be able to scale this to several more nodes.
Peer-to-peer replication is also an option in SQL 2005, however all updates
should originate on one node.
Merge can be used, but it adds latency to each DML.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C84C50BF-571D-4F43-BBB7-BF81BE06E34F@.microsoft.com...
> Hi,
> In our global company, there is a proposal to have SQL Server hubs at
> specific locations, say one in North America (NA) and the other in Asia
> (with
> scalability option that other hubs could be added as our company grows).
> With this mind, we are in the process of designing a strategy to support
> bi-directional (and may be 3-way or X number of way in the future)
> transactional replication of our PORTAL database between NA and Asia so
> that
> any changes made in the DB in NA server is reflected to the DB in the Asia
> server and vice versa. NA server will be used by NA clients and the Asia
> server, by Asian clients.
> Any ideas what would be the best strategy to implement this, keeping
> latency, network speed, and most importantly choice of replication method
> in
> mind? The PORTAL database is designed to be less than 15 GB.
> Your thoughts and comments are appreciated. TIA.

Architecture design to support global data replication

Hi,
In our global company, there is a proposal to have SQL Server hubs at
specific locations, say one in North America (NA) and the other in Asia (with
scalability option that other hubs could be added as our company grows).
With this mind, we are in the process of designing a strategy to support
bi-directional (and may be 3-way or X number of way in the future)
transactional replication of our PORTAL database between NA and Asia so that
any changes made in the DB in NA server is reflected to the DB in the Asia
server and vice versa. NA server will be used by NA clients and the Asia
server, by Asian clients.
Any ideas what would be the best strategy to implement this, keeping
latency, network speed, and most importantly choice of replication method in
mind? The PORTAL database is designed to be less than 15 GB.
Your thoughts and comments are appreciated. TIA.I would recommend transactional replication. It if it two way replication I
would use bi-directional transactional replication. With careful filtering
you should be able to scale this to several more nodes.
Peer-to-peer replication is also an option in SQL 2005, however all updates
should originate on one node.
Merge can be used, but it adds latency to each DML.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C84C50BF-571D-4F43-BBB7-BF81BE06E34F@.microsoft.com...
> Hi,
> In our global company, there is a proposal to have SQL Server hubs at
> specific locations, say one in North America (NA) and the other in Asia
> (with
> scalability option that other hubs could be added as our company grows).
> With this mind, we are in the process of designing a strategy to support
> bi-directional (and may be 3-way or X number of way in the future)
> transactional replication of our PORTAL database between NA and Asia so
> that
> any changes made in the DB in NA server is reflected to the DB in the Asia
> server and vice versa. NA server will be used by NA clients and the Asia
> server, by Asian clients.
> Any ideas what would be the best strategy to implement this, keeping
> latency, network speed, and most importantly choice of replication method
> in
> mind? The PORTAL database is designed to be less than 15 GB.
> Your thoughts and comments are appreciated. TIA.

Architecture changes from RS2000 and RS2005

Where can I find information on the architecture changes from Reporting
Services 2000 and Reporting Services 2005?Not sure what you are looking for. You can check the pre-release
documentation of RS 2005 here:
http://msdn2.microsoft.com/en-us/library/ms228038(en-US,SQL.90).aspx
Generally, reports that work on RS 2000 will also work on RS 2005 (they get
upgrade on-the-fly). For SOAP applications interacting with RS
specificially, the old RS 2000 SOAP namespace is still supported on RS 2005.
However, if you want to take advantage of some of the new functionality, you
will need to use the new RS 2005 SOAP end point.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:DA305369-16C6-4FFA-B636-914AD510C87F@.microsoft.com...
> Where can I find information on the architecture changes from Reporting
> Services 2000 and Reporting Services 2005?|||Thanks! This helps!
"Robert Bruckner [MSFT]" wrote:
> Not sure what you are looking for. You can check the pre-release
> documentation of RS 2005 here:
> http://msdn2.microsoft.com/en-us/library/ms228038(en-US,SQL.90).aspx
> Generally, reports that work on RS 2000 will also work on RS 2005 (they get
> upgrade on-the-fly). For SOAP applications interacting with RS
> specificially, the old RS 2000 SOAP namespace is still supported on RS 2005.
> However, if you want to take advantage of some of the new functionality, you
> will need to use the new RS 2005 SOAP end point.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:DA305369-16C6-4FFA-B636-914AD510C87F@.microsoft.com...
> > Where can I find information on the architecture changes from Reporting
> > Services 2000 and Reporting Services 2005?
>
>

Architecture advice

I need some advice on architecture.
I am a newbie to SQL Server, but a long time Access and FrontPage developer.
With these two apps, I have a "publish to the web" mentality, so I have
envisioned having my development SQL Server database on my local harddisk
and periodically "publishing" it to my production SQL Server out on the
Internet. (By development server, I mean where data creation and update is
accomplished.)
My idea: Update the production SQL Server periodically (weekly) from the
development server, but I need to capture data received from users on the
production server, then edit and merge on the development server for later
republishing to the production server. Alternatively, all data updates could
be done on the production server with backups to the local harddisk server.
Problem: My Internet SQL Server provider does NOT allow replication nor the
creation or implementation of DTS packages, but does support the DTS
import/export functions to a point of connection to and selection of my
database.
Which would be best approach to implement given the ISP's restrictions? Or
can you suggest another approach?
Many thanks for your expertise to point me in the right direction.
CharaxDo all your production changes on your production server,
and don't mix your Dev server with your prod server. If
you really need to do things first on your Dev server and
then publish 'it' to the prod server, you should then
treat your dev server as a prod server unless of course
by 'publishing' you meant scheduled releases in a
controlled fashion.
Linchi
>--Original Message--
>I need some advice on architecture.
>I am a newbie to SQL Server, but a long time Access and
FrontPage developer.
>With these two apps, I have a "publish to the web"
mentality, so I have
>envisioned having my development SQL Server database on
my local harddisk
>and periodically "publishing" it to my production SQL
Server out on the
>Internet. (By development server, I mean where data
creation and update is
>accomplished.)
>My idea: Update the production SQL Server periodically
(weekly) from the
>development server, but I need to capture data received
from users on the
>production server, then edit and merge on the development
server for later
>republishing to the production server. Alternatively, all
data updates could
>be done on the production server with backups to the
local harddisk server.
>Problem: My Internet SQL Server provider does NOT allow
replication nor the
>creation or implementation of DTS packages, but does
support the DTS
>import/export functions to a point of connection to and
selection of my
>database.
>Which would be best approach to implement given the ISP's
restrictions? Or
>can you suggest another approach?
>Many thanks for your expertise to point me in the right
direction.
>Charax
>
>.
>|||Thanks for the quick response, Linchi. Yes, you are quite right and my
terminology was wrong. (Remember, I'm an SQL server newbie!)
I now will call the server on my local harddisk the 'production' server. The
production server has the most current data and any changes to data or
database structure are made there. The ISP's SQL Server on the Internet is
simply a copy of the production server that web users can access -- let's
call it the 'slave'. Is this a reasonable architecture?
If so, how do you recommend that I update the slave server on the Internet
from the production server on local harddisk? Please keep in mind that the
slave server does not support replication, and only supports the DTS
import/export functions to a point of connection to and selection of my
database. On my local production server, I have all replication and DTS
features.
Many thanks for your ideas and help to a new guy.
Charax
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:339801c3e1c8$08021990$a601280a@.phx.gbl...
> Do all your production changes on your production server,
> and don't mix your Dev server with your prod server. If
> you really need to do things first on your Dev server and
> then publish 'it' to the prod server, you should then
> treat your dev server as a prod server unless of course
> by 'publishing' you meant scheduled releases in a
> controlled fashion.
> Linchisql

Architecture advice

I need some advice on architecture.
I am a newbie to SQL Server, but a long time Access and FrontPage developer.
With these two apps, I have a "publish to the web" mentality, so I have
envisioned having my development SQL Server database on my local harddisk
and periodically "publishing" it to my production SQL Server out on the
Internet. (By development server, I mean where data creation and update is
accomplished.)
My idea: Update the production SQL Server periodically (weekly) from the
development server, but I need to capture data received from users on the
production server, then edit and merge on the development server for later
republishing to the production server. Alternatively, all data updates could
be done on the production server with backups to the local harddisk server.
Problem: My Internet SQL Server provider does NOT allow replication nor the
creation or implementation of DTS packages, but does support the DTS
import/export functions to a point of connection to and selection of my
database.
Which would be best approach to implement given the ISP's restrictions? Or
can you suggest another approach?
Many thanks for your expertise to point me in the right direction.
CharaxDo all your production changes on your production server,
and don't mix your Dev server with your prod server. If
you really need to do things first on your Dev server and
then publish 'it' to the prod server, you should then
treat your dev server as a prod server unless of course
by 'publishing' you meant scheduled releases in a
controlled fashion.
Linchi

quote:

---

>--Original Message--
>I need some advice on architecture.
>I am a newbie to SQL Server, but a long time Access and

---

FrontPage developer.

quote:

---

>With these two apps, I have a "publish to the web"

---

mentality, so I have

quote:

---

>envisioned having my development SQL Server database on

---

my local harddisk

quote:

---

>and periodically "publishing" it to my production SQL

---

Server out on the

quote:

---

>Internet. (By development server, I mean where data

---

creation and update is

quote:

---

>accomplished.)
>My idea: Update the production SQL Server periodically

---

(weekly) from the

quote:

---

>development server, but I need to capture data received

---

from users on the

quote:

---

>production server, then edit and merge on the development

---

server for later

quote:

---

>republishing to the production server. Alternatively, all

---

data updates could

quote:

---

>be done on the production server with backups to the

---

local harddisk server.

quote:

---

>Problem: My Internet SQL Server provider does NOT allow

---

replication nor the

quote:

---

>creation or implementation of DTS packages, but does

---

support the DTS

quote:

---

>import/export functions to a point of connection to and

---

selection of my

quote:

---

>database.
>Which would be best approach to implement given the ISP's

---

restrictions? Or

quote:

---

>can you suggest another approach?
>Many thanks for your expertise to point me in the right

---

direction.

quote:

---

>Charax
>
>.
>

---

|||Thanks for the quick response, Linchi. Yes, you are quite right and my
terminology was wrong. (Remember, I'm an SQL server newbie!)
I now will call the server on my local harddisk the 'production' server. The
production server has the most current data and any changes to data or
database structure are made there. The ISP's SQL Server on the Internet is
simply a copy of the production server that web users can access -- let's
call it the 'slave'. Is this a reasonable architecture?
If so, how do you recommend that I update the slave server on the Internet
from the production server on local harddisk? Please keep in mind that the
slave server does not support replication, and only supports the DTS
import/export functions to a point of connection to and selection of my
database. On my local production server, I have all replication and DTS
features.
Many thanks for your ideas and help to a new guy.
Charax
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:339801c3e1c8$08021990$a601280a@.phx.gbl...

quote:

---

> Do all your production changes on your production server,
> and don't mix your Dev server with your prod server. If
> you really need to do things first on your Dev server and
> then publish 'it' to the prod server, you should then
> treat your dev server as a prod server unless of course
> by 'publishing' you meant scheduled releases in a
> controlled fashion.
> Linchi

---