Showing posts with label customers. Show all posts
Showing posts with label customers. Show all posts

Friday, March 23, 2012

Many pubs or single pub with dynamic filters ??

Hello...
Some background: We have merge replication set up between SQL 2000 and
mobile devices running SQL Mobile. Each of our customers has a unique
customer id and currently we have a separate publication for each one...that
has a static filter such as "WHERE CustomerID = 12345". And on each device,
it subscribes to its own publication...based on the Customer ID.
What I am thinking about...in the near future, as our customer base grows,
is that we'll be faced with maintaining lots of Publications on the server -
one for each Customer ID.
So, would it be better to have a single publication that has dynamic
filters...based on the HostName property...which I'd set in code on the
device (based on the Customer ID) or keep things the way we have them now?
I am mostly concerned about replication/synchronization performance between
the server and the mobile devices...some of which use GPRS for connectivity.
I don't want to sacrifce any performance gains, if there are any, by having
lots of publications.
thanks for any thoughts.
- will
Here are the issues that you need to consider when moving to a single
publisher model:
1. Concurrency: syncing multiple subscribers at the same time is not
optimized in SQL2000, which will end up serializing the merge process. In
SQL2005, performance is greatly improved by allowing multiple subscribers to
sync at the same time.
2. Partitioning: When a subscriber with a given ID syncs, SQL2000 will scan
the publisher's articles for changes pertaining to that subscriber. This
process has its performance implications for large publication with large
number of changes. In SQL2005, this issue was also addressed by the use of
partition groups.
Regards,
Rafik
This posting is provided "AS IS" with no warranties, and confers no rights.
"dw" <dw@.discussions.microsoft.com> wrote in message
news:7730CDFB-81A8-437B-80A5-C5B0131814B9@.microsoft.com...
> Hello...
> Some background: We have merge replication set up between SQL 2000 and
> mobile devices running SQL Mobile. Each of our customers has a unique
> customer id and currently we have a separate publication for each
> one...that
> has a static filter such as "WHERE CustomerID = 12345". And on each
> device,
> it subscribes to its own publication...based on the Customer ID.
> What I am thinking about...in the near future, as our customer base
> grows,
> is that we'll be faced with maintaining lots of Publications on the
> server -
> one for each Customer ID.
> So, would it be better to have a single publication that has dynamic
> filters...based on the HostName property...which I'd set in code on the
> device (based on the Customer ID) or keep things the way we have them now?
> I am mostly concerned about replication/synchronization performance
> between
> the server and the mobile devices...some of which use GPRS for
> connectivity.
> I don't want to sacrifce any performance gains, if there are any, by
> having
> lots of publications.
> thanks for any thoughts.
> - will
|||Hi...
Thanks for the quotes from the BOL. However, we cannot move to SQL 2005 for
a while...so these won't help me now and for the near future.
Any other thoughts regarding my question...which is simply, "Is it better to
have one publication that multiple subscribers sync with...using their
Customer ID in the filtering of the data, or is it better to have multiple
publications that are unique to each subscriber?" And, when I ask which is
"better", I mean from a replication/synchronization performance aspect...from
the subscriber's end of the things.
thanks
- will
"Rafik Robeal" wrote:

> Here are the issues that you need to consider when moving to a single
> publisher model:
>
> 1. Concurrency: syncing multiple subscribers at the same time is not
> optimized in SQL2000, which will end up serializing the merge process. In
> SQL2005, performance is greatly improved by allowing multiple subscribers to
> sync at the same time.
>
> 2. Partitioning: When a subscriber with a given ID syncs, SQL2000 will scan
> the publisher's articles for changes pertaining to that subscriber. This
> process has its performance implications for large publication with large
> number of changes. In SQL2005, this issue was also addressed by the use of
> partition groups.
>
> Regards,
> Rafik
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:7730CDFB-81A8-437B-80A5-C5B0131814B9@.microsoft.com...
>
>
|||I have found that static filters perform better than dynamic ones. Obviously
however, there is more management (DBA work) to do.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply and info. That is what I was thinking...that the
performance would be better forindividual publications based on a static
filter...but figured I should toss this question out to the group, in case I
was missing something.
thanks
- will
"Paul Ibison" wrote:

