MAPI xp_sendmail vulnerabilities...?

What are risks of 1) granting exec on master..xp_sendmail to a sql user acco
unt (web application front end) 2) running mapi protocol on the db server i
n order to use xp_sendmail? Will limiting to outbound mail only provide need
ed security?
Thanks in advance,
ChrisYou "may" be vulnerable to any security holes in the MAPI client as sending
mail invokes the client process in memory. To confirm this, monitor the run
ning processes then send mail. If using Outlook as the MAPI client you will
notice outlook.exe starts
as a process. If using an Exchange mailbox, Outlook will (by default) exami
ne the mail headers of the incoming mail (you may need to block incoming mai
l in Exchange or at the gateway to prevent unsolicited emails).
If using xp_sendmail and/or the SQL Agent mail then I suggest you keep your MAPI client
software patched. If you only need to send mail from an extended stored procedure you
may wish to consider xp_smtp_sendmail (www.sqldev.net) which has a smaller
attac
k area (doesn't use a MAPI client like Outlook).

many tempdb locks?

hi all,

we have a SQL2000SP3 runing in W2K3. The application is JDEdwards.

recently I've observed that once in a while (about a few hours), there
would be a process from the ERP application that holds quite a number
of extent locks in tempdb, can be as high as 10000 locks. when I run
sp_lock on that spid, it gives something like this:

697200EXT1:156760 XGRANT
697200EXT1:94896 XGRANT
697200EXT1:132224 XGRANT
697200EXT1:140488 XGRANT
697200EXT1:181552 XGRANT
697800DB SGRANT
697200EXT1:165280 XGRANT
697200EXT1:127888 XGRANT
697200EXT1:173544 XGRANT
697200EXT1:152624 XGRANT
697200EXT1:160888 XGRANT
697200EXT1:144616 XGRANT
697200EXT1:198336 XGRANT
697200EXT1:107296 XGRANT
697200EXT1:99176 XGRANT
697200EXT1:169344 XGRANT
697200EXT1:115704 XGRANT

I am wondering what action is it doing, creating temp tables?? many
thanks.New MSSQL DBA (boscong88@.gmail.com) writes:
> we have a SQL2000SP3 runing in W2K3. The application is JDEdwards.
> recently I've observed that once in a while (about a few hours), there
> would be a process from the ERP application that holds quite a number
> of extent locks in tempdb, can be as high as 10000 locks. when I run
> sp_lock on that spid, it gives something like this:
> 697 2 0 0 EXT 1:156760 X GRANT
> 697 2 0 0 EXT 1:94896 X GRANT
> 697 2 0 0 EXT 1:132224 X GRANT
> 697 2 0 0 EXT 1:140488 X GRANT
> 697 2 0 0 EXT 1:181552 X GRANT
> 697 8 0 0 DB S GRANT
> 697 2 0 0 EXT 1:165280 X GRANT
> 697 2 0 0 EXT 1:127888 X GRANT
> 697 2 0 0 EXT 1:173544 X GRANT
> 697 2 0 0 EXT 1:152624 X GRANT
> 697 2 0 0 EXT 1:160888 X GRANT
> 697 2 0 0 EXT 1:144616 X GRANT
> 697 2 0 0 EXT 1:198336 X GRANT
> 697 2 0 0 EXT 1:107296 X GRANT
> 697 2 0 0 EXT 1:99176 X GRANT
> 697 2 0 0 EXT 1:169344 X GRANT
> 697 2 0 0 EXT 1:115704 X GRANT
>
> I am wondering what action is it doing, creating temp tables?? many
> thanks.

Yes, that is likely to be locks for allocating space for temp tables.
It could also be overflow space for table variables, internal work
tables for sort etc.

Since the locks linger, this happens within a non-committed transaction.
It could be because it's a single query, or a long user-defined transaction
that accumulates data.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Go to this link and follow the instructions provided. It has proven
INVALUABLE to me!

http://support.microsoft.com/defaul...kb;en-us;328551

Must have at least .818 patch installed.
Must add the -T1118 startup parameter.
Split the tempdb data file into equal sized files and uncheck the
'autogrow' function.sql

Many databases on one or multiple SQL2000 servers

Hi there. We currently have an application which requires separate database
s
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times!
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2G
B
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:

> Hi there. We currently have an application which requires separate databa
ses
> for each "project" we work on. We are in the process of replacing our 2 S
QL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previousl
y,
> we implemented with 2 servers due to performance reasons. That was back o
n
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times!
> So, do I get 2 servers or a single (larger in processor and memory) server
?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
[vbcol=seagreen]
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users wi
th
> an acceptable level of performance. The hardware is a 4-way server with 6
GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via
2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in su
ch
> an implementation. If you can buy a decent server with 6GB or so of RAM a
nd
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this i
s
> just my "professional" opinion based on the limited information you gave u
s.
> Bryan
>
> "John J. Berlo" wrote:
>

Many databases on one or multiple SQL2000 servers

Hi there. We currently have an application which requires separate databases
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times! :)
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:
> Hi there. We currently have an application which requires separate databases
> for each "project" we work on. We are in the process of replacing our 2 SQL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> we implemented with 2 servers due to performance reasons. That was back on
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times! :)
> So, do I get 2 servers or a single (larger in processor and memory) server?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users with
> an acceptable level of performance. The hardware is a 4-way server with 6GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in such
> an implementation. If you can buy a decent server with 6GB or so of RAM and
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this is
> just my "professional" opinion based on the limited information you gave us.
> Bryan
>
> "John J. Berlo" wrote:
> > Hi there. We currently have an application which requires separate databases
> > for each "project" we work on. We are in the process of replacing our 2 SQL
> > 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> > we implemented with 2 servers due to performance reasons. That was back on
> > SQL 7 though.
> >
> > We have approximately 400 databases which range in size from .5GB to about
> > 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> > option at this time (gotta love software companies that don't keep up with
> > the times! :)
> >
> > So, do I get 2 servers or a single (larger in processor and memory) server?
> >
> > --John J. Berlo
> > Lend Lease Corportation
> > Collaboration Technologies