> I have found that static filters perform better than dynamic ones. Obviously
> however, there is more management (DBA work) to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Wednesday, March 21, 2012

Many databases vs. 1 database

Hi,

I am trying to determine what the overhead is per database in SQL
Server 2000 Standard. I have the option to put several customers in one
database, or give each customer their own database. I would like to put
each customer in their own database to simplify maintenance and
strengthen security.

I have found the following document which shows the memory used by
various objects in SQL Server:

http://msdn.microsoft.com/library/d..._ar_ts_8dbn.asp

Based on this info I get the following *additional* memory requirements
per database:

Open Database (1 file, 1 filegroup): 6k
Open Objects (250 objects, 30 indexes): 692k
Total: 698k

Is this an accurate calculation of the overhead? Is there something
else that would affect the overhead that I am overlooking? Are there
any other downsides to having many databases versus a few databases?

Thanks,
MikeHi

Having 100's of databases does slow EM down as it need to list them all.
That is about it.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

<mike@.rumblegroup.com> wrote in message
news:1112124274.309091.156730@.f14g2000cwb.googlegr oups.com...
> Hi,
> I am trying to determine what the overhead is per database in SQL
> Server 2000 Standard. I have the option to put several customers in one
> database, or give each customer their own database. I would like to put
> each customer in their own database to simplify maintenance and
> strengthen security.
> I have found the following document which shows the memory used by
> various objects in SQL Server:
>
http://msdn.microsoft.com/library/d..._ar_ts_8dbn.asp
> Based on this info I get the following *additional* memory requirements
> per database:
> Open Database (1 file, 1 filegroup): 6k
> Open Objects (250 objects, 30 indexes): 692k
> Total: 698k
> Is this an accurate calculation of the overhead? Is there something
> else that would affect the overhead that I am overlooking? Are there
> any other downsides to having many databases versus a few databases?
> Thanks,
> Mike|||You said that you want to have many databases to simplify maintenance.
I'm not sure that I understand that. Especially with regards to change
control. With multiple databases you run the risk of one or more
databases becoming out of sync, either intentionally or
unintentionally. This can turn into a real headache if you aren't very
careful.

Also, will you ever want to research information on your customers as a
whole? You didn't include any information as far as what these
databases actually hold (this would have been useful to know), but
assuming that they hold sales data as an example... if you wanted to
find your total sales across all customers then you would have to
select across many databases. If you got a new customer you would now
have to change any queries that select across these databases to
include the new database.

Good luck,
-Tom.|||(mike@.rumblegroup.com) writes:
> I am trying to determine what the overhead is per database in SQL
> Server 2000 Standard. I have the option to put several customers in one
> database, or give each customer their own database. I would like to put
> each customer in their own database to simplify maintenance and
> strengthen security.

Putting all customers in the same database may be a good idea if
the customers does not access the data themselves.

But since you say "security", I assume that the customers will access
the databases.

One can handle security for customers in a shared database, so that
they only see their own data, but:

o If there is a slip somewhere, a customer can by mistake get access
to someone else's data.
o Even if correctly implemented, "Row-level security" is not waterproof,
since the views that typically implement such scheme can be provoked
to leak information.

And if the overhead of many databases are your only concern, there is
no reason for doubt. As Mike said, the overhead is negligible. You
will have to automate backups and all that, but that is not a major
issue.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> You said that you want to have many databases to simplify maintenance.
> I'm not sure that I understand that. Especially with regards to change
> control. With multiple databases you run the risk of one or more
> databases becoming out of sync, either intentionally or
> unintentionally. This can turn into a real headache if you aren't very
> careful.

Version control and a automated way of propagating changes.

Note also that this cuts both ways. If you have one single database and
you want to upgrade from version 1.2 to 1.3 and big-whiz says no? Or what
of big-whiz wants special features that are useless to most other
customers? With one big database, how do you beta-test? And what if
you find that the server does not cut it anymore, and you want to
scale out? Move a bunch to another server, easy as a piece of cake
with multiple databases. The monolith is more difficult to deal with.

> Also, will you ever want to research information on your customers as a
> whole? You didn't include any information as far as what these
> databases actually hold (this would have been useful to know), but
> assuming that they hold sales data as an example... if you wanted to
> find your total sales across all customers then you would have to
> select across many databases. If you got a new customer you would now
> have to change any queries that select across these databases to
> include the new database.

Views with a whole bunch of unions can easily be build dynamically on
demand.

But I the most decisive factor in this question is security. A multi-
customer database is more or less destined to leak data among customers.
Whether this is acceptable or completely unacceptable could be different
from business to business case.

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

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

Many data entry tables - which ones hold records?

We have an asp.net app with about 200 data entry forms. Customers may
enter data into any number of forms. Each form's data is persisted in
a corresponding sql table. When data entry is complete, it needs to be
processed. Here's where the questions start.

How can we easily determine in which tables a customer has data and how
best to select that data?

We're not opposed to putting all the data in a single table. This
table would wind up having ~15 million records and constantly have CRUD
operations performed against it by up to 5000 users simultaneously.
With sufficient hardware, is this too much to ask of the db?heromull (heromull@.gmail.com) writes:
> We have an asp.net app with about 200 data entry forms. Customers may
> enter data into any number of forms. Each form's data is persisted in
> a corresponding sql table. When data entry is complete, it needs to be
> processed. Here's where the questions start.
> How can we easily determine in which tables a customer has data and how
> best to select that data?
> We're not opposed to putting all the data in a single table. This
> table would wind up having ~15 million records and constantly have CRUD
> operations performed against it by up to 5000 users simultaneously.
> With sufficient hardware, is this too much to ask of the db?

Whether the 200 tables should be 1, 10, 74, or 200 is impossible to tell
from without knowledge about what's in them.

But from a performance point of view, it would not really matter whether
it's one or two hundred tables. Provided, that is, the the single table
has proper indexing.

There are a couple a ways of finding to find data to process:

1) Timestamp column. A timestamp column is automatically updated by SQL
Server with a database-unique value that is monotonically increasing.
(Binary, completely unrelated to date and time). The process that looks
for data would keep track of the most recent timestamp per table, and
retrieve the rows with higher timestamp value. If the process updates the
rows itself, it needs to combine the lookup with a status column. The
drawback with this solution is that the timestamp column must be indexed,
and since it's updated each time the row is updated, there will be a lot
of shuffling around in that index.

2) IDENTITY column. All tables would have an identity column, and then
the process would keep track of the most recently processed value. With
this solution you can only handle inserts, not if users update existing
data.

3) Having triggers on the that enters data about rows to process into a
table. Again, you may need a mechanism to differentiate between user-entered
changes and changes from your processing.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, the data to select for processing is not time based, it's based
on whatever tables a customer has data in at the time of processing,
which is determined by the customer. So, option three would be the
best and is similar to our current process. Also, the data is not
updated by the processing, only selected. Even with a table that tells
us which tables have data, how would we build the select statement(s)
to only include those tables with data?|||heromull (heromull@.gmail.com) writes:
> Thanks, the data to select for processing is not time based, it's based
> on whatever tables a customer has data in at the time of processing,
> which is determined by the customer. So, option three would be the
> best and is similar to our current process.

Not that see what time has to do with it. I did say timestamp, but
the timestamp datatype has nothing to do with time. Then again, if you
already have a process similar to option three, then go with that.

> Also, the data is not updated by the processing, only selected. Even
> with a table that tells us which tables have data, how would we build
> the select statement(s) to only include those tables with data?