Managing reporting services models

Hi everybody,

I have the following scenario: I have web application which is creting new SQL Server database each time when new customer is created in the application.

I would like to give users the possibility of creating ad hoc reports and thus I need to create new connection and report model (and deploy them to my reoport server) each time when the new database is created (report models can not use multiple databases). Does anybody knows how can I do that?

Maybe there is another approach to this kind of problem?

Thank you in advance,

Marek

You can create datasources and autogenerate models from these datasources using the SSRS SOAP API. Check out this article in BOL for information on how to get started using the SOAP API:

http://msdn2.microsoft.com/en-us/library/ms159162.aspx

Managing memory usage of MSDE

I am getting ready to deploy a manufacturing application that uses a SQL2K
MSDE database. It is not a very large database but it gets updates every few
seconds, so it is very active.
When I first start the application, the total memory allocated to SQL (as
seen in task manager) is about 6M. this is before any transactions stat with
the application.
Once the transactions start, the memory usage grows consistantly. After
about 12hours of operation, the usage is over 600M (per Task Manager).
My question is, is this normal? Can it be controlled? If so, how? If not,
what options do I have to manage this?
It becomes a problem when the PC running the application must start caching
resources in order to maintain SQL performance.
Thanks in advance for your help...
hi rick,
"rick" <rick@.discussions.microsoft.com> ha scritto nel messaggio
news:E22FF67D-E9C5-4537-BC6B-DD18B697EF84@.microsoft.com
> I am getting ready to deploy a manufacturing application that uses a
> SQL2K MSDE database. It is not a very large database but it gets
> updates every few seconds, so it is very active.
> When I first start the application, the total memory allocated to SQL
> (as seen in task manager) is about 6M. this is before any
> transactions stat with the application.
> Once the transactions start, the memory usage grows consistantly.
> After about 12hours of operation, the usage is over 600M (per Task
> Manager).
> My question is, is this normal? Can it be controlled? If so, how? If
> not, what options do I have to manage this?
> It becomes a problem when the PC running the application must start
> caching resources in order to maintain SQL performance.
> Thanks in advance for your help...
please have a look at http://tinyurl.com/5qrjf about memory requirements,
and please keep in mind SQL Server works at best when it's running, alone,
on a database server, physically separated from an application server, print
server and so on..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" wrote:

> hi rick,
> "rick" <rick@.discussions.microsoft.com> ha scritto nel messaggio
> news:E22FF67D-E9C5-4537-BC6B-DD18B697EF84@.microsoft.com
> please have a look at http://tinyurl.com/5qrjf about memory requirements,
> and please keep in mind SQL Server works at best when it's running, alone,
> on a database server, physically separated from an application server, print
> server and so on..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
Thanks for the reply, and I know that this is typically the case, but for
this application's requirements and the infrastructure that I have built,
keeping the MSDE on the same box as the application is critical. The MSDE is
only used for local storage as a buffer area for data replication to the
enterprise server and only when the Enterprise server requests the
information (based on network traffic, data popularity, and other metrics).
I found the stored procedure 'sp_configure' and the 'max server memory'
parameter and applied it according to the needs of the system. Efficiency may
be degraded slightly compared to a system that is stacked full of RAM, but
for the type of machine that this is running on, limitting the server memory
usage does not hurt performance as much as dik caching does.
Thanks for the reply though...

Managing Concurrency

I want to centralize my previous standalone application. Previous application was using VB.NET and Access XP. Now I want to keep a centralized database (SQL Server 2005) and VB.NET 2005. At this point of time thousands of concurrent users will connect to the database at the same time.

In my application, when a ticket is being issued to a tourist, an SQL query finds the max(ticketno) for the current month from the main table and reserves this number for the current ticket. When the operator has finished entering information and clicks SAVE button, the record is saved to the main table with this ticket no. Previously there was no issue since the database was standalone.

I want to know how to block the new ticket no so that other concurrent users are not using the same number for saving a record. How to better tune the database for thousands of concurrent users at the same time? I also want that the other user must not get an error message when he attempts to save a record. It should be automatically handled by the database.

I would NOT try to lock a process that first gets a ticket number, then waits until someone clicks the 'SAVE' button, and then updates the master table and then unlocks the process.

This will not scale and will be a MAJOR headache.

I recommend that you use an IDENTITY field for the 'ticketno', and let the 'system' take care of the updating/incrementation.

|||Can you please illustrate?|||

Arnie's suggestion is a good one...there doesn't seem to be a strong justification to pull in the complexity of some kind of key management system and there doesn't appear to be a need for the client application to have any knowledge of the ticketno value prior to saving the information. If you need to maintain referential integrity between tables, you can use the @.@.IDENTITY system function to get the last identity value that was entered in the master table. For example:

CREATE TABLE Tickets (TicketID INT IDENTITY, PurchaseDate datetime, CustomerID int) <NOTE: I'm leaving out the key relationship to the identity column on a customer table>

DECLARE @.TicketID AS int

INSERT INTO Tickets (PurchaseDate, CustomerID) VALUES (GETDATE(), @.SomeValue)

SET @.TicketID = @.@.IDENTITY

INSERT INTO Table2 ( @.SomeInfo, @.SomeInfo2, @.TicketID)

|||

Which part, how to define a 'TicketNo' column in a table as INDENTITY?

Look in Books Online, Topics: IDENTITY, CREATE TABLE

OR, the problems with scaleability?

It if is the later, consider that you have a choke point that every user must wait in line to access. And if some user takes a bit more time before clicking the [SAVE] button, well everyone else just has to wait because the number incrementing process is locked in the scenario you described.

|||

I recommend using SCOPE_IDENTITY instead of @.@.IDENTITY.

Under some situations, @.@.IDENTITY may provide inaccurate data.

|||Ticket No in our case is not a sequence number but in this format:

000123-0507-0101

The first part is the max(TicketNo) of current month. The second part contains month and year and the third part contains the code of the place. After each month end, the ticket no starts with 1 again.

Here I also want to know whether taking a string (like above) a primary key affects performance as opposed to a numeric primary key.