I can not say that. I don't know your tables. I don't know in which context
this process runs etc. I would assume that since there are 200 tables,
that you would have a stored procedure or a SELECT statement for that
table, as I would assume that all tables would generally have a different
set of columns. If they all have the same schema, then there is a strong
indication of that you should have one single 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|||"heromull" <heromull@.gmail.com> wrote in message
news:1130029056.070783.59510@.g14g2000cwa.googlegro ups.com...
> We have an asp.net app with about 200 data entry forms. Customers may
> enter data into any number of forms. Each form's data is persisted in
> a corresponding sql table. When data entry is complete, it needs to be
> processed. Here's where the questions start.
> How can we easily determine in which tables a customer has data and how
> best to select that data?
> We're not opposed to putting all the data in a single table. This
> table would wind up having ~15 million records and constantly have CRUD
> operations performed against it by up to 5000 users simultaneously.
> With sufficient hardware, is this too much to ask of the db?

Don't design a database schema around a user interface. Design your database
schema around your DATA and BUSINESS RULES, then build a data access layer
that supports the UI. You say "Each form's data is persisted in a
corresponding sql table". This make little sense to me as a description of a
data model. I suspect (admittedly on the basis of too little information)
that with a better design your perceived problems would disappear. The
stataement "We're not opposed to putting all the data in a single table"
also suggests a very arbitrary approach to database design. Is your current
design a normalized one? If so, I don't understand your confusion about "How
can we easily determine in which tables a customer has data". The answer is
presumably that you use the Customer key - whatever that is in your model.

--
David Portas
SQL Server MVP
--|||Erland Sommarskog wrote:
...
> If they all have the same schema, then there is a strong
> indication of that you should have one single table.
...

Yes, this is the case. They share the same schema.

David Portas wrote:
> Don't design a database schema around a user interface.

I think this is our first mistake.

> Design your database
> schema around your DATA and BUSINESS RULES, then build a data access layer
> that supports the UI. You say "Each form's data is persisted in a
> corresponding sql table". This make little sense to me as a description of a
> data model. I suspect (admittedly on the basis of too little information)
> that with a better design your perceived problems would disappear.

I think you're right. It's that "better design" that we're
researching.

> The
> stataement "We're not opposed to putting all the data in a single table"
> also suggests a very arbitrary approach to database design.

It does and I say that only to mean that it's not too late for us to
implement a better design.

> Is your current
> design a normalized one? If so, I don't understand your confusion about "How
> can we easily determine in which tables a customer has data". The answer is
> presumably that you use the Customer key - whatever that is in your model.

Here's DDL that describes our current "model" (I hear you laughing
already). Notice that all 200 talbes have the same schema. If we
determine a single table approach would be a better design, what would
be some areas of concern? Obviously indexing is on the list.

CREATE TABLE [Customer] (
[CustomerId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (100),
CONSTRAINT [PK_Customer] PRIMARY KEY NONCLUSTERED
(
[CustomerId]
)
)
--we have ~200 tables with this table's schema
CREATE TABLE [Table1] ( --Table1 thru Table200
[Table1Id] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerId] [int] NOT NULL ,
[Value] [nchar] (1024),
CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
[Table1Id]
) ,
CONSTRAINT [FK_Table1_Customer] FOREIGN KEY
(
[CustomerId]
) REFERENCES [Customer] (
[CustomerId]
)
)
GO
--each of the 200 tables have a trigger
--similar to this (i'll omit the delete trigger from the DDL)
create trigger
Table1CreateInterview
on
Table1
for insert
as
begin
declare @.CustomerId int
declare @.RecordId int
select
@.RecordId = Table1Id,
@.CustomerId = CustomerId
from
inserted

insert into
interview
(
CustomerId,

TableId,--represents the table caused the trigger eg. 1=Table1
RecId--the value of the primary key of the record causing the trigger
)
values
(
@.CustomerId,
1,
@.RecordId
)
end
GO
--a record is inserted into this table each
--time a record is written to any of the 200 tables
--a record is deleted from this table each time
--a record is deleted from any of the 200 tables
CREATE TABLE [Interview] (
[InterviewId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerId] [int] NULL ,
[TableId] [int] NOT NULL ,
[RecId] [int] NULL
CONSTRAINT [PK_Interview] PRIMARY KEY NONCLUSTERED
(
[InterviewId]
),
CONSTRAINT [FK_Interview_Customer] FOREIGN KEY
(
[CustomerId]
) REFERENCES [Customer] (
[CustomerId]
)
)
GO
insert into customer(name) values ('some name')
insert into table1(customerid, value) values (1, 'my value in table1')|||heromull (heromull@.gmail.com) writes:
>> Don't design a database schema around a user interface.
> I think this is our first mistake.

An unusually candid confession!

>> If they all have the same schema, then there is a strong
>> indication of that you should have one single table.
> ...
> Yes, this is the case. They share the same schema.

You should make them one table, adding one more column that specifies
the entity that is now hidden in the table name. That table would look
like:

CREATE TABLE [Answers] ( --Table1 thru Table200
[CustomerId] [int] NOT NULL ,
TableNo int NOT NULL,
RowNo smallint NOT NULL,
[Value] [nchar] (1024),
PRIMARY KEY (CustomerID, TableNo, RowNo)

I've added RowNo, beause I don't know if one customer can add more
than one value in the same form. If he can't RowNo should not be
there. I've very deliberate taken out the IDENTITY column, because
this table should have a composite key. My RowNo is indeed a surrogate,
but only in the sub-realm of CustomerId, TableNo. (And TableNo is just
a name I use, in lack of knowledge about the business domain.)

I put the key on CustomerID, TableNo, but depending how you use the
table, you may also have a need for an index on (TableNo, CustomerID).
CustomerID first is good for queries like "what is customer 1233 up to"?,
but not for "What do we have in table 12?".

> It does and I say that only to mean that it's not too late for us to
> implement a better design.

That's great to hear!

> begin
> declare @.CustomerId int
> declare @.RecordId int
> select
> @.RecordId = Table1Id,
> @.CustomerId = CustomerId
> from
> inserted

Uh-uh, classic mistake. A trigger fires once per statement, not once
per row. Yes, as long as data through that form, it will only come
one by one, but then suddenly there is a batch processing loading lots
of data at the same time. So write your trigger as:

INSERT interview (...)
SELECT ...
FROM inserted

> insert into
> interview

And INSERT without a column list is very poor practice in production code.
Someone adds a column to the table, and the statement blows up. That's
bad.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> An unusually candid confession!

Acceptance is the first step right?

Anyway, thank you so much for the support! I think the suggestions are
going to help us out a lot. A lightbulb went off over my head when I
read your composit key explaination.|||Thank you so much for the support! I think this will work for us. My
only other question would be about managing the surrogate column
(RowNo). During Inserts, is there a way to manage it within SQL Server
or would my client apps need to select max(RowNo) and increase it by 1?

Again, thanks!|||On 26 Oct 2005 07:55:17 -0700, heromull wrote:

>Thank you so much for the support! I think this will work for us. My
>only other question would be about managing the surrogate column
>(RowNo). During Inserts, is there a way to manage it within SQL Server
>or would my client apps need to select max(RowNo) and increase it by 1?

Hi heromull,

A typical INSERT statement would roughly look like this:

INSERT INTO Answers (CustomerId, TableNo, RowNo, [Value])
SELECT @.CustomerId, @.TableNo, COALESCE(MAX(RowNo),0) + 1, @.Value
FROM Answers
WHERE CustomerId = @.CustomerId
AND TableNo = @.TableNo

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks! Works perfectly.

Saturday, February 25, 2012

Managing Customer Email Communications via SQL Server

What is the best way for an internet business to manage its email

correspondence with all it customers, given that all customer data

including their email addresses is stored in a SQL Server 2005 table.

Is there a good way for Outlook (for example) to keep an address book

synchronized with a field in the database?

You can connect to sqlserver from outlook via VBA. So, it's just a matter of query the data and update outlook.

Here is outlook vba reference.

http://www.microsoft.com/downloads/details.aspx?familyid=A1CEAD80-23E2-456F-8618-4DA50CC2C38C&displaylang=en